- Design the high-level structure.
You won’t know exactly what the layout of the model will be until you actually start building the calculations, but you should have some idea of the tabs. Start by assembling the data you have so far into the broad categories.
- Design outputs — summaries, charts, and reports.
Because you already know the problem that your financial model needs to solve, you should have an idea of what that answer might look like. For example, if you’re making a decision about investing in a new product, the output might be the resulting cash flow and a net present value (NPV). By thinking about the output of your model early on in the process, you’ll be more focused and will ensure that all your calculations work toward the desired end result.
- Design inputs.
Set up where the inputs and source data will go. Even if you don’t have all the information yet, set it up so that it can be dropped in at a later date. This can help you make sure that you ask for or gather the data in the right format, as well as design the model correctly. For example, do you need data for the calendar year or the financial year? Will you need to use the same assumption for every month/year or it is going to change?
- Start calculating.
Start with a tab labeled “workings” or “calculations,” but keep in mind that this will probably expand as the model grows. Link the formulas to your inputs, but break larger problems into smaller ones and don’t try to attempt too much at once. You might begin by thinking that all expenses can go on one tab, but if depreciation, for example, begins getting rather complicated, you might decide that depreciation needs a tab of its own.
- Connect outputs.
Link your calculations to the outputs page. Charts are a great way of visualizing and presenting the output of your model. As you’re building the outputs, test at every stage to make sure that the model makes sense, and adjust as necessary.
- Add scenarios.
When the model is working correctly, you can add sensitivities and scenario analysis. If you’ve designed the model well in the first place, adding scenarios is a fairly straightforward process.
- Assumptions documentation.
Most of these steps are sequential, but assumptions documentation should never be left right to the end. Do it as you go!