B D E F G H J L 2 3 PART CODE IMAGE 1 SUPPLIER SUPPLIER CODE COST EACH MIN STOCK IN STOCK BACKORDER CATEGORY RE-ORDER? RE-ORDER QTY PX723541 Arm 320 White & Portico W529196/78 $1.74 200 78 Y 4 PX723542 Arm 35 Buegins B557761/77 $2.39 200 198 5 PX374561 IMAGE UNAVAILABLE Axle White & Portico W664956/39 $2.74 200 63 Y 6 PX820261 Block 12/A White & Portico W771072/53 $16.89 50 235 7 PX820262 Block Front Buggins B567350/21 $12.09 100 192 8 PX1004683 Block Rear 14-X White & Portico W701735/28 $14.29 100 103 9 PX820266 U Block Rear Bugeins B326854/81 $11.54 100 206 10 PX723593 Bolt H&W H448251/40 $1.74 200 219 11 PX374089 Bracket 552 White & Portico W426061/24 $53.11 50 207 12 PX723557 Foot Buggins B472237/18 $2.74 200 196 Y 13 PX723820 Foot Sq Buggins B420251/20 $2.09 200 27 Y 14 PX807530 Grip White & Portico W564399/34 $3.24 100 56 Y 15 PX820728 Knob White & Portico W591861/33 $2.09 200 101 Y 16 PX723594 Home Large Bolt H&W H326781/31 $2.49 200 191 17 PX723206 Large Foot Buggins B433191/70 $2.08 200 68 Y 18 PX373195 Latch White & Portico W592160/27 $3.24 100 112 19 PX723745 Leg Buggins B675073/41 $3.11 100 71 Y 20 PX577199 Nut Super White & Portico W361589/24 $1.74 200 106 Y 21 PX725125 Nut H&W H467610/39 $2.59 200 163 Y 22 A B D E F H I K L PX725125 Nut H&W H467610/39 $2.59 200 163 Y 22 PX796490 Nut Wing Buggins B712863/57 $2.39 200 261 23 PX807531 Pin 12 White & Portico W439743/32 $1.66 200 294 24 PX807532 Pin 33 White & Portico W629507/25 $1.74 200 168 25 PX723566 Pin 10 H&W H683213/67 $2.19 200 96 Y 26 PX820263 RH Nut Buggins B347655/87 $2.19 200 298 27 PX792653 Rivet Use: 1004688-16 White & Portico w724130/13 $2.16 200 79 Y 28 PX823385 Screw 2.4mm H&W H578246/26 $1.66 200 143 29 PX860085 Screw 1.2mm H&W H396220/43 $1.99 $ 200 67 30 PX823386 Screw Special H&W H319247/65 $1.74 200 144 Y 31 PX820834 Single Grip Bugeins B668438/72 $2.49 200 130 Y 32 PX723596 Small Bolt H&W H558841/85 $2.59 200 219 33 PX723742 Socket White & Portico W678322/30 $1.66 200 50 34 PX723743 A Socket Buggins B517373/85 $2.09 200 91 Y 35 PX797516 Spring 3a H&W H539656/85 $1.91 200 233 36 PX242979 Spring 5b White & Portico W487032/43 $1.91 200 116 Y 37 PX823978 Vice Jaw Front White & Portico W325980/58 $25.19 50 237 38 PX807540 Vice Screw Sa H&W H531341/87 $4.16 100 144 39 PX807539 Vice Screw Sa H&W H410102/85 $19.54 50 63 40 PX809486 O Washer Special H&W H456602/63 $1.74 200 46 41 42 N 0 P Q R 1 2 3 2 Supplier Min Order Qty Parts Average Value Items on Backorder H&W 25 4 White & Portico 30 5 Buggins 55 6 7 Reorder % over Minimum 25% 8 Question Instruction Marks Done Section F 15 F1 2 F2 2 F3 2 F4 2 The following are to be completed in the Inventory Worksheet 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. In Q4 calculate the average cost of parts supplied by the supplier shown in N4. Copy the formula down to 26. Some parts have been re-ordered 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. 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. 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. Challenge Question (limited help will be given) When we re-order 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 re-order not required. The suppliers also impose a minimum order quantity (see column O). Modify your calculation so that if we need to re-order and the re-order quantity is less than the Min Order Qty for the supplier, it returns the appropriate Min Order Qty. F5 3 F6 4 (2 Marks will be given for doing just the first part correctly). TOTAL MARKS 80 A C H M 0 3 PARTCODE IMAGE SUPPLIER SUPPLIER CODE COSTEACH MIN STOCK INSTOCK BACKORDER CATEGORY RE-ORDER? RE-ORDER TY Supplier Min order ty Parts Avere Value Items on Badoordar PX723541 Arm 320 White & Portice WS29196/781 $174 200 78 Y HEW 25 PX723542 Arm 35 B557761/77 $239 200 198 White Portico 30 PX374561 Axle White & Portie W664956/39 $2.74 200 63 Y Buens 55 PX820261 Block 12/A White & Portico W771072/53 $16.89 50 235 PX820262 Block Front | Buggins 8567350/21 $12.00 100 192 Reorder over Minimum 25% PX100468 Block Rear 14- White & Portico W701735/28 $1429 100 103 PX820266 Block Busins 8326854/81 $1154 100 206 10 PX723593 HOW Bolt H148251/40 $174 200 219 11 PX3 74089 Bracket 552 W426061/24 $53.11 50 White & Portico 207 12 PX723557 Foot 200 8472237/18 $2.74 Busins 196 13 Y Foot sa PX723820 14 |Bugins 8420251/20 5209 200 27 Y PX807530 Grip White & Portico WS 54399/14 $324 100 56 Y 15 Knot White & Portic W591861/33 $2.09 200 101 Y PX820728 16 PX723594 17 Bolt HOW H026781/31 $249 200 191 PX723206 Large Foot Bugans 1433191/70 $2.00 200 68 Y 10 PX373195 Latch NDD White & Portico W592160/27 $324 100 19 112 PX723745 Buggins 8675073/41 $3.11 100 Y 20 PX577199 Nut Super White & Portico W361589/24 $1.74 200 106 21 Y Y PX725125 HW Nut H167610/39 $2.59 163 200 Y PX796490 Nut Wing Bugis B712863/57 $239 200 261 Pin 12 White & Portice 439743/32 $166 200 294 PX807531 24 PX807532 25 Pin 33 White&Portico W629507/25 $174 200 168 Pin 10 HAW PX723566 26 H683213/67 $2.19 200 96 Y PX820263 RH Nut Buggins 8347655/87 $2.19 200 298 PX792653 Rivet Use 1004688-16 White & Portico W724130/13 $216 200 79 Y 28 PX823385 Screw 2.4 mm HW H578246/26 $166 200 143 29 Serow 1.2 mm PX860085 30 HEW 1396220/43 $1.99 200 67 PX823386 HAW H319247/65 $1.34 200 144 Y 31 PX820834 | Single Grip Buggins 3668438/72 $249 200 130 Y PX723596 Small Bolt HOW 219 H558.841/85 $2.59 200 Sochet White & Portico W678322/30 $166 200 50 PX723742 34 PX723743 35 Bugens 8517373/85 $209 200 91 Y PX797516 Sering sa HOW 539656/85 $191 200 233 PX242979 Soring5b White & Portico W87032/43 $191 116 Y 37 PX823978 Vice Jaw Front White & Portice W325980/58 $2519 50 237 30 PX807540 Vice Screw se HOW H531341/87 $416 100 100 144 39 PX807539 Vice Screws Haw H410102/85 $1954 so PX809486 HAW H156602/63 $1.74 200 46 41 42