The Six Stages of Creating a Financial Model

Financial models underpin some of an organisation’s most important decisions. Their development can be complex, involve many stakeholders, and take considerable time. They are often serious tools, whose ongoing use and maintenance is critical to senior decision-makers. As such, their development - like any serious project - requires a structured and thorough approach.

Having developed financial models for clients while working within the London-based modelling team of PwC, a Big 4 professional services firm, as well as teaching model development there, we know that adopting such an approach is critical. It gives the model the best chance of successful completion and acceptance by stakeholders.

In this article, we give an overview of the Model Development Framework, created by Claritix, which underpins our course, Excel Modeller. This framework comprises six stages, which we recommend are undertaken in all model developments.

Figure 1: The Claritix Model Development Framework

Scope

Begin by establishing the parameters of the model.

The first stage should be to produce an initial high-level plan of what needs creating. In particular, stakeholders should be identified, and their general requirements sought. The following are examples of just some of the types of questions that should be asked:

• What is the model’s aim? What will it be used for?
• Will it be used on an ongoing or one-off (transactional) basis?
• Who are the stakeholders? Who has an interest in the outputs, who will develop it, and who will operate it?
• What is the timeline length - e.g. 3, 5, or 10 years? And at what granularity - monthly, quarterly, or annually?
• What are the risks associated with the model?
• Where and when will inputs and assumptions come from?

Figure 2: Scoping questions should be broad and varied 

By the end of this stage, you should have a much better understanding of what needs creating and how you might go about it. For simpler model builds, you can proceed to the Design stage. For more complex ones, continue to the Specify stage.

Specify

Create a detailed plan of the model and get it signed off.

Once Scoping is complete, a Specification stage may be needed for complex projects. For simpler models, Scoping alone may suffice, and Specification can be skipped.

The Specification stage involves creating a detailed plan of the financial model and its development that builds on the initial Scope. It is effectively a blueprint of what will be created and can be referred back to during the model’s build. It should be documented and agreed upon by all stakeholders before the design and build of the model take place.

The document should include mock examples of outputs, definitions of any complex calculations, and specify the granularity and format of inputs. A clear, structured approach - such as the extract shown in Figure 3 - demonstrates professionalism and encourages stakeholders to engage seriously with the process.

Figure 3: Formalise the specification into a detailed and professional document 

The benefit of this approach is that it provides a robust way to ascertain and agree stakeholder requirements, particularly if the document is iteratively circulated for feedback. This helps avoid ‘scope creep’ - where stakeholders later ask for additional functionality that has not been included in the model’s design and therefore has to be wrestled in.

💡 Tip: Work backwards when specifying the model! Define outputs, establish the calculations required to create these, and then the inputs needed.

Design

Produce a high-level plan of the approach to the physical build.

Now that you have a strong plan of what needs to be built, resist the urge to jump straight into Excel. Instead, spend a little time planning your approach to the build. For instance, what worksheets will the financial model have and what is their purpose?

Creating a Model Map - such as the one in Figure 4 - is highly recommended. This is a diagram of the model’s worksheets, their purpose, and the flow of data between them. It can also be included within the model’s documentation to give new users a high-level understanding of how the model works.

Figure 4: Create a Model Map to plan the structure and approach of the model 

For complex parts of the model, or areas that are not fully understood, consider creating prototype models to clarify problems and explore solutions. Share these with stakeholders to get their feedback or approval.

Build

Physically create the model using best practices.

With the financial model scoped, specified, and designed, it is now time to physically build it in Excel. Keep referring to the Specification and Design stages as you go, to stay on track. Whilst there is no universally accepted way to create financial models, there are widely accepted best practices. Ensure you employ them wherever possible to aid transparency, usability, and reduce risk.

Our Excel Modeller course comprehensively covers best practices and many modelling techniques for creating: timelines, outputs, calculations, inputs, checks systems, and specialist areas such as Discounted Cash Flow (DCF) analysis and sensitivity analysis. Figure 5 gives an example of a technique covered in the course.

Figure 5: 'Corkscrew calculation' technique used to model opening and closing balances

Here we provide just a few high-level tips to think about when modelling:

• Keep things simple and transparent. As you model, keep asking yourself the question: “Could this be done in a clearer way?”
• Keep formulas short and break calculations into steps to increase transparency and reduce risk.
• Label consistently, clearly, and concisely to aid transparency and usability.
• Adopt a consistent approach to the structure of the model to increase predictability and reduce risk.
• Use dummy figures during development to capture errors in calculation and formatting.
• Create checks - and a checks system - as you go.

💡 Tip: Test formulas as soon as they are created to resolve problems as they occur and to leave an easier task in the next stage.

Test

Check for errors in calculations and commercial logic.

Practically all financial models contain some form of error(s) - particularly those that have just been created - so thoroughly testing the model is essential not only to get correct results, but also to give stakeholders confidence.

Most importantly, have an independent person test the model, so that they are free from your bias.

Begin with high-level testing, such as changing inputs and switches and seeing whether the resulting changes in the outputs are as expected. If the model is already loaded with realistic inputs, sense-check its results and visualise trends.

Perform unique formula review on complex components, or if detailed testing is required. This typically involves using specialist software to produce a formula map - such as the one in Figure 6 - to determine the consistency of formulas in the model. A formula that is created and then copy/pasted to adjacent cells is considered a unique formula. Testing just one of these formulas is sufficient to determine that all are correct, provided they are consistent.

Figure 6: Arixcel formula map and colour legend 

Consider model audit diagnostic tools, such as the Operis Analysis Kit (OAK), if you are working with a large, complex model. In addition to comprehensive formula map functionality, this Excel add-in includes an array of diagnostic tools that examine formulas, complexity, and risk.

We always recommend the Arixcel add-in when using models, as it aids fast navigation and the breakdown of formulas (see our video here). This tool is also excellent for the unique formula review mentioned above and includes more basic formula map functionality - suitable for testing smaller models.

Finally, consider User Acceptance Testing (UAT) to familiarise end users with the model and identify issues with its use. This typically involves providing the tester with a copy of the model and a range of tasks to perform in it. The outcome of each task is compared to an expected result, and any deviations - such as those caused by misunderstanding the model’s use - are investigated and resolved.

Document

Help users understand and operate the model.

A stage that is often overlooked is documenting the financial model. The aim here is to ensure that other users understand the model and can operate it effectively. If you weren’t here tomorrow, could one of your colleagues pick it up and run with it?

Aim to provide a range of types of documentation - generally speaking, the more the better.

At a minimum, provide a User Guide. This should be a thorough but concise manual explaining the model’s purpose, what it produces, and how it’s operated. For short guides, this can potentially be embedded within the Excel file itself, allowing it to always travel with the model - see extract in Figure 7. For more comprehensive guides, create a separate Word document and state the version of the model to which it relates.

Figure 7: User Guide documentation 

Include within the model a Model Map. The one presented in the Design stage was intended for planning and focused on examples of the inputs, calculations, and outputs that needed creating. The example in Figure 8 is produced following completion of the model. It gives precise worksheet names and shows which feed into which. This provides users with a high-level visual understanding of the worksheets involved and the flow of calculations between them.

Figure 8: Model Map documentation 

Consider including a Version Control Log within the model, such as the one in Figure 9. Whilst this does not explain the model’s usage, it acts as a record of how the model has been updated over time, the impacts of those updates, and the version in which each change can be found. This is extremely useful for keeping track of what has happened in the model, explaining its results, and creating bridges between two versions - for example, the impact of each change between them.

Figure 9: Version Control documentation  

💡 Tip: Don’t leave documentation until the end of the model’s development. Instead, create it throughout the process - while issues and explanations are front of mind - to avoid a large and daunting task at the end.

Conclusion: Turning Process into Practice

Creating a robust financial model is about more than just technical skill - it’s about applying a structured approach that reduces risk, improves stakeholder confidence, and delivers real business value. The six-stage Model Development Framework provides a repeatable process that ensures clarity at every step, from scoping through to stakeholder sign-off.

If you're looking to learn how to build financial models in Excel and apply this framework in your own work, our Excel Modeller course covers it in detail. You’ll learn the approach, techniques, and best practices taught and used within Big 4 modelling teams – putting them into action with a practical model build.

Whether you're building your first serious model or refining your current process, this commercially-focused financial modelling course can help you develop models that are structured, reliable, and guaranteed to raise your value within your organisation.

👉 Learn more and start building better financial models today: Excel Modeller