Financial Modeling Guide

Financial models are often used to make major financial decisions that involve several millions of dollars worth of capital. It is therefore essential that these financial models do not contain errors or bugs that render calculations and results incorrectly. A good financial analyst will always start a financial model audit assuming that every financial model has errors in it. A small financial model of 1,000 rows by 30 columns will contain 30,000 cells of logic, and even if a small margin of error of 0.1% will produce 30 errors in the financial model.

We have looked at using some basic spreadsheet tools that show you how to check for errors in a financial model before, such as the cell info window, spreadsheet check sums, auditing toolbar, trace error and the Excel camera tool. We have also looked at using a standard spreadsheet layout before that may help to minimize errors in a financial model. We will now examine 2 other techniques that can be useful in auditing and detecting potential errors in a financial model.

Use of Historical Data

Probably one of the best tests of the logic of a financial model is to test the functionality of the financial model using actual historical data as data input. For instance, in an annual budget financial model, it is often quite possible to get the numbers from previous years and feed them into the financial model to see if you can replicate previous year’s results. If key output results such as profit, growth rates and financial ratios from this historical data match up against the results you compute from your financial model, you are likely to be on the right track.

Use Spreadsheet Cell Maps in Excel Spreadsheets

We talked about spreadsheet cell maps before, and this is the technique for putting it in practice in a financial modeling assignment. Microsoft Excel has a useful command that allows a financial analyst to highlight and scan a large area of spreadsheet cells to determine if the formula pattern contained within the highlighted area is consistent. The command will compare the formula pattern of a cell with its adjacent cell, and point out any cells that do not have a consistent pattern in terms of formulae. This auditing method is extremely useful for identifying errors where a number has been accidentally hard coded or typed over a cell that is meant to contain a formula, or for detecting instances where a series of formulas have not been properly copied over.

This pattern match command for spreadsheet cell maps in Excel can be accessed through the following menu commands:

Edit –> Goto –> Special –> ROW or COLUMN Differences

In the illustration below, the cell P58 is highlighted since it does not match the pattern of other cells highlighted in the spreadsheet cell map:

Spreadsheet Macro Errors

While on the topic of financial model audits, let us also touch on a process for checking the logic of macros to determine if computation errors are present. Errors in spreadsheet macro logic are often more obvious or easily detectable than errors in spreadsheet cell logic, and the following 4 step checking process will help a good financial analyst to audit and test spreadsheet macros in a financial model:

1. Check that all menus and buttons in the spreadsheet macro are working properly. Very often, the simpliest and most obvious errors are when cell outputs for choices selected in a menu or button are incorrectly assigned to a spreadsheet macro, leading to wrong data input into spreadsheet macro calculations.
2. Check that all data links between different workbooks called by the spreadsheet macro are functioning correctly and calling the right data sets. Make sure that macro links and cell links do not contain the full directory path otherwise the links will not be easily migrated if the financial model if moved to a different computer or put on a computer network for common access.
3. Check all external database links. If the financial model links to an external database such as an enterprise resource planning (ERP) database to pull financial data into the financial model, make sure that the links you use are range names and not cell addresses.
4. In general, it is prudent for a financial analyst to use range names instead of cell addresses in spreadsheet macro logic, otherwise the spreadsheet macro may not function properly if row or columns are inserted / deleted from the financial model.

If you're new to Financial Modeling Guide, you may want to join our sister site, the Finance 3.0 network, to enroll in online financial training programs, ask questions, attend seminars, connect with others and download free finance spreadsheet templates about financial modeling, financial management, corporate finance & valuation, quantitative finance and accounting.
Over 50,000 finance professionals, entrepreneurs, analysts and investors from 195 countries have benefited.
Don't miss out on this outstanding opportunity for high quality, globally recognized financial education: Sign Up For Free Now!

After reading this article, people also reviewed these financial modeling and valuation resources: