Share with your network!

This blog is an extension of our previous blog 10 Great Simplification Tips for a Scalable Financial Model.

Scalable: A model is said to be scalable if it can be scaled up or down easily to handle addition or deletion of variables and / or time periods. The model should not necessitate disproportionate efforts when you are asked to add one more product in the portfolio, one more asset class in the depreciation schedule or extend it to include two more years of projections. A common error in this category, for example, will be display of REF!, #VALUE, DIV!0 etc or wrong values in the cells when you extend your projection period by one year by selecting the last column and dragging it to next one. A model also loses its scalability if formulas are not protected by dollar signs.

Few suggestions to make your model scalable have been detailed below. In each of these illustrations, assume that we had initially modeled the financials till FY16 and we had now been asked to extend it till FY17. We are aiming to extend it to one more year by simply selecting the last column and dragging it to next column:

1. Protect the formula by dollar sign so that if you extend your model for 1 more year by dragging the last column, you end up getting the correct values. An illustration is shown below:

A. Incorrect Modeling Technique

B. See what happens when last column is dragged to extend projection for 1 more year

C. Correct Modeling Technique 

D. Now see what happens when last column is dragged to extend projection for 1 more year

2. Model depleting items (at constant rate) with caution. Use “min” function to cap the depletion. For example in case of depreciation, debt repayment should be modeled with usage of min function so that the value in a year is the depletion rate or the balance amount, whichever is lower. Look at the illustration below:

A. Incorrect Modeling Technique

B. See what happens when last column is dragged to extend projection for 1 more year 

C. Correct Modeling Technique

D. Now see what happens when last column is dragged to extend projection for 1 more year

3. Maintain the consistency in the format of modeling across all the items. Let’s take example of interest modeling. Consider a debt of Rs. 100 Cr on the books of a company which is supposed to be compensated by means of equal yearly installment (EYI) in 5 years time. You have rolled out the debt amortization schedule using PMT function and linked the interest portion of the EYI to the interest expense in P&L. Common way of doing it and the associated error on dragging is illustrated below followed by correct method of modeling:

A. Incorrect Modeling Technique 

B. See what happens when last column is dragged to extend projection for 1 more year

C. Correct Modeling Technique 

Have some unique suggestions and tips to design a scalable model? Please feel free to discuss them with us below.