Prob 05 01 by tBl2zAf

VIEWS: 26 PAGES: 15

									Prob 6-1, Part b. First enter formulas in the tan
cells (those items related to operations that were
determined in Prob 5-1 in the Projection of FCF
tab). For now, enter zeros for short-term           Prob 7-1, Part d. Extend the projections by copy
investments and short-term debt.                    the formulas in the tan cells into the blue cells.
Van Leer Products, Inc. Actual Projected Projected Projected Projected
Income Statement                     2003      2004      2005      2006       2007
Net Sales                         1,000.0
Cost Of Goods Sold                  640.0
Selling, general & administrative   215.0
Depreciation                         45.0
                Operating profit    100.0
Interest income                       -
Interest expense                     10.0
          Earnings before taxes      90.0
Taxes                                36.0
                   Net income        54.0
Dividends                            16.0
Additions to RE                      38.0


Balance sheet
Cash                               50.0
Short term investments             25.0
Inventory                         100.0
Accounts receivable                75.0
           Total current assets   250.0
Net PP&E                          300.0
                  Total assets    550.0

Accounts payable                        75.0
Accrued expenses                        10.0
Short-term debt                         25.0
         Total current liabilities     110.0
Long-term debt                          99.0
                  Total liabilities    209.0
Common stock                           125.0
Retained earnings                      216.0
          Total common equity          341.0
   Total liabilities and equity        550.0
Prob 6-1, Parts c, d, e, f & g. Fill in
formulas in the tan cells below.             Projected Projected Projected Projected
Making the sheets balance                     2004    2005     2006     2007
                      Specified assets
                   Specified liabilities
                    Asset-liability gap
          Discrepency (balance check)
Prob 7-1, Part e, f, g. Calculate the value of
operations, value of equity, and stock price in the
blue cells below.
Valuation
Free cash flow
Growth rate in free cash flow
WACC

Horizon value                   na       na       na       na       na
Value of operations              #DIV/0!  #DIV/0!  #DIV/0!  #DIV/0!  #DIV/0!

Value of investments
Total value of firm
Value of all debt, preferred
stock, and other nonoperating
liabilities
Value of equity
Number of shares (millions)
Estimated price per share,
end of 2003 fiscal year
end the projections by copying
an cells into the blue cells.
            Projected Projected Projected
                2008        2009    2010




          Projected Projected Projected
    2008      2009   2010




na         #DIV/0!
 #DIV/0!   #DIV/0!
Prob 5-1, Part c. Using the ratios and growth rates from the
Inputs tab, enter formulas (in the yellow cells) to calculate the   Prob 7-1, Parts b&c. Extend the projection
projections needed for determining free cash flow. Compare          by copying the formulas from 2004 through
your results to Exhibit 5-5.                                        2010 in the blue cells.
Van Leer Products, Inc.                   Actual       Projected    Projected Projected
Income Statement                                2003        2004         2005       2006
Net Sales                                    1,000.0
Cost Of Goods Sold                             640.0
Selling, general & administrative              215.0
Depreciation                                     45.0
                  Operating profit             100.0

Balance sheet
Cash                                            50.0
Inventory                                      100.0
Accounts receivable                             75.0
   Total operating current assets              225.0
Net PP&E                                       300.0
    Total net operating assets                 525.0

Accounts payable                                75.0
Accrued expenses                                10.0
 Total operating current liabilities            85.0


Free Cash Flow Calculations
Operating Income                                 0.0
Tax on Operating Income                          0.0
NOPAT                                            0.0
Net Operating WC                                 0.0
Net Operating Long Term Assets                   0.0
Total Net Operating Assets                       0.0
Investment in net operating assets               0.0
Free Cash Flow                                   0.0
ROIC                                          0.00%
arts b&c. Extend the projections
the formulas from 2004 through
 blue cells.
             Projected Projected Projected Projected
                  2007      2008      2009      2010
                                                                   Prob 7-1, Part a. Enter ratio
Prob 5-1, Part b. Enter ratio                                      projections (in yellow and blue
projections (in yellow cells) for                                  cells) for 2004-2010 as
2004 as discussed in the text and                      Projected   discussed in the text and shown
shown in Exhibit 5-4.                                  Parameters in Exhibits 7-4 and 7-5.
Van Leer Products, Inc.                 3-year Average        2004       2005        2006
Ratios to calculate operating proft
Sales growth rate                              0.00%
COGS / Sales                                   0.00%
SGA / Sales                                    0.00%
Depreciation / Net PPE                         0.00%
Ratios to calculate operating capital
Cash / Sales                                   0.00%
Inventory/ Sales                               0.00%
Accts. Rec. / Sales                            0.00%
Net PPE / Sales                                0.00%
Accts. Pay./ Sales                             0.00%
Accruals / Sales                               0.00%
Ratios to calculate operating taxes
Tax Rate (Taxes/EBT)                           0.00%



                                                                   Prob 7-1. Enter financial policy
Prob 6-1, Part a. Enter financial policy assumptions and           assumptions and interest rates in
interest rates in tan cells. (As in Exhibit 6-3.)                  tan and blue cells.
Dividend and debt ratios
Dividend policy: growth rate                     0.00%
Long-term Debt / operating assets                0.00%
Interest Rates
Interest rate on short-term invest.              0.00%
Interest rate on debt                            0.00%


Problem 6-2. Change the percentages under the projected parameters column one at a
time, as indicated in the text. Cells B34 and B35 have the base case results, and cells
H10 and H11 show the results from changing the inputs. Be sure to change each
parameter back to the base case before changing the next parameter. Fill in the table
below with the results.
                                            Base          New
Short-term investments
Short-term debt                         New s-t                    New s-t
                                        investments    Change      debt       Change
Sales growth + 1%
CGS/Sales + 1%
SGA/Sales + 1%
Depr./Sales + 1%
Cash/Sales + 1%
Inv./Sales + 1%
Accts. Rec/Sales + 1%
Accruals/Sales + 1%
Tax rate + 1%
Dividend g + 1%
L-t debt/Op. Assets + 1%




Prob 7-2. Change one rate or ratio as indicated in the sensitivity analysis chart reproduced
below. Construct a table that summarizes the resulting intrinsic values.
Then continue on to the Proj. of Financials & Valuation tab and record the changes in the
estimated intrinsic value of Van Leer's stock, before returning to this tab to change the next
rate or ratio. [Only change one rate or ratio at a time, then reset it before going on to the next
change.]
Hint: To avoid having to switch back and forth between tabs, just create an entry in the
Inputs tab that copies the intrinsic value estimate from the the Proj. of Financials & Valuation
tab. After each change to the inputs, manually copy the numerical value from this cell into
the Table below before making the next change to the Inputs tab.

Sensitivity analysis:
parameter affected                    change
base case scenario                    none -- keep original inputs
a. sales growth rate                  decrease by 1% for every year
b. COGS/Sales ratio                   increase by 2 percentage points for every year
c. Cash/sales ratio                   increase to 5% of sales for every year

d. market risk premium (WACC tab) increase by 1 percentage point
e. SGA/Sales                      increase to 25% of sales in every year


Q: Of the four changes, which has
the most severe consequences for
estimated value?                  A:
art a. Enter ratio
 in yellow and blue
04-2010 as
n the text and shown
 -4 and 7-5.
                   2007   2008   2009   2010




nter financial policy
s and interest rates in
e cells.
hart reproduced

changes in the
o change the next
oing on to the next

n entry in the
ancials & Valuation
rom this cell into
                      Share value



          share
          value
Van Leer Products, Inc.

Cost of Capital Calculation

Proportions of Long-term Debt
and Equity Financing                  Value   Source
Current market price of stock=        $40.12 Given, per share
Shares of common stock
outstanding (millions)=                    10 Given, in millions
Current market value of equity=       $401.20 Price per share multiplied by shares outstanding

Book value of long-term debt=             $99 From financial statements in Actual worksheet
Current ratio of book value of debt
to sum of book value of debt and
market value of equity=                 19.8% Calculated from above info
Target ratio of book value of debt
to sum of book value of debt and
market value of equity=                 19.8% Judgment, based on historical ratios in Hist Analys sheet a

Target ratio of market value of
equity to sum of book value of
debt and market value of equity=        80.2% = 1 - debt ratio

Estimate Cost of Equity
Beta=                                    1.40   Given
Risk-free rate=                          6.0%   Given
Market risk premium =                    5.0%   Given
Cost of equity=                         13.0%   Calculated from above info

Cost of Debt
Interest rate on debt=                   0.0% From Inputs Worksheet
Tax rate=                                0.0% From Inputs Worksheet
After-tax cost of long-term debt=        0.0% Calculated from above info

Calculation of WACC
            WACC=                      10.43% Calculated from above info
es outstanding

al worksheet




os in Hist Analys sheet and market conditions.
Van Leer Products, Inc.
Prob 5-1, Part a. Enter formulas (in yellow cells) that use information from the actual
financial statements (on the "Actual" tab) to calculate free cash flow, ratios, and
growth rates that represent the historical performance of Van Leer. Check your
results against Exhibits 5-3 and 5-5.

Historical Free Cash Flow Calculations (dollar amounts in millions)
                                         Actual   Actual   Actual
                                             2001     2002     2003
Operating Income
Tax on Operating Income (40%)
NOPAT
Net Operating WC
Net Operating Long Term Assets
Total Net Operating Assets
Investment in net operating assets
Free Cash Flow
growth in FCF
ROIC

Calculation of Historical Ratios         Actual   Actual   Actual
                                             2001     2002     2003 3-year Average
Ratios to calculate operating profit
Sales growth rate                               na
COGS / Sales
SGA / Sales
Depreciation / Net PPE
Ratios to calculate operating capital
Cash / Sales
Inventory/ Sales
Accts. Rec. / Sales
Net PPE / Sales
Accts. Pay./ Sales
Accruals / Sales
Ratios to calculate operating taxes
Tax Rate (Taxes/EBT)
Dividend and debt ratios
Dividend policy: growth rate                    na
Long-term Debt / operating assets
Interest Rates
Interest rate on short-term invest.             na
Interest rate on debt                           na
Van Leer Products, Inc.

         Annual Financial Information (millions or dollars)
                                     Actual     Actual     Actual
Income Statement                          2001       2002       2003
Net Sales                                840.0      944.0    1,000.0
Cost Of Goods Sold                       520.0      625.0      640.0
Selling, general & administrative        200.0      205.0      215.0
Depreciation                               41.0      42.0        45.0
                    Operating profit       79.0      72.0      100.0
Interest income                             0.0        1.0        0.0
Interest expense                            9.0        9.0       10.0
             Earnings before taxes         70.0      64.0        90.0
Taxes                                      28.0      25.0        36.0
                      Net income           42.0      39.0        54.0
Dividends                                  12.0      11.0        16.0
Additions to RE                            30.0      28.0        38.0

                                    Actual    Actual     Actual
Balance sheet                            2001      2002       2003
Cash                                     42.0       47.0       50.0
Short term investments                   10.0       15.0       25.0
Inventory                                75.0       85.0     100.0
Accounts receivable                      65.0       70.0       75.0
               Total current assets     192.0     217.0      250.0
Net PP&E                                275.0     280.0      300.0
                      Total assets      467.0     497.0      550.0

Accounts payable                           80.0      70.0       75.0
Accrued expenses                            8.0      10.0       10.0
Short-term debt                            50.0      30.0       25.0
             Total current liabilities    138.0     110.0      110.0
Long-term debt                             54.0      84.0       99.0
                      Total liabilities   192.0     194.0      209.0
Common stock                              125.0     125.0      125.0
Retained earnings                         150.0     178.0      216.0
              Total common equity         275.0     303.0      341.0
       Total liabilities and equity       467.0     497.0      550.0

								
To top