Hi, I am having trouble understanding what excel formulas to use for these questions.
Question Instruction Section F The following are to be completed in the Inventory Worksheet F1 The inventory shows a list of parts kept in stock, who supplies them and how many are currently in stock. In P4 calculate how many parts are supplied by the supplier shown in N4. Copy the formula down to P6. Marks 15 Dane F2 In Q4 calculate the average cost of parts supplied by the supplier shown in N4. Copy the formula down to C16. F3 Some parts have been reordered but not yet delivered, these are said to be on backorder and are indicated with a Y in the backorder column. In R4 calculate how many parts on backorder are supplied by the supplier shown in N4. Copy the formula down to R6. F4 We have decided to classify parts into two categories. Parts that are less than $5 will be category A and all other products will be Category B. Create a formula in the Category column to calculate the correct category for each part. F5 If a stock item falls below the minimum stock level shown in column G and is NOT on backorder, then we need to reorder it. In K4 create a calculation that will return \"Y\" if the item needs reordering and leave the cell blank if it does not. F6 Challenge Question (limited help will be given) When we reorder we like to ensure we will have 25% more than the minimum stock level. Create a calculation that checks if we need to reorder, if we do, calculate the number we need to re-order to have an in stock value of 25% over the min stock level, return 0 if reorder not required. The suppliers also impose a minimum order quantity (see column O}. Modify your calculation so that if we need to reorder and the re-order quantity is less than the Min Order Qty for the supplier, it returns the appropriate Min Order Qty. (2 Marks will be given for doing just the rst part correctly). TOTAL MARKS 80 PARTCODE IMAGE DESCRIPTION SUPPLIER SUPPLIER COD COST EACH MIN STOCK IN STOCK BACKORDER CATEGORY RE-ORDER? RE-ORDER QTY PX723541 DESCRIPTION White & Portico W5 29196/78 $1.66 200 239 PX723542 DESCRIPTION Buggins B557761/77 $1.99 200 198 PX374561 DESCRIPTION White & Portico W6 64956/39 $2.7 200 63 PX820261 DESCRIPTION White & Portico W771072/53 $12.9 100 292 PX820262 DESCRIPTION Buggins 8567350/21 $13.19 100 163 PX100468 DESCRIPTION White & Portico W701735/28 $13.19 100 256 PX820266 DESCRIPTION Buggins B326854/81 $10.99 100 66 10 Y PX723593 DESCRIPTION H&W H4 48251/40 $1.91 200 268 PX374089 12 DESCRIPTION White & Portico W4 26061/24 $53.11 50 105 PX723557 DESCRIPTION Buggins 8472237/18 $249 200 249 PX723820 DESCRIPTION Buggins 14 8420251/20 $2.49 200 138 PX807530 DESCRIPTION White & Portico W5 64399/34 $2.99 200 181 PX820728 16 DESCRIPTION White & Portico W591861/33 $2.19 200 272 PX723594 DESCRIPTION H&W H326781/31 $2.39 200 86 Y PX723206 DESCRIPTION 200 18 Buggins 8433191/70 $1.99 177 PX373195 19 DESCRIPTION White & Portico W592160/27 $2.49 200 231 PX723745 DESCRIPTION 20 Buggins B675073/41 $3.11 100 36 PX577199 DESCRIPTION W3 61589/24 $1.95 200 21 White & Portico 39 Y PX725125 DESCRIPTION H&W H467610/39 $2.19 200 90 Y PX796490 23 DESCRIPTION Buggins B712863/57 $2.49 200 180 PX807531 DESCRIPTION White & Portico W439743/32 $1.66 200 145 PX807532 DESCRIPTION $1.74 200 25 White & Portico W529507/25 183 PX723566 26 DESCRIPTION H&W H683213/67 $2.29 200 202 PX820263 DESCRIPTION Buggins B347655/87 $2.49 200 234 PX792653 DESCRIPTION 28 White & Portico W724130/13 $2.08 200 52 Y PX823385 29 DESCRIPTION 578246/26 $1.66 200 273 PX860085 30 DESCRIPTION H&W 396220/43 $2.19 200 207 PX823386 DESCRIPTION H&W H319247/65 $1.74 200 13 PX820834 DESCRIPTION Buggins 8668438/72 $1.99 200 32 300 PX723596 DESCRIPTION H&W H558841/85 $2.49 200 171 PX723742 34 DESCRIPTION White & Portico W678322/30 $1.74 200 68 X723743 DESCRIPTION Buggins 8517373/85 $2.29 200 92 Y PX797516 DESCRIPTION H&W 1539656/85 $1.83 200 36 195 PX242979 DESCRIPTION White & Portico W4 87032/43 $1.83 200 280 PX823978 38 DESCRIPTION White & Portico W3 25980/58 $26.24 50 74 PX807540 DESCRIPTION H&W $3.33 100 39 H531341/87 209 PX807539 40 DESCRIPTION HEW H410102/85 $18.69 50 107 PX809486 o DESCRIPTION H&W H456602/63 $2.08 200 106N O P Q R S Supplier Min Order Qty Parts Average Value Items on Backorder H&W 25 White & Portico 30 Buggins 55 Reorder % over 25% MinimumB C G H M O P Q R Name Box 1 Inventory 3 PART CODE IMAGE DESCRIPTION SUPPLIER SUPPLIER CODE COST EACH MIN STOCK IN STOCK BACKORDER CATEGORY RE-ORDER? RE-ORDER QTY Supplier Min Order Qty Parts Average Value Items on Backorder PX723541 DESCRIPTION White & Portico W529196/78 $1.66 200 239 H&W 25 PX723542 DESCRIPTION Buggins B557761/77 $1.99 200 198 White & Portico 30 PX374561 IMAGE DESCRIPTION White & Portico V664956/39 $2.74 200 63 NAVAILABLE Buggins 55 PX820261 DESCRIPTION White & Portico W771072/53 $12.99 100 292 PX820262 DESCRIPTION Buggins B567350/21 $13.19 Reorder % over 100 163 25% Minimum PX1004683 DESCRIPTION White & Portico W701735/28 $13.19 100 256 PX820266 DESCRIPTION Buggins B326854/81 $10.99 100 66 10 PX723593 DESCRIPTION H& W H448251/40 $1.91 200 268 11 PX374089 DESCRIPTION White & Portico W426061/24 $53.11 50 105 12 PX723557 DESCRIPTION Buggins B472237/18 $2.49 200 249 13 PX723820 DESCRIPTION Buggins B420251/20 $2.49 200 138 14 PX807530 DESCRIPTION White & Portico W564399/34 $2.99 200 181 15 PX820728 DESCRIPTION White & Portico W591861/33 $2.19 200 272 16 PX723594 CRIPTION H&W H326781/31 $2.39 200 86 17 PX723206 DESCRIPTION Buggins B433191/70 $1.99 200 177 18 PX373195 DESCRIPTION White & Portico W592160/27 $2.49 200 231 19 PX723745 DESCRIPTION Buggins B675073/41 $3.11 100 36 20
