Develop a spreadsheet model, and use it to find the project\'s NPV and MIRR Key

Question

Develop a spreadsheet model, and use it to find the project's NPV and MIRR Key Output: NPV - art 1. Input Data (in thousands of dollars) MIRR uipment cost et WC/Sales irst year sales (in units) ales price per unit ariable cost per unit onvariable costs $12,500 5% 1,100 $28.00 $18.00 $1,450 $1,500 40% 12% 2.5% arket value of equipment at Year 4 ax rate ACC nflation Years Accum'd art 2. Depreciation and Amortization Schedul Initial Cost 2 4Depr'n ear 20.0% 32.0% 19.0% 12.0% quipment Depr'n Rate quipment Depr'n, Dollars nding Bk Val: Cost - Accum Dep'rn Equipment art 3. Net Salvage Values, in Year 4 stimated Market Value in Year 4 ook Value in Year 4 xpected Gain or Loss axes paid or tax credit et cash flow from salvage

Explanation / Answer

The completed tables are provided as below:

_____

The formula for calculating NPV is given as below:

NPV = Net Cash Flow Year 0 + Net Cash Flow Year 1/(1+WACC)^1 + Net Cash Flow Year 2/(1+WACC)^2 + Net Cash Flow Year 3/(1+WACC)^3 + Net Cash Flow Year 4/(1+WACC)^4 = -15,272 + 6,661/(1+12%)^1 + 7,402/(1+12%)^2 + 6,897/(1+12%)^3 + 11,506/(1+12%)^4 = $8,798

____

The MIRR is calculated with the use of MIRR function of EXCEL as provided below:

=MIRR(values,finance_rate,reinvest_rate) where values = Cash flows from Year 0 to Year 4 (you will have to select all the values in the spreadsheet, finance_rate = 0 and reinvest_rate = 12% (WACC)

Part 1. Input Data (in thousands of dollars) MIRR = 25.5% Equipment cost $12,500 Net Operating WC/sales 9% Market value of equipment at Year 4 $1,500 First year sales (in units) 1,100 Tax rate 40% Sales price per unit $28.00 WACC 12% Variable cost per unit $18.00 Inflation 2.5% Non-variable costs $1,450 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, Dollars $2,500 $4,000 $2,375 $1,500 $10,375 Ending Bk Val: Cost - Accum Dep'rn $2,125 Part 3. Net Salvage Values, in Year 4 Equipment Estimated Market Value in Year 4 $1,500 Book Value in Year 4 2,125 Expected Gain or Loss -625 Taxes paid or tax credit -250 Net cash flow from salvage $1,750 Part 4. Projected Net Cash Flows (Time line of annual cash flows) Years 0 1 2 3 4      Investment Outlays at Time Zero: Equipment -12,500      Operating Cash Flows over the Project's Life: Units sold 1,100 1,100 1,100 1,100 Sales price $28.00 $28.70 $29.42 $30.15 Variable costs $18.00 $18.45 $18.91 $19.38 Sales revenue $30,800 $31,570 $32,359 $33,168 Variable costs 19,800 20,295 20,802 21,322 Non-variable operating costs 1,450 1,486 1,523 1,561 Depreciation (equipment) 2,500 4,000 2,375 1,500 Oper. income before taxes (EBIT) 7,050 5,789 7,658 8,784 Taxes on operating income (40%) 2,820 2,316 3,063 3,514 After-tax operating income 4,230 3,473 4,595 5,271 Add back depreciation 2,500 4,000 2,375 1,500          Operating cash flow $6,730 $7,473 $6,970 $6,771      Terminal Year Cash Flows: Required level of net operating working capital $2,772 $2,841 $2,912 $2,985 $0 Required investment in NWC ($2,772) ($69) ($71) ($73) $2,985      Terminal Year Cash Flows: Net salvage value 1,750 Net Cash Flow (Time line of cash flows) ($15,272) $6,661 $7,402 $6,897 $11,506

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.