Financial Modeling Guide

A good financial analyst will always use the built-in spreadsheet auditing & testing tools of Microsoft Excel that allow checking for errors in cell logic (and macro logic, if Excel VBA macros are used) in the financial model.

Doing so on a regular basis is good financial modeling discipline and good spreadsheet risk management practice, ensuring error minimization or avoidance in the financial model.

Use the Cell Info Window to check individual cells

• Use the button on the auditing toolbar to open the Cell Info Window.
• Displays the cell’s formula, value, name, precedent cells, dependent cells.

Check logic by selecting cell precedents and dependents

• Use the buttons on the auditing toolbar to display arrows connecting the active cell to related cells.
• References to other sheets can be traced in the same manner – double click on the arrow see the reference.
• For local references, this method provides a better auditing path than using long names for each cell.

Trace errors using the “Trace Error” button whenever Excel reports an error.

Check formula replication by using Row Differences or Column Differences.

• Verify that the formula in the first cell of the row or column is correct.
• Select the row, column or range you want to check for hard coded data or inconsistencies in cell formulas.
• Use Edit -> GoTo -> Special -> Row Differences or Column Differences.
• Only cells which differ from the first cell will be highlighted, allowing you to quickly identify inconsistencies in cell logic.

Use the Excel Camera tool to monitor the output results of a specific Worksheet location from another Worksheet.

• Drag and drop the Excel Camera command onto your Excel toolbar: Tools -> Customize -> Commands -> Tools -> Camera.
• Highlight the cell, row, column or range that you’d like to monitor, click the Camera button to capture the results.
• Embed the Camera results at another convenient location on the same Worksheet, or another Worksheet altogether.
• You can also create a single cross-check sheet that consolidates Excel Camera snapshots from several different Worksheet locations on a single Worksheet.
• When used together with Conditional Formatting, you can create a powerful, color coded dashboard to monitor the results of your financial model.

However, the most effective way to detect errors in a financial modeling exercise is still to build cross-checks such as check-sums into the financial model, as well as ensuring diligence and consistency when building 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: