Planning and Designing Your Financial Model
lanning and design can sometimes be the most difficult part of building a financial model — especially for those of us whose skills are more technical and numerical than visual. When building your model, you need to pay close
attention to the design layout and ensure that it’s clear, coherent, and logically structured. Even a very simple model can become complex if poorly designed, and a well-designed model will be so logical that it will simply speak for itself.
Before jumping in and starting to build the model, take a moment to think about what your model needs to achieve — this will help with the design build.
Identifying the Problem That Your
Financial Model Needs to Solve
A financial model is usually built in order to answer a question or to solve a problem. For example, the question “Should I purchase this new asset?” could result in a model containing cash flow analysis, which compares the cash flow if
the asset is purchased versus if it is not purchased. “How much should I pay for this new asset?” is an entirely different question, and the answer will be a single number or a range of possible numbers.
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
In the example shown in Figure 3-1, a small bus company has serviced two bus routes for many years. The financial model shows 12 months of historical data and has forecast the next 12 months. Due to a change in demographics and a new train line servicing the area, ticket sales for the northern route have been declining consistently, and the company expects this trend to continue. If the company does nothing, as shown, the profits will more than halve over a two-year period.
FIGURE 3-1:
“Do nothing”
scenario
You can download a sample copy of this model in File 0302.xlsx at www.dummies. com/go/financialmodelinginexcelfd.
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” sce- nario 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 shown in Figure 3-2, you can see that although not all the lost profitability has been recouped, the bus company is still viable.
FIGURE 3-2:
“Do something”
scenario.
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.
Figure 3-3 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.
FIGURE 3-3:
Comparison between scenarios.
