Chapter 11_Build a Model Spreadsheet - MARINA MORGAN...

This preview shows pages 1–2. Sign up to view the full content.

MARINA MORGAN 11/30/2011 Chapter 11. Solution to Ch 11-18 Build a Model a. Develop a spreadsheet model, and use it to find the project’s NPV, IRR, and payback. Key Output NPV = \$3,463 Part 1. Input Data (in thousands of dol ars) IRR = 21.1% MIRR = 17.0% Equipment cost \$10,000 Net WC/Sales 10% Market value of equipment at Year 4 \$500 First year sales (in units) 1,000 Tax rate 40% Sales price per unit \$24.00 WACC 10% Variable cost per unit \$17.50 Inflation 3.0% Nonvariable costs \$1,000 Part 2. Depreciation and Amortization Schedule Years Accum'd Year Initial Cost 1 2 3 4 Depr'n Equipment Depr'n Rate 20.0% 32.0% 19.0% 12.0% Equipment Depr'n, Dol ars \$2,000 \$3,200 \$1,900 \$1,200 \$8,300 10,000 \$1,700 Part 3. Net Salvage Values, in Year 4 Equipment Estimated Market Value in Year 4 \$500 Book Value in Year 4 1,700 Expected Gain or Loss -1,200 Taxes paid or tax credit -480 Net cash flow from salvage \$980 Part 4. Projected Net Cash Flows (Time line of Annual Cash Flows) Years 0 1 2 3 4 Investment Outlays at Time Zero: Equipment (\$10,000) Operating Cash Flows over the Project's Life: Units sold 1,000 1,000 1,000 1,000 Sales price \$24.00 \$24.72 \$25.46 \$26.23 Variable costs \$17.50 \$18.03 \$18.57 \$19.12 Sales revenue \$24,000 \$24,720 \$25,462 \$26,225 Variable costs 17,500 18,025 18,566 19,123 Nonvariable operating costs 1,000 1,030 1,061 1,093 Depreciation (equipment) 2,000 3,200 1,900 1,200 Oper. income before taxes (EBIT) \$3,500 \$2,465 \$3,935 \$4,810 Taxes on operating income (40%) 1,400 986 1,574 1,924 After-tax operating income \$2,100 \$1,479 \$2,361 \$2,886 Add back depreciation 2,000 3,200 1,900 1,200 Operating cash flow \$4,100 \$4,679 \$4,261 \$4,086 Terminal Year Cash Flows: Required level of net working capital \$2,400 \$2,472 \$2,546 \$2,623 \$0 Required investment in NWC (\$2,400) (\$72) (\$74) (\$76) \$2,623 Terminal Year Cash Flows: Net salvage value 980 Net Cash Flow (Time line of cash flows) (\$12,400) \$4,028 \$4,605 \$4,185 \$7,689 Part 5. Key Output: Appraisal of the Proposed Project Net Present Value (at 10%) \$3,463 IRR 21.09% MIRR 16.99% Payback (See calculation below) 2.90 3 Data for Payback Years 0 1 2 3 4 Net cash flow (12,400) 4,028

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 2

Chapter 11_Build a Model Spreadsheet - MARINA MORGAN...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online