Hi everyone,
I have a new job as a Financial Analyst and was given a new project and am not sure how to proceed. I need to come up with ideas on how to track the progress of new products that my company will be producing and need to set up a model. My company uses a process called Stage Gate that is a business model for new products, and essentially goals have to be met for the product to pass through each stage or phase. If the product passes through all of the stages (there are 5 total stages or gates), we can then produce the product. I need to track a few things that pass through all 5 stages/gates. The Product Manager has to forecast for the product a list of things and then we need to compare them to the actuals after the product has launched. Below an example of the forecast vs actuals that I need to track. Thanks in advance for your help.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Forecast[/TD]
[TD]Forecast[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gate 4[/TD]
[TD]Gate 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12 month unit forecast[/TD]
[TD]20,000[/TD]
[TD]18,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Est. Gross Revenue 12 month[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"] 1,400,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"] 1,260,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Est. Gross Margin 12 month[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]35%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]32%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Costs per unit[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]35.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]36.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]COGS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]700,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]648,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Sales Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]70.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]70.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Est. Launch Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]3/1/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]4/1/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30 day[/TD]
[TD]60 day[/TD]
[TD]90 day[/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]12 month Units Forecast[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5,000[/TD]
[TD]3,000[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Est. Gross Revenue 12 month[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"] 350,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"] 210,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"] 70,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Est. Gross Margin 12 month[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]32%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]32%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]32%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Costs per unit[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]36.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]36.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]36.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]COGS[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]180,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]108,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]36,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Sales Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]70.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]70.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]70.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have a new job as a Financial Analyst and was given a new project and am not sure how to proceed. I need to come up with ideas on how to track the progress of new products that my company will be producing and need to set up a model. My company uses a process called Stage Gate that is a business model for new products, and essentially goals have to be met for the product to pass through each stage or phase. If the product passes through all of the stages (there are 5 total stages or gates), we can then produce the product. I need to track a few things that pass through all 5 stages/gates. The Product Manager has to forecast for the product a list of things and then we need to compare them to the actuals after the product has launched. Below an example of the forecast vs actuals that I need to track. Thanks in advance for your help.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Forecast[/TD]
[TD]Forecast[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gate 4[/TD]
[TD]Gate 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12 month unit forecast[/TD]
[TD]20,000[/TD]
[TD]18,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Est. Gross Revenue 12 month[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"] 1,400,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"] 1,260,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Est. Gross Margin 12 month[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]35%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]32%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Costs per unit[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]35.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]36.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]COGS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]700,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]648,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Sales Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]70.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]70.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Est. Launch Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]3/1/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]4/1/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[TD]Actuals[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30 day[/TD]
[TD]60 day[/TD]
[TD]90 day[/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]12 month Units Forecast[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5,000[/TD]
[TD]3,000[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Est. Gross Revenue 12 month[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"] 350,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"] 210,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"] 70,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Est. Gross Margin 12 month[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]32%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]32%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]32%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Costs per unit[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]36.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]36.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]36.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]COGS[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]180,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81, align: right"]108,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]36,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl65, width: 158"]Sales Price[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]70.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]70.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84, align: right"]70.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]