There are all sorts of complicated definitions of financial modelling, and in my experience there is quite a bit of confusion around what a financial model is exactly.
A financial model is a structure (usually built in Excel) to solve real world financial problems mathematically, containing dynamic and flexible inputs and outputs.
A few years ago, we put together a Plum Solutions survey about the attitudes, trends, and uses of financial modelling, asking respondents “What do you think a financial model is?” Participants were asked to put down the first thing that came to mind, without any research or too much thinking about it. I found the responses interesting, amusing, and sometimes rather disturbing.
Extract from Using Excel for Business Analysis, Chapter One
Some answers were overly complicated and highly technical:
- “Representation of behavior/real-world observations through mathematical approach designed to anticipate range of outcomes.”
- “A set of structured calculations, written in a spreadsheet, used to analyse the operational and financial characteristics of a business and/or its activities.”
- “Tool(s) used to set and manage a suite of variable assumptions in order to predict the financial outcomes of an opportunity.”
- “A construct that encodes business rules, assumptions, and calculations enabling information, analysis, and insight to be drawn out and supported by quantitative facts.”
- “A system of spreadsheets and formulas to achieve the level of record keeping and reporting required to be informed, up-to-date, and able to track finances accurately and plan for the future.”
- “A numerical story.”
- “Forecasting wealth by putting money away now/investing.”
- “It is all about putting data into a nice format.”
- “It is just a mega-huge spreadsheet with fancy formulas that are streamlined to make your life easier.”
- “Something to do with money and fashion?”
- “I really have no idea.”
And some downright profound:
- “A complex spreadsheet.”
There are many (often very complicated and long-winded) definitions available from different sources, but I actually prefer the last, very broad, but accurate description: “a complex spreadsheet.” As long as a spreadsheet has inputs and outputs, and is dynamic and flexible, you can call it a model and if it’s for the purpose of financial projections, a financial model.
Pretty much the whole point of financial modeling is that you change the inputs and the outputs. This is the major premise behind scenario and sensitivity analysis—this is what Excel, with its algebraic logic, was made for! Most of the time, a model will contain financial information and serve the purpose of making a financial decision, but not always. Quite often it will contain a full set of financial statements: profit and loss, cash flow, and balance sheet; but not always.
According to the more staid or traditional definitions of financial modeling, the following items would all most certainly be classified as financial models:
- A business case that determines whether or not to go ahead with a project.
- A five-year forecast showing profit and loss, cash flow, and balance sheet.
- Pricing calculations to determine how much to bid for a new tender.
- Investment analysis for a joint venture.
But what about other pieces of analysis that we perform as part of our roles? Can these also be called financial models? What if something does not contain financial information at all? Consider if you were to produce a spreadsheet for the following purposes:
- An actual-versus-budget monthly variance analysis that does not contain scenarios and for which there are no real assumptions listed.
- A risk assessment, where you enter the risk, assign a likelihood to that risk, and calculate the overall risk of the project using probability calculations. This does not contain any financial outputs at all.
- A dashboard report showing a balance scorecard type of metrics reporting like headcount, quality, customer numbers, call volume, and so on. Again, there are few or no financial outputs.
Don’t get hung up on whether you’re actually building something that meets the definition of a financial model or not. As long as you’ve got inputs and outputs that change flexibly and dynamically, you can call it a financial model. If you’re using Excel to any extent whereby you are linking cells together, chances are you’re already building a financial model—whether you realize it or not. The most important thing is that you are building the model (or whatever it’s called!) in a robust way, following the principles of best practice, which this book will teach you.
Generally, a model consists of one or more input variables along with data and formulas that are used to perform calculations, make predictions, or perform any number of solutions to business (or nonbusiness) requirements. By changing the values of the input variables, you can do sensitivity testing and build scenarios to see what happens when the inputs change.
Sometimes managers treat models as though they are able to produce the answer to all business decisions and solve all business problems. Whilst a good model can aid significantly, it’s important to remember that models are only as good as the data they contain, and the answers they produce should not necessarily be taken at face value.
“The reliability of a spreadsheet is essentially the accuracy of the data that it produces, and is compromised by the errors found in approximately 94 percent of spreadsheets.” 1
When presented with a model, the savvy manager will query all the assumptions, and the way it has been built. Someone who has had some experience in building models will realise that they must be treated with caution. Models should be used as one tool in the decision-making process, rather than the definitive solution.
Plum Solutions builds customised models for virtually any purpose.
- A comparison calculator which translates a complicated pricing structure so consumers can find the best deal.
- A capital expenditure model used to approve, capture and report on company-wide capital expenditure.
- A survey model used to capture data from customers surveyed. The model then automatically manipulates the data to produce customised reports and graphs in Excel.
- A business case which calculates the expected number of customers using take-up rates based on demographic information.
- A competitor analysis with results shown using dynamic Excel graphs which can be easily modified by the user to display the desired combination of variables