Please Calculate operating after tax cash flows, terminal cash flow and total project cash flows. Estimate the products operating cash flows inclusive of cannibalization effects.

CAPITAL BUDGETING: NPV, PAYBACK, IRR & PI

Honey Buns management is currently evaluating a new product, Honey Bulla. A test marketing program carried out in 2016 at a cost of US$150,000 showed enthusiastic acceptance for the product, which would cost more than the regular Goldie and Honey Bun but offer 25% fewer calories and had more appeal to the Heart Foundation of Jamaica. You were engaged and retained by a firm as financial analysts to analyze this project and present the findings to the companys executive committee.

Facilities for the Honey Bulla product would be set up in an unused section of Honey Buns Retirement Crescent plant. The machinery with an estimated cost of US$350,000 would be purchased, but shipping costs to move the machinery to Honey Bun plant would add another US$10,000, and installation charges would amount to another US$15,000. Further, Honey Buns inventories (raw materials, work-in-process, and finished goods) would have to be increased by US$15,000 at the time of the initial investment. Twenty percent (20%) of this would be recoverable at the end of the project. If the machinery is purchased, it would have an economic life of 4 years. The machinery is expected to have a salvage value of US$0.00 after 4 years of use.

Honey Buns management expects to sell 400,000 16-ounce cartons of the new product in each of the next 4 years. The price is expected to be US$2.00 per carton. Fixed costs are estimated to be US$100,000, and variable cash operating costs are estimated at US$1.25 per unit. Note that operating costs are a function of the number of units sold rather than unit price, so unit price changes have no direct effect on operating costs.

In examining the sales figures, a team member of yours, Adam, noted a short memo from Honey Buns Marketing Executive, Janelle, which expressed concern that the Honey Bulla project would cut into the firms sales of the regular Honey Buns. Specifically, the Marketing Executive estimated that regular Honey Bun sales would fall by five percent (5%) if Honey Bulla were introduced. Adam pursued this further with both the sales and production managers, and they estimated that the new project would probably lower the firms regular Honey Bun sales by $40,000 per year. However, this volume reduction would also reduce regular Honey Bun production costs by $20,000 per year on a pretax basis.

Company	Beta	Required Rate of Return
Honey Bun (HONBUN)	0.266	4.68%

Required:

Financial Excel Model:

1. Prepare a spreadsheet showing the operating after tax cash flows, terminal cash flow and total project cash flows. Estimate the products operating cash flows inclusive of cannibalization effects. What is the projects NPV, PI, IRR, and Payback Period?
2. Prepare a Sensitivity Analysis of the variable(s) and/or value driver(s), you deem to be KEY to the outcome of the project (i.e. prepare the base case, then sensitivities on how the results from the base case could change if certain key variables change from the values used in the base case). Graphical illustrations of the sensitivities will enhance your submission.