Could you help me to work out these instructions and what formulas and steps to use in excel would be greatly appreciated?

are to be comple ted in the Inventory Worksheet The following Section C Go to the Inventory Sheet. This sheet contains a list of all the items the company keep in stock to build the robotics. Each part is identified by a unique code. Convert the Range A3:153 to a Table. Name the table Inventory. Name the following ranges: C1 C2 A4:A53 Code Cost_Price E4:E53 (and any other ranges you feel may prove useful) In F3:F53 calculate the retail price (paid by customer) using the Cost Price and the % Markup in L3. (Note the markup may be changed and your calculation should still produce the correct answer.) L4 indicates the maximum stock level. In G3:G53 create a formula to check if the 4 quantity in stock is over the maximum stock in L4 (this level can be adjusted). If it is over the stock level put Yes in the cell, otherwise leave it empty (do not put No or a space). Formula should copy down, check it is working correctly L5 indicates the minimum stock level while column D indicates if an item is on back 5 order (already been reordered). If a stock item is below the min imum and not on Backorder (is blank) we need to reorder it. In H3:53 create a formula to check if the item needs to be re-ordered. If it does put Yes in the cell, otherwise leave it empty (do not put No or a space). Formula should copy down, check it is working correctly. Reorder quantities are based on price as shown in the Reorder Quantities data (K11:L16), e.g. currently for items that cost from $100 up to (but not including) $300 6 we re-order 35. Create a calculation in 14 that will check if we need to reorder and if so return the correct reorder amount otherwise return 0. Ensure the formula is copied down for the whole table. Note stock levels and reorder quantities will change, you should have one consistent calculation for the column and the calculations should work regardless of values or sort order of the inventory data. In L6 calculate how many item codes require reordering. C7 8 In L7 calculate how many item codes are over stocked. Use Excel's built in help to investigate the SUMPRODUCT function. In L8 calculate the total cost of all inventory items currently in stock. CG fx H4 A B C D E F G H J K L |Inventory 1 2 3 Code 4 C1019 5 M1021 6 M1022 7 R1001 On Backorder Cost Price (each) Description Connedtor Pack MGX 8025 Chip Set MGX 8066 Chip Set Robot Motor Klt Robot Kit 33A Reorder Qty In Stock Relail Price Over-Stocked Re-Order? % Markup Maximum Stock Level Minimum Stock Level No.Item Codes to re-order 28% $90,75 25 0 $70.90 C Yes $129.80 $166.14 0 $151.20 $193.54 0 $1,387.00 $1,775.36 $2,779.52 No. Item Codes Over-Stacked 8 R1002 9 R1003 10 R1004 4. $2,171.50 $2,798.10 $2,711.90 $2,745.00 Total Inventory Value Robot Kit 38A 0 Yes $3,581.57 Reorder Quantities Robot Kit 38G Robot Kit SP3 Rabot Kit 448 $3,471.23 $3,513.60 $3,876.86 C 11 R1005 12 R1006 Re-Order Qty 11 Item Cost $3,028.80 24 50 13 R1007 $100 $300 Robot Kit 555 2 Yes $3,292.60 $4,214.53 35 Relays Small Pack Relays Medium Pack Relays Large Pack Servo Motors A21 (4 pack) Servo Motors B21 (4 padk) Servo Motors A21 (8 padk) Servo Motors B21 (8 padk) SMC Pneumatic Part 3-44 SMC Pneumatic Part X-22 SMC Pneumatic Part X-33 SMC Pneumatic Part 3-47 SMC Pneumetie Part 3-48 SMC Pneumatic X 200mm SMC Pneumatic X 250mm SD Drive emc Connector Pock leme MGX 8025 Ohip Set |emc MGX 8066 Chip Set emc Robot Motor Kit emc Robot Kit 33A emc Robot Kit 38A emc Robot Kit 3BG emc Robot Kit SP3 emc Robot Kit 448 14 R1023 20 26 $272.80 $349.18 YES $500 $1,000 15 R1024 10 1 Yes $298.50 $382.08 16 R1025 17 $1008 18 S1009 19 S1010 20 $1011 21 S1012 22 S1013 23 S1014 24 S1015 25 S1016 26 S1017 27 $1018 28 S1020 29 C1219 30 M1221 31 M1222 32 R1201 33 R1202 34 R1203 35 R1204 36 R1205 37 R1206 38 R1207 39 R1223 40 R1224 41 R1225 42 $1208 43 S1209 44 S1210 45 S1211 46 S1212 47 $1213 48 S1214 49 $1215 50 $1216 51 $1217 52 $1218 53 S1220 $313.00 $400.64 5 Yes 8 $1,171.58 $915.30 Yes $1,061.40 $1,662.50 $1,980.00 $605.80 $776.40 $764.40 $717.20 $1,358.59 2,128.00 39 YES 26 $2,534.40 $775.42 YES 0 Yes $993.79 $978.43 12 Yes 4 $918.02 27 $734.80 $940.54 YES S537.60 $723.60 6 $6BB.13 $926.21 0 Yes $89.90 0 $115.07 $91.65 $169.34 $202.75 YES $71.60 36 $132.30 $158.40 $1,372.40 $2,428.40 $2,745.80 $3,000.40 $3,080.50 $3,186.60 $2,934.00 Yes $1,756.67 $3,108 35 18 3 24 $3,514.62 $3,840.5 18 $3,943.04 $4,078.85 $3,755.52 emc Robot KIt 555 emc Relays- Small Padk emcRelays Medium Padk 18 $225.70 $288.90 12 $319.10 $40B.45 emc Relays Large Padk emc Servo Motors A21 (4 pack) emc Servo Motors B21 (4 pack) emc Servo Motors A21 (8 pack) emc Servo Motors B21 (8 pack) emc SMC Pneumotic Part 3-44 emc SMC Pneumatic Part X-22 emc SMC Pneumatic Part X-33 emc SMC Pneumatic Part 3-47 24 $342.30 $43B.14 $867.10 $990.00 0 Yes $1,109.89 $1,267.20 $2,195.20 $2,380.80 $918.78 0 24 $1,715.00 $1,860.00 $717.80 10 Yes $798.20 $1,021.70 Yes $819.50 $762.60 $1,048.96 $976.13 C 6 $1,042.82 $731.14 $909.06 $127.23 emc SMC Pneumofic Part 3-48 emc SMC Pneumofic X 200mm $814.70 26 YES YES $571.20 $710.20 S99.40 27 13 emc SMC Pneumatic X 250mm amc SD Drive 54 are to be comple ted in the Inventory Worksheet The following Section C Go to the Inventory Sheet. This sheet contains a list of all the items the company keep in stock to build the robotics. Each part is identified by a unique code. Convert the Range A3:153 to a Table. Name the table Inventory. Name the following ranges: C1 C2 A4:A53 Code Cost_Price E4:E53 (and any other ranges you feel may prove useful) In F3:F53 calculate the retail price (paid by customer) using the Cost Price and the % Markup in L3. (Note the markup may be changed and your calculation should still produce the correct answer.) L4 indicates the maximum stock level. In G3:G53 create a formula to check if the 4 quantity in stock is over the maximum stock in L4 (this level can be adjusted). If it is over the stock level put Yes in the cell, otherwise leave it empty (do not put No or a space). Formula should copy down, check it is working correctly L5 indicates the minimum stock level while column D indicates if an item is on back 5 order (already been reordered). If a stock item is below the min imum and not on Backorder (is blank) we need to reorder it. In H3:53 create a formula to check if the item needs to be re-ordered. If it does put Yes in the cell, otherwise leave it empty (do not put No or a space). Formula should copy down, check it is working correctly. Reorder quantities are based on price as shown in the Reorder Quantities data (K11:L16), e.g. currently for items that cost from $100 up to (but not including) $300 6 we re-order 35. Create a calculation in 14 that will check if we need to reorder and if so return the correct reorder amount otherwise return 0. Ensure the formula is copied down for the whole table. Note stock levels and reorder quantities will change, you should have one consistent calculation for the column and the calculations should work regardless of values or sort order of the inventory data. In L6 calculate how many item codes require reordering. C7 8 In L7 calculate how many item codes are over stocked. Use Excel's built in help to investigate the SUMPRODUCT function. In L8 calculate the total cost of all inventory items currently in stock. CG fx H4 A B C D E F G H J K L |Inventory 1 2 3 Code 4 C1019 5 M1021 6 M1022 7 R1001 On Backorder Cost Price (each) Description Connedtor Pack MGX 8025 Chip Set MGX 8066 Chip Set Robot Motor Klt Robot Kit 33A Reorder Qty In Stock Relail Price Over-Stocked Re-Order? % Markup Maximum Stock Level Minimum Stock Level No.Item Codes to re-order 28% $90,75 25 0 $70.90 C Yes $129.80 $166.14 0 $151.20 $193.54 0 $1,387.00 $1,775.36 $2,779.52 No. Item Codes Over-Stacked 8 R1002 9 R1003 10 R1004 4. $2,171.50 $2,798.10 $2,711.90 $2,745.00 Total Inventory Value Robot Kit 38A 0 Yes $3,581.57 Reorder Quantities Robot Kit 38G Robot Kit SP3 Rabot Kit 448 $3,471.23 $3,513.60 $3,876.86 C 11 R1005 12 R1006 Re-Order Qty 11 Item Cost $3,028.80 24 50 13 R1007 $100 $300 Robot Kit 555 2 Yes $3,292.60 $4,214.53 35 Relays Small Pack Relays Medium Pack Relays Large Pack Servo Motors A21 (4 pack) Servo Motors B21 (4 padk) Servo Motors A21 (8 padk) Servo Motors B21 (8 padk) SMC Pneumatic Part 3-44 SMC Pneumatic Part X-22 SMC Pneumatic Part X-33 SMC Pneumatic Part 3-47 SMC Pneumetie Part 3-48 SMC Pneumatic X 200mm SMC Pneumatic X 250mm SD Drive emc Connector Pock leme MGX 8025 Ohip Set |emc MGX 8066 Chip Set emc Robot Motor Kit emc Robot Kit 33A emc Robot Kit 38A emc Robot Kit 3BG emc Robot Kit SP3 emc Robot Kit 448 14 R1023 20 26 $272.80 $349.18 YES $500 $1,000 15 R1024 10 1 Yes $298.50 $382.08 16 R1025 17 $1008 18 S1009 19 S1010 20 $1011 21 S1012 22 S1013 23 S1014 24 S1015 25 S1016 26 S1017 27 $1018 28 S1020 29 C1219 30 M1221 31 M1222 32 R1201 33 R1202 34 R1203 35 R1204 36 R1205 37 R1206 38 R1207 39 R1223 40 R1224 41 R1225 42 $1208 43 S1209 44 S1210 45 S1211 46 S1212 47 $1213 48 S1214 49 $1215 50 $1216 51 $1217 52 $1218 53 S1220 $313.00 $400.64 5 Yes 8 $1,171.58 $915.30 Yes $1,061.40 $1,662.50 $1,980.00 $605.80 $776.40 $764.40 $717.20 $1,358.59 2,128.00 39 YES 26 $2,534.40 $775.42 YES 0 Yes $993.79 $978.43 12 Yes 4 $918.02 27 $734.80 $940.54 YES S537.60 $723.60 6 $6BB.13 $926.21 0 Yes $89.90 0 $115.07 $91.65 $169.34 $202.75 YES $71.60 36 $132.30 $158.40 $1,372.40 $2,428.40 $2,745.80 $3,000.40 $3,080.50 $3,186.60 $2,934.00 Yes $1,756.67 $3,108 35 18 3 24 $3,514.62 $3,840.5 18 $3,943.04 $4,078.85 $3,755.52 emc Robot KIt 555 emc Relays- Small Padk emcRelays Medium Padk 18 $225.70 $288.90 12 $319.10 $40B.45 emc Relays Large Padk emc Servo Motors A21 (4 pack) emc Servo Motors B21 (4 pack) emc Servo Motors A21 (8 pack) emc Servo Motors B21 (8 pack) emc SMC Pneumotic Part 3-44 emc SMC Pneumatic Part X-22 emc SMC Pneumatic Part X-33 emc SMC Pneumatic Part 3-47 24 $342.30 $43B.14 $867.10 $990.00 0 Yes $1,109.89 $1,267.20 $2,195.20 $2,380.80 $918.78 0 24 $1,715.00 $1,860.00 $717.80 10 Yes $798.20 $1,021.70 Yes $819.50 $762.60 $1,048.96 $976.13 C 6 $1,042.82 $731.14 $909.06 $127.23 emc SMC Pneumofic Part 3-48 emc SMC Pneumofic X 200mm $814.70 26 YES YES $571.20 $710.20 S99.40 27 13 emc SMC Pneumatic X 250mm amc SD Drive 54