A. Disregard all the assumptions made in part A, and assume there was no alternative use for the building over the next 4 years. Complete the table below in excel Lemon Juice Project (Total Cost in Thousands) End of Year 0 1 2 3 4 Investment Outlay Equipment cost Installation Increase in Inventory Increase in accounts payable Total net investment Operating Cash Flows Unit sales (thousands) Price/unit Total revenues Variable Cost Fixed Cost Depreciation Total costs Operating income before taxes (EBIT) Taxes on operating income Operating income after taxes (NOPAT) Depreciation Operating cash flow 11 Terminal Year Cash Flows Return of net operating working capital Salvage value Tax on salvage value Total termination cash flows IV. Project Cash Flows Project cash flow V. Results NPV = IRR = MIRR = B. Calculate the project's NPV, IRR, and MIRR. Do these indicators suggest that the project should be accepted? (1) Perform a sensitivity analysis on the unit sales, salvage value, WACC, and sales price for the project? Assume that each of these variables deviates from its base-case, or expected, value by plus and minus 5%, 10%, and 20% (2) What are the two ways you can identify the variable with the most impact on NPV? The CFO wants answers to these two questions? (1) At what sales level will the company break even (NPV = 0)? (2) At what sales price will the company break even (NPV = 0)? (1) Assume that inflation is expected to average 3% over the next 4 years, and this expectation is reflected in the WACC. Moreover, inflation is expected to increase C. D. E G the price by 3%, but cost will remain stable Price in year 1 is still 10.99. Create a new table with the inflation information included. (2) Continue with the inflation scenario. How would your answer change if sales were to grow by 2% each year? Sales in year 1 are 95,000. What is the NPV? Assume that you are confident about the estimates of all the variables that affect the cash flows except unit sales. Continue with the inflation scenario, but not the krowth rate scenario. If product acceptance is poor, sales would be only 90,000 units a year, the sale price needs to be discounted to $10.39, while a strong consumer response would produce sales of 110,000 units and the sale price could be increased to $11.39. in either case, variable costs would still be $7.59. You believe that there is a 30% chance of poor acceptance, a 20% chance of excellent acceptance, and a 50% chance of average acceptance (the base case). (1) What is the worst case NPV? The best-case NPV? (2) Use the worst, most likely (or base), and best-case NPVs, with their probabilities of occurrence, to find the project's expected NPV, standard deviation, and coefficient of variation. After the scenario analysis, would you accept the project? The CFO wonders whether the company should switch to a straight line depreciation schedule to improve the NPV. Would switching to straight line depreciation improve the NPV? What is the NPV with straight line depreciation? The CFO is skeptical about the profitability of the project and asks you to performa Monte Carlo simulation. Please use the MACR depreciation. The basic assumptions for the simulation are the following Total Equipment cost (including installation) is normally distributed with an average of 270000 and a standard deviation of 30000 Working Capital is normally distributed with an average of 20000 and std. dev. of 3000 Sales are normally distributed mean 95000 and std. dev. 3000 Sales Price is fixed at 10.99 Variable Cost is triangular distributed with the most likely 7.59, highest 8.39 and lowest 6.79 Fixed cost is normally distributed between 230000 and std dev. 20000 Salvage Value is normally distributed between 20000 and std dev. 5000 Tax Rate remains at 35% . WACC is normally distributed with a mean of 10% std. dev. 1% Please find the average NPV, the maximum NPV and the minimum NPV as well as the std. dev. Please create a graph to show your results including the normal distribution graph. (Please create a graph with the cumulative distribution as well as the returns of the normal distribution for the specified mean and standard deviation). What is the probability the investment will fail to break even? After performing the Monte Carlo simulation, would you accept the project? (Please see write up requirement at the beginning of project . . . . In your first column you are required to use the basic assumptions from part B. The next 199 columns should be randomly distributed according to the distribution function for each category. What is the maximum variable cost in the Monte Carlos simulation that would make the NPV equal to 0 (Please find the solution for the first column only!) Problem 1: Your boss is considering expanding into the fruit juice business with a new fresh lemon juice product You must evaluate the new project. The lemon juice would be produced in an unused building. Your company owns the building, which is fully depreciated. The required equipment would cost $220,000, plus an additional $50,000 for shipping and installation. In addition, inventories would rise by $30,000, while accounts payable would increase by $10,000. All of these costs would be incurred at t = 0. By a special ruling, the machinery could be depreciated under the MACRS system as 3-year property. The applicable depreciation rates are 33%, 45%, 15%, and 7%. The project is expected to operate for 4 years, at which time it will be terminated. The cash inflows are assumed to begin 1 year after the project is undertaken, or att = 1, and to continue out to t = 4. At the end of the project's life (t = 4), the equipment is expected to have a salvage value of $20,000 Unit sales are expected to total 95,000 units per year, and the expected sales price is $10.99 per unit. Variable costs per unit are $7.59. Fixed cost is estimated to be $230,000 per year. Tax rate is 35%, and its WACC is 10%. Tentatively, the lemon juice project is assumed to be of equal risk to other assets. You have been asked to evaluate the project and to make a recommendation as to whether it should be accepted or rejected. To guide you in your analysis, your boss gave you the following set of questions. Please at the end write a summary of whether the project should be accepted or not. Why would you recommend, why would you reject the project? A. Disregard all the assumptions made in part A, and assume there was no alternative use for the building over the next 4 years. Complete the table below in excel Lemon Juice Project (Total Cost in Thousands) End of Year 0 1 2 3 4 Investment Outlay Equipment cost Installation Increase in Inventory Increase in accounts payable Total net investment Operating Cash Flows Unit sales (thousands) Price/unit Total revenues Variable Cost Fixed Cost Depreciation Total costs Operating income before taxes (EBIT) Taxes on operating income Operating income after taxes (NOPAT) Depreciation Operating cash flow 11 Terminal Year Cash Flows Return of net operating working capital Salvage value Tax on salvage value Total termination cash flows IV. Project Cash Flows Project cash flow V. Results NPV = IRR = MIRR = B. Calculate the project's NPV, IRR, and MIRR. Do these indicators suggest that the project should be accepted? (1) Perform a sensitivity analysis on the unit sales, salvage value, WACC, and sales price for the project? Assume that each of these variables deviates from its base-case, or expected, value by plus and minus 5%, 10%, and 20% (2) What are the two ways you can identify the variable with the most impact on NPV? The CFO wants answers to these two questions? (1) At what sales level will the company break even (NPV = 0)? (2) At what sales price will the company break even (NPV = 0)? (1) Assume that inflation is expected to average 3% over the next 4 years, and this expectation is reflected in the WACC. Moreover, inflation is expected to increase C. D. E G the price by 3%, but cost will remain stable Price in year 1 is still 10.99. Create a new table with the inflation information included. (2) Continue with the inflation scenario. How would your answer change if sales were to grow by 2% each year? Sales in year 1 are 95,000. What is the NPV? Assume that you are confident about the estimates of all the variables that affect the cash flows except unit sales. Continue with the inflation scenario, but not the krowth rate scenario. If product acceptance is poor, sales would be only 90,000 units a year, the sale price needs to be discounted to $10.39, while a strong consumer response would produce sales of 110,000 units and the sale price could be increased to $11.39. in either case, variable costs would still be $7.59. You believe that there is a 30% chance of poor acceptance, a 20% chance of excellent acceptance, and a 50% chance of average acceptance (the base case). (1) What is the worst case NPV? The best-case NPV? (2) Use the worst, most likely (or base), and best-case NPVs, with their probabilities of occurrence, to find the project's expected NPV, standard deviation, and coefficient of variation. After the scenario analysis, would you accept the project? The CFO wonders whether the company should switch to a straight line depreciation schedule to improve the NPV. Would switching to straight line depreciation improve the NPV? What is the NPV with straight line depreciation? The CFO is skeptical about the profitability of the project and asks you to performa Monte Carlo simulation. Please use the MACR depreciation. The basic assumptions for the simulation are the following Total Equipment cost (including installation) is normally distributed with an average of 270000 and a standard deviation of 30000 Working Capital is normally distributed with an average of 20000 and std. dev. of 3000 Sales are normally distributed mean 95000 and std. dev. 3000 Sales Price is fixed at 10.99 Variable Cost is triangular distributed with the most likely 7.59, highest 8.39 and lowest 6.79 Fixed cost is normally distributed between 230000 and std dev. 20000 Salvage Value is normally distributed between 20000 and std dev. 5000 Tax Rate remains at 35% . WACC is normally distributed with a mean of 10% std. dev. 1% Please find the average NPV, the maximum NPV and the minimum NPV as well as the std. dev. Please create a graph to show your results including the normal distribution graph. (Please create a graph with the cumulative distribution as well as the returns of the normal distribution for the specified mean and standard deviation). What is the probability the investment will fail to break even? After performing the Monte Carlo simulation, would you accept the project? (Please see write up requirement at the beginning of project . . . . In your first column you are required to use the basic assumptions from part B. The next 199 columns should be randomly distributed according to the distribution function for each category. What is the maximum variable cost in the Monte Carlos simulation that would make the NPV equal to 0 (Please find the solution for the first column only!) Problem 1: Your boss is considering expanding into the fruit juice business with a new fresh lemon juice product You must evaluate the new project. The lemon juice would be produced in an unused building. Your company owns the building, which is fully depreciated. The required equipment would cost $220,000, plus an additional $50,000 for shipping and installation. In addition, inventories would rise by $30,000, while accounts payable would increase by $10,000. All of these costs would be incurred at t = 0. By a special ruling, the machinery could be depreciated under the MACRS system as 3-year property. The applicable depreciation rates are 33%, 45%, 15%, and 7%. The project is expected to operate for 4 years, at which time it will be terminated. The cash inflows are assumed to begin 1 year after the project is undertaken, or att = 1, and to continue out to t = 4. At the end of the project's life (t = 4), the equipment is expected to have a salvage value of $20,000 Unit sales are expected to total 95,000 units per year, and the expected sales price is $10.99 per unit. Variable costs per unit are $7.59. Fixed cost is estimated to be $230,000 per year. Tax rate is 35%, and its WACC is 10%. Tentatively, the lemon juice project is assumed to be of equal risk to other assets. You have been asked to evaluate the project and to make a recommendation as to whether it should be accepted or rejected. To guide you in your analysis, your boss gave you the following set of questions. Please at the end write a summary of whether the project should be accepted or not. Why would you recommend, why would you reject the project