Financial Modeling Basics

Introduction to Financial Modeling Basics

A financial model represents the financial performance of a company. It represents the financial performance for both the past and future.

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 make 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?

Historical and Assumption

Historical input data should always be taken from the right source. While making financial modeling basics assumption for the forecasted year historical data is one of the input criteria that need 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 analysts think about the company

Color Coding

Color coding is one of the important formatting factors in financial modeling basic. 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 belongs 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 calls, 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 the 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 an 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 need 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

Equation	Items	Description
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 the 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

 Shareholders equity

Similarly,

For long-term Debt

Income statement

An income statement is a financial statement that 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.

Depreciation

It can be calculated as a percentage of fixed assets.

Interest expense

It can calculate by multiplying the interest expense rate and the average closing debt balance

Income tax

One can calculate the tax rate using the historical and use the same rate in future

Profit

This is calculated by deducting expenses from the revenue

Balance sheet

 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

Goodwill is not amortized so it needs to be kept constant in the future years.

Cash

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

Current asset

• 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.

• Inventory

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.

Current liabilities

• 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

Recommended Articles

Here are some articles that will help you to get more detail about the Financial Modeling basics so just go through the link.

1. Content Marketing
2. Financial planner business plan
3. Financial Planning Success
4. Gross Profit Margin Formula