Brain Tech, an innovative technology startup firm, has asked your team to evaluate a proposed capital budgeting project. Your team has been charged with analyzing the Wave Monitor, a miniature portable electroencephalogram (EEG) device. The target market for this device is individuals at risk for aneurisms and/or strokes. Market research data suggests that the firm can sell 5000 units per year, at a price of $_______ each. Variable costs are expected to be _____ per unit. Fixed costs for the project will run at $___________ per year. The project is expected to have a 6 year life. The firm will need to invest in $2,400,000 of equipment at the start of the project. The cost to install the equipment will be $250,000. MACRS 7-year class life depreciation is to be used. In six years, the equipment is expected to have a market value of $900,000. Net working capital is expected to increase by $500,000 at the start of the project and stay at this level until the end, when it will be recovered. The cost of capital for Brain Tech is 13% and the tax rate is 21%.

1. Set up a capital budgeting analysis in Excel. Given information must be shown in the upper left corner of your spreadsheet. Point to these cells as you build your analysis.

2. Calculate NPV in Excel. Should you accept this project based on the NPV method? Explain.

3. Calculate IRR in Excel. Should you accept this project, based on the IRR method? Explain.

4. Calculate PI in Excel. Should you accept this project, based on the PI method? Explain.

5. Use Goal Seek to find the breakeven sales volume for the project. In other words, at what sales volume would NPV = 0?

6. Available data suggests that the sales volume for this product may be as high as 7,000 or as low as 3,000 in the coming years. Variable cost per unit may run as high as $650 or as low as $350 per unit. Prepare a scenario analysis for this project, including calculation of NPV and IRR for each scenario. Set up your Excel model with four sheets.

Base case Goal seek Best case scenario Worst case scenario Build your base case model first. When you are confident that it is correct, copy it to three additional sheets, giving them the names listed above. In sheet two, perform break even sensitivity analysis on expected units sold. What is the number of units sold that will allow for NPV of zero. Sheet three shows the numbers for a best case scenario while sheet four shows the numbers for a worst case scenario. You can copy the base case and then just change the number of units sold for the best case and worst case scenarios. Go back to the base case and build a table below your existing analysis. The table should show NPV, IRR, PI for your worst case, base case and best case. You can pull in the NPV, IRR, and PI values automatically from the various sheets as you change inputs to your spreadsheet.