Model for New Product Tracker

R

RBburn

New Member
Joined
Oct 21, 2017
Messages
2
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]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

T
  • Question Question
Linear Extrapolation / Forecast for upcoming months
Replies
0
Views
586
Trepidatioussaurus
T
H
  • Question Question
IF drop down Question - Adding launch month & year to product forecast
Replies
0
Views
567
HarrWi
H
R
  • Question Question
Pivot Table - Forecast at completion based on actuals to date
Replies
0
Views
2K
ramgouda
R
C
  • Question Question
Need Help Editing Another Person's old Macro
Replies
3
Views
493
crinaldi90
C
R
  • Question Question
vlookup help
Replies
0
Views
1K
Roosterfosho
R

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top