Your small biotech firm operates a fleet of two specialized delivery vans in Chicago. As a policy, your firm has decided that the operational life of a van is 3 years (a cycle), and both vans are purchased at the same time to receive discounted fleet pricing. The driving demands placed on the vans are uncertain, as are the maintenance costs, and each van is different in its use, demand, and costs. In the past, the firm has been surprised by unexpectedly high (and low) maintenance costs associated with the vans; thus, it is important to analyze the potential of cost variation and to use this information in the annual-budgeting process. You decide to model the arrival of failures (breakdowns of the van) that lead to maintenance costs-each failure has a cost.

You and your staff decide that the model should be simple, but that it should reflect reality. The model should also determine the variation in maintenance costs for 3-year cycles of vehicle use. To determine maintenance cost, you assume the following: 1) Miles Demand for each van is randomly selected from a defined probability distribution (Table 1) for each year of operation; thus, 3 Miles Demand (one for each year) for each van in a cycle. 2) Once the Miles Demand is known, a Yearly Failure Rate is determined (Table 2). This is a Poisson-average yearly arrival rate and a Poisson distribution with this arrival rate is then sampled to determine Actual number of Failures. 3) Each failure arrival is assigned a randomly selected cost from a set of normally distributed costs (Table 3). Finally, costs are aggregated for all vans over the 3 year cycle (an experiment) and many trials are simulated to create a risk profile for total 3-year maintenance cost.

a) Create a Monte Carlo simulation that simulates the 3-year cost of maintenance for the fleet. A suggested structure is provided to simplify your efforts. Simulate 5000 trials (experiments).

b) Provide the risk profile for the model in (a), along with the summary statistics-mean, standard deviation, and 5th and 95th percentile.

c) Calculate the 95% confidence interval for the mean of the simulation.

d) What is the value ($ reduction in cost) that you would derive if you could reduce the Yrly Fail-Rate by 1 for all Miles Demand for Van 1, through a preventative maintenance program? For example, in table 2 the rate for 25000 would change to 1, the rate for 40000 would change to 2, etc. Produce the new Risk Profile and determine the new summary stats.

e). How much would you budget for the 3-year maintenance cycle to meet up to 90% of the maintenance costs?

Table 1

Table 2

Table 3

Attachment:- Problem Excel.xlsx