Get documents about "Prob 08 01 solution
Document Sample
Prob 8-1, Parts d, e, and f, continued. Compare financial statement projections for
2012 using Chapter 7's assumptions vs. using regression method on selected items.
Projections, all
items based on Projections with
Chapter 7's selected items based on
assumptions. regression methods.
Van Leer Products, Inc. Actual Projected Projected
Income Statement 2011 2012 2012
Net Sales 1,000.0 1,110.0 1,110.0
Cost Of Goods Sold 640.0 693.8 721.4
Selling, general & administrative 215.0 249.8 234.1
Depreciation 45.0 56.6 49.8
Operating profit 100.0 109.9 104.7
Interest income - 0.8 0.8
Interest expense 10.0 11.2 11.2
Earnings before taxes 90.0 99.5 94.3
Taxes 36.0 39.5 37.4
Net income 54.0 60.0 56.9
Dividends 16.0 17.6 17.6
Additions to RE 38.0 42.4 39.3
Balance sheet
Cash 50.0 33.3 54.9
Short term investments 25.0 - -
Inventory 100.0 122.1 109.7
Accounts receivable 75.0 84.4 80.0
Total current assets 250.0 239.8 244.6
Net PP&E 300.0 377.4 331.8
Total assets 550.0 617.2 576.4
Accounts payable 75.0 89.9 74.8
Accrued expenses 10.0 11.1 11.9
Short-term debt 25.0 40.2 23.0
Total current liabilities 110.0 141.2 109.7
Long-term debt 99.0 92.6 86.5
Total liabilities 209.0 233.8 196.2
Common stock 125.0 125.0 125.0
Retained earnings 216.0 258.4 255.3
Total common equity 341.0 383.4 380.3
Total liabilities and equity 550.0 617.2 576.4
Making the sheets balance Projected Projected
2012 2012
Specified assets 617.2 576.4
Specified liabilities 577.0 553.5
Asset-liability gap 40.2 23.0
Discrepancy (balance check) - -
How to project this item
Projection of FCF tab
Projection of FCF tab
Projection of FCF tab
Projection of FCF tab
Ch. 7
Ch. 7
Ch. 7
Ch. 7
Projection of FCF tab
plug--formula already entered
Projection of FCF tab
Projection of FCF tab
Projection of FCF tab
Projection of FCF tab
Projection of FCF tab
plug--formula already entered
Ch. 7
Constant (Ch. 7)
Prob 8-1, Parts d, e, and f. Compare projections for 2012 using Chapter 7's assumptions vs.
using regression method on selected items. When finished here, continue on the Proj. of
Financials & Valuation tab.
Projections, all items
based on Chapter 7's
Van Leer Products, Inc. Actual assumptions.
Income Statement 2011 2012
Net Sales 1,000.0 1,110.0
Cost Of Goods Sold 640.0 693.8
Selling, general & administrative 215.0 249.8
Depreciation 45.0 56.6
Operating profit 100.0 109.9
Balance sheet
Cash 50.0 33.3
Inventory 100.0 122.1
Accounts receivable 75.0 84.4
Total operating current assets 225.0 239.8
Net PP&E 300.0 377.4
Total net operating assets 525.0 617.2
Accounts payable 75.0 89.9
Accrued expenses 10.0 11.1
Total operating current liabilities 85.0 101.0
Free Cash Flow Calculations
Operating Income 100.0 109.9
Tax on Operating Income 40.0 43.6
NOPAT 60.0 66.3
Net Operating WC 140.0 138.8
Net Operating Long Term Assets 300.0 377.4
Total Net Operating Assets 440.0 516.2
Investment in net operating assets 38.0 76.2
Free Cash Flow 22.0 -9.9
ROIC 14.9% 15.1%
ing Chapter 7's assumptions vs.
here, continue on the Proj. of
Projections with
selected items based on
regression methods
where indicated in the
problem.
2012 How to project this item
1,110.0 Ch. 7
721.4 a + b(Sales)
234.1 a + b(Sales)
49.8 Ch. 7
104.7
54.9 a + b ln(Sales)
109.7 a + b ln(Sales)
80.0 a + b ln(Sales)
244.6
331.8 a + b ln(Sales)
576.4
74.8 a + b ln(Sales)
11.9 a + b ln(Sales)
86.7
104.7
41.6
63.1
157.9
331.8
489.7
49.7
13.4
14.4%
Van Leer Products, Inc.
Historical Free Cash Flow Calculations (dollar amounts in millions)
Actual Actual Actual Actual
2007 2008 2009 2010
Operating Income 69.0 63.0 79.0 72.0
Tax on Operating Income (40%) 27.1 24.1 31.6 28.1
NOPAT 41.9 38.9 47.4 43.9
Net Operating WC 88.5 86.0 94.0 122.0
Net Operating Long Term Assets 200.0 270.0 275.0 280.0
Total Net Operating Assets 288.5 356.0 369.0 402.0
Investment in net operating assets 67.5 13.0 33.0
Free Cash Flow -28.6 34.4 10.9
growth in FCF -220.5% -68.4%
ROIC 13.3% 11.9%
Calculation of Historical Ratios Actual Actual Actual Actual
2007 2008 2009 2010
Ratios to calculate operating profit
Sales growth rate na 3.90% 5.00% 12.38%
COGS / Sales 64.94% 63.75% 61.90% 66.21%
SGA / Sales 22.21% 23.38% 23.81% 21.72%
Depreciation / Net PPE 15.00% 14.81% 14.91% 15.00%
Ratios to calculate operating capital
Cash / Sales 4.87% 4.88% 5.00% 4.98%
Inventory/ Sales 8.31% 8.75% 8.93% 9.00%
Accts. Rec. / Sales 7.79% 7.75% 7.74% 7.42%
Net PPE / Sales 25.97% 33.75% 32.74% 29.66%
Accts. Pay./ Sales 8.70% 9.63% 9.52% 7.42%
Accruals / Sales 0.78% 1.00% 0.95% 1.06%
Ratios to calculate operating taxes
Tax Rate (Taxes/EBT) 39.34% 38.19% 40.00% 39.06%
Dividend and debt ratios
Dividend policy: growth rate na 0.00% 50.00% -8.33%
Long-term Debt / operating assets 40.25% 44.90% 11.82% 17.43%
Interest Rates
Interest rate on short-term invest. na 10.00% 10.00% 10.00%
Interest rate on debt na 8.70% 8.70% 8.70%
Actual
2011
100.0
40.0
60.0
140.0
300.0
440.0
38.0
22.0
102.3%
14.9%
Actual
Percentages
5-year from Ch. 7
2011 Average for 2012
5.93% 6.80% 11.00%
64.00% 64.16% 62.50%
21.50% 22.52% 22.50%
15.00% 14.94% 15.00%
5.00% 4.99% 3.00%
10.00% 9.31% 11.00%
7.50% 7.55% 7.60%
30.00% 30.80% 34.00%
7.50% 8.15% 8.10%
1.00% 1.00% 1.00%
40.00% 39.69% 39.70%
45.45% 29.04% 10.00%
18.86% 16.03% 15.00%
0.00% 6.67% 3.00%
8.80% 8.73% 9.00%
Prob 8, Parts a & b. Insert two columns to the left of the first
year of actual data. Insert a new row for the natural log of sales
right above the current row for net sales.
Prob 8, Part c. Enter the regression INTERCEPT and SLOPE
functions for selected accounts with yellow cells. When done,
click on the Projection of FCF tab for parts d, e, and f.
Van Leer Products, Inc. Actual Actual
Regression Regression
Income Statement intercept slope 2007 2008
ln(Sales) 2.88649 2.90309
Net Sales 770.0 800.0
Cost Of Goods Sold -32.15 0.6789 500.0 510.0
Selling, general & administrative 55.36 0.1610 171.0 187.0
Depreciation 30.0 40.0
Operating profit 69.0 63.0
Interest income 0.0 0.0
Interest expense 13.1 18.5
Earnings before taxes 55.9 44.5
Taxes 22.0 17.0
Net income 33.9 27.5
Dividends 8.0 8.0
Additions to RE 25.9 19.5
Balance sheet 2007 2008
Cash -279.70 109.8787 37.5 39.0
Short term investments 0 0
Inventory -765.79 287.4943 64 70
Accounts receivable -303.96 126.0698 60 62
Total current assets 161.5 171.0
Net PP&E -1560.54 621.4036 200 270
Total assets 361.5 441.0
Accounts payable 46.11 9.4262 67 77
Accrued expenses -87.12 32.5138 6 8
Short-term debt 18 33
Total current liabilities 91.0 118.0
Long-term debt 145.5 198.0
Total liabilities 236.5 316.0
Common stock 125.0 125.0
Retained earnings
Total common equity 125.0 125.0
Total liabilities and equity 361.5 441.0
Actual Actual Actual
2009 2010 2011
2.92428 2.97497 3.00000
840.0 944.0 1,000.0
520.0 625.0 640.0
200.0 205.0 215.0
41.0 42.0 45.0
79.0 72.0 100.0
0.0 1.0 0.0
9.0 9.0 10.0
70.0 64.0 90.0
28.0 25.0 36.0
42.0 39.0 54.0
12.0 11.0 16.0
30.0 28.0 38.0
2009 2010 2011
42.0 47.0 50.0
10.0 15.0 25.0
75.0 85.0 100.0
65.0 70.0 75.0
192.0 217.0 250.0
275.0 280.0 300.0
467.0 497.0 550.0
80.0 70.0 75.0
8.0 10.0 10.0
50.0 30.0 25.0
138.0 110.0 110.0
54.0 84.0 99.0
192.0 194.0 209.0
125.0 125.0 125.0
150.0 178.0 216.0
275.0 303.0 341.0
467.0 497.0 550.0
