# Financial Modeling and Pro Forma Analysis - PowerPoint PPT Presentation Download Presentation Financial Modeling and Pro Forma Analysis

Financial Modeling and Pro Forma Analysis Download Presentation ## Financial Modeling and Pro Forma Analysis

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. Chapter 17 Financial Modeling and Pro Forma Analysis

2. Chapter Outline 17.1 Goals of Long-Term Financial Planning 17.2 Forecasting Financial Statements: The Percent of Sales Method 17.3 Forecasting a Planned Expansion 17.4 Valuing the Planned Expansion 17.5 Growth and Firm Value

3. Learning Objectives Understand the goals of long-term financial planning Create pro forma income statements and balance sheets using the percent of sales method Develop financial models of the firm by directly forecasting capital expenditures, working capital needs, and financing events

4. 17.1 Goals of Long-Term Financial Planning Identify important linkages Sales, costs, capital investment, financing, etc. Analyze the impact of potential business plans Plan for future funding needs

5. 17.2 Forecasting Financial Statements: The Percent of Sales Method A forecasting method that assumes that certain balance sheet and income statement items grow proportionately with sales. Purpose: To predict the amount of Net New Financing that will be needed to support a forecasted increase in sales Do we need to issue more debt or stock?

6. 6 Table 17.1 KMS Designs 2007 Income Statement and Balance Sheet

7. The Percent of Sales Method Step 1: Pro Forma Income Statement Income Statement Accounts impacted: Sales Costs Except Depreciation Depreciation Expense Taxes: Use Tax Rate Income Statement Accounts not impacted: Interest Expense: based on debt levels, not sales All other Income Statement line items are calculations

8. The Percent of Sales Method Step 2: Pro Forma Balance Sheet Asset/Liability Accounts impacted: All Assets All Current Liabilities � only Accounts Payable in example NOTE: Notes Payable and Short�Term Debt would not be adjusted, but those accounts are not used in example, hw problem, and test. Asset/Liability Accounts not impacted: Long-Term Debt: based on need for additional funding Stockholders� Equity: Increases by Retained Earnings (Net Income � Dividends) All other Balance Sheet line items are calculations

9. The Percent of Sales Method Step 3: Net New Financing Initially, the Balance Sheet will not balance: Assets ? Liabilities + Equity The difference between Assets and L+E indicates the Net New Financing to fund growth The amount we need to add to the L+E side of the pro forma balance sheet to have it balance is often referred to as the plug. Net New Financing will be added to Debt or Equity (i.e., issue common or preferred stock) Incremental Interest Expense will be ignored if Debt is utilized.

10. KMS Designs Example Assumptions: KMS forecasts an 18% growth in sales from 2007 to 2008. 70% of Net Income is retained by KMS Increases Retained Earnings 30% of Net Income distributed as dividends

11. KMS Designs Example Step 1: Pro Forma Income Statement 1) Calculate 2008 Sales: 2007 Sales x (1+ Sales Growth Rate) 74,889 x 1.18 = 88,369 2) Calculate 2008 Costs Except Depreciation: 2007 Costs x (1+ Sales Growth Rate) 58,413 x 1.18 = 68,928 3) Calculate 2008 Depreciation Expense: 2007 Depreciation x (1+ Sales Growth Rate) 5,492 x 1.18 = 6,480

12. KMS Designs Example Step 1: Pro Forma Income Statement 4) Calculate the Tax Rate: 2007 Income Taxes / Pretax Income 3,737 / 10,678 = 35.0% 5) Create Pro Forma Income Statement

13. Table 17.2 KMS Designs Pro Forma Income Statement for 2008

14. KMS Designs Example Step 2: Pro Forma Balance Sheet 1) Calculate 2008 Asset Amounts: 2007 Asset Amount x (1+ Sales Growth Rate) Cash: 11,982 x 1.18 = 14,139 A/R: 14,229 x 1.18 = 16,790 Inv: 14,978 x 1.18 = 17,674 PPE: 49,427 x 1.18 = 58,324 2) Calculate 2008 Current Liability Amounts: 2007 Liability Amount x (1+ Sales Growth Rate) A/P: 11,982 x 1.18 = 14,139

15. KMS Designs Example Step 2: Pro Forma Balance Sheet 3) Calculate the Change in Retained Earnings (? R/E): ? R/E = 2008 Net Income � Dividends (30%) ? R/E = 8,226 � 2,468 = 5,758 4) Calculate 2008 Stockholder�s Equity: 2008 S/E = 2007 S/E + ? R/E 2008 S/E = 74,134 + 5,758 = 79,892 5) Create Initial Pro Forma Balance Sheet To Identify Net New Financing Needed

16. Table 17.3 First-Pass Pro Forma Balance Sheet for 2008

17. KMS Designs Example Step 3: Net New Financing At this point, the Balance Sheet does not balance: Assets ? Liabilities + Equity The difference between Assets and L+E indicates the Net New Financing to fund growth Assets � Liabilities/Equity 106,927 � 98,531 = 8,396 Net New Financing must be added to Debt or Equity (i.e., issue common or preferred stock) Assume Debt Create Final Balance Sheet

18. Table 17.4 Second-pass Pro Forma Balance Sheet for KMS

19. Practice Problem KMS now expects its sales to grow by only 10% next year. In addition, KMS plans on revising its dividend policy to distribute 50% of Net Income as dividends. Finally, any Net New Financing will be addressed through a common stock sale. Create the 2008 Pro Forma Income Statement and Balance Sheet for KMS using the Percent of Sales Method.

20. 17.3 Forecasting a Planned Expansion The Percent of Sales Method works as a short-term projection (1-year) for large stable companies, or as general overview, but ignores real-world �lumpy� investments in capacity. Most firms can�t buy half of a factory, or add additional retail space by the square foot. It is added in one lump investment in new P,P,+E. Thus, firms often make a large investment that will provide sufficient capacity for several years into the future. Firms will create detailed analysis to understand their market size and market share.

21. The Big Question Will the planned expansion increase the value of your company? Steps to answer the �big question� Identify capacity needs and financing options Construct pro forma income statements and forecast future cash flows Use forecasted free cash flows to assess the impact of expansion

22. The Starting Point: KMS Designs 2007 Income Statement and Balance Sheet

23. Table 17.5 KMS Forecasted Production Capacity Requirements If current capacity is 1,100 units, when does KMS need to expand?

24. Forecasting Expansion New PP+E expansion = \$20 million It must be purchased in 2008 to meet minimum capacity requirements Currently, KMS must invest \$5 million each year to replace depreciated equipment After expansion, KMS must invest \$8 million per year to replace depreciated equipment between 2009-2012

25. Table 17.6 KMS Forecasted Capital Expenditures

26. Equation 17.1 Financing the Expansion KMS will fund recurring investment from operating cash flows KMS finance the \$20 million investment in new PP+E by issuing 10-year coupon bonds at 6.8%.

27. Table 17.7 KMS Planned Debt and Interest Payments

28. Pro Forma Income Statement Value of new investment opportunity = future cash flows from investment Estimate cash flows: Project future earnings Consider working capital and investment needs and estimate free cash flow Compute value of company with/without expansion.

29. Forecast Earnings KMS Sales: 10.5 million units x 11% market share x 76.51 average sales price \$88.369 millions

30. KMS Example We still assume costs � depreciation = 78% of sales, thus: 78% x \$88,369 = \$68,928 With Sales, Costs, Depreciation, and Interest Expense, we can create Pro Forma Income Statements for 2008 � 2012

31. Table 17.8 Pro Forma Income Statement for KMS Expansion

32. Working Capital Requirements Increases in working capital reduce free cash flow; therefore, we must calculate working capital requirements for our firm KMS Example: We assume minimum cash requirements will remain 16% of sales, A/R = 19% of sales, Inventory = 20% of sales, A/P = 16% of sales as in 2007 *Assume Excess cash is distributed as dividends.

33. Table 17.9 KMS Projected Working Capital Needs

34. Forecasting the Balance Sheet Remember basic accounting equation, A=L+E. When L+E>A, excess cash is available Options: Build extra cash reserves Retire debt Distribute excess as dividends Repurchase shares Invest the cash When L+E<A, additional financing is needed

35. Table 17.10 Pro Forma Balance Sheet for KMS, 2008

36. Table 17.11 Pro Forma Balance Sheet and Financing

37. 17.4 Valuing the Planned Expansion We must total the net present value of the increase in cash flows generated by the additional investment. First, we calculate cash flows as they would occur. Thus: Start with Net Income Add additional tax shield from interest expense Add back depreciation (not a cash expense) Subtract NWC and Capital Expenditures

38. 17.5 Growth and Firm Value While the expansion we just analyzed for KMS turned out to be very valuable growth, not all growth is worth the price. It is possible to pay so much to enable the growth that the firm, on net, is worth less. Even if the cost of the growth is not an issue, other aspects of growth can leave the firm less valuable. For example, expansion may strain managers� capacity to monitor and handle the firm�s operations. It may surpass the firm�s distribution capabilities or quality control or even change customers� perceptions of the firm and its brand.