NAME: NAME: Plan Requirements for Simulation 2 Inventory and Capacity Management by Littlefield Technologies Submit the Excel Plan in Canvas by 11:15 am Friday 4-14-23. The simulation will begin at11:15 am on Monday 4-17-23 and end on Friday 4-21-23 at 11:15 am. The Plan consists of the sections given below and is worth 20 points. You are REQUIRED to show how all cell values were derived to receive full credit. You can either cell reference each calculation or indicate in writing how you obtained the values in each cell. Values in cells without any calculation referenece will receive a grade of zero. Forecast of demand: Demand Forecast Use excel formulas and cell references to calculate all information. The forecast for the following days is based on the demand pattern from the first 50 days PLUS the information given in the Miyaoka Word document explanation about the simulation. Enter the point forecast for each in the chart day, and the upper and lower bound limits. These MUST be CALCULATED and clacutions shown! Forecast Limits DAY Point Forecast Sigma() Standard Deviation of Demand Lower Bound Upper Bound actual value used to calculate capacity and inventory requirements; this will be a number between the lower and upper bound 51 11.16 3.44 0.84 21.48 12 75 10.2 3.44 -0.12 20.52 10 100 9.2 3.44 -1.12 19.52 4 125 8.2 3.44 -2.12 18.52 9 150 7.2 3.44 -3.12 17.52 8 175 6.2 3.44 -4.12 16.52 12 200 5.2 3.44 -5.12 15.52 4 225 4.2 3.44 -6.12 14.52 2 250 3.2 3.44 -7.12 13.52 5 Customer Response Strategy Your team can respond to the customer and obtain a premium price if it delivers the contract on time. If the contract is not delivered on time, then the penalty per job depends on the contract you selected. The information in the table below can be found at the web site and in the simulation 2 Word document Quoted Maximum Maximum lead time lead time Revenue Contract Data (days) (days) ($$) Contract 1 7 14 750 Contract 2 1 3 1000 Contract 3 0.5 1 1250 Your team should not switch contracts until you have reduced your lead time to the quoted lead time. Enter your contract goals below. (Put an 'X' below the contract you intend to be using on that day.) DAY Contract 1 Contract 2 Contract 3 51 75 100 125 150 175 200 225 250 Forecast of capacity needs: The amount of capacity you will need on any given day depends on the demand and on the time required to process each job on each machine. A. Calculate the time required per job on each work station by using the data from the first 50 days. Work Station 1 (from cell J66) Work Station 2 (from cell J67) Work Station 3 (from cell J68) Processing time per job 5.579057592 1.916230366 1.84921466 NOTE: be sure to show your calculations by using formulas in the worksheet below. Worksheet: Use the following steps for each work station Work Station No. of Machines at the workstation 24 Hours Available to work a day Total Hours Available to work in 50 days(show calcuations in cell) Average Utilization for 50 days(show calcuations in cell) Hours used in 50 days(show calcuations in cell) Jobs processed in 50 days at work station(show calcuations in cell) Hours Per Job (i.e., process time per job)(show calcuations in cell) 1 3 24 3600 0.888 3196.8 573 5.579057592 2 1 24 1200 0.915 1098 573 1.916230366 3 1 24 1200 0.883 1059.6 573 1.84921466 B. Show forecasted capacity requirements for each work station below. Express it as the number of machines needed on that day. Remember that in this simulation, each machine provides 24 hours of capacity. Work Station 1 Work Station 2 Work Station 3 DAY Hours Machines Hours Machines Hours Machines 51 Days 51 to Day 268 are based on the forecast 75 66.9 2.8 23.0 1.0 22.2 0.9 100 55.8 2.3 19.2 0.8 18.5 0.8 125 22.3 0.9 7.7 0.3 7.4 0.3 150 50.2 2.1 17.2 0.7 16.6 0.7 175 44.6 1.9 15.3 0.6 14.8 0.6 200 22.3 0.9 7.7 0.3 7.4 0.3 225 11.2 0.5 3.8 0.2 3.7 0.2 250 27.9 1.2 9.6 0.4 9.2 0.4 Forecast of inventory plan A. Calculate the standard deviation in demand experienced during the first 50 days; it will be the same value used for the demand forecast. Put the data into sheet 2, do not put it here just put the standard deviation here. 3.44 B. State the delivery lead time for replenishment from supplier here (see simulation 2 Word document and Littlefield simulation overview document) C Calculate the average demand during the delivery lead time here (show your work with cell reference calculations). D. Calculate the safety stock here. State why you selected the z level that you used. (Show your work with cell reference calculations) E. Calculate your reorder point here. Show your work with cell reference calculations F. Calculate the EOQ here. Use 268 days as a year. Show your work with cell reference calculations use $60/batch/year as the inventory holding cost See Littlefield Simulation Overview Document for fixed ordering cost