This is a thought experiment. You don’t actually have to do anything. Just THINK about HOW you would do it.
As you can see from the lack of many words below, this is a simple challenge!
You’re creating a Financial Model for a group that has 400 operating units. Each has a profit and loss forecast on an Excel spreadsheet. The model covers 12 months from the Current Month.[Image]
At the group level, the model is an aggregation of these 400 operating units.
The operating units have to provide 2 scenarios in addition to the standard forecast – optimistic and pessimistic.
The scenarios are specific to the locality, the local business conditions, and the business the operating unit is in. So the sensitivity analyses is not (for example) a ‘blanket 20% on sales’ that applies across the group.
So, they will effectively be three sets of forecasts for each operating unit.
Each month the forecast model will need to be Rolled Forward one month. That is, it will go from the new Current Month to 12 months forward.
How would you set this up?
When you have thought about this, and decided how you will proceed, you can try Part 2. [TBA]