Top 12 Financial Modeling Concept Checkers

FY 07	FY 08
B: Beginning	B: Beginning
Add: A: Additions	Add: A: Additions
Less: S:Subtractions	Less: S:Subtractions
Closing E: Ending	Closing E: Ending

FY07’s Ending balance is FY08’s Beginning balance.

#5 – How to do Financial Projections and Analysis?

The basic financial statements of any company are, Income Statement, Balance sheet and Cash flow statement.

In Forecasting, the important things are assumptions which are also called as Drivers as these drivers causes the projections of financial statements. Basically drivers are calculated on the basis of historical information. These assumptions include, growth rate, Costs incurred, capital expenditure etc.

In Financial Modeling we have to create independent Schedules like Depreciation schedule, Shareholders Equity Schedule, Shares Outstanding Schedules, Debt Schedule. Through creation of different schedules we get, easy access to projected information which is required for linking in other places.

For calculation of expenses, they are grouped by departments as per the industry for which you are doing modeling. General categorizations of expenses include General and Admin Expenses, Research and Development, Sales & Marketing Expenses.

Based on Key Ratios, benchmarking of projections can be done.

Sensitivity analysis is a way through which we can check for the impact of changes in major assumptions.

#6 – What is Revenue Buildup?

Revenue buildup schedule is prepared for estimating a company’s sales. For forecasting revenue of companies, there are various factors which can be considered one of them is geography.

With revenue buildup you get an idea, that where your projections are coming from and the potential variance in them.

#7 – How costs are projected?

Cost sheet is a document which reflects the cost of the products or the services required for a particular project or the department for the performance of the business. Cost sheet consists of a DIRECT COSTS like cost of materials, purchase of stock in trade etc. and INDIRECT COSTS like employee benefits expenses, other expenses.

These historical costs are computed as percentage of sales, and based on historical values assumptions are made and future costs are projected.

#8 – What is Working Capital?

Net working capital is the difference between non-cash current assets and non-debt current liabilities.

These components of net working capital are often projected as percentages of sales or COGS.

Important ratios are, accounts receivable days, accounts payable days, inventory days, and inventory turnover shown here are imputed.

#9 – What is Depreciation?

In business we have assets which last for more than certain amount of years for e.g. Buildings, machinery, equipment, furniture, computers, outdoor lighting  but these assets will not last indefinitely.

For every accounting period i.e. year, quarter, month, etc. a portion of the cost of these assets is being used up. This portion being used up is reported as Depreciation Expense on the income statement.

In effect depreciation is the transfer of a portion of the asset’s cost from the balance sheet to the income statement during each year of the asset’s life.

Following is a rough guideline on preparation of Depreciation schedule:

a. Create the required line items
b. Reference net sales as the driver
c. Enter past capital expenditures
d. Project capital expenditures estimated over the forecast period
e. Reference past PP & E balances
f.  Depreciation Expense

– Forecasted depreciation from existing PP & E
– Forecasted depreciation from new Capex
– Calculate the Total Depreciation Expense

g. Complete Net PP & E table

h. Calculate relevant ratios

i. Consider the Capex and PP & E and make adjustments (smoothen the curve!) as required

j. Link up the following in the model

             – Depreciation Expense to Income Statement

             – Depreciation and Capex to Cash Flow Statement

             – Net PP & E to Balance Sheet

#10 – What is Amortization?

Amortization is similar in concept to depreciation. This term is used with mortgage loans. The lender often provides the borrower with a loan amortization schedule. With the amortization schedule allows the borrower can see how the loan balance will be reduced over the life of the loan.

Following is a rough guideline on preparation of Amortization schedule:

a. Create the lines items

b. Reference net sales as a driver

c. Fill in details of past additions to intangibles

d. Forecast estimated additions to intangibles

e. Reference past intangibles balances

f. Amortization expense

           -Forecast amortization from existing intangibles

            -Forecast amortization from additions to intangibles

             – Calculate total amortization expense

g. Complete the amortization table and calculate the required ratios

h. Link up the following

              – Amortization to Income Statement

               – Amortization and additions to intangibles to Cash Flow Statement

                – Intangibles to Balance Sheet

#11 – What is Shareholder’s Equity?

Shareholders equity includes firm’s total assets minus its total liabilities. Also it is calculated as share capital plus retained earnings minus treasury shares.

Shareholders’ Equity = Total Assets-Total Liabilities

Shareholders’ Equity = Share Capital + Retained Earnings – Treasury    Shares

It is also known as “share capital”, “net worth” or “stockholders’ equity”.

Following is a rough guideline on preparation of Shareholder’s Equity schedule:

a.Equity Schedule Overview

b. Set up the line items and input past information

c.Forecast

        – Share Repurchases

         – Option proceeds & new shares issued for exercised options

         – Dividends

         – Future equity balances

 d. Link up

          – Ending balances to Balance Sheet

          – Repurchase, options and dividends to Cash Flow Statement

#12- What amount of Debt is required?

When company is in need of funds it borrows it from lenders as an obligation owed by the borrower of fund to the lender of fund. This is  called as Debt and it is not just the amount but it has certain cost associated with it which is called as Interest Expense. The debt can be bonds, mortgages or bank loan.

General steps to be performed for making debt schedule:

a. Calculate Cash Flow Available for Debt Repayment

-Reference in past information on Long Term Debt
-Make sure to enter the mandatory Long Term Repayments  (refer to repayment schedule provided in the Annual Report in the discussion on Debt Section)
-Calculate forecasted Long Term Debt Repayments

b. Calculate Cash Flow Available for Revolver

– Revolver = Cash Sweep

c. Calculate Interest Expense/ Income

– Refer to average debt balances
-Revolver interest expenses
– Long Term interest expenses
– Cash Balance for Interest/ Income

d. Link Up

-Forecasted interest expense/ income to Income Statement
-Forecasted debt repayments/ borrowings to Cash Flow Statement
-Forecast debt balances to the Balance Sheet

Top 12 Financial Modeling Concept Checkers

 Learn the juice of this article in just a single minute Top 12 Financial Modeling Concept Checkers Infographic.

Also, have a look at this Financial Modeling Course. This course is a bundle of 10+ Sector Financial Modeling tutorials.

You can also learn Free Financial Modeling here.

Recommended Articles

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

1. Amazing Things To Know About Project Finance Jobs
2. Amazing Things To Know About Format Tables- Using Excel Table Format
3. 9 Helpful Ways Of Funding Requirements for Startup Business
4. Best Financial Modeling Job Descriptions and Skills of 2016
5. 15 Most Common Business Startup Mistakes You Should Avoid
6. APEX Interview Questions: Amazing Guide