course bg
EduPristine>Blog>Great Tips to Induce Robustness in a Financial Model

Great Tips to Induce Robustness in a Financial Model

May 7, 2013

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:

Correct Modeling Technique for Taxes where PBT retains its positive trend

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”

Incorrect Modeling of Debt and Cash Balances

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”

Resultant Impact on the Balance Sheet

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.

Inducing Robustness in the Model

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

Resultant Behavior of the 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.

About Author

avatar EduPristine

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.

Comments

Interested in this topic?

Our counsellors will get in touch with you with more information about this topic.

* Mandatory Field

`````````````````````````````````````````````````` Post ID = 26725