Prob 08 01 solution

Document Sample
Prob 08 01 solution Powered By Docstoc
					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

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:11
posted:9/14/2012
language:English
pages:12