Building a Financial Model under Distress

A DCF model is an elaborate method of valuing a company or its equity. At the end of the exercise, when we look back to see all the sheets that we have incorporated, we realize that assumption sheet has far more number of rows populated than any other sheet in the model. So precisely, a financial model contains more assumption than data.

Imagine a situation where you don’t have luxury of time to build a detailed DCF model. But you still have been asked to value a company using DCF. Company is not willing to part with the data or their business plans. Even if they are, you don’t have time to look into them and analyse to support your assumptions. You have been asked to produce a DCF model in next half an hour when you have heard the name of the company for the first time at that very moment.

Don’t panic.

A DCF model can still be developed within the stipulated time frame and with bare minimum information from the company. You need to have just three things to start with:

1. At least last year’s audited P&L and Balance Sheet
2. Some random guess, estimates, projections of the revenue for the next few years
3. Capital expenditure plan of the company

You can sail through a difficult time and come up with a defendable DCF model. We illustrate this on the basis of small case –

Raj was smart but a silent guy. Shilpa was mediocre but a chirpy girl.

He was dark while she was fair.

He came out as a closed person in numerous group discussions while she appeared to be an open person.

While he used to be serious in his thoughts, she used to be funny.

He was good in Marketing, while she had an edge in finance.

While he accepted, she dominated.

Wow, how complementary to each other.

The two got together and started a venture on Shilpa’s suggestions. Shilpa has a thought through business plan which they attempted to execute. While Raj brought customers for the company, Shilpa used to handle finances. He thought he knew everything about her while she knew everything about the business.

But that was two years ago. Today, Shilpa was clueless about the volatile nature of the industry their company was operating in while Ravi was clueless about Shilpa.

They were trying to figure out the cash requirement by the business and the funding plans over the next few years.

The problem that they are facing is how to prepare the projected financial statements in the absence of knowledge about funding requirements. They have a very vague idea about the business over next 5 years:

They want to maintain a minimum cash level of Rs. 12 Lakhs in the business and borrow at the existing interest rate of 12% if required. Please make an attempt to help them out based on limited information.

This situation can be developed into a financial model step by step as displayed below: Ensure iterations are turned on as per steps below:

1. Go to windows symbol at the top left corner of excel file
2. Click on “excel options” at the bottom
3. Go to Formulas tab, turn on the iterations

Now proceed to model, iterative calculations will be taken care of.

We ll soon be coming up with an amazing Quiz on building Financial Model. So STAY TUNED and strengthen your understanding of Financial Modeling.