You need to identify the problem before beginning the model-building process.
For example, if the model you’re building is for the purpose of making a decision, you need to build at least two scenarios — one with the existing business and one including the new venture — as well as a comparison between them. Modelers sometimes call this a “do nothing” versus as “do something” scenario. So the model will consist of three components:- “Do nothing” scenario
- “Do something” scenario
- Scenario comparison
You can download a sample copy of this model in File 0302.xlsx.
You start building this model by creating the three tabs and determining that the comparison sheet should contain a comparison between the two scenarios. Then you design the “do nothing” scenario and then look at how different the numbers are if the company adds a new bus line.
Keeping models consistent is important. For this reason, the “do nothing” scenario contains an extra blank row in each block of data, which is where the new western route can be inserted. The Total Profit line is shown in row 27 of both scenario pages, which makes the model easier to follow, and less prone to error when linking the charts and summary page to the outputs.
Because this model is quite small, you don’t need separate input and assumptions sheets, as you do with larger models. The inputs and assumptions are listed within the scenario sheets themselves.If the company decides to put on a new route to service the western regions, you can replace some of the lost sales from the northern routes with the new service. In the “do something” scenario, you can see that although not all the lost profitability has been recouped, the bus company is still viable.
When using a financial model to make a decision, you need to look at the difference between the two scenarios. If you only look at the “do something” scenario in isolation, it doesn’t look particularly appealing.
This image compares the two scenarios to help decide on a course of action. You can see from the comparison sheet that the best course of action would be to “do something” — assuming, of course, that these are the only options available to us.