Part 1) In column G some items have a Y against them indicating that they are Premium items. Premium items incur a higher mark-up than standard items, both, the standard and premium mark-up rates can be found in O3 and O4. In H4 calculate the retail price by adding the appropriate mark-up amount to the Cost price. The formula should automatically copy down. (Note the rates can change so ensure you refence the cells do not type the rates into the calculation.) Part 2) In column K calculate the number of items to reorder if a reorder is required. If not, the calculation should return 0 (not 0). Reorder quantities are based on two factors, the category shown in column C and whether or not the item is premium or standard, the lookup table in M10:O14 shows the appropriate number for each combination. For example: for a Category A premium item we need to reorder 25 whereas a regular item we only reorder 20. This is a challenge question so limited help can be given, but you may choose a simpler option and just return the standard amount for all items that need reordering, and if correct you will still receive 3 marks. Note reorder values in the lookup table can change, so dont type them, your calculation must be consistent and work regardless of values or sort order.) Please don't consider this as 2 separate questions. These are 2 parts from the same question.

K4 xv fx A 1 SafeTNET Network Equipment Inventory B D E F G . H J L M M N O Premium? Retail Price Over Stocked Reorder? - Reorder Qty Standard Markup: : Premium Markup: Maximum Stock Level: Minimum Stock Level: 20% 25% 30 5 Y Y Y Y Category Num In Stoc-On Backorde A 12 A 28 B 33 20 D 5 Y 11 B 8 D 7 Y A 19 Y A 34 Y B 15 34 c 14 Y 13 29 Y Y Reorder Amounts Lookup Category A B D E Premium 25 20 15 10 5 Regular 20 15 10 5 2. Y Y Y Y Item Codes Over Stocked: Premium Regular 6 10 Y Y 20 5 Itern Codes to Reorder Reorders A B C D E Y Y Y Y Y 3 Item Code 4 EIF-CZ-73600 5 EIF-CZ-97375 6 EIF-A-64872 7 EIF-CZ-48270 8 EIF-CZ-66531 9 10G-XF-75398 10 10G-XF-96371 11 10G-XF-92273 12 E1M-SX-85914 13 E1M-LX-85720 14 E1M-LH-64896 15 E1M-CW-32246 16 EIM-CW-78899 17 EIM-CW-72258 18 EIM-CW-28966 19 EIM-CW-36738 20 EIM-CW-40232 21 E1M-CW-28783 22 EIM-CW-31995 23 FES-CZ-51280 24 FES-CZ-23915 25 FES-CZ-65136 26 FES-DC-62571 27 FES-DC-25460 28 FES-DC-59060 29 FES-PR-77806 30 FES-PR-91987 31 FES-PR-88772 32 FES-PR-27658 33 FES-PR-84308 34 FES-PR-78728 35 FES-WL-42909 36 FES-WL-97334 37 FES-WL-57764 38 FES-WL-69103 39 FES-WL-85721 40 FES-WL-84668 41 FES-PO-81088 42 FES-PO-24400 43 FES-CZ-78088 44 FES-CZ-44941 - 45 FES-DC-92979 46 FES-PR-63235 47 FES-PR-76605 48 FES-PO-55523 Item Description EIF 24 10/100 +2 Combo GbE EIF 48 10/100 +2 Combo GbE 20 FE/1000 +4 C/F GbE 48-port 10/100/1000 with up to 4-port combo SFP slots EIF with 8-port XFP slots and complete L2 SW 10GbE SR XFP, MMF, LC 10GbE LR XFP, SMF, LC 10GbE ER XFP optics, SMF, LC connector SX MMF SFP LX SMF SFP LHA SMF SFP SOKm, 1470nm, LC connector 80km, 1490nm, LC connector SOKm, 1510nm, LC connector SOKm, 1530nm, LC connector SOKm, 1550nm, LC connector SOKm, 1570nm, LC connector SOKm, 1590nm, LC connector SOKm, 1610nm, LC connector 24p 2 C/F w 1 AC 48,2 C/F & 1 AC 96p, 4 C/F & AC 24p, 2 C/F & 1 DC 48,2 C/F & 1 DC 96p, 4 C/F & DC Prem 24p, 2 C/F & 1 AC Prem 4&p, 2 C/F & 1 AC Prer 96p, 4 C/F & AC Prem 24p, 2 C/F & 1 DC Prem 48p, 2 C/F & 1 DC Prer 96p, 4 C/F & DC FES-port 10/100Base-TX (RJ45) plus 2-port 1000Base-T and 1000Base- FES-port 10/100Base-TX (RJ45) plus 2-port 1000Base-T and 1000Base- FES-port 10/100Base-TX (RJ45) plus 4-port 1000Base-T and 1000Base- FES-DC with integrated TRONPoint WLAN/L2 SW and 24-port 10/100B FES-DC with integrated TRONPoint WLAN/L2 SW and 48-port 10/100B FES-DC with integrated TRONPoint WLAN/L2 SW and 96-port 10/100Ba| FES-PO with integrated TRONPoint WLAN/L2 SW and 24-port 10/100B FES-PO with integrated TRONPoint WLAN/L2 SW and 48-port 10/100B TRONPoint WLAN upgrade kit for FES Switch 12G C/F w 1 AC 12G C/F w 1 DC 12G C/F Prem w 1 AC 12G C/F Prem w 1 DC POE 24p 2-GCF C c B D B D D B 35 32 20 32 24 Cost Pro $1.044.75 $1,745.85 $3,492.70 $5,043.90 $11.434.80 $1,424.80 $2,937.15 $11,214.90 $524.30 $1,171,65 $4,526.85 $5,824.70 $5,769.75 $5,659.85 $5,879.65 $5,824.70 $5,769.75 $5,659.85 $5,769.75 $2,850,90 $5,094.90 $10,052.65 $3 280.70 $5,612.70 $10,400.00 $4,526.85 $7,554.75 $14,346.80 $4,938.85 $7,869.75 $14,658.80 $4,037.25 $6,165.90 $11,071.70 $4,467.25 $6,757.50 $11,206.00 $4570.80 $6,792.85 $1,127 85 $6,792 85 $7,202.00 $8,970.90 $9,490.00 $3,459.75 Y Y Y 23 D B 29 15 23 24 9 23 27 Y Y D D B B D B c. Y Y Y Y 3 32 4 Y Y Y Y D B Y 0 26 17 25 18 23 A c C c. B Y Y 5 29 14 Y Y K4 xv fx A 1 SafeTNET Network Equipment Inventory B D E F G . H J L M M N O Premium? Retail Price Over Stocked Reorder? - Reorder Qty Standard Markup: : Premium Markup: Maximum Stock Level: Minimum Stock Level: 20% 25% 30 5 Y Y Y Y Category Num In Stoc-On Backorde A 12 A 28 B 33 20 D 5 Y 11 B 8 D 7 Y A 19 Y A 34 Y B 15 34 c 14 Y 13 29 Y Y Reorder Amounts Lookup Category A B D E Premium 25 20 15 10 5 Regular 20 15 10 5 2. Y Y Y Y Item Codes Over Stocked: Premium Regular 6 10 Y Y 20 5 Itern Codes to Reorder Reorders A B C D E Y Y Y Y Y 3 Item Code 4 EIF-CZ-73600 5 EIF-CZ-97375 6 EIF-A-64872 7 EIF-CZ-48270 8 EIF-CZ-66531 9 10G-XF-75398 10 10G-XF-96371 11 10G-XF-92273 12 E1M-SX-85914 13 E1M-LX-85720 14 E1M-LH-64896 15 E1M-CW-32246 16 EIM-CW-78899 17 EIM-CW-72258 18 EIM-CW-28966 19 EIM-CW-36738 20 EIM-CW-40232 21 E1M-CW-28783 22 EIM-CW-31995 23 FES-CZ-51280 24 FES-CZ-23915 25 FES-CZ-65136 26 FES-DC-62571 27 FES-DC-25460 28 FES-DC-59060 29 FES-PR-77806 30 FES-PR-91987 31 FES-PR-88772 32 FES-PR-27658 33 FES-PR-84308 34 FES-PR-78728 35 FES-WL-42909 36 FES-WL-97334 37 FES-WL-57764 38 FES-WL-69103 39 FES-WL-85721 40 FES-WL-84668 41 FES-PO-81088 42 FES-PO-24400 43 FES-CZ-78088 44 FES-CZ-44941 - 45 FES-DC-92979 46 FES-PR-63235 47 FES-PR-76605 48 FES-PO-55523 Item Description EIF 24 10/100 +2 Combo GbE EIF 48 10/100 +2 Combo GbE 20 FE/1000 +4 C/F GbE 48-port 10/100/1000 with up to 4-port combo SFP slots EIF with 8-port XFP slots and complete L2 SW 10GbE SR XFP, MMF, LC 10GbE LR XFP, SMF, LC 10GbE ER XFP optics, SMF, LC connector SX MMF SFP LX SMF SFP LHA SMF SFP SOKm, 1470nm, LC connector 80km, 1490nm, LC connector SOKm, 1510nm, LC connector SOKm, 1530nm, LC connector SOKm, 1550nm, LC connector SOKm, 1570nm, LC connector SOKm, 1590nm, LC connector SOKm, 1610nm, LC connector 24p 2 C/F w 1 AC 48,2 C/F & 1 AC 96p, 4 C/F & AC 24p, 2 C/F & 1 DC 48,2 C/F & 1 DC 96p, 4 C/F & DC Prem 24p, 2 C/F & 1 AC Prem 4&p, 2 C/F & 1 AC Prer 96p, 4 C/F & AC Prem 24p, 2 C/F & 1 DC Prem 48p, 2 C/F & 1 DC Prer 96p, 4 C/F & DC FES-port 10/100Base-TX (RJ45) plus 2-port 1000Base-T and 1000Base- FES-port 10/100Base-TX (RJ45) plus 2-port 1000Base-T and 1000Base- FES-port 10/100Base-TX (RJ45) plus 4-port 1000Base-T and 1000Base- FES-DC with integrated TRONPoint WLAN/L2 SW and 24-port 10/100B FES-DC with integrated TRONPoint WLAN/L2 SW and 48-port 10/100B FES-DC with integrated TRONPoint WLAN/L2 SW and 96-port 10/100Ba| FES-PO with integrated TRONPoint WLAN/L2 SW and 24-port 10/100B FES-PO with integrated TRONPoint WLAN/L2 SW and 48-port 10/100B TRONPoint WLAN upgrade kit for FES Switch 12G C/F w 1 AC 12G C/F w 1 DC 12G C/F Prem w 1 AC 12G C/F Prem w 1 DC POE 24p 2-GCF C c B D B D D B 35 32 20 32 24 Cost Pro $1.044.75 $1,745.85 $3,492.70 $5,043.90 $11.434.80 $1,424.80 $2,937.15 $11,214.90 $524.30 $1,171,65 $4,526.85 $5,824.70 $5,769.75 $5,659.85 $5,879.65 $5,824.70 $5,769.75 $5,659.85 $5,769.75 $2,850,90 $5,094.90 $10,052.65 $3 280.70 $5,612.70 $10,400.00 $4,526.85 $7,554.75 $14,346.80 $4,938.85 $7,869.75 $14,658.80 $4,037.25 $6,165.90 $11,071.70 $4,467.25 $6,757.50 $11,206.00 $4570.80 $6,792.85 $1,127 85 $6,792 85 $7,202.00 $8,970.90 $9,490.00 $3,459.75 Y Y Y 23 D B 29 15 23 24 9 23 27 Y Y D D B B D B c. Y Y Y Y 3 32 4 Y Y Y Y D B Y 0 26 17 25 18 23 A c C c. B Y Y 5 29 14 Y Y