Using only excel solve the following: The parking superintendent is responsible

Question

Using only excel solve the following: The parking superintendent is responsible for snow removal at his parking garage. The probabilities for the number of days per year requiring snow removal are shown in the chart below. These probabilities are independent from year to year. The superintendent can contract for snow removal at a cost of $500 per day. Alternatively, he can purchase a snow-removal machine for $40,000. It is expected to have a useful life of 10 years and no salvage value at that time. Annual costs for operating and maintaining the machine are estimated to be $14,000. MARR (Minimum attractive rate of return) is 10% per year.

Number of Days per Year

Probability

For the following questions, determine an analytical solution:

a)Determine the mean and standard deviation of the present worth of the savings resulting from purchasing the snow-removal machine.($)
b)Assuming the present worth is normally distributed, what is the probability of a positive present worth of the savings resulting from purchasing the machine?(%)

c)For the following questions, determine a simulation solution using @RISK:
d)Using a Latin hypercube simulation with 10,000 iterations, estimate the mean and standard deviation of present
worth and the probability of positive present worth.

e)Using a Monte Carlo simulation with 10,000 iterations, estimate the mean and standard deviation of present
worth and the probability of positive present worth.

Number of Days per Year

Probability

Explanation / Answer

a) The easier way to obtein the result is to calculate the mean number of days per year with snow

To obtain the average its necessary to multiply the probability for the number of days and summarize all values

Mean = (20*0.15)+(40*0.15)+(60*0.35)+(80*0.3)+(100*0.05)=59 days per year

If we pay for each day, mean that we will spent an average of 59 days * $500=$29,500 per year

Because each year is independent, we multiply that value for 10 years, $29,500*10= $295,000, if we buy the snow machine it will cost $40,000+ maintaingin cost per year=($14,000*10)=$140,000, total cost=$140,000+$40,000=$180,000

Then the mean savings will be $295,000-$180,000=$115,000

Then to obtain the standar deviation, we use the same logic,

fisrt we calculated the standar deviation for one year

standar deviation = squared root ((20-59)2+(40-59)2+(60-59)2+(80-59)2+(100-59)2)/300)=3.65 days per year

3.65*$500=$1,826.88 per 10 years = $18,268,80

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.