Production/Inventory Planning

A manufacturing plant supplies various engine components to manufacturers of motorcycles on a just-in-time basis. Planned production capacity for one component is 100 units per shift. Because of fluctuations in customers' assembly operations, demand fluctuates and is historically between 80 and 130 units per day. To maintain sufficient inventory to meet its just-in-time commitments, Tracway's management is considering a policy to run a second shift if inventory falls to 50 or below. For the annual budget planning process, managers need to know how many additional shifts will be needed. The fundamental equation that governs this process each day is:

Ending inventory = Beginning inventory + Production - Demand

Develop an Excel spreadsheet model to simulate 260 working days (one year) and count the number of additional shifts that are required. Use this variable as a forecast cell for a Crystal Ball simulation of the model to find a distribution of the number of shifts required over the next year. Then use the Crystal Ball Bootstrap tool to find the sampling distribution of the maximum number of additional shifts required and compute a 95% confidence interval for this statistic. Summarize your findings in a report to the plant manager.