What is financial modeling?
Financial modeling is the act of building a financial representation of a company by interpreting various features of its operations, such as accounts receivable, inventory and long term debt, which are mathematical simulations of real-world situations. The modeling process typically involves creating a summary of a business’s financial performance in Excel spreadsheets, which enables them to modify different variables to ascertain how changes may impact the company further down the line. Some examples of financial models include:
Sensitivity analysis model
This is a model designed to ask ‘what if?’ questions in order to test how sensitive a company is to changes in the market environment. For instance, they may wonder “what if my supplier costs rise by 25%?”, and how this would impact the business. So, a sensitivity analysis model would need to be able to adjust a ‘supplier cost’ variable, enabling the company to evaluate the effect of these rising costs on its operations. Sensitivity analysis tends to involve altering one variable at a time to ascertain its impact on the whole model in isolation.
Scenario analysis model
A scenario analysis model entails creating a scenario involving a number of different inputs or variables. For instance, a company might make three scenarios for the future: a ‘base case’, a ‘best case’ and a ‘worst case’ scenario. The latter could involve taking various key variables, analyzing whether the business could handle all of them performing badly over a designated period, with a best case scenario involving all of them performing well, and a base case scenario in the middle.
As the name suggests, this model helps a business work out its budgets for an upcoming period. This involves breaking down expense categories in more detail, enabling a company to assign limits and control spending based on what it can afford. A budget model will not only help businesses understand how much they can spend, but when they can do so.
How to build a financial model in Excel
There are three main building blocks of a financial model in Excel:
Inputs are the data entered into an Excel financial model, eventually enabling it to produce outputs. Both inputs and outputs will include the names of the metrics which describe the data, such as expenses or gross profit, as well as the period it relates to, like a particular year or month. It is important to note that inputs must be clearly differentiated from the processing and outputs of a financial model to ensure that all data is accurate. A good way to do this is to use different colours for each.
The processing stage is simply about translating inputs into outputs, and companies will want to use Excel formulas to calculate the output data. Examples of formulas to utilize include:
The FV (Future Value) formula simply works out the value of an asset after a certain period of time, based on regular payments and an assumed rate of growth.
The PMT (payment) formula calculates values for a loan with regular payments and a constant interest rate.
The EFFECT formula works out the effective interest rate — which takes the effects of compounding into account on top of the interest rate itself.
The RATE formula calculates the interest rate per period of an annuity.
DB stands for Declining Balance, with the DB formula working out an asset’s depreciation expense over a specified period.
The outputs are the products of processing, and represent the set of results from a financial model. They may be simple, such as the cost of a single product in a single cell, or a more complex table or chart, such as a balance sheet. It is recommended to always put the outputs into a separate spreadsheet for display purposes, allowing users to review them without having to trawl through the entire model.