Creating a Financial Model Step by Step
|Posted by financial-modelling.net under Tutorials|
Successfully building a financial model can be a complex process. This article shows you the main steps you should follow.
1a. Planning the project
How you plan time and effort for a financial modelling project depends a lot on how you usually handle your projects. A lot of experts like to use the feature list (of the specification) and assign "person-days" or "person-hours" to each task or sub-task. One person-hour means that one person works for one hour; consequentially, if you have two project team members working on a model for 10 hours each, that equals 20 person-hours.
For this, you would need the model's specification. But at the same time, writing the specification is part of the project plan - it is a chicken/egg situation. Work around this problem by communicating a deadline for the specification, and making clear the rest of the project's plan will be based on that specification. Do not get worried: Over time, you will get a feeling how to approach this.
1b. Specification of the financial model
A solid and thorough specification is one cornerstone for a successful financial modelling process. In practice, you will find this is especially true when working with external clients - much more so if you have been contracted for a fixed flat fee, where every additional hour of work creates more value to the client without making you earn more money.
In fact, when you are working with an external client, you should either include the specification in the contract, or let the contract reference to the specification. It is highly recommended to agree on a final version of the specification to avoid misunderstandings and unpaid extra-work. At the same time, the specification will influence the project plan: The feature wish list determines the amount of time you will need to accomplish your task.
What is often done is to align the specification and the documentation. This will save you time later on in the modelling process (see below). You could structure your specification like this:
- A general description of the scope of the financial model. This should not take up more than one page, if at all. In fact, if you fail to describe the model's scope and purpose in just a few sentences, perhaps you (or your client) should reconsider what this model is supposed to be used for.
- A list of the input variables.
- A description of the assumptions that affect the model structure.
- A definition of the outputs that the model should generate - similar to the description of the model's scope, but in more detail. For example, if the model's purpose is to generate a KPI forecast to banks, simply stating that in the scope description will suffice there, and here, in the output descriptions, you should list in detail which KPIs are supposed to be in the report.
Distinguish between inputs and assumptions
As explained above, your specification should contain detailed assumptions. Some of these will affect the structure of your model - for example the level of granularity in your model. Think of a retailing company that wants to use a financial model to assess its plan of entering a new country: Should sales, cost etc. be forecast on a regional level, or should the model be applied on store level? Decisions like these have a huge impact on the way you construct your financial model and should be done as early as possible, preferably even be part of the specification document. Making models less complex in the process is almost always an annoying, but manageable problem - but making them more complex or detailed at a late stage is a huge problem that you need to avoid.
Other assumptions will be simple input variables that have an influence on your model outputs, but can be changed without changing the model structure, for example the assumed inflation rate. It is very likely that many of these inputs will change while the financial model is being built, but that should not affect the development as such.
When working with external clients, they sometimes may have trouble distinguishing between assumptions that affect the structure and assumptions that do not. Help them understand the difference so that you do not get caught up in pointless discussions about mere input values, while decisions that have a structural impact are postponed or not done properly.
2. Designing and building the financial model
Designing and building the financial model is the actual core phase of the process. If your specification is good and reflects the users' wishes properly, this phase will be much easier.
- The specification should lay out the structure of the model in detail. When creating the sheets, make sure you work with one empty template sheet (which is formatted according to any specifications given) and use that for every other sheet created.
- Build the input sheets first, and make sure you have some data to work with. Ask the model users to provide you with actual data, or if that is not to be sent out, at least virtual, but realistic data.
- Fill the other sheets with life, that is: formulas. Make sure you start with the basic sheets (such as P&L and balance sheet). Include checks as you go along to avoid making mistakes which you would otherwise uncover only much later.
- Stay in touch with the model users at all times, and discuss the financial model regularly. That way you make sure that the it meets the expectations and that everyone "is on the same page" regarding what the model does and what it does not.
3. Testing the financial model
Naming this as a separate phase is more of a habit taken from software developers than a proper reflection of reality - because a lot of the testing will have happened while building the model. Mostly, the testing as such occurs in either of two settings:
- While building the model, you will include checks directly in the forms. Some of these checks will be very generic and will therefore be included early, such as testing a balance sheet's sum of assets vs. sum of equity and liabilities. Other checks will be more model-specific, and the need for them will not be obvious at the beginning - therefore, the inclusion of new checks will happen throughout the model building phase. And whenever one of those checks throws an error, you will naturally instantly look for the cause.
- Some models, but not necessarily all models, will additionally contain complex macros. These are either essential for the model to function (e.g. in cases where input data must be transformed and complex calculations are needed that cannot be done with formulas), or rather optional (such as a sensitivity analysis macro, which would generate an additional output but does not affect the "normal" outputs). These macros might need separate testing, and these methods (especially for code consistency) would relate to typical software testing.
But whether your model contains macros or not, the basic way to test your model subsequent to the building phase is to play with the inputs. The best practice from software testing is that you test every input variable with values that reflect all possibilities, or at least all possibilites that differ substantially. That means, if you want to test for different loan interest rates, rates of 6.0% and 6.1% are not substantially different; but instead, look at your model's output for substantially different loan interest rates such as 0% and 20%. If you have the time, the most thorough way would be to actually go through your list of input variables and try 3 to 5 different values, if your model allows that many input changes without the recalculation times driving you crazy.
4. Documentation of the financial model
Like writing the specification, writing the documentation is one particularly boring part of the process. Yet, it almost always needs to be done. A good documentation is needed to ensure that the model is handled properly, even by users who were not involved in the development process. Ideally, your specification will serve as the basis for the user documentation: The better your specification, the more of it you can "recycle" in the final user guide.
Consider that not all users of the model will be Excel "power users". If you know that could be the case, put some additional care in your documentation - make sure to explain what a macro is before you explain what your macros do, and do the same for model checks before you explain how a user can go error-hunting.
The structure of your documentation could look like this:
- Introduction: The first section of your documentation should explain the scope and the goal of the model. The user should get an idea what to use the model for and what to expect from it.
- Assumptions and Inputs: Following the introduction, you should explain the structural assumptions and the relevant inputs. The user also needs instructions how the inputs can be changed and to
- Macro Handling: It makes sense to include a separate chapter on macros in the model if they are complex and require some user proficiency. Depending on the client this may even get technical with some code explanations. In simpler models with no (or automatic) macros you may drop this section.
- Outputs: One chapter should be dedicated to the output sheets. Here, you can explain what the sheets show and how they should be read. This is also the section where you should include the definitions of the KPIs (key performance indicators).
- Known Issues: Obviously there should not be any issues with your model when it is final. Nonetheless, there may be points you will want to highlight to make sure that "user expectation" does not diverge from reality too far. Typical issues like that could be:
- Extreme elasticity effects: Your model may be accurate for certain input values, like a planned equity ratio anywhere from 5% to 95%, but may deliver wrong results for ratios of 0% or 100%. Known effects like that should be documented. Ideally, you would also explain the reason, e.g. if this is due to a calculatory simplification in order to increase usability of the model.
- Performance issues: Complex models may require a lot of processor power and memory, and recalculations and macros may take their time. Let the user know if this is the case. If applicable, give advice on how to improve the model's performance.
- Compatibility issues: It happens rarely, but in case you know your model has problems with certain versions of Excel (or Office), Windows, locale settings etc., do include that in your documentation.