The Salida Salt Company is considering making a bid to supply the highway department with rock salt to drop on roads in the county during the winter. The contract will guarantee a minimum of 25,000 tons in each year, but the actual quantity may be above that amount if conditions warrant.
A. Set up a worksheet containing all of the relevant information in this problem, and operating cvash flow statement that shows the total annual cash flows for each yearm including the initial outlay.
B. Calculate the payback period, NPV, IRR, and MIRR of this project, Is this project acceptable.
C, IF the state decided to open the project for competitive bidding, what is the lowest bid price that you can enter without reducing shareholder wealth? Explain why your answer is correct.
D. Perform a Monte Carlo simulation with 1,000 trials to determine the expected NPV and the standard deviation of the expected NPV. The uncertain variables and their probability distribution are given below. The quantity of rock salt sold should be simulated for each year independently of the others (i.e., it is five separate variables)
Variable- Tons of rock salt in each year. Distribution- Triangular with a minimum of 25,000, most likely of 40,000, and maximum of 50.000
Variable- Variable cost per ton. Distribution- Normal with a mean of $95 and a standard deviation of $5.
Variable- Salvage value of equipment. Distribution- Uniform with a minimum of $70,000 and a maximum of $200,000.
E. Create a histogram showing the probability of distribution of NPV.
Using the output of the simulation, what is the probability that the NPV will be less than or equal to Zero? Would you suggest that the project be accepted?
Please provide solutions in Excel compatible mode and also please link formulas to corresponding cells.