Chapter 11_Build a Model Spreadsheet

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

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

View Full Document Right Arrow Icon
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
Background image of page 1

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

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

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 Right Arrow Icon
Ask a homework question - tutors are online