E1 E2 E3 The Inventory sheet contains a list of all the items the company keep in stock to build customer networks. Each part is identified by a unique item code. Convert the Range A3:K1245 to a Table. Change the table name (not range) to Inventory. Note: Your table does have headers. If you make a mistake creating the table and insert an extra row of headings, immediately undo and try again, or you may risk losing a lot of marks. 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 03 and 04. 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.) Column D contains the number of items in stock for each item code. Cell 05 indicates the desired maximum stock level. In 14 create a calculation that returns "Y" if the item is over the maximum stock level and leaves the cell blank if it is on or below the maximum level (do not put a -, N or space). Ensure the formula copies down correctly. In column E, items on backorder (items that have been re-ordered but have not yet been delivered) are indicated with a Y. In J4 create a formula to put a Y in the cell if the stock level is below the minimum stock level as shown in 06 (value may change) and the item is not on backorder. Otherwise leave the cell blank (do not put a -, N or space). Ensure the formula copies down correctly. Add conditional formatting to the Reorder data (34:J1245) so that all items that need reordering show as light red fill with dark red text. In column K calculate the number of items to reorder if a reorder is required. If not, the calculation should return 0 (not O). 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:014 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. E4 E5 E6 Note reorder values in the lookup table can change, so don't type them, your calculation must be consistent and work regardless of values or sort order.) In 016 create a formula to count how many of the item codes have been overstocked. E7 Question Instruction E8 E9 A named range called Category has been created, but it does not include all the data in the Category column. Edit the named range so that it refers to the cells C4:C1245. (Do not add a new named range, change the existing one.) In N20 create a formula to count how many premium item codes of category A need to be reordered. Use appropriate cell referencing so that the formula can be dragged down and across to complete the table. (Note: for full marks you will need to use correct mixed cell references.) B C D E F G H J L M N o 1 SafeTNET Network Equipment Inventory Premium? Retail Price Over Stocked Reorder? Reorder Qty 20% Category A Num In Stocl 11 On Backorde Y Standard Markup: Premium Markup: Maximum Stock Level: Minimum Stock Level: 25% 30 A 15 Y 2 Y 5 B 5 Y 12 Y D A 2 Y Y Reorder Amounts Lookup Category A Premium Regular 20 B 30 25 D 0 Y B 20 15 33 Y 15 10 A A Y D 10 5 24 33 B Y E 5 2 29 Y 2 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 E1M-CW-78899 17 E1M-CW-72258 18 E1M-CW-28966 19 E1M-CW-36738 20 E1M-CW-40232 21 E1M-CW-28783 22 E1M-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 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 SRXFP, MMF, LC 10GbE LR XFP, SMF, LC 10GbE ER XFP optics, SMF, LC connector SX MMF SFP LX SMF SFP LHA SMF SFP 80Km, 1470nm, LC connector 80km, 1490nm, LC connector 80Km, 1510nm, LC connector 80Km, 1530nm, LC connector 80Km, 1550nm, LC connector 80km, 1570nm, LC connector 80Km, 1590nm, LC connector 80Km, 1610nm, LC connector 24p 2 C/F w 1 AC 48p, 2 C/F & 1 AC 96p, 4 C/F & AC 24p, 2 C/F & 1 DC 48p, 2 C/F & 1 DC 96p, 4 C/F & DC Prem 24p, 2 C/F & 1 AC 0 Cost Pr $1,064.65 $1,796.70 $3,459.75 $5,291.15 $11,654.70 $1,465.90 $2,937.15 $11,214.90 $519.40 $1,116.90 $4,658.70 $5,879.65 $5,714.80 $5,714.80 $5,714.80 $5,824.70 $5,604.90 $5,659.85 $5,714.80 $2,990.65 $5,194.80 $9,770.80 $3,187.85 $5,612.70 $10,700.00 $4,482.90 Y Item Codes Over Stocked: 29 Y 0 Y Y Y Item Codes to Reorder Reorders A Premium Regular 27 15 17 Y Y B 14 B 16 Y D 28 E D 23 Y B 29 Y 31 Y D 1 Y Y B 24 no E1 E2 E3 The Inventory sheet contains a list of all the items the company keep in stock to build customer networks. Each part is identified by a unique item code. Convert the Range A3:K1245 to a Table. Change the table name (not range) to Inventory. Note: Your table does have headers. If you make a mistake creating the table and insert an extra row of headings, immediately undo and try again, or you may risk losing a lot of marks. 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 03 and 04. 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.) Column D contains the number of items in stock for each item code. Cell 05 indicates the desired maximum stock level. In 14 create a calculation that returns "Y" if the item is over the maximum stock level and leaves the cell blank if it is on or below the maximum level (do not put a -, N or space). Ensure the formula copies down correctly. In column E, items on backorder (items that have been re-ordered but have not yet been delivered) are indicated with a Y. In J4 create a formula to put a Y in the cell if the stock level is below the minimum stock level as shown in 06 (value may change) and the item is not on backorder. Otherwise leave the cell blank (do not put a -, N or space). Ensure the formula copies down correctly. Add conditional formatting to the Reorder data (34:J1245) so that all items that need reordering show as light red fill with dark red text. In column K calculate the number of items to reorder if a reorder is required. If not, the calculation should return 0 (not O). 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:014 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. E4 E5 E6 Note reorder values in the lookup table can change, so don't type them, your calculation must be consistent and work regardless of values or sort order.) In 016 create a formula to count how many of the item codes have been overstocked. E7 Question Instruction E8 E9 A named range called Category has been created, but it does not include all the data in the Category column. Edit the named range so that it refers to the cells C4:C1245. (Do not add a new named range, change the existing one.) In N20 create a formula to count how many premium item codes of category A need to be reordered. Use appropriate cell referencing so that the formula can be dragged down and across to complete the table. (Note: for full marks you will need to use correct mixed cell references.) B C D E F G H J L M N o 1 SafeTNET Network Equipment Inventory Premium? Retail Price Over Stocked Reorder? Reorder Qty 20% Category A Num In Stocl 11 On Backorde Y Standard Markup: Premium Markup: Maximum Stock Level: Minimum Stock Level: 25% 30 A 15 Y 2 Y 5 B 5 Y 12 Y D A 2 Y Y Reorder Amounts Lookup Category A Premium Regular 20 B 30 25 D 0 Y B 20 15 33 Y 15 10 A A Y D 10 5 24 33 B Y E 5 2 29 Y 2 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 E1M-CW-78899 17 E1M-CW-72258 18 E1M-CW-28966 19 E1M-CW-36738 20 E1M-CW-40232 21 E1M-CW-28783 22 E1M-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 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 SRXFP, MMF, LC 10GbE LR XFP, SMF, LC 10GbE ER XFP optics, SMF, LC connector SX MMF SFP LX SMF SFP LHA SMF SFP 80Km, 1470nm, LC connector 80km, 1490nm, LC connector 80Km, 1510nm, LC connector 80Km, 1530nm, LC connector 80Km, 1550nm, LC connector 80km, 1570nm, LC connector 80Km, 1590nm, LC connector 80Km, 1610nm, LC connector 24p 2 C/F w 1 AC 48p, 2 C/F & 1 AC 96p, 4 C/F & AC 24p, 2 C/F & 1 DC 48p, 2 C/F & 1 DC 96p, 4 C/F & DC Prem 24p, 2 C/F & 1 AC 0 Cost Pr $1,064.65 $1,796.70 $3,459.75 $5,291.15 $11,654.70 $1,465.90 $2,937.15 $11,214.90 $519.40 $1,116.90 $4,658.70 $5,879.65 $5,714.80 $5,714.80 $5,714.80 $5,824.70 $5,604.90 $5,659.85 $5,714.80 $2,990.65 $5,194.80 $9,770.80 $3,187.85 $5,612.70 $10,700.00 $4,482.90 Y Item Codes Over Stocked: 29 Y 0 Y Y Y Item Codes to Reorder Reorders A Premium Regular 27 15 17 Y Y B 14 B 16 Y D 28 E D 23 Y B 29 Y 31 Y D 1 Y Y B 24 no