Create Financial Models For Business

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.

campaign-image-1

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:

campaigng-blog-2-image-2

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:

campaigng-blog-2-image-3

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.

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.