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