You are planning on opening a beauty salon. You expect that you will need $20,000 in rent annually, and will need to pay $60,000 for hair stylists annually. Janitorial and cashier help will cost $40,000 annually and utilities will be $10,000 annually. Assume that variable costs are 20 percent of sales per customer. You expect to be open six days a week and to have 30 customers a day, with an average order of $36 per customer. The salon will operate 50 weeks a year.

Sales are expected to grow at 5 percent each year from year 1 to 5 and then at 4 percent from year 5 to year 6. You would like to run the business for 6 years and then shut down. You will need to invest in equipment that costs $180,000 today and depreciates straight-line to a salvage value of $30,000 at the end of the sixth year. You would like a salary of $90,000 annually for yourself. You plan to finance the equipment by borrowing at 6.25 percent. Your tax rate is 25 percent. The required return (cost of capital) on other salon investments of similar risk is 8 percent. Given the data above:

(a) Using Excel, calculate the expected net cash flow each year from year zero (today) to year 6. Make sure your calculations are broken down clearly.

(b) Find the NPV, PI, IRR of this project.

(c) Create a sensitivity analysis table for NPV against discount rate and create a graph for discount rates of 0, 2 percent, 4 percent, six percent, etcto 20 percent.

(d) What must average sales per customer be to have an NPV of exactly zero? Please show all excel formulas used.