I do forecast modeling and scenario planning in Excel and it is quite simple:
- Be clear on your assumptions — typically top left in the worksheet
- Document your formulas by including a comment on input (column M below)
Follow these two points and you can easily update your model, or do a handover to your successor without too much training.
Price and cost per unit are constant throughout the year while volume is varying. I use three cases, which is typical for scenario planning:
In addition I have included a monthly ramp-up percentage.
If your assumptions change (and they will!), you update your assumption in B2-B6 and your whole model will update — both your table, your graph and your scenarios.
This example displays a very simple forecast model, but the same principles applies to more extensive models.