Introduction to Financial Modeling Basics
Confused….whether you should really invest in this company….Are you expecting too much from a company …. Are your these expectations based on just some vague thinking that is coming into your mind….. Do you really think your these vague points are the right base to jump to a conclusion….
Let’s go through this financial modeling basics tutorial and try to stop to take these vague decisions and unnecessarily increase our risk…Let’s learn financial modeling basics and think and take a decision in a more structured way ….. To get the in-depth knowledge of preparing a financial model through video tutorial please go through Financial Modeling Training
So, let’s start with understanding the most basic point of financial modeling tutorial …
What are the Financial Modeling basics?
A financial model represents the financial performance of a company. It represents the financial performance for both past and future.
Historical and Assumption
Historical input data should always be taken from the right source. As while making financial modeling basics assumption for the forecasted year historical data is one of the input criteria needs to be considered.
The assumption is referred to as “drivers”.
- The other criteria which one should consider while making an assumption are
- No bias should get into the assumptions on the business
- Clearly understand the expected changes in future performance
- Understand Management expectations
- Check out what other financial modeling analyst think about the company
Color coding is one of the important formatting factor in a financial modeling basics. This is done so that a person who has never seen your model comes to know easily whether a certain financial data is a historical input data, calculations or linkages.
Financial Modeling basics, One formatting tip
If your year 2009 comes in cell B2 of the income statement then it is advisable that 2009 in other sheets like balance sheet, cash flow statement should also come in cell B2. This financial modeling basics formatting helps during linkages.
Preparation of financial modeling basics
In order to prepare a financial model the very first thing is that one needs to understand the company on which the person is preparing the model. The person should also have a thorough knowledge of the industry, its competitors in which his company belong to.
In order to analyze a company, one should not only just check the annual report of the company but one should also read the transcript, conference call, presentations published by the company
Compilation of historical
One should compile the past 5 years of historical data while preparing a financial model.
While compiling g please keep certain points in mind:-
- You are an “Analyst”, not an “Auditor”. So if the historical data available in the annual report published by the company does not compile then don’t panic and sit to tally the financials. Just take the financials as they are.
- You should always refer to the latest annual report for the historical data. In other words, if your historical year starts from 2008 till 2012 and you are compiling the data for the year 2011 then you need to pick up the figures from 2011- 2012 annual report which is the latest one and not from 2010-2011. As the new annual report contains the revised figures.
- A financial model should always consist of key financial terms like gross profit, EBITDA, EBIT, net profit etc. If you cannot find these terms in the annual report then you need to prepare in your financial model and record the items accordingly.
This is how the format of income statement should appear
Financial Modeling basics , BASE Equation
In order to calculate the ending balance for this financial year there are certain items that needs to be added and subtracted.
Let’s understand this base equation in detail.
For Fixed Asset
In order to find out ending fixed asset, we need to have an opening fixed asset, capital expenditure, Depreciation, the sale of the asset. Let’s understand what amount needs to add or subtracted in order to find out the ending fixed asset
|B (Beginning)||Beginning fixed asset||This is the fixed asset which is carried forward from last year|
|A (Addition)||Capital expenditure||This is the extra asset you bought this financial year|
|S (Subtraction)||Depreciation/ sale of asset||Depreciation is an expense you need to deduct it Since the assets are sold in this financial year that amount needs to be deducted.|
|E (Ending)||Ending fixed asset||This fixed asset amount is generated after the addition and subtraction of all the necessary items in this financial year and will be carried forward in the next year|
|B (Beginning)||Beginning shareholders fund|
|A (Addition)||Net income / Issuance of equity|
|S (Subtraction)||Repurchase of equity / Dividend paid|
|E (Ending)||Ending equity balance|
For long-term Debt
|B (Beginning)||Beginning long-term Debt|
|A (Addition)||Issuance of debt|
|S (Subtraction)||Repayment of Debt|
|E (Ending)||Ending debt balance|
An income statement is a financial statement which measures the company’s financial performance over a specific accounting period. It gives a summary of how the business incurs its revenues and expenses through both operating and non-operating activities.
Let’s see how we can project the income statement
Net sales Calculation
One can calculate net sales as year on year growth or on CAGR or if you find any information on where the management has discussed the expected sales growth rate in the future you can take that percentage as well
Calculation of Direct cost and selling and admin cost
One can calculate the direct cost and the selling and admin cost as a percentage of sales. Calculate for the historical and then take up an average and find out the forecasted figures.
It can be calculated as a percentage of fixed assets.
It can calculate by multiplying the interest expense rate and the average closing debt balance
One can calculate the tax rate using the historical and use the same rate in future
This is calculated by deducting expenses from the revenue
Fixed asset, long-term debt, shareholders fund
These items need to be calculated using the Base equation and then needs to be linked in the balance sheet
Goodwill is not amortized so it needs to be kept constant in the future years.
Cash item needs to be linked by the ending cash balance from the cash flow statement
Current assets and current liabilities
These items need to be calculated in the working capital schedule. Now let’s understand the working capital schedule in detail
Working capital schedule
- Accounts receivable
Calculate the accounts receivable in days for the historical using the ratio 365*Average debtor divided by net sales. Using the past historical data assume the number of days for the forecasted years.
Calculate the inventory in days for the historical using the ratio 365*Average inventory divided by net COGS. Using the past historical data assume the number of days for the forecasted years.
- Accounts payable
Calculate the accounts receivable in days for the historical using the ratio 365*Average payable divided by net purchases. Using the past historical data assume the number of days for the forecasted years.
- Other current liability
One can calculate the other current liability for the historical as a percentage on the COGS and then you can take an average and assume for the future.
After completing this working capital schedule link it to the balance sheet
Cash flow statement
Cash flow statements assess the amount, timing, and predictability of cash-inflows and cash-outflows, and are used as the basis for budgeting and business-planning.
It answers the questions:
- Where the money came (will come) from?
- Where it went (will go)?
The accounting data is presented usually in three main sections:
- Operating-activities ( sales of goods or services),
- Investing-activities (sale or purchase of an asset, for example), and
- Financing-activities (borrowings, or sale of common stock, for example).
Whatever changes you have incurred in the amount of the item in the income statement, balance sheet in the financial year those changes will be recorded in the cash flow statement according to that items cash inflows and outflows.
Let’s look at the proforma of the cash flow statement
Financial Modeling Basics, Infographics
learn the juice of this article in just a single minute, Financial Modeling basics Tutorial
Here are some articles that will help you to get more detail about the Financial Modeling basics so just go through the link.