Modify the format of the data on the BearingData worksheet so that zero values are displayed with a 0 instead of the default dash, aligned on the right side of the column. On 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. On a new worksheet named "Simulation\" {no quotes}, use the high and low limits you just calculated 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 BEK3. In column A, starting in cell A4, 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 30day data for that customer. Generate the data for 240 instances {days} for each customer in cells BAH-(243. Create a new worksheet named \"Simulation Data 1\" [no quotes}. Set this up to look exactly like the Simulation worksheetsame customer numbers and days in the same cells. Copy the data you generated in Simulation cells B4:K243 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. By pasting the values in another sheet, we prevent the accidental changing of the values generated. On the Simulation Data 1 worksheet, in column L, calculate the total demand by day for the 10 customers in cells L4:L243. Use this daily demand total data to calculate the overall mean, mode, 9 . LUhlUllltlh IIl Ltfllh LH'.L'+D. Uht: llllb LJETIIIIEII UUIIIGIIU LUldI Udld I.L_.l LdILUIdlU LII: U'U'tildll IIIUEIIIF IIIUUU' median, and standard deviation for the combined requirements of all 10 customers in cells P21:P24 respectively. Label the cells in 021:024. Use cell shading to clearly identify your calculation area. Generate a rankinglof the 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 RANK.EQ function}. Put "rank\" in cell M3. Again, use cell shading to clearly identify columns with your calculations. Create a listing of the top [cells P4:F'8]I and bottom {cells Q4118} 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. 10. The warehouse manager has tracked a total of five different parts over the past five days, recording 11. 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 Demandxlsx. Each line item represents a single shipment of one item. Create a new worksheet named "Actual Demand\" [no quotes}. Copy the data from the Demand.xlsx 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. Item Description Bearings II Items Shipped Total Dollar 1'v'alue of Average II Days Held Items Shipped in Inventory Timing Belts Air Filters Fan Belt Electronic Board In the it Items Shipped column, write a formula to determine the number of bearings shipped. Write the formula so that it can be copied down the column to automatically determine the number of timing belts shipped, the number of air filters shipped, and so on. In the Total Dollar Value of Items Shipped column, write a formula to determine the value of all bearings shipped. Again, write the formula so that it can be copied down the column to automatically determine the value of timing belts, air filters, and so on. In the Average #Days Held in Inventory column, write a formula to determine the average number of days bearings shipped were held in inventory; again, write the formula so that it can be copied down the column. Based on the recommended inventory level you previously calculated for bearings, would you have had enough bearings in the warehouse to cover these orders? Place your answer in cell F9 on the Actual Demand worksheet. Clearly identify this answer and highlight it in yellow. Doublecheck all values and formulas for correct implementation. Include sufficient formatting and titles to clearly identify the worksheet elements. Save and close the MTSU Parts Analysisxlsx workbook.