Best Practice Financial Modeling

In a previous article I looked at the need for financial plans for a new business and the benefits of a financial model. At Pivotal180, we strongly believe building a financial model for your business provides unparalleled value in financial planning.

In this article I am going to look more specifically at what a financial model is and why it is such a valuable asset in financial planning.

At a high level, a financial model is a tool that businesses use to predict likely financial outcomes based on a set of defined goals and assumptions.

At the practical level, a financial model is a spreadsheet, typically built in Microsoft Excel, that forecasts a business’s financial performance. The model uses a set of agreed on assumptions to calculate revenue, operating expenses, inventory, capital expenditure, depreciation, and to prepare 3-way financial statements. The three key financial statements are the income statement, balance sheet and cashflow.

Once these basic functions are built, the model can also provide more advanced capabilities. These include more detailed calculations such as discounted cashflows, and terminal values or detailed analysis using sensitivities and scenarios, data tables, model optimization and goal seek.

The beauty of preparing a financial model is that it allows you to stress test the robustness of the business’s financial performance by seeing the outcomes that occur when you change the revenue, prices, costs, and sales. Financial models also give you the capability to understand key drivers of the business and important relationships between different variables and assumptions through the building of sensitivities and scenarios.

The objective of financial modeling is to combine accounting, finance, and business metrics to create a forecast of a company’s future results.

Best Practice Financial Modeling

Now that we have introduced the basics of what a financial model is, let’s discuss the key principles of best practice financial modeling.

At Pivotal180, we have defined three characteristics of best practice financial models:

1. Clear and concise
2. Simple and transparent
3. Robust and flexible

A clear and concise model is one that is well presented and includes all the relevant inputs but omits irrelevant or unimportant information or calculations. A simple and transparent model means that it’s easy to both use and understand; some highly detailed models may be accurate but if the user cannot easily manipulate the inputs and understand the results it will have limited impact. Additionally, a model built in a simple and transparent structure makes it much easier for creators and auditors to identify and correct mistakes. A robust and flexible model will have accurate results with the key information required for decision making and will also enable the user to effortlessly make necessary changes.

You can learn more about Pivotal180’s financial modeling best practice guiding principles in a previous blog by Haydn and Alison.

Financial Modeling Skills

Best practice financial modeling does require developing certain skills and an understanding of “dos” and “don’ts.”

The first skill you need to learn is financial modeling coding and some tips and tricks including excel shortcuts. These shortcuts and the basics of financial modelling coding are taught in each of our financial modelling courses.

Once you have these skills you and start to set up a model including input and calculation sheets. The inputs, calculations, and outputs are all separated by using different tabs within the Excel spreadsheet. This ensures that the inputs are only entered once and all the assumptions and data are entered in one place within the model. We also use this tab for our sensitivity cases. The calculations, which are in a separate tab, are the engine room of a model, and this is where all the formulas are stored. The third tab is the outputs tab. This is where the results of the financial model are found, such as financial statements, ratios, graphs, and sensitivity tables. This use of separate tabs assists the user to navigate throughout the model and to understand where to make changes to the inputs, amend the calculations, or to analyze the outputs.

Analytical tools

When we build a financial model, we are not just interested in showing a single cash flow projection. One of the greatest advantages of Excel is how readily we can modify the inputs and see how a change in certain variables would impact the project cash flow or the IRR or the ability of the project to repay debt. We can quickly and efficiently evaluate what would happen if we selected different equipment or if we offer a different pricing structure under the off-take agreement. The challenge with running these kinds of scenarios is that inexperienced modelers either create a new model for each different scenario or they manually update their base case model each time they want to run a new scenario with different assumptions. Sensitivities and scenarios are powerful tools for this type of analysis and will be a skill addressed in our new introduction to financial modeling course

The best practice that we would recommend is to create what is called a scenario manager and data tables Something extraordinarily handy about Scenario Manager is that you do not need to look at a single scenario which is currently active and live within the model, and you do not have to manually cycle through each case one by one. You can see the results of all the scenarios in one place by using a data table

A financial model is an incredible tool in analyzing and understanding  the key drivers of a business Even with a best practice  financial model, you may not be able to forecast every line item perfectly, but you will understand the relationship between the key elements of the business and which aspects you need to focus on  A financial model helps you  to  understand your cash flow and identify risks or periods when you may run low on cash, and will be able to create a strategy to sustainably fund your business

The tools available in a financial model provide a clear and transparent demonstration of the value and risks of your business, which are critical to understand not only for founders, but also lenders or investors from whom you may seek funding

These benefits also form the basis of our next online course which focuses on financial modeling for businesses.