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.
You must submit:
Your Excel workbook with all your analyses.
Your Executive Summary report.
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.
- 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.)
- 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.
2.1 Calculate the probability of meeting the assembly target on any given day based on the simulation.
2.2 Calculate the standard error of the probability of meeting the assembly target (See accompanying video.)
2.3 Calculate the lower and upper limits of the 95% confidence interval of the probability of meeting the assembly target. (See accompanying video.)
2.4 Find the minimum assembly time based on the simulation.
2.5 Find the maximum assembly time based on the simulation.
2.6 Calculate the median assembly time based on the simulation.
2.7 Calculate the mean assembly time based on the simulation.
2.8 Calculate the standard deviation of the assembly time based on the simulation.
2.9 Find the minimum number of bikes assembled based on the simulation.
2.10 Find the maximum number of bikes assembled based on the simulation.
2.11 Calculate the median number of bikes assembled based on the simulation.
2.12 Calculate the mean number of bikes assembled based on the simulation.
2.13 Calculate the standard deviation of the number of bikes assembled based on the simulation.
- Create a frequency histogram of the 500 total time simulations.
- Create a snapshot of your results in a new worksheet (see Simulation Modeling Demonstration video for instructions).
- 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.
