Wikipedia defines financial modeling as “the task of building an abstract representation (a model) of a real world financial situation.” Many of these financial models are built in Excel. In this article, I will outline the basic principles of financial modeling in Excel. Following these principles will ensure that your financial model is easier to maintain and will ultimately reduce the potential for errors.
4 Building Blocks of a Financial Model in Excel
There are four primary building blocks of any financial model in Excel:
- Inputs
- Data
- Calculations
- Outputs (cells, charts, pivot tables)
Inputs of a Financial Model
Inputs are the point of engagement for end users. They define and enter data that powers your financial model. You may be interested in capturing user entry such as historical financial data of a company, cost estimates of an investment property, or cost estimates of a budget model. Some models may only require a few input fields, while others may require thousands of data points to run.
It is always good practice to decouple inputs from the other building blocks of your model. Keep them in a different worksheet solely dedicated to inputs. While it may be easier to achieve this in simpler models, more complex models may require displaying inputs and outputs together for review purposes (see the screenshot below). The yellow cells are inputs and the other cells constitute outputs of the financial model.
Financial Modeling in Excel is Driven by Data
This section refers to the underlying data that is used to analyze inputs. This data is typically updated periodically by the administrator of the financial model. For example, mortality tables are used in actuarial models and are updated rather very infrequently. Another example is mutual fund performance data that can be used in an investment model. This type of data is typically updated quarterly to provide the most accurate results.
It is important to decouple this type of data from the rest of the model in a separate worksheet (or set of worksheets). This will allow the administrator to update the data without impacting any other part of the model.
It is also recommended that each data table has a named range. Always reference the data in your formulas using its named range instead of its cell reference. It is easier to maintain a formula that references a mortality table such as =VLOOKUP(Age,MortalityTable,2,FALSE), rather than a formula like =VLOOKUP(Age,Sheet2!A1:D100,2,FALSE).
While updating your data, always make sure to check the definition of the named range, especially if you are uploading data with more rows or columns than the existing one. If you don’t adjust the named ranges, the copied data that falls outside of the original data may never be used in your calculations.
Calculations is the Engine of Financial Modeling in Excel
This is the most important part of your financial model, where you combine inputs and data to calculate your results.
It is important to keep the calculation section decoupled from the rest of your model. This will make it easier to maintain your model. You can keep your calculations in one worksheet or expand them over multiple worksheets.
Outputs of your Financial Model
This defines the set of results from your financial model. In most cases, this is what you want your end user to see. The output can be a calculated cell, a table, a chart, or a pivot table. You may have a simple model that calculates the cost of a product in a single cell that is dependent upon various user inputs. A more complex model may output a full table of values, such as balance sheet (see screenshot below).
Some models may output results in a more visual format like charts:
The outputs of your model will be coming from the calculations section. Regardless of how simple or complex your outputs are, always bring them into a separate worksheet for display purposes, even if it means some duplication. This will make it is easier to maintain your financial model.
