Stress Test – Financial Modeling
What is “Financial Modeling – Stress Testing”?
A financial model needs to be accurate and error-free – stress testing is one of the key checking techniques employed by analysts for this purpose. To arrive at an accurate and error-free financial model, construction best practices should be followed and integrity checking techniques should be applied throughout the process. These are:
- Sense checking: is the output reasonable?
- Structure checking: is the formula construction consistent and as expected?
- Stress testing: does the model react to changes as predicted?
Stress checking involves making a change to the model (typically an assumption) and then checking to establish that it has behaved as predicted. Thought should be given to the change that will give the maximum information to the model builder. It can be best to make an extreme change where the output effect will be easily seen. For example, changing the COGS assumption to 100% of sales means that in the income statement, COGS will then equal sales, gross profit will be zero, and all other subtotals will be negative. This simple check can confirm that the model is working as predicted.
Key Learning Points
- Stress testing is one of the checking techniques deployed by analysts to ensure that the financial model is accurate and error-free
- Stress testing changes in assumptions should be large or extreme as the output effect can be easily observed
- Stress testing the entire financial model is very powerful and will often find subtle errors or those that are not causing an obvious problem
- Stress testing can be done after a combination of sense checks, structure checks, and mathematical checks are used to ensure that the balance sheet balances.
The stress testing process is stated below:
- Change in assumption: for example, change the CAPEX as a % of sales assumption from 3.7% to 50% (i.e. make an extreme change, where the output effect can easily be seen)
- Predict output change: the output change predicted from the change in assumption (as stated above) is that PP&E should go up and cash should go down
- Check the model behaving as predicted: check and see that in the model the PP&E has gone up and cash has gone down, and check to see if the model still balances or not
- Undo stress test: undo the stress test assumption change and make any necessary changes, then recheck the model
One must note that extreme changes in assumptions are easier to observe. Making isolated changes in assumptions is also best because single adjustments (such as sales growth assumption) can impact many line items. Moreover, always stress test several line items to ensure they work when populated. Lastly, always stress test line items which can be either positive or negative.
Stress Testing – Example
In the following example, CAPEX as a percentage (%) of sales has been changed from 3.7% to 50%. As a result, PP&E will increase, the business will run out of cash and a revolver drawdown will be necessary. As the example shows, the model is working as expected.
However, in the following example given below, something is wrong with cash and revolver. PP&E has increased as expected but cash is negative and the revolver is still zero. The error should be found and fixed.
Finally, any output that is zero or that has been left blank (as an interim measure) should always be stress-tested since sense and structure checking may not work as normal. The dividends payout assumption is changed from 0.0% to 32.0% and now the balance sheet does not balance. The error should be investigated and fixed.
In conclusion, one of the most time-consuming parts of building a 3-statement model is finding mistakes that cause the balance sheet to be out of balance. These should be resolved before a thorough check of the entire model is undertaken. A combination of sense checks, structure check and mathematical check should be used to ensure that the balance sheet balances.
Note that once the balance sheet balances, the entire model should be checked in detail using these three checking techniques. Stress testing the entire model at this stage is very powerful and will often find subtle errors or those that are not causing an obvious problem. Remember to always stress test assumptions that are likely to change or which are currently producing a zero amount in the income or cash flow statements (or an amount equal to the historical data in the balance sheet).
Enroll on our online financial modeling course to learn the best methods for checking, diagnosing and preventing errors when building financial models, and gain a practical understanding of building, editing, and checking financial models.