70.2K

Verified Solution

Link Copied!

FIN 610-Excel Assignment This is an individual assignment. Please do not collaborate. The posted Excel template should be used. The assignment should be uploaded through Canvas. The Cold & Sweet Company manufactures ice-cream bars. They are considering the purchase of a new machine that will top the bar with high quality chocolate. The cost of the machine is $900,000; it has a life of 10 years and the company will have to increase its net working capital by $20,000 to use it. The machine can produce up to 1,000,000 ice-cream bars annually. The marketing director believes that if the company will spend $20,000 in advertising every year, the company will be able to sell 400,000 bars for $1.30 each. The cost of producing each bar is $0.50; and other costs related to the new product are $40,000 annually. The companys cost of capital is 14% and the corporate tax rate is 30%. The company will depreciate 10% of the machines value every year. The company expects the equipment to be actually sold for $200,000 after 10 years. Part a Base model (8 points): Requires you to set up a worksheet containing all the relevant information in this problem, and build a model that calculates the initial outlay, annual operating cash flow, terminal cash flow and the NPV (a template to help you start is provided). 1) Calculate the initial outlay, annual operating cash flow and terminal cash flow (4 points). 2) Complete the table with the timeline and calculate the NPV of the project. Is the project acceptable? (4 points) Part b Extended model, simulation (42 points): Requires you to extend the model and use Monte Carlo simulation to incorporate risk. The uncertain variables and their probability distributions are given below. Variable Distribution Quantity sold Triangular with a minimum of $250,000, most likely of 400,000 and maximum of 580,000 Advertising Expenses Normal with a mean of $20,000 and a standard deviation of $300 Actual salvage value of the equipment Uniform with a minimum of $120,000 and a maximum of $340,000 Note that each years quantity sold and advertising expenses are independent of the quantity sold and advertising expenses of other years, so they have to be simulated separately. 1) Run the simulation 10,000, create a brief report of the simulation and attach it to the spreadsheet as a separate sheet (14 point) 2) Using the simulation outputs find the probability that the NPV will be less than zero. What is the expected NPV? Would you suggest that the project be accepted? (You need to use functions to compute the P(NPV)<0 and expected NPV) (14 points) 3) What is the uncertain variable that affects NPV the most? Why? (Answer should be based on the simulation sensitivity analysis chart) (14 points)

Answer & Explanation Solved by verified expert

See Answer