Big B Coffee Roaster is considering replacing one of its existing machines with a new, more automated and efficient machine. The old machine was bought five years ago for $$93,750.$ It is being depreciated on a straight$-$line basis over a fifteen$-$year life. The machine could be sold today at $$20,000.$

The new machine will cost $$96,000$ and its depreciable life is ten years with a salvage value of $$13,000.$ However, in accordance with U$.$S$.$ tax law, the asset will be depreciated down to zero over the ten years via the straight$-$line method.

In $2009,$ Big B$$s existing coffee roasting machine accounted for annual revenues $($sales$)$ of $$50,000$ and had annual operating costs of $$25,000.$ The new machine will increase this revenue to $$72,000$ per year. The machine will also increase operating costs by $$3,000$ per year. Finally, the new machine requires that the company raises its inventory of green coffee beans by $$4,000.$

The cost of capital for Big B is $14\%$ and the corporate tax rate is $40\%.$ Big B has profitable ongoing operation that can be used to offset losses. Should Big B replace the old machine with the new one?

QUESTION $1(2$ POINTS$)$:

On the first worksheet of the template, identify the relevant and irrelevant cash flows in this problem and further categorize the relevant cash flows into three categories: a$)$ Initial Outlay, b$)$ After$-$tax annual cash flows, and c$)$ Terminal Cash Flow. On the second worksheet of the template file complete the data section, and calculate the initial outlay, annual ATCF and terminal cash flow. Then, calculate the cash flows each year $($years $0-10),$ complete the timeline table and calculate the NPV and IRR. Should the project be accepted?

QUESTION $2(2$ POINTS$)$:

The managers of Big B now realize that some of the base case assumptions they made are not totally correct. Specifically, the increase in revenues and operating costs are unpredictable. The same is true for the salvage value of the new equipment at the end of its life $(10$ years from today$).$

Even though projected increase in operating costs is $$3,000,$ past experience from other companies using the same equipment indicates that operating costs vary significantly from year to year. One of Big B$$s cost analysts has come to the conclusion that increase in operating costs will follow a normal distribution with a mean of $$5,000$ and a standard deviation of $$500.$

Increase in revenues will depend on the overall state of the economy in a given year. Analysts believe that the increase in sales is not going to be less than $$18,000$ but if the economy is in good state the increase in sales can reach as much as $$27,000.$ Big B$$s analysts have decided to

use a uniform distribution with a minimum of $$18,000$ and a maximum of $$27,000$ to model the increase in sales variable.

Finally, the actual salvage value of the new equipment at the end of its life is most likely going to be $$13,000$ but it could range from $$5,000$ to $$20,000$ depending on the technological advancements in the industry. Big B$$s analysts have decided to use a triangular distribution to model the sale price of the new equipment in $10$ years.

Big B wants to evaluate how these uncertainties might affect their decision to buy or not the new equipment. They want to find out what the expected NPV and IRR of the project are, which variables have the biggest effect on them and what they can do to limit the uncertainty. Use @Risk to run a simulation and provide answers to the following questions. Make sure that you create include the full report of the simulation to the files you post online and justify your answers $($name the workbook $$Simulation $1$ results$).$ Remember that each year should be modeled independently of the rest. Use $10,000$ trials for each simulation.

a$)$ What are the expected NPV and IRR?

b$)$ What is the probability of getting a negative NPV$?$

c$)$ What is the most important source of uncertainty?

d$)$ How important is the actual sale price of the equipment at the end of year $10?$

QUESTION $3(1$ POINT$)$:

Assume the company that is selling the equipment is offering the following service plan with their equipment: If the operating costs in a given year exceed $$5,100$ they will pay for the additional costs. For example, if the operating costs in a given year are $$5,300$ then they are going to give Big B $$200,$ if the costs are $$5,150$ then they will give Big B $$50.$ If the operating costs turn out to be $$4,800$ then nothing happens. Incorporate this adjustment in your model $($you will need to use the IF function$),$ re$-$run the simulation and answer the following questions. Include a copy of the simulation report in the files you post $($name this workbook $$Simulation $2$ results$).$

a$)$ What is the expected NPV with the service plan?

b$)$ What is the probability of getting a negative NPV$?$ Does it improve compared to Q$2?$

c$)$ How much would you be willing to pay for a service plan like this and why?