Matthew then donates office space he owns in Ottawa for a high-end restaurant called Vagrance. Restaurant equipment will cost $5 million, including renovation costs for the office space. Vagrance will also require an initial investment of $1 million in net working capital and will generate pre-tax revenues that are $3 million for the first year. The pre-tax revenues will grow at an annual rate of 2.5 percent for the next 14 years as the federal government throws more lavish parties for journalists which are later cancelled. The pre-tax operating costs are low as most of the high-end food is donated from cancelled parties and will only be $1 million/year for the 15 years, mostly to pay wages for the homeless and to keep the décor and food at Vagrance fresh. The salvage value of restaurant equipment will be $1 million at the end of year 15.
The initial equipment purchased falls into a CCA Asset Class 8 at a rate of 20 percent. The corporate tax rate is 40 percent and the cost of capital is 15 percent. Assume the initial working capital investment will be made at the time of the purchase of the equipment and recovered or returned at the end of year 15. For simplicity, all cash flows for a given year occur at the end of the year.
a. What are the NPV and IRR in 2023 of Vagrance? Do the calculations by hand (with a calculator) to practice for the final exam and submit your handwritten answer. (8 points)
Adjust the formulas in the Excel spreadsheet “Use Solver or GoalSeek to get IRR-lecture 5 6” (please download from eclass) to calculate a growing annuity of revenues and to incorporate the net working capital investment and recovery. Then input the numbers into the Excel spreadsheet and check your NPV and IRR calculations for 2023.
b. Write down the changes you made to the Excel spreadsheet to calculate the NPV of Vagrance in 2023. (It is very important to know what changes to make to the Excel spreadsheet to solve a particular NPV problem. You may be asked a similar question on the final exam which would involve different changes made to the spreadsheet.) Submit your handwritten answer. (5 points)
c. Print out the page in pdf which shows the NPV in 2023 calculated by Excel. You must submit this Excel page. (1 point)
d. Now use Solver to find the IRR in 2023. Print out the page in pdf which shows the IRR calculated by Goalseek or Solver. You must submit this Excel page. (1 point)
Next, adjust the formulas in the Excel spreadsheet “Use Solver or GoalSeek to get IRR-lecture 5 6” to calculate NPV for 2026 and 2029.
e. Write down the changes you made to the Excel spreadsheet after parts 4b.-4d. to calculate the NPV of Vagrance in 2026. Submit your handwritten answer. (4 points) Print out the page in pdf which shows the NPV in 2026 calculated by Excel. You must submit this Excel page. (1 point)