1. 2. 3. F1. Upen the workbook named Partsxlsx and then save It as M ISU Parts analyslslyour last name].xlsx. Modify the format of the data on the BearingData worksheet so that zero values are displayed with a D instead of the default dash, aligned on the right side of the column. 0n the BearingData worksheet, take the existing 30day data for these bearings for each customer to calculate the high and low limits of the bearing demand by customer. [In a new worksheet named "Simulation\" {no quotes}, use the high and low limits you just calculator to simulate daily requirements for each customer to obtain a combined daily requirement. In cell A3, enter "Day\". Copy the customer numbers from the Bearing Data sheet and paste them in Simulation in cells BSKS. In column A, starting in cell .514, create a numeric list of days {A4 will be 1, A5 will be 2, etc.) up to 240. Assume that the daily requirements will vary for each customer randomly between the high and low limits you have calculated from the existing SIDday data for that customer. Generate the data for 240 instances {days} for each customer in cells B4:K243. Create a new worksheet named \"Simulation Data 1\" [no quotes}. Set this up to look exactly like the Simulation worksheet same customer numbers and days in the same cells. Copy the data you generated in Simulation cells E4:l(243 as values {paste special as values} to the same cells in Simulation Data 1. a. FYI we copy and paste special the data because the function used to create these numbers will run (and update] every time you make a change to your workbook, changing the numbers. Ely pasting the values in another sheet, we prevent the accidental changing of the values generated. On the Simulation Data 1 worksheet. in column | . calculate the total demand by dav for the 1f} 1CII. 11. On the Simulation Data 1 worksheet, in column L, calculate the total demand by day for the 1D customers in cells L4:L243. Use this daily demand total data to calculate the overall mean, mode, median, and standard deviation for the combined requirements of all 10 customers in cells P21:P24 respectively. Label the cells in 0211324. Use cell shading to clearly identify your calculation area. Generate a ranking ofthe data {1 to 240] in cells M4:M243 so that the day with the least total demand has a rank of 1 {HINT: Use the RANKEQ function}. Put "rank\" in cell M3. Again, use cell shading to clearly identifyr columns with your calculations. Create a listing ofthe top [cells P4:P8l and bottom {cells {14:08} five daily combined demands from the simulated instances (HINT: Use the LARGE and SMALL formulas}. Clearly identify this listing using borders and shading. Recommend a target inventory level needed for a fiveday period based on the following: to be on the cautious side, assume each day's supply will be equal to the average daily demand for all locations combined, plus three standard deviations. Since the mean and standard deviations might not already be integers, round the daily demand up to the nearest whole number in your formula. Then, use this calculated daily demand {average plus 3 standard deviations} to calculate demand over a fiveday period. Place this recommendation in cell P14, again clearly identifying it. Add the label Bearing Recommended to identify the cell containing the actual value. The warehouse manager has tracked a total of five different parts over the past five days, recording for each shipment the value of the part and the number of days it was in storage. One of these tracked parts is the bearing you have just analyzed. The data has been compiled in a workbook named Demandsodsx. Each line item represents a single shipment of one item. Create a new worksheet named "Actual Demand\" [no quotes). Copy the data from the Demandxlsx workbook to Actual Demand. In Actual Demand, summarize the data to determine the number shipped by part, the total values of those shipments by part, and the average number of days that part was stored. Create this table below in cells F1: IE