Corporate Finance Class 07 Financial Modeling Daniel Sungyeon Kim Peking University HSBC Business School
Class Outline • Quick review of last Wednesday • Financing modeling • Model II • Model III
Next step: Building sales-driven pro formas • A pro-forma is a prediction of how the firm’s financial statements -- balance sheet, income statement – will look in future years • Pro-formas are not easy to prepare; we have to ensure that all financial statements are consistent • assets & liabilities must match • connections between balance sheet & income statement must be maintained • Any assumption about sales and costs affects not only the income statement, but also the balance sheet • Balance sheets and income statements are internally connected
The Problem: Unlike the Income Statement, the Balance Sheet has to balance! Assets = Debt + Equity
How do we balance the pro-forma balance sheet? • When valuing a firm, we care about its future operations • Therefore we want to use firm analysis to project these operating-related items independently • E.G., sales, COGS, AR, AP • So we cannot change operating-related items to make the balance sheet balance and to maintain the relationship b/t IS and BS • Use financing side items to balance!
The concept of plug • We consider a candidate for the plug from three financing activities • Cash/marketable securities • Debt • Equity • Any combination of these three can also be plugs • The choice of a plug reflects a firm’s financial policy. From a financial viewpoint, this tells you how the firm finances its shortfalls and what it does with its excess cash
Model I: Debt as the plug • A firm decides the following • If there is cash shortfall (FCF<0), the firm will borrow new debt to fund free cash flow • If there is excess cash if (FCF>0), the firm will pay down existing debt • No new stock is ever issued • Firm’s cash balance never changes • Then debt is the “plug” Link B/S with I/S and make BS balance
How to solve circular relations? • No closed-form solution • We have to go for a numerical solution • Enabling numerical solution in Excel: Iterations in the pro-forma statements • In Excel 2007 click the Microsoft Office Button/Excel Options/Formulas/Calculation options section, select the Enable iterative calculation check box.
Major Problems with Model 1 • Assumes that firms only use debt financing • Then why do we actually observe so many firms using equity financing? • This problem is fixed in Model 2
Model 2: Target debt-equity ratios • What if the firm has a target debt-equity ratio that it wishes to meet? • A firm cannot issue/repay debt and maintain the Target • Debt issues/repayment must be balanced with equity issues/repurchases • In such a model, both debt & equity are plugs • Our example: firm attempts to reduce its debt-equity ratio from 56% in Year 0 to 40% in Year 5
Model 2: Target debt-equity ratios • TARGET_D_E denotes the target debt-equity ratio of the firm: • Debt = TARGET_D_E * Equity • Equity = Stock + Retained Earnings • Stock = Total Assets – Current Liabilities – Debt – Retained Earning
Major Problems with Model 1 & 2 • Model does not incorporate cash explicitly • This creates a problem when sales growth is low and debt approaches 0 • Redo Model 1 with sales growth = 0 • What happens to debt in this case? • This problem is fixed in Model 3
Comments on Models 1 and 2 • Both models 1 and 2 assume that firm continuously issues and repays debt • Is that a realistic assumption? • What if there is no debt left to repay? • What happens if existing debt cannot be repaid? • So we need a model where firm can accumulate cash & marketable securities (and earn interest income on them)
Model 3: A model with cash • Incorporates cash into the pro-formas: • Cash is a non-operating asset, not related to sales • So we list it separately from operating CA • Assumption: Firm can issue (i.e., increase) debt but cannot repay (i.e., decrease) it in the time horizon being considered, i.e., • if FCF<0, firm issues debt or draws down cash • If FCF>0, firm’s cash increases • So both cash and debt are plugs
Model 3… • Balance Sheet with two Plugs:
Model 3 relationships • Recall that when debt was the only plug, then debt was issued to finance shortfalls and repaid when there was an excess of funds • In Model 3, we can only raise debt, we cannot repay it • But if debt cannot decrease, the definition of debt depends on if the firm is operating with a shortfall or an excess • So we have to consider excesses and shortfalls separately • If FCF < 0, Debt or Cash is the plug • Debt = Operating Assets – Current Liabilities – Stock – Retained Earnings • Cash and Securities go to 0 when debt is issued • If If FCF > 0, Cash is the plug • Debt = Previous period’s debt • Cash and Securities = Total Liabilities and Equity – Current Assets – Fixed Assets
Model 3 relationships… • Suppose • Total operating assetst– CLt – Equityt > Debtt-1 • i.e., there is need to raise more debt • Then, • Debtt = Total operating assetst – CLt – Equityt • Else, Debtt = Debtt-1 • Casht = Total Liabilitiest + Equityt – total operating assetst
More on building accurate pro-formas • Projecting the rest of income statement • Non-depreciation operating costs: Distinguish between fixed and variable costs • Projecting the rest of balance sheet • Use efficiency ratios to project NWC needs • Capital expenditure: Distinguish between replacement needs and expansion needs
Non-depreciation operating costs • Depreciation is not directly linked to sales • We want to focus on: • Cost of goods sold (COGS) • Sales, general & administrative expense (SG&A) • We project Operating costs • Operating costs = COGS+SG&A-Depreciation • Combining COGS/SG&A helps remove depreciation
Projecting Income Statement Items • Projecting Operating Costs • Focus on Cost of Goods Sold (COGS) and Selling, General, & Administrative Expenses (SG&A) • Before projecting operating costs, we need to exclude depreciation charges • Depreciation not directly linked to sales • If companies allocate depreciation to COGS and SG&A, instead of reporting it separately, the depreciation component must be removed
Projecting COGS and SG&A • It is important to distinguish between fixed and variable operating costs • Variable costs are proportional to level of sales • Fixed costs don’t change with small changes in level of sales • So in general, it is not advisable to project COGS and SG&A as a fixed % of sales • Unless, (COGS/sales) and (SG&A/sales) ratios have been fairly constant in the past
Projecting COGS and SG&A… • Predicting operating costs based on a percentage of sales tends to misstate costs as sales change • Better to divide costs into Fixed & Variable components • COGS+SG&A-Depreciation = FC + (%VC) × Sales • Estimate this regression using past data: • COGS+SG&A-Depreciation = b + m*sales • Obtain ‘b’ and ‘m’; they will be your estimates of FC and %VC, respectively
Example: Fixed vs. variable costs • Past information: • If projected sales are $205,000, then what are non-depreciation operating costs based on the “percentage of sales” approach?
Example continued… • Consider the regression results from regressing Costs (the y-variable) on Sales (the x-variable) • If sales increases to $205,000 • What are the fixed and variable costs? • What are the predicted non-dep operating costs?
Remarks on projecting costs • In using regression analysis to project costs, we are assuming that firm’s cost structure will remain the same • What if firm was operating inefficiently in the past, but is planning improvements? • Use industry average estimates (analyze competitors) • Treatment of inflation • Inflation usually affects both nominal sales and nominal costs equally → regression of nominal costs on nominal sales is ok.
Projecting Income Statement Items • Projecting Operating Costs – R&D • Projecting R&D expenditures is tricky • R&D often leads sales, so using % of sales might be misleading • R&D might be related to sales growth • R&D may be % of sales or may grow at the sales growth rate • In high growth firms (early stages of life) R&D may grow faster than sales and then eventually level off as the firm matures • The company may provide some projections and information
Projecting NWC items • The following ratios provide clues on firm’s credit policy, inventory policy, etc.
Projecting NWC items • Use these past ratios to project future ratios and NWC items:
Example: Projecting NWC items • A firm expects 25% of its sales to come from credit customers, while the rest would be cash sales. The firm’s policy for credit sales is to offer a 60 day credit (assume that credit customers use the full 60 day credit). • What is the projected average collection period for the firm? • Suppose the firm expects sales of $12 million next year, what are its projected accounts receivables? • Suppose the firm’s projected inventory days is 70 days, and the projected gross margin is 30%, what is the firm’s projected inventory?
Projecting Fixed Assets • Fixed assets are difficult to project because although they depend on sales, the relation is not always linear • Why?: • Suppose the firm is currently operating its plant & machinery at 70% capacity • It can increase production and sales without adding to fixed assets • On the other hand, if it is operating at (close to) 100% capacity, it will need to buy fixed assets • Unfortunately, we do not observe utilization rates
Projecting Fixed Assets Two types of capital expenditure: • Expansion of productivity capacity (i.e., to achieve growth) is undertaken when: • Current capacity utilization rates are close to 100%, • and/ or management is buying PP&E in anticipation of future sales growth • Maintenance of current productive capacity (replacement needs)
Estimating fixed assets needed for expansion of capacity • GFA-turnover ratio is useful is projecting a firm’s capital expenditure needs • GFA-turnover = (Sales/ Gross Fixed Assets) • Examine how this ratio varied over time • Compare firm’s GFA turnover with that of its competitors • GFA turnover can provide useful clues on firm’s capacity utilization levels: • Suppose GFA turnover declined from 4 to 3.2 • Current utilization rate, sales growth???
Estimating fixed assets needed for expansion of capacity… • Suppose capacity utilization is almost 100%: • Firm needs new assets to generate new sales • You can use GFA turnover to project GFA Projected GFA = Projected Sales/ GFA-turnover • Note: A firm could add new capacity even when it is operating less than 100% capacity • Why? In anticipation of future growth… • So always read firm’s “Annual Report” to find out the firm’s capital expenditure plans
Example • A firm had sales of $1 million. • The GFA-turnover for the firm is 1.35, while comparable firms in the industry have a GFA-turnover of 1.70. • Compute the maximum sales the firm can achieve without expanding capacity? (Assume that comparable firms are operating at 100% capacity utilization)
Estimating fixed assets needed for replacement of capacity • You have to obtain this information from the “notes to financial statements” • Here’s a simple (crude) method: • For all past years (or for as many years as possible), find out how much of capex was for replacement of productive capacity • Compute past growth rates; then compute the average growth rate • Assume that, in future, capex needed for replacement will grow at this average growth rate
Projecting Fixed assets • Projecting Fixed Assets – replacement • Some useful indicators:
Projecting Fixed Assets • Projecting Fixed Assets – replacement – Example • HP has fixed assets at cost of $12,120. The annual depreciation is 1,869. Accumulated depreciation is 6,212. • What is the average expected life? • What is the fraction of life exhausted? • How many years are remaining before replacement?
For next class • Holden Ch 15 • Do Holden Ch 15 problems • PROJECT PROJECT PROJECT!!!!