Question

Burlington Mills produces denim cloth that it sells to jeans manufacturers. It is negotiating a new contract to provide cloth on a weekly basis to BJ Jeans. The demand for cloth from BJ Jeans is expected to vary each week according the following discrete probability distribution:

Demand (yd)
0 0.05

100 0.15 200 0.40 300 0.30 400 0.10

Burlington’s plant capacity available for this new job will vary each week because of other commitments and occasional breakdowns. Burlington estimates that available capacity will vary from 100 to 500 yards and follow a Uniform probability distribution (see note below).

Simulate the performance of the Burlington plant for 20 weeks. You may manually write your results provided your work is clear, neat, and easy to read. Attach your simulation to the Managerial Report.

Using your simulation results, determine the following:

1. Average weekly demand for cloth from BJ Jeans.

2. Average weekly available capacity for this contract at the Burlington plant.

3. Number of weeks that demand exceeds available plant capacity. Based on this result, also

   calculate the probability that demand will exceed available capacity.

Probability

Page 2 of 3

NOTE:

A Uniform probability distribution is one for which any value in a number interval is equally likely. In the Burlington example, then, available capacity will vary from 100 to 500 yards, with any value in this interval as probable as any other.

Since the calculator’s rand function returns a random real number from a Uniform distribution between 0.0 and 1.0, we can use it to model any Uniform distribution:

Low Value + rand*(High Value – Low Value)

For example, the weekly available capacity for the BJ Jeans contract varies uniformly from 100 to 500 yards. If in the simulation of some week, the rand function generates a value of 0.3486, then for that week the randomly selected available capacity will be:

100 + 0.3486*(500 – 100) = 239.44 yards

Answer 1

Please find the simulation and answers on the right side

Explanation and Steps

Initial 4 rows are just an initial check for random number, demand and capacity. Actual Simulation starts from A7

1. A8:A27 are week numbers, starting from 1 to 20
2. B8: B27 are random number variables. The random number is generated by RAND(), entering RAND() in all cells from B8:B27 gives us random numbers
3. Simulation of Demand: Demand follows a discrete probability as shown in the problem. For each cell, corresponding to the random number generated, we simulate demand as IF(B8<0.05,0,IF(B8<0.2,100,IF(B8<0.6,200,IF(B8<0.9,300,400)))) where B8 is random number and the resultant demand is entered in C8. For next cell, the formula would be IF(B9<0.05,0,IF(B9<0.2,100,IF(B9<0.6,200,IF(B9<0.9,300,400)))) and so on
4. Capacity is simulated by 100+(500-100)*RAND() for each cell

Results

• Mean demand: average of the cells C8:C27 = 235
• Mean Capacity : average of the cells D8:D27 = 263.11
• Number of weeks demand exceed capacity is 9. I have entered a column E7:E27, where I am entering 1 if demand is greater than capacity, otherwise 0. IF(C8>D8,1,0). And then take the sum of this E8:E27 to get the count
• Probabilty = 9/20 = 0.45