A bike assembly company (Ready to Go Bikes) assembles bikes for retail bike shop chains and ships the assembled bikes to the bike shops on a daily basis. This allows the bike shops to put them on the showroom floor for sale immediately.

A new bike shop franchise owner is opening three bike shops in Delaware. She has asked the Ready to Go Bikes to supply 12 bikes per day. The assembly shop manager at Ready to Go Bikes has been asked by the company president if he can meet the demand if one new fully trained assembly staff person is hired. As the Business Data Analyst for Ready to Go Bikes, you decide to run a simulation model to determine the likelihood that the demand can be met. (Note: You will use Excel’s built-in capabilities to complete this assignment.)

In the workbook (Ready to Go Bikes Assembly Simulation.xlsx Download Ready to Go Bikes Assembly Simulation.xlsx), you will find the mean and standard deviation for the three steps in bike assembly (Unpacking, Assembly, Tuning). Assume that the variation in completion time for each step follows a normal distribution. Also, assume that any surplus of bikes made in a day cannot be used to meet the next day’s demand because they will be shipped to other shops. For example, if the new staff member assembles 14 bikes one day, the extra two bikes are not available the next day. Finally, assume that there are no partially completed bikes at the end of the day. This means that the Bike Assembled column is always a whole number.

The assembly staff person works an 8 hour day.

1. Create a simulation model in Excel based on 500 simulations using the known data and the assumptions above. (Watch the accompanying simulation demonstration video called Simulation Modeling Demonstration.)
2. Your final model should include all the summary statistics shown below and in the workbook Ready to Go Bikes Assembly Simulation.xlsx.Download Ready to Go Bikes Assembly Simulation.xlsx.
   1. Calculate the probability of meeting the assembly target on any given day based on the simulation.
   2. Calculate the standard error of the probability of meeting the assembly target (See accompanying video.)
   3. Calculate the lower and upper limits of the 95% confidence interval of the probability of meeting the assembly target. (See accompanying video.)
   4. Find the minimum assembly time based on the simulation.
   5. Find the maximum assembly time based on the simulation.
   6. Calculate the median assembly time based on the simulation.
   7. Calculate the mean assembly time based on the simulation.
   8. Calculate the standard deviation of the assembly time based on the simulation.
   9. Find the minimum number of bikes assembled based on the simulation.
   10. Find the maximum number of bikes assembled based on the simulation.
   11. Calculate the median number of bikes assembled based on the simulation.
   12. Calculate the mean number of bikes assembled based on the simulation.
   13. Calculate the standard deviation of the number of bikes assembled based on the simulation.
3. Create a frequency histogram of the 500 total time simulations.
4. Create a snapshot of your results in a new worksheet (see Simulation Modeling Demonstration video for instructions).
5. Using your snapshot, write a brief Word document summary of your simulation process and findings for the assembly shop manager including the likelihood that demand will be met on any given day.
   • Guidance for the summary report
     • Include a description of the problem/question that is being addressed.
       • What is the question?
       • What technique/model is used to address the question?
       • What are the assumptions that were made to run the model?
     • Summarize the major findings.
       • What is the predicted probability of meeting the bike demand target?
       • What is the confidence interval around the predicted probability?
       • What is the shape of the distribution of assembly times?
       • What is the minimum and maximum bike assembly time in the simulation?
       • What are the mean and median bike assembly time in the simulation?
       • What is the minimum and maximum number of bikes assembled in a day in the simulation?
       • What are the mean and median number of bikes assembled across all days in the simulation?
     • State your conclusions and any recommendations that you have.
     • Include any tables and graphs that support your report findings. 

Target Number of Bikes Assembled (Demand) =

12

Simulation Number

Total Time

Bikes Assembled

Work hours in the Day =

8

1,2,3

4

Completion Time for Bike Assembly Steps (minutes)

5

Unpacking

Assembly

Tuning

Total Time

6

Mean

3.20

19.70

12.10

35.00

7

Standard Deviation

0.70

2.40

2.70

8

9

Simulation Base

10

11

Probability of Meeting the Target

12

Standard Error of the Probability

13

95% Confidence Interval Lower Limit of the Probability

14

95% Confidence Interval Upper Limit of the Probability

15

16

Minimum Assembly Time

17

Maximum Assembly Time

18

Median Assembly Time

19

Mean Assembly Time

20

Standard Deviation of Assembly Time

21

22

Minimum Number of Bikes Assembled

23

Maximum Number of Bikes Assembled

24

Median Number of Bikes Assembled

25

Mean Number of Bikes Assembled

26

Standard Deviation of Number of 
Bikes Assembled

27

NUMBERS CONTINUE TO 500

Answer rating: 100% (QA)

To perform the simulation and calculate the required statistics follow these steps 1 Open the Excel file Ready to Go Bikes Assembly Simulationxlsx 2 In the Simulation Number column Column A enter the ... View the full answer