Problem:

You work for Starbucks. You propose a marketing plan, whereby the company would add a second drivethru to every store. You believe this would be a good plan, since drive-thru sales are higher-ticket sales (bigger than in-store). Each of the 2,500 drive-thrus costs $200,000 (total cost of $500,000,000) plus an additional $10,000 for installation and setup (total installation and setup of $25,000,000). At the end of its useful life in 3 years, the drive-thru equipment will have to be replaced, so will be worthless, and you will use straight-line depreciation. You also expect to need extra Net Working Capital of $1,000,000 to start the project today (a one-time increase in short-term borrowing until the initial sales kick in). You will recover this NWC in Year 3. You are going to use old microphones to run the new drive-thru. The old microphones could be sold for a total of $350,000 today if you werent repurposing them. You also commissioned a market study for an idea that youre not going to use, which cost $50,000 with an outside consultant. You think you will be able to sell more coffee with the new drive-thrus. You expect to sell a total of 50,000,000 additional cups of coffee with this program the first year, and expect to increase the number of cups of coffee by 10% per year in Years 2 and 3, until the drive-thrus have to be replaced, at which point the company will likely move on to other strategies. You are going to price the coffee at $5 per cup, and keep that same price per cup for all 3 years. You expect some customers who currently come into the store to switch to the new drive-thru, reducing in-store sales by $4,000 per store, for a total of $10,000,000 per year. You also think customers buying coffee in the new drive-thru will also buy an additional $10,000 of snacks (which you are already purchasing from an outside vendor) per store, worth a total of $25,000,000 per year (NOTE: Ignore any changes in costs due to Side Effects). Each year, you expect $10,000 in added fixed costs per store, for a total of $25,000,000 additional fixed costs per year. Variable cost is for the number of additional cups to be purchased (50,000,000 in Year 1, then increasing by 10% per year), each of which costs $1, to add the drive-thru. Your tax rate is 30% and your cost of capital is 6%.

(Required) Solve for:

a. Calculate the Initial Investment of the project. Show each item that goes into the Initial Investment on its own row, and label each row you use. b. Calculate i. EBIT ii. Net Operating Profit After Tax (NOPAT) iii. Operating Cashflow (OCF) for each year of the project. Show each item that goes into these calculations on its own row, and label each row you use. In other words, make a pro-forma income statement for each of the 3 years of the project, and adjust the Net Income to Operating Cashflow. c. Calculate the Terminal Cashflow at the end of the projects 3rd year. Show each item that goes into the Terminal Cashflow on its own row, and label each row you use. d. Calculate the total Cash Flow From Assets (CFFA) for today, and for each year of the project. e. Calculate the projects NPV (IMPORTANT NOTE: The Excel NPV function does NOT work the same way as your calculators NPV function. If you use the Excel NPV function, make sure to use it correctly). i. State whether the firm should accept or reject the project based on NPV. ii. Explain why you accepted or rejected the project based on NPV. In other words, what does it mean for the NPV to be positive or negative (whichever you calculated)? f. Calculate the projects IRR (use the Excel IRR function NO credit for just entering a number, even if correct). i. State whether the firm should accept or reject the project based on IRR. ii. Explain why you accepted or rejected the project based on IRR (i.e., what does IRR measure)? g. Calculate the Payback Period in years (to 1 decimal place). i. The Comptroller has a 2.5-year minimum payback. State whether they would accept or reject the project. ii. The CFO has a 3-year minimum payback. State whether they would accept or reject the project. iii. Explain why Payback Period might cause the Comptroller and CFO to disagree on whether to do the project (regardless of your calculation). iv. Cite one reason why Payback Period is inferior to both NPV and IRR for making investment decisions about capital budgeting projects (regardless of your calculation).

This is the format and where answers should be placed in Excel