You are working in the financial analysis division of Apple Computer, which is deciding whether to launch a new product, Apple Car, an electric car based on the iOS platform to compete with Tesla. Your boss has asked you to perform a capital budgeting analysis that he can present to Tim cook and take the credit for himself. (Son of a $%^&*@!) You have the following information.

The marketing department has performed a study that estimates that the Apple Car, should it be introduced, would have a five-year product life cycle. (It would be made obsolete by then by either Tesla, Rivian, or Apple’s own product launch at that time.) The marketing study, which cost $1,500,000 to perform, predicts that first-year sales would be $45,000,000 and grow by 50% each year thereafter. The study also indicates that the introduction of the Apple Car would cause some synergies that will likely increase Apple iPhone sales by $15,000,000 per year due to the complementary nature of the products (i.e., both use iOS). On the other hand, customers would be driving more and working out less. So, it is expected to decrease sales of Apple Fitness+ by $10,00,000 per year. These latter two figures will not grow over time.

The operations department has indicated that the Cost of Goods Sold (COGS) for the Apple Car is expected to be 60% of sales. Since Apple will be gaining some iPhone sales, they will have to incur more production costs for iPhones. COGS for Apple computers is typically 50% of sales. Since they will be losing Fitness+ sales, they will incur fewer COGS in that product line. COGS for Fitness+ is 40% of sales.

In addition, introducing Apple Car would create additional operating expenses of $8,000,000 annually, excluding depreciation.

The company is not currently using the production facility that will be used to produce the Apple Car. That plant was purchased for $100,000,000 three years ago. It could otherwise be sold for $75,000,000 today and has five years remaining in its useful life. It is being depreciated down to zero using straight-line depreciation. It is expected to be worth $2,000,000 five years from now.

The introduction of the Apple Car would require an initial increase in inventory of $6,000,000. Furthermore, the new product will cause accounts receivable to increase by $3,000,000. At the end of the project, however, these working capital investments will be recovered as inventory is liquidated and receivables collected. The tax rate is now 21% based on the Tax Cut and Jobs Act of 2017, and the required rate of return on the project is 15%.

1. Construct a spreadsheet model to forecast the total cash flows associated with the Apple Car.
   • You might consider expressing figures in millions with one or two decimal points to avoid cluttering your work.
   • Since introducing Apple Car will have an impact on two other product lines, you will need three lines to calculate Net Sales and Net COGS – one for each product line: Apple Car, iPhone, Fitness+. Combine these numbers for a Net Sales and Net COGS figure.
   • Make sure you have a separate input (i.e., assumptions) section for all your assumptions and case data.
   • Use cell references instead of numbers in your cash flow projections.
   • Make sure that you properly anchor cell references in your formulas so that you copy formulas rather than re-enter them manually.
2. What is the project’s Net Present Value (NPV)?
3. At what discount rate is the project no longer attractive? In other words, what is the Internal Rate of Return (IRR)? 
4. What is the project’s Modified Internal Rate of Return (MIRR)? 
5. What is the project’s Payback Period?