Changing Calendar -- Too complicated for me!

[kpigden]

Hi there,

I have a complicated excel problem, which I want to solve with formulas if possible. Basically, it's a forecasting tool that changes based on different launch dates.

For example: Launch Date is April 2013, and the quantity in that month is a percentage of the total market (say 50).

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In year 2, we have an expected growth rate of 0.2% (no matter what the launch date is).

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In October 2014, there is a new contract that we expect to achieve that gives another % market share (say another 20 units). If we were to have launched in September 2014, we would still get that contract, but if launch is October 2014 and later, we won't get that contract.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]71[/TD]
[TD]71[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]71[/TD]
[TD]71[/TD]
[TD]71[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to do this? I'd want the formulas in the calendar itself, but the forecast amounts will be in separate cells (ie. the 50 original units, the growth rate, and any contract amounts and start dates).

Thank you very much in advance for any help!

 

[ca.moazzam]

Some more explanation required.

 

[kpigden]

Ok, so here are the variables:

Launch date: ____________ (Not fixed)
Market Quantity Per Month: Fixed (Say 70)
Expected Growth: Fixed (0.2% per year)

Not contracted quantity: 71% of Market (50 units)
October 2014 Contract: 29% of Market (20 units)

If April 2013 was the launch month, then this would be the grid:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]51[/TD]
[TD]71[/TD]
[TD]71[/TD]
[TD]71[/TD]
[/TR]
</tbody>[/TABLE]

If April 2014 was the launch month, then this would be the grid:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]70[/TD]
[TD]70[/TD]
[TD]70
[/TD]
[/TR]
</tbody>[/TABLE]

If October 2014 was the launch month, then this would be the grid:
(We would miss the start date of the October 2014 contract date in this case).
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

Does that help at all?