Hi,
I am having trouble knowing what excel formulas to use for these questions. I have tried to include as much of the data as I can.
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 N 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
