Only need the answer for F6 Challenge question

Only part F6

the last photo is the question

Marks Done NE D 2 2 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 06. F3 Some parts have been re-ordered but not yet delivered, these are said to be on backorder and are indicated with a Yin the backorder column. In R4 calculate how many parts on backorder are supplied by the supplier shown in N. 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. FS 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 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, retum of 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. 12 Marks will be given for doing just the first part correctly) 3 4 T U V w IMAGE DE CRIPTION SUPRIR SLIPPLIER CODE COST EACH MIN STOCK IN STOCK RACKORDER CATEGORY FORDFRY RF-ORDER OT Win Orsery Parts Avence Val Thomson Rapin Supplier H5W Arm $20 White Blerk W52996/78 $2.03 200 12 Again , R55 $3.59 ro 100 White Prim ME AX watu WD/ M 300 35 11 Block 12/4 white & W771072/53 200 Alerk met Buggins RSITM/ $12.00 no Heerder over nickler 14-X while 2 Portia: w71719/35 $11. 300 Block Near ugalific 200 Balt HRW H443251/40 SL 33 200 102 nurk 92 White Portion w211/4 54.51 Pool Luanto 11/223/11 $3:24 200 21/ Foots Bugalne B42008/20 $249 200 23 White Part W170/ $2.91 900 106 15 Krabi whil& w281/ $2.03 200 16 Large Boh HAW H326751/31 $2.09 200 249 0411191/9 300 221 18 L while weny 3.13 300 130 B57302314 $3.11 255 20 Nr Super White Portion W61529/24 S.99 ro 109 NU 15 40291039 300 22 Information Labour costs HR Fixed Casts HAM CVP Analysis . Profit Volume Forecast Inventory T th ACE Powie Dag Pagelar severe F Accent ? Cum MEZ No O Inventory BACKORDER CATEGORY MIN STOCK IN STOCK RE-ORDER? RE-ORDER QT IMAGE DESCRIPTION PART CODE SUPPLIER SUPPLER CODI COST EACH 7X723541 Am 120 A White Hortico W529196778 52.0 200 PX723542 Arm35 Busin) 8557761/17 52.59 200 198 PX378561 White & Portico W664956/39 5124 100 LARI 63 295 PX20251 Boa 12/A White Portico W771072/53 $12.99 100 B P820262 Block Front Begins 8567850/21 $12.09 100 262 3 PX100453 Block Rear 14- White & Portico w701735/28 $13.74 100 11 8 PX820266 10 Block Rear Bussins 3336854/81 512.09 100 257 23593 Bolt HAW H448251/40 $18 200 102 Y FX40S 12 Bracket 552 White Portice W426061/24 54461 50 258 B PX723557 Busins 3472237/18 $3.24 100 217 PART CODE IMAGE DESCRIPTION SUPPLIER SUPPLIER COOL COST EACH MIN STOCK IN STOCK BACKORDER CATEGORY RE-ORDER? RE-ORDER OTY PX723557 Foot 8422237/18 $3.24 100 212 A 1 PX72320 Foot Sa 8420251/20 $2.49 200 23 Y PX807530 Grip White & Portico W56499/34 200 166 A Y PX820728 Knob White & Portico W591861/33 $2.09 200 97 Y PX721594 17 HW Large Bot 1326781/31 $2.09 200 249 A PX723205 18 Large Foot Buce 8433191/10 51.66 200 221 A P73195 19 c atch White & Portico W592160/27 53.11 100 130 A PX23745 20 LA Busin 3675073/41 53.11 100 255 X577199 21 White & Portico W361589/24 St 99 200 169 A Y 2X725125 22 Nur KW H467610/39 $2.59 200 286 A PX7600 13 Busins 8712063/57 $2.49 200 117 Y 531 Pin 12 White & Portico W419743/32 $1.91 200 283 MACE PARTCODE SUPPLIER SUPPLER CODI COST EACH DESCRIPTION MIN STOCK BACKORDER IN STOCK CATEGORY RE-ORDERT RE-ORDER TY Nut HAW 025125 529 M67610/39 200 26 22 PX7600 hawn Y Bu 712863/57 200 117 A TESORA Who W41974/12 1615 300 283 Po 25 WP w7105 220 A X2 PO HOW 63321376 200 BGT A 20265 200 27 A X2653 Niet 100418-14 Worte WALIO 200 12 V SET MTH 245/26 150 200 25 V SRO . w H220/41 HA 200 1 A 8236 HOW 14 515 200 225 SOS 4 . 66 ES 263 SUEDE Sot WH H5500/ 200 53 IN STOCK BACKORDER CATEGORY RE-ORDER? RE ORDER TY IMAGE SUPPLIER DESCRIPTION Screw VARTCODE P2 31 SUPPLIER CODI COST EACH MIN STOCK 019247/65 51.91 200 A 225 HOW A 52.39 261 200 431/72 2004 Singerie H558541/35 200 57 $1.99 HEW PX72596 Small A 200 W678322/30 $1.99 White & Portico PX723742 275 200 261 517373/ A 52.00 Sock PX72374 35 D 200 HAW 1530656/85 27 PX797590 V A Soring W7032/4) w Partico 200 $1.90 125 Y 242972 Son 50 P278 Vicero We artico W325980/55 50 186 O Vice Screw Sa P0540 13 HEW 100 130 Vice Screw 5 HOW H410102785 51699 50 2 Y Wher Special HBW H45660063 S166 200 View Help Power Pivot Table Design Normal 2 Normal General Insert Delete For Currency Percent 3 Currency 1 Title 2 2 60% - Acceeit. Conditional Formatas Percent 2 Formatting Table Wrap Text Normal $ - % Cells Number Algement R S M N Supplier Items on Backorder Min Order Qty Parts Average Value H&W 4 25 12 $3.49 30 White & Portico 15 $8.18 Buggins 55 11 $4.23 1 Reorder % over Minimum 25% 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. F2 In Q4 calculate the average cost of parts supplied by the supplier shown in N4. Copy the formula down to Q6. 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. 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. FS 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 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 o 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. (2 Marks will be given for doing just the first part correctly). TOTAL MARKS Further Clarification: 1. The BOM is like a recipe. It itemises which components and how many we need to make one of eae product, 2. In the CVP, the Breakeven costs are the costs incurred to produce the number of units required to it usually costs $10 in materials to make one unit, and I need to sell 5 units to break even, my Break costs are $50 If any cells are incorrectly formatted teshin M 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. F2 In Q4 calculate the average cost of parts supplied by the supplier shown in N4. Copy the formula down to Q6. 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. 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 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 the re-order quantity is less than the Min Order Qty for the supplier, it returns the we need to re-order and appropriate Min Order Qty. (2 Marks will be given for doing just the first part correctly). TOTAL MARKS