Great Tips to Induce Robustness in a Financial Model

This blog is an extension to our previous blog aboutQuick Tips & Tricks to make your Financial Model Scalable

Robust: A model is robust if it does not break down when assumptions are changed from one end of the spectrum to another. A frequently noticed error in this category is incorrect / inconsistent tax computation if assumptions are changed such that PBT changes sign from positive to negative. Another frequently noticed error is that balance sheet starts showing negative cash if capital expenditure in a year is doubled or tripled.

Few tips to ensure robustness of your model:

1. Ensure all the possible scenarios are modeled. For example, if tax is modeled simply as

Tax = Tax Rate x PBT

Or Tax = if (PBT <0, 0, Tax Rate x PBT)

Your model will break down in case PBT is negative for a few years in succession. It will not return the benefit of accumulated losses.

Tax modeling is quite a complicated topic in itself. Keep watching this space for a detailed blog on a comprehensive tax modeling. We present a simple method below (for the sake of readers) that can help you sail through the situation where PBT retains its positive trend after becoming positive once.

Under this assumption, it should actually be modeled as:

Tax = if (PBT<0, 0, if (Cum PBT<0, 0, Tax rate x min (PBT, Cum PBT)

Correct Modeling Technique for Taxes where PBT retains its positive trend after becoming positive once:

2. Another common error in modeling is not to link debt balances with cash balance. Accordingly, if any drastic change in assumption makes the cash flow during the year highly negative wiping out previous balances, the closing cash balance on the balance sheet will turn negative. Hence, provision for shortfall should always be incorporated in model such that if cash balance is expected to fall below the threshold in any year, model automatically draws down more debt to make up for the shortfall and brings the cash balance back to threshold level. Consider this illustration below:

Suppose we have modeled a linked excel model as detailed below:

A. Incorrect Modeling of Debt & Cash Balances

No provision for shortfall in cash, no protection against cash balance going negative”

In a review meeting, it was decided that capex during FY15 will be Rs. 10 Cr instead of current Rs. 5 Cr assumed. We have been asked to make a change in our model suitably”

B. Resultant Impact on the Balance Sheet

As soon as Capex is changed, cash balance on the balance sheet turns negative- Model breaks down, unacceptable”

C. Inducing Robustness in the Model

Instead model the provision for shortfall so that model draws down additional debt if shortfall in cash in expected.

D. Resultant Behavior of the Model now

“Now see the impact of changing Capex, model automatically draws additional debt to restore cash balance to threshold level- induces robustness to model

Remember, our understanding of a valuation model is inversely proportional to the number of inputs required for the model. Simpler valuation models do much better than complex ones. So we should never aim for making a complicated model. We should always try developing a scalable and robust financial model without compromising on its simplicity.

Do you have some more tips to induce robustness in a financial model? You may discuss the same with us and our numerous readers below.