Question

(All answers were generated using 1,000 trials and native Excel functionality.)

The management of Brinkley Corporation is interested in using simulation to estimate the profit per unit for a new product. The selling price for the product will be $45 per unit. Probability distributions for the purchase cost, the labor cost, and the transportation cost are estimated as follows:

 

Procurement Cost ($)	Probability	Labor Cost ($)	Probability	Transportation Cost ($)	Probability
10	0.25	20	0.10	3	0.75
11	0.45	22	0.25	5	0.25
12	0.30	24	0.35
		25	0.30

 

 

(a)	Construct a simulation model to estimate the average profit per unit. What is a 95% confidence interval around this average?
	Round your answers to two decimal places.
	Lower Bound: $
	Upper Bound: $

Answer 1

The available date to solve the problem is as below,

a.

Calculate profit per unit for base case, worst-case, and best-case as shown below:

The formula to calculate profit is as below:

….. (1)

Now, substitute the values in the equation (1) as shown below,

Calculate the profit per unit for base case as shown below,

Calculate the profit per unit for worst-case as shown below,

Calculate for profit per unit for best-case is as shown below:

b.

The procedure to find the estimated mean profit per unit is as shown below:

1. Open excel sheet.

2. Go to formula tab on menu bar.

3. Now, select insert function and search for Psi function.

4. Now, use PsiDiscrete ($X$1: $X$5, $Y$1: $Y$5) function and select the target cells.

The formula to find the estimated mean profit per unit in spreadsheet is as shown below:

c.

Simulation will deliver a distribution of the profit per unit values. Scheming the percentage of replication trials providing a profit that is less than $5 per unit would deliver an estimate of the probability the profit per unit will be unsatisfactorily low.

d.

The results from simulation to estimate the probability that the profit per unit will be less than $5 in spreadsheet is as shown below:

As seen in the chart above, there is approximately a probability that profit per unit will be less than $5.