You are the Finance Department of Apple and your team has been asked to make a goo-go decision on the iPhone 11 based solely on a financial perspective.
Calculate the projects NPV, payback period, and IRR, given the following assumptions:
1. Initial investment: $10M in year 0, with $1M salvage value at the end of year 4
2. Sales: Expected to be $60M in year 1, growing by 25% in year 2, then dropping by 50% in each of the following 2 years, after which the product will be retired.
3. COGS: Expected to run at 50% of sales in year 1, and declining by 5 percentage points of sales per year through the end of the project.
4. Working capital: Apple expects a large initial investment in working capital as it fills its pipeline with inventory in year 1, equivalent to $5M. From year 2 and beyond, working capital is expected to rise/fall by 1% of the change in sales.
5. Other costs: Due to Apples frequent product refresh cycle, it expects only 60% of the iPhone 7s pre-tax cash flows to be incremental to its overall business.
6. Depreciation: initial investment will be depreciated on a 4 year straight line schedule.
7. Taxes: 35%
8. Is a terminal value needed? Why or why not?
9. Discount rate of 20%
10. Should Apple proceed with the investment? Why or why not?

Please submit your team's spreadsheet model with the answers highlighted in yellow.ALL DATA NEEDED IS ABOVE IN ASSUMPTIONS #1-7 AND 9.

Insert Draw Page Layout Formulas Data Review View Help Search Share Comment File Home AX Cut A = General FEO AutoSum Poste Copy Copy Calibri BIU - 11 - A A == SE Wrap Text Merge & Center 47 0 Sort & Find & Filter Select 4 Ideas S 60.00 " Conditional Format as Cell Formatting Table Styles Insert Delete Format Clear Format Painter Ciphoard % -% Number Alignment Foting K26 B C D E F G H I J K L M N O P Q R S T U V W X Y Z M ABAC NONE 1 Discount Rate 6 Yr 1-1 Growth Rate 7 Perpetulty Growth Rose 3 Initial COGS 9Y24 COUSC 10 Initial Working Capital 11 Working Cap Choof sales) 12 Percent incremental 13 Depreciation per year 14 The Rate 17 Capex ia Salwale 20 Sales 21 COGS 25 Chain Sales 20 Chin Working Capital 22 Depreciation Dp 30 Prelax cash flows THE 39 Add back depreciation ex 33 Nut Cul Flow 37 Terminal Answer it a terminal value should be included here 39 NPV Formula 40 IRR Foruma Apple's iPhone 11 NPV Insert Draw Page Layout Formulas Data Review View Help Search Share Comment File Home AX Cut A = General FEO AutoSum Poste Copy Copy Calibri BIU - 11 - A A == SE Wrap Text Merge & Center 47 0 Sort & Find & Filter Select 4 Ideas S 60.00 " Conditional Format as Cell Formatting Table Styles Insert Delete Format Clear Format Painter Ciphoard % -% Number Alignment Foting K26 B C D E F G H I J K L M N O P Q R S T U V W X Y Z M ABAC NONE 1 Discount Rate 6 Yr 1-1 Growth Rate 7 Perpetulty Growth Rose 3 Initial COGS 9Y24 COUSC 10 Initial Working Capital 11 Working Cap Choof sales) 12 Percent incremental 13 Depreciation per year 14 The Rate 17 Capex ia Salwale 20 Sales 21 COGS 25 Chain Sales 20 Chin Working Capital 22 Depreciation Dp 30 Prelax cash flows THE 39 Add back depreciation ex 33 Nut Cul Flow 37 Terminal Answer it a terminal value should be included here 39 NPV Formula 40 IRR Foruma Apple's iPhone 11 NPV