Excel Model

Create an Excel spreadsheet. Do all of your work in a single Excel tab. You should format the spreadsheet so that it will print on no more than two sheets of paper. The spreadsheet should be formatted to be attractive and professional. Except for the source information, you should not type any other numbers into the spreadsheet. You should use functions and references to create a working model. All calculations should be done in the spreadsheet. You may not calculate or display any value more than once, or enter any source data more than once. You must also record all assumptions in a section of your worksheet which is you label Assumptions. The assumptions section can be above or below your model.

Your employer is considering opening a new location of their pizza chain at a one-time cost of $2,150,000. The restaurant will be installed in an area of a building that was refurbished by your employer for another restaurant concept they operate in 2018. At that time, the refurbishment cost $1,500,000. If it is not employed by this project, the building will remain unused. The new restaurant and its equipment, if opened, will be depreciated on a straight-line basis over six years, to a book value of $0. If implemented, the project will cause an immediate need for additional inventory of $250,000. It will also cause immediate increases in Accounts Receivable of $12,000, Accounts Payable of $117,000 and $1,710,000 in long term debt.

If implemented, the project is expected to generate annual sales of $1,120,000 by the end of the first year. Sales are expected to increaseby 8% each year for the life of the project. Costs are expected to be $770,000 during the first year. Thereafter, costs are expected to remain at a constant percentage of Sales. At the end of the projects six-year life, the location will be closed to prepare for another business venture, and the equipment will be sold for an estimated $145,000. At that time, Inventory, Accounts Receivable and Accounts Payable will return to their pre-project levels.

If the project is implemented, it will likely cannibalize/erode sales of the firms other pizza locations. The net impact of the cannibalization/erosion is expected to be a $75,000 annual reduction in pre-tax profits. The cannibalization/erosion will be the same each year.

Your employers tax rate is 21%. The firm has 750,000 shares of common stock outstanding. The firm requires an 11% rate of return on capital projects.

Prepare a cash flow analysis to determine whether your employer should implement this project. Your analysis should provide answers to each of the following questions. Answer the following questions in empty cells on your worksheet.

1. What is the initial investment amount?

2. What are the future annual operating cash flow amounts? (OCF should be a line in your model)

3. What is the NPV of this project?

4. What is the IRR of this project?

5. What is the Profitability Index of this project?

6. If the firm implements the project, what will be the impact on the stocks price per share?

7. Should the firm implement the project? Why or why not?