emilyneeley_unit8_Challenge

# emilyneeley_unit8_Challenge - 4,600,000 5,510,000 7,330,000...

This preview shows page 1. Sign up to view the full content.

Unit 8 Excel Spreadsheet (no formulas) Step1: Calculation of cash Flows Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Sales(in Units) 70,000 80,000 100,000 85,000 75,000 Sales(in Dol ar) 23,800,000 27,200,000 34,000,000 28,900,000 25,500,000 Less: Variable Cost 14,000,000 16,000,000 20,000,000 17,000,000 15,000,000 Fixed Costs (4,500,000) (4,500,000) (4,500,000) (4,500,000) (4,500,000) Less: Depreciation 3,300,000 3,300,000 3,300,000 3,300,000 3,300,000 Earning Before Tax 2,000,000 3,400,000 6,200,000 4,100,000 2,700,000 Less: Taxes 700,000 1,190,000 2,170,000 1,435,000 945,000 Earning After Tax 1,300,000 2,210,000 4,030,000 2,665,000 1,755,000 Add: Depreciation 3,300,000 3,300,000 3,300,000 3,300,000 3,300,000 Cash Flow from Operations
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 4,600,000 5,510,000 7,330,000 5,965,000 5,055,000 Initial Investment-\$16,500,000 Investment in Working Capital-\$6,000,000 \$4,600,000 5,510,000 7,330,000 5,965,000 5,055,000 Net Cash Flow 1. What is the IRR of the project? Year Cash Flow \$(6,000,000) Capital souweights after tax coWACC 1 \$4,600,000 debt 0.4 0.08 0.032 2 \$5,510,000 common st 0.6 0.16 0.096 3 \$7,330,000 0.128 4 \$5,965,000 5 \$5,055,000 i IRR 84.95% 2. What is the NPV of the project, based on the required rate of return of 12%? PV Factor @12% Year Cash Flow 1 Present Value \$12,383,083.20 \$(6,000,000) (6,000,000.00) 1 \$4,600,000 2 \$5,510,000- 3 \$7,330,000- 4 \$5,965,000- 5 \$5,055,000- NPV \$12,383,083.20...
View Full Document

{[ snackBarMessage ]}

Ask a homework question - tutors are online