What you need to know is:
- Knowledge about the purpose and audience.
- Identification of key drivers in the financial business.
- Knowledge of accounting and financial statement analysis.
Financial Modeling is in fact about projections and it should indicate the following kinds of final “output”:
- Income Statements
- Balance Sheets
- Sources and Uses of Cash Statements (Cash Flow Statements)
Following are the Top 12 Financial Modeling concepts which one should be aware of:
#1 – What should be the color coding in Financial Modeling?
While preparing Excel based financial models it is a good and standard practice, to use different colors for better understanding.So in Excel Best practices, we use different colors for links, constants and formulas.
• Blue Font Color: This color is used for hard-coded values that are entered manually. It is used for historical financial data and for assumptions part of the cell.
• Black Font Color: Black Color is used for the calculation purpose. That is the cells which are calculated from linking of other cells.
• Green Font Color: Green color is used for the linkages purpose that is wherever there is a need of linking values from other sheets or workbooks.
#2 – Why Ratio Analysis is done?
- Ratios can often be more informative than raw numbers
- Put numbers in perspective with other numbers
- Helps control for different sizes of firms
- Ratios provide meaningful relationships between individual values in the financial statements
Solvency Ratios: Analysts practice these ratios to determine the firm’s ability to pay its short-term liabilities.
These ratios include:
Operating Efficiency ratios: Examines how management uses its assets to generate sales and it considers the relationship between various asset categories and sales.
- Operating Profitability
- DuPont Formula
- Extended DuPont Formula
Risk: Risk analysis examines the uncertainty of income for the firm and for an investor.
Total firm risks can be decomposed into three basic sources –
- Business Risk
- Financial risk
- External liquidity risk
Limitation of Financial Ratios
- Accounting treatments may vary among firms, especially among non-U.S. firms
- Always consider relative financial ratios. They do not make any sense when viewed in isolation
- Firms may have divisions operating in different industries making it difficult to derive industry ratios
- Conclusions cannot be made by just looking at only one set of ratios
- Ratios outside an industry range may be cause for concern
#3 – What is SGR?
SGR stands for Sustainable Growth Rate. It measures how a firm can grow, without having additional equity and funds.
Sustainable Growth Rate (SGR) = Retention Rate (RR) * Return on Equity (ROE)
#4 – What is BASE Rule?
BASE rule is an acronym for Beginning, Additions, Subtractions, Ending.
For financial year we have beginning balance which is remainings of last year, into which additions and subtractions of items are done. Finally whatever remains is your closing balance, which is going to be an opening balance for the next year.
Beginning + Additions – Subtractions = Closing Balance
|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
– Share Repurchases
– Option proceeds & new shares issued for exercised options
– 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.
Here are some articles that will help you to get more detail about the Financial Modeling so just go through the link.