My name is Allen Aravindan and I am a trainer, CFA Charter holder and a Fund manager for 11 Years and I have been teaching and implementing Financial Modeling for about 8 years and trained 3000 candidates . I also teach financial modeling my comprehensive online course. This will be a free financial modelling course guide on a travel agency business. This is the same technique, could also be used for start up business as well.
Download the template and start with me.
Free Financial Modeling Course: Tutorial
Objective : Financial Model
Leaving aside the fascination of any finance student or professional on learning this skill, financial model is nothing but business. Having said that business’s take decisions and decisions making is financial modeling. I will take you through a very simple, three statement model, at the end of which we decide if we want to start this business or not.
Here are 6 simple steps:
- Download Template –
- Assumptions & Objective Setting
- Create the Income Statement
- Balance Sheet
- Cash Flow Statement
- Return Calculation and Sensitivity
- Take Decisions- Basis NPV & IRR
Step by Step Tempalte- Easy to use
The template of this model is an easy one, just so that your basic concept of what is financial modeling and how it is applied is clear. Remember financial modeling is a decision making tool and hence template should help us in making decisions. It is not necessary for the template to be very complicated. So don’t get to crazy about complication but target simplication and keep in mind the objective
You could also check my video on “ History of Financial Modeling”
Assumptions & Objective Setting
Assumptions are the most critical part of a financial Model, primary because financial models are basically used for business and finance decision making.In this model we are making a financial model in excel to take decision whether the feasibility of a start up business in the travel industry. Hence the first step in any financial model you build is to first list down the assumptions we will need to take the decision and then think about the model template.
So to sum it up till here!
- Gather the assumptions
- Design the structure of the model template
Believe it or not, your financial model is as good as your assumptions. A lot of time has to be spent, on understanding the business details, which is not as easy as it may sound. You need to break down the business drivers and cost drivers. Business drivers means, its revenue sources in ( Price X Quantity) structure, and a similar structure for the business costs.
Create the Income Statement
The Income statement is given in the template and its not important for the income statement to be according to some accounting standard but logical.
- Calculate the revenue first by looking out at the assumptions of tariff, seating capacity and load factor (Calculate for 365 Days)
- Leave the other income part for now (other income can be calculated only after we have the cash)
- Calculate costs in a similar manner by Finding the total fuel required (Ill let you think that!)
- Calculate Gross profit in the model by reducing total cost of revenues from Revenue + Other Income
- Depreciation is basically the cost of the bus spread across years according to the matching principle of accounting- Remember bus is the asset here
- Create the amortization schedule of debt- If you don’t know how to do that you can watch the tutorial for it which I have linked above
- Finally arrive at Net Income
Balance Sheet tells the position of the business at a point in time and is one of the three statements we create in this financial model
- Remember the balance sheet starts from year 0, since your investment happens before your operations begin
- So in year 0 there will be gross fixed assets and on the liability side there will be the financing of that asset using debt and equity
- Populate all the years and give special attention to calculations you perform.
Cash Flow Statement
Cash flow statement is one of the most important statements and which helps us in valuing this business. Pay attention on the calculation methodlogy I cover in the tutorial to learn how to make the cash flow statement in the financial model easily.
This will also teach you how the other income in the form of interest income gets calculated
Return Calculation and Sensitivity
Once you have the entire model in place then perform a sensitivity analysis to calculate how your assumptions affect your financial Model. Also the return calculation method we used in this model is IRR and NPV.
Financial Modeling is a core skill for careers in investment banking . If you are confused on, how to make your careers in investment banking then watch this video on the same.