The foundation of a vital FP&A function comes from their ability to accurately and dynamically model business cases and drivers into Excel. Once the process is established and trust is gained, finance can move to higher levels of sophistication and automation. 

Because Excel is the most prevalent business software, it helps to establish rapport with business stakeholders in their most familiar environment. No matter where you are in your FP&A journey, all roads lead through Excel, which accounts for over 73% of tools used in planning. Providing a refresher on best practices for the experienced and newly initiated alike is worthwhile. 

Figure 1 

For anyone unfamiliar, a financial model is a tool used to reflect the economics of a business scenario that can be used to track, monitor, and predict a company's financial performance. Financial modelling is a critical skill for all finance professionals as it teaches them the business and the critical business cause-and-effect drivers. Financial models are crucial to understanding a company's financial situation and forecasting its future performance.

While the approach and tools might seem intuitive, building a financial model in Excel is not easy, and there are many pitfalls to watch out for. Armed with the proper knowledge and approach, you will drastically increase your odds of building a great model right the first time around.

As Vignesh Dumonceau, CFO at Flex, at the Digital Middle Eastern FP&A Board explained, "Excel-based models can't meet the following needs, deep data mining, cross-industry insights, testing of hypotheses, and examining correlations," but they are the starting point to get to these eventualities.

 Figure 2

By following the steps outlined in this article, you can build a dynamic financial model that can help you understand a company's financial situation and forecast its future performance.

Fundamental Excel Financial Modeling Principles

Organisation basics

The structure of an Excel financial model is critical to making it organised, dynamic, and user-friendly. Segregating each section makes it easier to audit and adjust the model while minimising errors and optimising time. 

Every model should be divided into at least three sections on separate sheets: 

1. Inputs/drivers, 
2. Calculations (projected financial statements), and 
3. Outputs (summary)

You can also incorporate a Cover Page (Tab), including the model's name, a description of the model's intent, the author's contact information, and any applicable disclaimers.

Question:  "Is the source data clear and unmodified? Is the summary clear and impactful? Are the calculations transparent and accurate?"

1. Know your audience.

Adjust the layout, documentation, and navigation to the skills and familiarity of downstream users.

Where possible, use dropdowns and data validation so users can select from a choice of options. Blank inputs = uncontrollable outputs.

Always keep a model as visually appealing as possible to reduce confusion. Use standard formatting practices and row/column labels. Disable gridlines for a polished look.

Question: "Will my teammates and audience understand the purpose of this workbook, be able to follow the logic, and understand what the summary represents?"

2. Define the purpose of your model

Before you start building your financial model, it's essential to understand its purpose. What decision are you trying to make? What inputs do you need to make that decision? By clearly defining the purpose of your model from the outset, you will save yourself a lot of time and effort later on.

By clearly defining the purpose of a model, you will best determine its optimal layout, structure, and end outputs. Part of the process involves ensuring that your model's key stakeholders sign off on your blueprint and process design before starting to build. 

3. Keep it Simple

A common mistake in building financial models is making them too complex too quickly. Remember, your model is only as good as its inputs, so don't try to include too many variables, or you'll risk introducing errors. Keep your model as straightforward as possible while still being able to answer your original question.

The goal should be to limit the complexity of the layout, the formulas, and the number of worksheets. Complexity increases the opportunity to create errors and reduce audibility. Only use one formula per cell, avoid nested Ifs, and strive to make the formulas readable and easy to follow.

Question:  "Is there a simpler way for me to design the model or reach the same results?"

Question:  "Is my formula and workflow process easily understood?"

4. Build Dynamically

Use Dynamic Links

Always use dynamic rather than static links when building a financial model. Dynamic links change and grow with the model instead of breaking and requiring a reset each time. If any of your assumptions change, your model will update automatically. This will save you a lot of time and effort in the long run. Dynamic links and references use logic to return the desired value(s). 

Never connect to a previous cell using the "=" function. 

Excellent Excel models will incorporate Scenario Planning with the associated likelihood of probabilities - Pessimistic, Most likely, Optimistic. 

Never link workbooks

Linking workbooks is a sure way to cause return errors and user frustration. Anytime a linked workbook is moved from its original location or the file path names changes, the original spreadsheet will invalidate all formulas connected to this data. It takes a while to figure out what happened and fix it.

Question:  "Do I enjoy sadism and causing deep mental anguish to others?"

Never hardcode in place of a formula

Data should be entered once, and the other spreadsheet parts should reference that data. Changing a few assumptions should make the spreadsheet dynamically adapt.

Question:  "Is the data entry spreadsheet the only place where raw data is being entered, and are all calculations handled by Excel formulas?"

Design for the long-term.

Workbooks should be designed as engines, whereas data is the fuel. When new data is added, the machine should be able to adapt and summarise the results quickly. Key assumptions such as exchange rates should be dynamic.

Question:  "Will this workbook meet today's needs and require little/if any modification to work tomorrow?"

5. Validate Your Model

Perform Testing

Before your financial model is used to make any decisions, you must test it thoroughly. You can run different scenarios through your model to see how it responds. Try using several different weeks or months of data to ensure that changes in the information will provide accurate results.

Testing a financial model will help you identify any potential errors or unexpected outputs, which will boost confidence in the results. Test the model with the request initiators to ensure functionality matches their expectations.

Question:  "Will the design and contents of this workbook be able to produce accurate results each time?" 

Build-in checks, controls, and alerts throughout each workbook.

Build-in controls that compare the raw data totals to the summary results. Use data validation to alert you to #N/A reference errors. Tie-outs should be built-in to ensure that information isn't lost or omitted and the subtotals always equal the details.

Question:  "Are processes in place to ensure data integrity and accuracy each time?"

Ask for Feedback

Ask other people to review your financial model. The second pair of eyes can catch errors, find improvements, and test the effectiveness of a workbook.

Question:  "Can others use and understand my workbook? Can they follow the flow and reasoning? Can they identify better ways of doing X?"

6. Be Prepared to Adjust

Even the best financial models are only ever an approximation of reality. As such, it's essential to be prepared to adjust your model as new information arises. Remaining flexible and responsive ensures your model will always be as accurate as possible.

Conclusion - Keys to Building a Dynamic Financial Model

Effective and dynamic Excel-based financial models are the foundation of FP&A. While the function continues to grow and evolve, mastering this essential skill and keeping your skills fresh will serve you well throughout your career.