Discover the importance of stress testing in improving the quality of a financial model and learn about various checks that can be placed within an Excel model to prevent errors.
The last key step in preparing a financial model is stress testing it. The ability to stress test a financial model and find any flaws within it is a useful skill in improving the quality of a financial model. Stress testing also ensures no errors will occur in the future when the model has been handed off to the final user.
Error checking
Because of the structured nature of the core statements, it is not too difficult to ensure the data and calculations are correct. Below are just a few types of checks that can be placed within an Excel model to ensure that values are adding up correctly.
- Does the balance sheet add up? Do Assets minus Liabilities minus Equity equal zero?
- Does the change to retained earnings in the current period equal net income minus dividends?
- Does the ending cash balance in the cash flow equal the cash balance in the balance sheet?
- Do ending values in the supporting schedules match their corresponding values in the core statements?
- One master error checks to determine whether all of the above are resulting correctly.
Placing these checks within a financial model enables the user to ensure calculations are being done correctly, and that no formula logic has been made erroneously. The more checks in place, the more secure the financial model is from errors appearing.
If the Balance Sheet is out of balance
Top Errors
|
Error |
Prevention |
Diagnosis |
|
Faulty subtotals |
• Build for historicals – never hard code • Check to source • Copy across |
• F2 for visual reference • Sense check overtime series |
|
Signs the wrong way around especially on cash flow statement |
• Use the rules of cash • Use the structure (L&E one way, assets the opposite) |
• Structure check line by line • Sense check overtime series |
|
Historic balance sheet feeding forecast incorrectly |
• One feed rule – inputs hardcoded once only • Use formatting for visual reference • Historic numbers never calculated – always input except for subtotals |
• Balance sheet out of balance by the same amount each year? • Visual check of historical backup calculations |
|
Bad clicks – usually off by one year |
• Final year shows zero? • Stress test by changing year 1 assumptions |
• Use the labels when formula building • Regular structure checks • Sense check over the time series |