course bg
EduPristine>Blog>Quick Tips & Tricks to make a Scalable Financial Model

Quick Tips & Tricks to make a Scalable Financial Model

April 17, 2013

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

Incorrect Modelling Technique

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

Extending Projection in Modeling

 

C. Correct Modeling Technique

Correct Modeling Technique

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

Dragging column to extend projection 

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

Incorrect Modeling Technique 

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

Dragging Column to Extend Projection

C. Correct Modeling Technique

Correct Modeling Technique

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

Correct Modeling 

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

Incorrect Modeling Technique

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

Dragging Column to Extend Projection

C. Correct Modeling Technique

Correct Modeling Technique

 

Have some unique suggestions and tips to design a scalable model? Please feel free to discuss them with us 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 = 26159