Using Excel spreadsheets to develop a financial model

A financial model can be used to assess a company’s past performance, predict its future performance, and make decisions about things like pricing, investment strategies, and capital structure.

Models can be prepared for different parties such as shareholders, leadership teams, department heads, bankers and potential investors. They can be as simple or as complex as you need them to be, but all good models should be based on sound economic principles and must cover the end user’s requirements.

Despite technology advancements in recent decades, research shows that most financial models are still built using spreadsheets.

Here are the main features of most financial models:

1. Summary table: The summary table is a high-level view of the model output and should be easy to understand. It should be located in a prominent position within the model.
2. Key assumptions and drivers: The key assumptions and drivers section explains the underlying assumptions of the model and how they can be changed to see different results. This is where most of the input data is stored, which then flows through to the rest of the model.
3. Income statement: The income statement shows a company’s revenue and expenses over a certain period of time. It should be broken down into each of the line items that are helpful to the end user.
4. Balance sheet: The balance sheet shows a company’s assets and liabilities at a specific point in time. This should be carefully linked to the model to capture the impact of each financial transaction.
5. Cash flow statement: The cash flow statement measures how much cash is flowing in and out of a company over a certain period of time and will be impacted by many assumptions such as working capital cycles and funding activities.
6. Supporting schedules: The supporting schedules section is where you’ll find more details regarding the data, assumptions and calculations that go into the model. This is important because it allows you to see how the numbers were derived, it provides more detail on specific line items and it also allows you to change any assumptions that you may want to test.
7. Key Performance Indicators: The KPIs section is where you’ll find all the most important metrics for your business. This is vital information that will help you track your progress and make sure that you’re on track to meet your goals.
8. Valuations: The valuations section is where you’ll be able to see what your business is worth. This is important information to have if you’re looking to sell your business or raise capital.
9. Break-even points: The break-even points section is where you’ll find the point at which your business will start to make a profit. This is an important metric to track, because it will help you determine when you can start reinvesting in your business.
10. Sensitivity analysis: The sensitivity analysis section is where you’ll be able to see how sensitive your financial model is to changes in assumptions. This is important because it allows you to stress test your model and ensure that it’s robust.
11. Scenario outcomes: The scenario outcomes section shows the results of different scenarios that have been modelled, such as different interest rates or revenue growth rates.
12. Tables and graphs: Tables and graphs are used to present the data in an easily digestible format and help to illustrate key trends or deviations.
13. Other stakeholder requirements: Any other stakeholder requirements should be included to ensure that the financial model captures all relevant information required by the end user.

Each of these sections plays an important role in the financial model, and each one provides critical information that can help you make sound business decisions.

Summary

A good financial model should be easy to understand and use. It should be clear, concise, and organized. All formulae should be clearly explained, and all assumptions should be clearly stated.

Financial models are only as good as the inputs that are used. The model must be accurate and should produce results that are consistent with real-world data. It should be flexible enough to accommodate different scenarios and help the end user make better business decisions by providing insights into what could happen in future under different circumstances.