CS代考 CAGR10.44%<-- =(D14/D3)^(1/11)-1

PowerPoint Presentation

Chapter 6: Pro Forma Model for Caterpillar

Copyright By PowCoder代写 加微信 powcoder

Illustrate building of pro forma model
Use Caterpillar financial statements to project future ratios and financial statements
Derive weighted average cost of capital (WACC)
Value Caterpillar

Step 1: Project Sales growth
Three methods
CAGR (compound annual growth rate)

Regression
“Seat of the pants”—ignore historical data, use common sense (or “uncommon sense”)
CAGR is more common, but regression is better
CAGR is very influenced by end points
Regression takes into account all of the data

This slide deserves a lot of attention!
Data on sales
Regression of sales data on years
CAGR (cell B17)
CAGR as function of end points

Projecting 2012 Sales using CAGR and Regression

The regression slope of 3,169 means that each year sales are projected to increase by 3,169.

Picking ratios for the Pro Forma Model

This slide illustrates:
Analysis of the financial statements for a particular ratio
“Search for predictability”—looking for a number to represent the CA/Sales and CL/Sales in the future (this is an art, not a science)
In this case:
Both ratios are fairly stable
I’ve used the last number (2011) to predict the future
If there’s a problem, maybe we’ll do sensitivity analysis

Operating costs vs Sales: year-on-year

Operating costs vs Sales: regression

Choosing the net PPE/Sales ratio for the model

Net FA appears to better correlate with Sales

Dividends per share—growth rate
(Highlighted is choice for the model)

A Depreciation rate for the model

From the financial statement, I determined the % of each kind of asset (Buildings, Machinery, Leased equipment)
From the notes on the financial statement, I took the average asset life (32.5, 6.5, 5.5 years respectively)
Depreciation was defined as straight-line over these lives.
The weighted average of the depreciation goes into the model.

Free cash flow (FCF) and share value

WACC for Caterpillar: 10.84% (see Chapter 3)

Note on previous slide
The LT growth rate in the previous slide is a PURE GUESS!
We now do a sensitivity analysis—what WACC and LT growth will give Caterpillar share value > current market share value of $90.60?
As the analyst, you have to determine whether these numbers (WACC/Growth) convince you to recommend the stock (or vice versa)

Data table

Is this Data Table convincing – recommend SALE of Caterpillar!? The WACC/Growth combinations point to either much lower WACC or very high growth in order to justify the current stock price.

Endingsales
Beginningsales

ABCDEFGHIJKL
Revenues of
Total sales
200018,9131,26220,175
200119,0271,42320,4501.36%
200218,6481,50420,152-1.46%
200321,0481,75922,80713.17%
200428,3361,97030,30632.88%
200534,0062,33336,33919.91%
200628,8692,64831,517-13.27%
200741,9622,99644,95842.65%
200848,0443,28051,32414.16%
200929,5402,85632,396-36.88%
201039,8672,72142,58831.46%
201157,3922,74660,13841.21%
CAGR10.44%<-- =(D14/D3)^(1/11)-1 Slope3,169<-- =SLOPE(D3:D14,A3:A14) Slope/2011 5.27%<-- =B18/D14 Intercept-6,320,495<-- =INTERCEPT(D3:D14,A3:A14) R-squared0.7579<-- =RSQ(D3:D14,A3:A14) 2001-201111.39%<-- =(D14/D4)^(1/10)-1 2002-201112.92%<-- =(D14/D5)^(1/9)-1 2003-201112.88%<-- =(D14/D6)^(1/8)-1 2006-201113.79%<-- =(D14/D9)^(1/5)-1 2001-20108.49%<-- =(D13/D4)^(1/9)-1 2002-20109.80%<-- =(D13/D5)^(1/8)-1 2003-20109.33%<-- =(D13/D6)^(1/7)-1 2005-20103.22%<-- =(D13/D8)^(1/5)-1 Growth measures CAGR is very affected by end points! ANALYSIS OF CATERPILLAR SALES y = 3168.7x -6E+06R² = 0.7579010,00020,00030,00040,00050,00060,00070,000200020012002200320042005200620072008200920102011Caterpillar Sales Regressed on YearTotal sales and revenuesLinear (Total sales and revenues)-50%-40%-30%-20%-10%0%10%20%30%40%50%200120022003200420052006200720082009201020112012CATERPILLAR, YEAR-ON-YEAR GROWTH 2011 sales 60,138<-- =D14 CAGR 10.44%<-- =B17 Projected 2012 sales 66415.64<-- =H33*(1+H34) 2011 sales 60,138<-- =D14 Slope 3,169<-- =B18 Projected 2012 sales 63,307<-- =H38+H39 Projecting next year's sales using CAGR Projecting next year's sales using regression 20072008200920102011 CA/Sales54.17%56.30%68.99%66.26%58.32%<-- =SUM('Page 163'!F5:F9)/'Page 162'!F5 CL/Sales25.90%26.05%28.37%32.96%31.45%<-- =SUM('Page 163'!F27:F32)/'Page 162'!F5 Model values CA/Sales58.32%<-- =F3 CL/Sales31.45%<-- =F4 CATERPILLAR ANALYSIS OF RATIOS 0%10%20%30%40%50%60%70%80%20072008200920102011NWC AnalysisCA/SalesCL/Sales 20072008200920102011 Operating costs from P&L 40,03746,87631,81938,62552,985<-- ='Page 162'!F13 Depreciation 1,7971,9802,3362,2962,527<-- ='Page 164'!F5 Operating costs net of depreciation 38,24044,89629,48336,32950,458<-- =F3-F4 44,95851,32432,39642,58860,138<-- ='Page 162'!F5 Net operating costs/Sales 85.1%87.5%91.0%85.3%83.9%<-- =F5/F6 CATERPILLAR OPERATING COSTS 80%82%84%86%88%90%92%20072008200920102011Net operating costs/Sales Intercept 3,833<-- =INTERCEPT(B5:F5,B6:F6) Slope 77.89%<-- =SLOPE(B5:F5,B6:F6) R-squared 99.14%<-- =RSQ(B5:F5,B6:F6) Regressing net operating costs on sales y = 0.7789x + 3833.3R² = 0.9914010,00020,00030,00040,00050,00060,00025,00030,00035,00040,00045,00050,00055,00060,00065,000Net Operating Costs vs Sales 20072008200920102011 Land 189575639682753 Buildings and land improvements 4,6474,9145,1745,857 Machinery, equipment and other 12,17312,91713,41414,435 Equipment leased to others 4,5614,7174,4444,285 Construction-in-process 1,5311,0341,1921,996 Total property, plant and equipment, at cost 19,20823,48724,22124,90627,326 Less: Accumulated depreciation -10,963-11,835-12,367-12,931 Property, plant and equipment—net 9,99712,52412,38612,53914,395 44,95851,32432,39642,58860,138 Net PPE/Sales 22.24%24.40%38.23%29.44%23.94% Gross PPE/Sales 42.72%45.76%74.77%58.48%45.44% Model value: Net PPE/Sales 25.00%<-- =AVERAGE(B15,C15,E15,F15) CATERPILLAR—ANALYSIS OF FIXED ASSETS y = 0.102x + 19108R² = 0.1265y = 0.0731x + 8983.3R² = 0.232705,00010,00015,00020,00025,00030,00030,00035,00040,00045,00050,00055,00060,00065,000Gross and Net Fixed Assets vs SalesGross FANet FA 20072008200920102011 Dividends 8459531,0291,0841,159 Year-on-year growth 12.78%7.97%5.34%6.92% Total dividend CAGR 2007-2011 8.22%<-- =(F3/B3)^(1/4)-1 2008-2011 6.74%<-- =(F3/C3)^(1/3)-1 Dividends per share 1.381.621.681.741.82 Dividend per share CAGR 2007-2011 7.16%<-- =(F9/B9)^(1/4)-1 2008-2011 3.96%<-- =(F9/C9)^(1/3)-1 CATERPILLAR DIVIDENDS 20072008200920102011 Other liabilities 2,0032,1902,4962,6543,583 Year-on-year growth 9.34%13.97%6.33%35.00% Whole period 15.65%<-- =(F3/B3)^(1/4)-1 Excluding 2011 9.83%<-- =(E3/B3)^(1/3)-1 Pension liabilities 5,0599,9757,4207,58410,956<-- ='Page 163'!F41 Year-on-year growth 97.17%-25.61%2.21%44.46%<-- =F8/E8-1 Whole period 21.31%<-- =(F8/B8)^(1/4)-1 Excluding 2011 14.45%<-- =(E8/B8)^(1/3)-1 Model value 17.88%<-- =AVERAGE(B10:B11) OTHER LIABILITIES AND PENSIONS 20072008200920102011 Profits before taxes 4,9904,5015693,7506,725<-- ='Page 162'!F20 Taxes 1,485953-2709681,720<-- ='Page 162'!F22 Tax rate 29.76%21.17%-47.45%25.81%25.58%<-- =F4/F3 Model value 25.58%<-- =AVERAGEIF(B5:F5,">0″)
CATERPILLAR ANALYSIS OF TAX RATE

200920102011
Sales44,95851,32432,39642,58860,138<-- ='Page 162'!F5 Cash 1,1222,7364,8673,5923,057<-- ='Page 163'!F4 Cash/Sales2.50%5.33%15.02%8.43%5.08%<-- =F5/F3 Debt28,42935,53531,63128,41834,592<-- ='Page 165'!F19 Debt/Sales63.23%69.24%97.64%66.73%57.52%<-- =F7/F3 Debt/Total assets50.65%52.43%52.68%44.39%42.47%<-- =F7/'Page 163'!F20 Model values Cash/Sales5.08%<-- =F6 Debt/Assets42.47%<-- =F9 CATERPILLAR: VARIOUS PLUGS vs SALES AND OTHER Depreciation analysis %Buildings and land improvements 20.21%21.73%21.79%22.46%23.83%<-- =F4/SUM(F$4:F$6) %Machinery, equipment and other 54.39%56.93%57.29%58.24%58.73%<-- =F5/SUM(F$4:F$6) %Equipment leased to others 25.40%21.33%20.92%19.29%17.44%<-- =F6/SUM(F$4:F$6) Depreciation rates Buildings and land improvements 3.1%<-- =1/32.5 Machinery, equipment and other 15.4%<-- =1/6.5 Equipment leased to others 18.2%<-- =1/5.5 Average depreciation rate 13.61%13.31%13.29%13.16%12.94% Model depreciation rate 13.26%<-- =AVERAGE(B28:F28) =SUMPRODUCT($B$25: $B$27,F20:F22) Free Cash Flow PAT 4,4334,2104,0353,7943,474<-- =G27 Add back depreciation 3,7114,5245,3016,1967,225<-- =-G24 Minus increase in CA -1,848-1,945-2,048-2,156-2,269<-- =F33-G33 Add back increase in CL 9971,0491,1041,1631,224<-- =G47-F47 Subtract CAPEX -6,820-5,447-6,272-7,218-8,301<-- =-(SUM(G35:G37)-SUM(F35:F37)) Add back net interest after taxes 389406423442461<-- =-(1-$B$15)*(G22-G23) Free Cash Flow 8612,7972,5442,2201,814<-- =SUM(G60:G66) WACC 10.84% Long-term FCF growth 9.00% Year 201120122013201420152016 FCF 8612,7972,5442,2201,814<-- =G67 Terminal value 107,487<-- =G74*(1+B71)/(B70-B71) Total 8612,7972,5442,220109,301<-- =G74+G75 Enterprise value: PV of FCFs and terminal value 75,515<-- =NPV(B70,C76:G76)*(1+B70)^0.5 Add back initial cash 3,057<-- =B32 Asset value 78,572<-- =B78+B79 Subtract year 0 debt -49,604<-- =-SUM(B48:B51) Imputed equity value 28,968<-- =B80+B81 Divide by # shares outstanding 647,533,344<-- ='Page 163'!F58 Share value 44.74<-- =B82/B83*1000000 Current market value per share 90.60 LT growth rate ↓ 6.0%7.0%8.0%9.0%10.0% 84.25374.31 =IF(B70>B71,B84,””)
46.13153.15
7.9742.51146.14
-11.145.5939.04139.41
-22.63-12.903.3035.71132.95
-30.31-24.03-14.601.1032.51
-43.18-40.79-37.72-33.63-27.91

/docProps/thumbnail.jpeg

程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com