Create Financial Models in Excel
I recently met a couple of professionals who are fresh graduates and working in the financial services sector. Since I was part of an investment team with a private equity fund where I have experience in creating investment memos and building the financial model from scratch. These guys asked me if there was an easy way to simplify a complex financial modeling. I thought of helping them out. The rest of this post is a result of that.
A typical financial model looks really intimidating. It has more than 10 sheets with few terms like IDC (Interest during construction), Tax calculation, DCF (discounted cash flows), IRR (Internal rate of return). Before I got into this field, it was daunting for me too.
Let me take a shot at this. So how do we begin? I think the best way to build a financial model is to understand how the business operates. In other words, what are the drivers for this business? By drivers, I mean what are the variable which determines the revenues and expenses. Let’s look at a business and try to figure out.
Let’s take an example of a school. What drives revenues of the school? You will come up with two quick drivers – # of students and Average tuition fees. Let’s go a bit deeper, how about admission fees which drive revenues from new enrollment and transport fees (based on the number of students taking transport fees). Let’s sum it up:
So the task is to figure out the sub-drivers of the business either by asking operators of the business or evaluating assumptions from research reports.
You can check out an article on Don’t do these 10 things while preparing a financial model, these must be part of your checklist on Do’s and Don’t while making financial model reports.
Let’s go to the cost drivers. Now there are two cost drivers – Capital expenditure and operating costs. In this post, I will focus on operating costs. What’s your guess on operating costs -? Books, teachers’ salary, student activity, affiliation, royalty (if franchised), admin expenses like utilities, repairs, audit and legal expenses, etc. If the school is not on the owned land, then maybe rent or lease?
Let’s sum it up by identifying sub-drivers:
The most important point to consider is that to look at the historical data of the business before you finalize the sub-drivers. For instance, the suppose student-teacher ratio for a particular category of the school is 20:1 but historically, the school uses 30:1, we will be off by our cost assumptions by a significant amount and that puts the analysis in danger. So, always compare market data, with historical data of the company you are planning to prepare a model.
By the end of this exercise, we have created a very simple model for a business. We still need to build projections to make it more effective for decision making. Next time, we will discuss on a preparation of projections along with balance sheet and cash flow.
I hope you found our article useful. If you any doubts or questions regarding this article, feel free to post them in the comment section below.