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

EduPristine is a member of Adtalem Global Education (NYSE: ATGE), a global education provider headquartered in the United States. Adtalem is a 3 billion dollars (20,000 crores) company that has about 9 institutions and companies with more than 16,000 employees spread across 145 locations. Adtalem takes pride in training 142,000 degree-seeking students all over the world.The organization's purpose is to empower students to achieve their goals, find success and make inspiring contributions to our global community. EduPristine is one of India's leading training providers in Analytics, Accounting, Finance, Healthcare, and Marketing. Founded in 2008, EduPristine has a strong online platform and network of classrooms across India and caters to self-paced learning and online learning, in addition to classroom learning

Comments

Interested in this topic?

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

* Mandatory Field

Post ID = 26725