Please fill out the spreadsheet by following these directions.. Please include the formulas, Thank you for your!

\f12:04 74 029% X g+ ... A B C D E F G H Description Value #Days in Inventory IN Timing Belts 34.95 31 Fan Belt 3.59 34 4 Fan Belt 3.59 23 Fan Belt 3.59 19 6 Bearings 43.25 35 7 Bearings 43.25 11 8 Fan Belt 3.59 19 9 Bearings 43.25 18 10 Bearings 135 21 71 Bearings 135 31 12 Bearings 43.25 12 13 Bearings 135 35 14 Timing Belts 34.95 11 15 Bearings 135 40 16 Air Filters 2.25 35 17 Bearings 135 33 18 Timing Belts 34.95 35 19 Bearings 43.25 25 20 Bearings 43.25 6 21 Air Filters 2.25 37 22 Timing Belts 34.95 18 23 Fan Belt 3.59 8 24 Bearings 135 18 25 Bearings 135 32 26 Air Filters 2.25 36 27 Fan Belt 3.59 11 28 Fan Belt 3.59 19 29 Bearings 43.25 8 30 Air Filters 2.25 30 31 Fan Belt 125 35 32 Bearings 43.25 4 33 Timing Belts 34.95 19 34 Fan Belt 3.59 22 35 Air Filters 2.25 21 36 Fan Belt 3.59 17 37 Air Filters 2.25 2 38 Air Filters 2.25 36 39 Air Filters 2.25 3 40 Fan Belt 125 20 41 Timing Belts 34.95 42 Fan Belt 34.95 38 43 Bearings 43.25 13 44 Bearings 34.95 30 45 Fan Belt 3.59 40 46 Bearings 135 32 47 Fan Belt 125 15 48 Fan Belt 3.59 18 49 Fan Belt 3.59 31 50 Air Filters 34.95 33 51 Air Filters 2.25 6 52 Air Filters 2.25 19 53 Timing Belts 34.95 15 54 Bearings 43.25 55 Fan Belt 3.59 40 56 Bearings 135 37 57 Bearings 135 27 58 Bearings 135 38 59 Air Filters 2.25 8 60 Roaringc 12 25 38 Sheet1 +12:04 "D" 9A D 29% 2. 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 30-day 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, use the high and low limits you just calculated to simulate daily requirements for each customer to obtain a combined daily requirement. 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 180 instances (days) for each customer, and then copy the results as values (paste special as values) to another new worksheet names Simulation Data 1. We copy the data over because the function used to create these numbers will run every single time you make a change to your workbook, changing the numbers. By pasting the values in another sheet we prevent the 12:04 "D" VA D 29% 4. On a new worksheet named Simulation, use the high and low limits you just calculated to simulate daily requirements for each customer to obtain a combined daily requirement. 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 180 instances (days) for each customer, and then copy the results as values (paste special as values) to another new worksheet names Simulation Data 1. We copy the data over because the function used to create these numbers will run every single 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 an adjacent column to the simulation data, calculate the total demand by day (for all 10 customers). Use this daily demand total data to calculate the overall mean, mode, median, and standard deviation melned 12:04 "D" VA D 29% 5. On the Simulation Data 1 worksheet, in an adjacent column to the simulation data, calculate the total demand by day (for all 10 customers). Use this daily demand total data to calculate the overall mean, mode, median, and standard deviation for the combined requirements of all 10 customers. Use cell shading to clearly identify your calculation area. Extend your analysis (on the same worksheet) to include a ranking of the data (1 to 180) so that the day with the least total demand has a rank of 1. (HINT: Use the RANK.EQ formula) Again, use cell shading to clearly identify columns with your calculations. To the right of the data, create a listing of the top and bottom five daily combined demands from the 180 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 averagadailydemand for all 12:05 9 "U" VA El 29% 5. On the Simulation Data 1 worksheet, in an adjacent column to the simulation data, calculate the total demand by day (for all 10 customers). Use this daily demand total data to calculate the overall mean, mode, median, and standard deviation for the combined requirements of all 10 customers. Use cell shading to clearly identify your calculation area. Extend your analysis (on the same worksheet) to include a ranking of the data (1 to 180) so that the day with the least total demand has a rank of 1. (HINT: Use the RANK.EQ formula) Again, use cell shading to clearly identify columns with your calculations. To the right of the data, create a listing of the top and bottom five daily combined demands from the 180 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 12:05 9 "U" VA El 29% 8. 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. Then, use this calculated daily demand (average plus 3 standard deviations) to calculate demand over a fiveday period. Place this recommendation just below the top/bottom analysis, 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 Demand.xlsx. Each line item represents a single shipment of one item. Copy the data 12:05 9 "U" VA El 29% 9. 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 Demand.xlsx. Each line item represents a single shipment of one item. Copy the data from the Demand.xlsx workbook to your workbook and place it on a worksheet named Actual Demand. . On the same worksheet, 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, using the format shown in the table below uni 9A El 29% It I!ll I!ll I!l I!l I!ll In the # 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, A 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. 11. Based on macecommended the table below 12:05 10 4 029% Total Dollar Value of Average # Days Held Items Shipped in Inventory blank blank blank blank blank blank blank blank blank blank In the # 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 12:05 8 0 9 4 029%% ne value of timing belts, air filters, Item Description # Items Shipped Bearings blank and so on. In the Average #Days Held blank blank Electronic Board blank Inventory column, write a formula In the # 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 average number of 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 ays bearings shipped were held in determine the value of all bearings ped. Apain, write the formula so that it can be copied down the column to automatically determine write the formula the value of timing belts, air filters, and so on. In the Average #Days Helo in Inventory column, write a formula to determine the average number of days bearings shipped were held in inventors again, write the formula so that it can be copied down the Share Edit wn the column. 11. Based on the recommended 11. Based on the recommended12:05 9 \"U" VA El 29% 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 a cell below your analysis 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