PX725125 Nut H&W H467610/39 $1.99 200 127 Y PX796490 Nut Wing Buggins B712863/57 $2.39 200 144 PX807531 Pin 12 White & Portico W439743/32 $1.99 200 177 --- PX807532 Pin 33 White & Portico W629507/25 $1.83 200 Y 170 PX723566 Pin 10 H&W H683213/67 $2.09 200 153 Y PX820263 RH Nut Buggins B347655/87 $1.99 200 Y 103 PX792653 Rivet Use: 1004688-16 White & Portico W724130/13 $1.74 200 11 Y PX823385 Screw 2.4 mm H&W H578246/26 $1.74 200 36 Y PX860085 Screw 1.2 mm H&W H396220/43 $2.39 200 107 PX823386 Screw Special H&W H319247/65 $1.66 200 277 PX820834 Single Grig Buggins 1668438/72 $1.99 200 154 Y PX723596 Small Bolt H&W H558841/85 $2.39 200 169 Y Y PX723742 A Socket White & Portico W678322/30 $1.99 200 44 PX723743 Socket Buggins B517373/85 $2.19 200 74 Y PX797516 Spring 3a H&W H539656/85 $1.91 200 155 PX242979 . Spring 5b White & Portico W487032/43 $1.66 200 66 Y PX823978 Vice law Front White & Portico W325980/58 $23.09 50 165 PX807540 Vice Screw Sa H&W H531341/87 $3.83 100 132 PX807539 Vice Screw Sa H&W H410102/85 $18.69 50 126 PX809486 Washer Special H&W H456602/63 $1.91 200 45 Y Marks Done 15 2 2 2 F4 2 Question Instruction Section 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. F2 In Q4 calculate the average cost of parts supplied by the supplier shown in N4. Copy the formula down to 26. F3 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 Oif 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. (2 Marks will be given for doing just the first part correctly). TOTAL MARKS F5 3 a F6 4 80 PX725125 Nut H&W H467610/39 $1.99 200 127 Y PX796490 Nut Wing Buggins B712863/57 $2.39 200 144 PX807531 Pin 12 White & Portico W439743/32 $1.99 200 177 --- PX807532 Pin 33 White & Portico W629507/25 $1.83 200 Y 170 PX723566 Pin 10 H&W H683213/67 $2.09 200 153 Y PX820263 RH Nut Buggins B347655/87 $1.99 200 Y 103 PX792653 Rivet Use: 1004688-16 White & Portico W724130/13 $1.74 200 11 Y PX823385 Screw 2.4 mm H&W H578246/26 $1.74 200 36 Y PX860085 Screw 1.2 mm H&W H396220/43 $2.39 200 107 PX823386 Screw Special H&W H319247/65 $1.66 200 277 PX820834 Single Grig Buggins 1668438/72 $1.99 200 154 Y PX723596 Small Bolt H&W H558841/85 $2.39 200 169 Y Y PX723742 A Socket White & Portico W678322/30 $1.99 200 44 PX723743 Socket Buggins B517373/85 $2.19 200 74 Y PX797516 Spring 3a H&W H539656/85 $1.91 200 155 PX242979 . Spring 5b White & Portico W487032/43 $1.66 200 66 Y PX823978 Vice law Front White & Portico W325980/58 $23.09 50 165 PX807540 Vice Screw Sa H&W H531341/87 $3.83 100 132 PX807539 Vice Screw Sa H&W H410102/85 $18.69 50 126 PX809486 Washer Special H&W H456602/63 $1.91 200 45 Y Marks Done 15 2 2 2 F4 2 Question Instruction Section 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. F2 In Q4 calculate the average cost of parts supplied by the supplier shown in N4. Copy the formula down to 26. F3 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 Oif 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. (2 Marks will be given for doing just the first part correctly). TOTAL MARKS F5 3 a F6 4 80