Teewinot Brewing is rapidly expanding craft brewery. Currently Teewinot sells beverages in bottles and cans. The brewery does not currently own the necessary equipment to package its own beer into cans. Instead, the brewery contracts with a larger local brewery to package their product into cans. The process involves using a tanker truck to transport Teewinots product to the other brewery, can the beer, transport the packaged beer back to Teewinot. The current process costs Teewinot $0.86 per can, plus an additional $4500 of transportation costs for each 26,000 cans.

Teewinot is considering the purchase and installation of a canning machine that would allow the company to package its own product. The canning machine will cost $2,000,000 and have a useful life of 10 years and will be depreciated using a straight-line method to a salvage value of $0. However, after 10 years, the machine is expected to be worth $150,000 and be sold.

Teewinots CFO expects that will it cost the Company $0.56 per can to run their own canning machine. Additionally, the brewery will need to hire 3 new employees to design, operate, and maintain the new piece of machinery. The total estimate cost for these employees is $230,000 annually, which includes all salary and benefits associated with the employees. Additionally, Teewinot expected to incur $300,000 of maintenance costs every 3 years.

Currently, Teewinot currently sells 1,300,000 per year, but they expect demand to increase by 10% each year for the next 4 years then drop down to 3% growth in volume in the 6th year and continue at 3% for the remaining life of the machine. Teewinots cost of capital is 12% and their tax rate is 25%.

Construct a dynamic financial model in excel that will answer for the following questions:

1) Noting the current annual volume of sales is 1,300,000 cans, what is the NPV of the canning machine project? Would you accept or reject this project?

2) Use the Goal Seek function in excel to calculate the break-even point in volume that would cause the brewery to reject the canning machine project (assume the same growth rates as previous example).

3) Calculate the IRR on this project, assuming the current growth rates of 10% for the first 4 years then falling to 3% growth rate. Create a data table to show the IRR of the project for the following scenarios (see Chap 24 for Data Tables): a. instead of a 10% growth rate for the first 4 years, the company expects 5% growth rate for first 4 years, then 3% rate b. The company expects 8% growth rate for first 4 years, then 3% rate c. The Company expects 12% growth rate first 4 years, the 3% rate