The Milk 4 All Company is considering branching into the ice-cream business. It will need a...

  

Transcribed Image Text:

The Milk 4 All Company is considering branching into the ice-cream business. It will need a machine costing $1,000,000 to produce the ice cream. The machine will be depreciated over ten years to zero salvage value. However, the ice-cream project is expected to last for only five years, so that at the end of the project the machine will be sold for its market value. The sale price of the machine at the end of five years will be uniformly distributed with a minimum value of $300,000 and a maximum value of $500,000. If the market value is less than the book value, the company will claim a loss on the difference. Sales in year I follow the triangular distribution with a minimum value of $2,000,000, a most likely value of $3,000,000 and a maximum value of $7,000,000. Thereafter, sales in year t, S₁, are related to sales in the previous year, Si-1, by the equation S, 750,000+ 0.875*S-1 + e where e, is normally distributed with mean 0 and standard deviation 150,000. In each year, manufacturing costs as a percentage of sales have a pert distribution with a minimum value of 70 percent, a maximum value of 95 percent, and a most likely value of 85 percent. Fixed cash cost (rent) is expected to be $100,000 in the first year. Thereafter the fixed cash cost is expected to grow at the expected inflation rate at 4 percent a year. The project will require an initial investment of $300,000 in net working capital. From year 1 onwards, the project requires net working capital level to equal to 10 percent of that year's projected sales. (Note: since there is an initial investment in working capital, you only need to determine the net change from year to year. Assume that you can recover your working capital investment at the end of the project.) Profits are subject to a 30 percent tax rate. The Milk 4 All Company is profitable enough so that any losses at the project level translate to a tax deduction at the corporate level. In other words, negative tax at the project level is a possible scenario. The cost of capital is 14 percent. The risk free rate is equal to 6%. Perform a simulation in answering the following questions. You need to submit: a. An Executive summary that describes the analysis you performed and the results you obtained in answer to the questions below. b. An appendix with supporting evidence (Printout of cash flow model, Output Summary, Tornado Diagram, etc.) c. An electronic copy of the spreadsheet work. Questions a) Assume that sales in year 1 follow the triangular distribution with a minimum value of $2,000,000, a most likely value of $3,000,000 and a maximum value of $7,000,000. Calculate the probability that year 1 sales will be between $4,000,000 and $6,000,000. b) Given that manufacturing costs as a percentage of sales have a pert distribution with a minimum value of 70 percent, a maximum value of 95 percent, and a most likely value of 85 percent, calculate the probability that manufacturing cost as a percentage of sales is greater than 75% and the probability that manufacturing cost as a percentage of sales is less than 90%. c) Calculate the average NPV of the project over 1000 iterations. What is the 95% confidence interval for the NPV? How many iterations would be required to narrow this confidence in half? d) What is the value at risk (VAR) for this project? What is the probability of having a negative NPV? e) Use a tornado diagram to rank the uncertain variables in terms of their influence on the NPV. f) What are your observations/recommendations about the project? For example, explain what (if anything) you could do to increase the average NPV of the project. The Milk 4 All Company is considering branching into the ice-cream business. It will need a machine costing $1,000,000 to produce the ice cream. The machine will be depreciated over ten years to zero salvage value. However, the ice-cream project is expected to last for only five years, so that at the end of the project the machine will be sold for its market value. The sale price of the machine at the end of five years will be uniformly distributed with a minimum value of $300,000 and a maximum value of $500,000. If the market value is less than the book value, the company will claim a loss on the difference. Sales in year I follow the triangular distribution with a minimum value of $2,000,000, a most likely value of $3,000,000 and a maximum value of $7,000,000. Thereafter, sales in year t, S₁, are related to sales in the previous year, Si-1, by the equation S, 750,000+ 0.875*S-1 + e where e, is normally distributed with mean 0 and standard deviation 150,000. In each year, manufacturing costs as a percentage of sales have a pert distribution with a minimum value of 70 percent, a maximum value of 95 percent, and a most likely value of 85 percent. Fixed cash cost (rent) is expected to be $100,000 in the first year. Thereafter the fixed cash cost is expected to grow at the expected inflation rate at 4 percent a year. The project will require an initial investment of $300,000 in net working capital. From year 1 onwards, the project requires net working capital level to equal to 10 percent of that year's projected sales. (Note: since there is an initial investment in working capital, you only need to determine the net change from year to year. Assume that you can recover your working capital investment at the end of the project.) Profits are subject to a 30 percent tax rate. The Milk 4 All Company is profitable enough so that any losses at the project level translate to a tax deduction at the corporate level. In other words, negative tax at the project level is a possible scenario. The cost of capital is 14 percent. The risk free rate is equal to 6%. Perform a simulation in answering the following questions. You need to submit: a. An Executive summary that describes the analysis you performed and the results you obtained in answer to the questions below. b. An appendix with supporting evidence (Printout of cash flow model, Output Summary, Tornado Diagram, etc.) c. An electronic copy of the spreadsheet work. Questions a) Assume that sales in year 1 follow the triangular distribution with a minimum value of $2,000,000, a most likely value of $3,000,000 and a maximum value of $7,000,000. Calculate the probability that year 1 sales will be between $4,000,000 and $6,000,000. b) Given that manufacturing costs as a percentage of sales have a pert distribution with a minimum value of 70 percent, a maximum value of 95 percent, and a most likely value of 85 percent, calculate the probability that manufacturing cost as a percentage of sales is greater than 75% and the probability that manufacturing cost as a percentage of sales is less than 90%. c) Calculate the average NPV of the project over 1000 iterations. What is the 95% confidence interval for the NPV? How many iterations would be required to narrow this confidence in half? d) What is the value at risk (VAR) for this project? What is the probability of having a negative NPV? e) Use a tornado diagram to rank the uncertain variables in terms of their influence on the NPV. f) What are your observations/recommendations about the project? For example, explain what (if anything) you could do to increase the average NPV of the project.

Answer rating: 100% (QA)

I EXECUTIVE SUMMARY Overview The Milk 4 All Company is considering branching into the icecream business From the all information given we prefer to build a financial model to analyze the model and mak... View the full answer