The inventory manager prefers to wait until a certain number of parts require re- ordering before...

Transcribed Image Text:

The inventory manager prefers to wait until a certain number of parts require re- ordering before placing the orders. He has specified this value in M5 but does adjust it depending on production levels. In M6 create a calculation to count how many Part Codes are due to be re-ordered and return a "Yes" if it is equal or over the value F3 in M5 and a "No" otherwise. In M15:M20 calculate how many Part Codes are on Backorder for each supplier (in column L). F4 M4 shows the Maximum Stock Level. In N15:N20 calculate how many part codes are over-stocked for each supplier, remember that the value in M4 can change. F5 (This is a challenge question.) Inventory Inventory Management 1 2 3 Part Coc- Product Description Alloy CR Hots Category Accessories and tuning - Supplier Price/Uni- Weight ( In Stock - On Backord Reorder Reorder Min Slock Level Max Slock Level Reorder At Reorder Due 20 4 AC1945 Cycla Werehouse $1.91 (0.3 9 25 40 5 GR1751 6 W12776 7 WH1260 8 CO2I04 9 GR1215 CNC brakes 200g Araken Graupset Cyrk Warehnuse 859 93 84 44 20 22 CNC V18 Supcr Grip Whe:k Whects Cy:ki Wisrehuse $152.03 770.1 35 15 CNC V10 Ullra Whees Wheels Cycle Warehouse $252.58 588 12 10 Cuslam Bar Plunn Durs Ace Chain Fairing M5 Butu Forta I CR Tyres Price Bracket Re-order Amount Cyrk Warnhnuse Neshbar Cycle Waretouse Cantact Poants $0 65 22 35 25 Groupset 25 25 526.51 217.6 3/ 25 10 AC1508 Accessories and tuning 30.52 0.8 35 50 20 11 WH2A58 Wheek Performanca S19 14 2289 42 25 100 200 15 12 WH1573 13 AC2964 l'erformence 20 25 Forte Light Tubes 10 Wheels Accessories and luning S01.83 26 Generic FD Adupler Cycle WarehuUse $2.42 23.6 22 On Backprder 14 CO140 15 FR1931 Genenc Foam Tape Tope Cantact Prints Cyrke Warnhnuse $0 18 22 25 Supplier Cycle Warehouse Nashbar Performance Bikes Plus Over-Stocked 25 15 Headset Frameset Bikes Mus S23.19 00.6 16 FR1235 J&L Furk Fiamesel Dikes Plus $179.52 344 24 1&I Plug J&L Top Csp & Screw KCNC Scandjuni Sualuual 17 FR2360 Frameset Hikns Plus S11 67 25 18 FR1080 Frameset Cycle Werehouse $1.05 7.8 20 25 19 Co1849 Cycki WiareliDuse Cycla Warehoune Cunlacl Puinty S78.32 180.1 20 20 Backcountry 20 FRIO81 Mr Ride Seat Clamp Frameset 25 LBS S14 28 95 21 Co1099 Nashber Certon Bar Contsct Points Neshbar S56.17 189 30 20 22 TRI32) Nistibar Carbon Frame Fiamesel Naeshbar $300.04 957 15 10 23 GR265 Nashhar Casnatte Groupset S38 02 25 25 Nashhar 197 2 38 24 GR1900 Nashber FD Groupset Neshbar 531.21 86.3 11 25 GR1432 26 GR17/0 27 Co2357 28 АС1914 Nistitar Levers Graupset Nieshibar $110.00 199.0 18 15 Nashbar RI Groupset Nashbar 539 71 202.8 1/ 25 RockBros CriMo Saddle Contect Foints Cycle Warehouse S15.01 210.6 3 25 RockBrus S60 Pedaks Accenks and luning Nistbar S21.25 131.6 41 25 29 CU143 30 co1088 31 W12942 Spacers Contact Ponts 25 25 Hikes Plus 531 36 2.4 4 Specislised Foem Tepe Tape Contect Points Cycle Warehouse 30.18 0.5 44 Tilasnium QR Skree Cycki Wisrehuse $4 74 $8. 70 Whecs 28.0 30 25 32 AC2/33 Accessones and tuning 25 25 Iitenium Stem Bots Cycla Warehouse 8.7 30 33 GR1408 Ultegra BB Groupset Backoountry $15.20 51 3 Lilikegra Castices Uhagre Cassette Ultegre CrenkS 34 GRI550 Groupset Nishtar S25 78 49.1 32 25 35 GR1836 36 GR2009 37 GRI 172 20 15 Groupset Neshbar S1.28 1/6 16 Groupset Nashbar $165.30 694.9 31 Lilikspra FD Groupset Nashbar S31 41 91.7 25 38 GR2589 5/2 00 216.8 20 25 Uhagre Levers Groupset Groupset Neshbar 42 Ulegre RD Lina 7050 Slem 39 GR1322 Backcountry Cyrk: Wiarche $43.16 201.8 3 40 C02202 Canlact Prins $13 94 130 9 11 Y 25 * ... March Sales | Labour Costs BaM CVP Analysis Profit Volume Forecast Inventory BOM The inventory manager prefers to wait until a certain number of parts require re- ordering before placing the orders. He has specified this value in M5 but does adjust it depending on production levels. In M6 create a calculation to count how many Part Codes are due to be re-ordered and return a "Yes" if it is equal or over the value F3 in M5 and a "No" otherwise. In M15:M20 calculate how many Part Codes are on Backorder for each supplier (in column L). F4 M4 shows the Maximum Stock Level. In N15:N20 calculate how many part codes are over-stocked for each supplier, remember that the value in M4 can change. F5 (This is a challenge question.) Inventory Inventory Management 1 2 3 Part Coc- Product Description Alloy CR Hots Category Accessories and tuning - Supplier Price/Uni- Weight ( In Stock - On Backord Reorder Reorder Min Slock Level Max Slock Level Reorder At Reorder Due 20 4 AC1945 Cycla Werehouse $1.91 (0.3 9 25 40 5 GR1751 6 W12776 7 WH1260 8 CO2I04 9 GR1215 CNC brakes 200g Araken Graupset Cyrk Warehnuse 859 93 84 44 20 22 CNC V18 Supcr Grip Whe:k Whects Cy:ki Wisrehuse $152.03 770.1 35 15 CNC V10 Ullra Whees Wheels Cycle Warehouse $252.58 588 12 10 Cuslam Bar Plunn Durs Ace Chain Fairing M5 Butu Forta I CR Tyres Price Bracket Re-order Amount Cyrk Warnhnuse Neshbar Cycle Waretouse Cantact Poants $0 65 22 35 25 Groupset 25 25 526.51 217.6 3/ 25 10 AC1508 Accessories and tuning 30.52 0.8 35 50 20 11 WH2A58 Wheek Performanca S19 14 2289 42 25 100 200 15 12 WH1573 13 AC2964 l'erformence 20 25 Forte Light Tubes 10 Wheels Accessories and luning S01.83 26 Generic FD Adupler Cycle WarehuUse $2.42 23.6 22 On Backprder 14 CO140 15 FR1931 Genenc Foam Tape Tope Cantact Prints Cyrke Warnhnuse $0 18 22 25 Supplier Cycle Warehouse Nashbar Performance Bikes Plus Over-Stocked 25 15 Headset Frameset Bikes Mus S23.19 00.6 16 FR1235 J&L Furk Fiamesel Dikes Plus $179.52 344 24 1&I Plug J&L Top Csp & Screw KCNC Scandjuni Sualuual 17 FR2360 Frameset Hikns Plus S11 67 25 18 FR1080 Frameset Cycle Werehouse $1.05 7.8 20 25 19 Co1849 Cycki WiareliDuse Cycla Warehoune Cunlacl Puinty S78.32 180.1 20 20 Backcountry 20 FRIO81 Mr Ride Seat Clamp Frameset 25 LBS S14 28 95 21 Co1099 Nashber Certon Bar Contsct Points Neshbar S56.17 189 30 20 22 TRI32) Nistibar Carbon Frame Fiamesel Naeshbar $300.04 957 15 10 23 GR265 Nashhar Casnatte Groupset S38 02 25 25 Nashhar 197 2 38 24 GR1900 Nashber FD Groupset Neshbar 531.21 86.3 11 25 GR1432 26 GR17/0 27 Co2357 28 АС1914 Nistitar Levers Graupset Nieshibar $110.00 199.0 18 15 Nashbar RI Groupset Nashbar 539 71 202.8 1/ 25 RockBros CriMo Saddle Contect Foints Cycle Warehouse S15.01 210.6 3 25 RockBrus S60 Pedaks Accenks and luning Nistbar S21.25 131.6 41 25 29 CU143 30 co1088 31 W12942 Spacers Contact Ponts 25 25 Hikes Plus 531 36 2.4 4 Specislised Foem Tepe Tape Contect Points Cycle Warehouse 30.18 0.5 44 Tilasnium QR Skree Cycki Wisrehuse $4 74 $8. 70 Whecs 28.0 30 25 32 AC2/33 Accessones and tuning 25 25 Iitenium Stem Bots Cycla Warehouse 8.7 30 33 GR1408 Ultegra BB Groupset Backoountry $15.20 51 3 Lilikegra Castices Uhagre Cassette Ultegre CrenkS 34 GRI550 Groupset Nishtar S25 78 49.1 32 25 35 GR1836 36 GR2009 37 GRI 172 20 15 Groupset Neshbar S1.28 1/6 16 Groupset Nashbar $165.30 694.9 31 Lilikspra FD Groupset Nashbar S31 41 91.7 25 38 GR2589 5/2 00 216.8 20 25 Uhagre Levers Groupset Groupset Neshbar 42 Ulegre RD Lina 7050 Slem 39 GR1322 Backcountry Cyrk: Wiarche $43.16 201.8 3 40 C02202 Canlact Prins $13 94 130 9 11 Y 25 * ... March Sales | Labour Costs BaM CVP Analysis Profit Volume Forecast Inventory BOM

Answer rating: 100% (QA)

Formula for F3 Min Stock Level 20 Max Stock Level 40 Reorder at 2... View the full answer