A B D E F G H J K L M N Inventory Inventory Management In Stock On Backord Reorder? Reorder Weight (g 0.3 6 Category Accessories and tuning Groupset Wheels Min Slock Level Max Slock Level Reorder At Reorder Due 20 40 22 2 84 778.1 34 588 28 21 2.2 217.6 0.8 9 Y 9 1 2 3 Part Code Product Description 4 AC1945 Alloy CR Bolts 5 GR1751 CNC brakes 200g Brakes 6 WH2778 CNC V16 Super Grip Wheels 7 WH1260 CNC V18 Ultra Wheels 8 CO2904 Custom Bar Plugs 9 GR1215 Dura Ace Chain 10 AC1508 Fairing M5 Bolts 11 WH2856 Forte LCR Tyres 12 WH1573 Forte Light Tubes 13 AC2964 Generic FD Adapter 14 CO1469 Generic Foam Tape Tape 15 FR1934 Headset 16 FR1235 J&L Fork 17 FR2360 J&L Plug 18 FR1080 J&L Top Cap & Screw 19 CO1649 KCNC Scandium Seatpost 20 FR1081 Mr. Ride Seat Clamp 21 CO1099 Nashbar Carbon Bar 22 FR1323 Nashbar Carbon Frame Price Bracket 0 50 100 200 Re-order Amount 25 20 15 10 9 22 Supplier Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycle Warehouse Nashbar Cycle Warehouse Performance Performance Cycle Warehouse Cycle Warehouse Bikes Plus Bikes Plus Bikes Plus Cycle Warehouse Cycle Warehouse Cycle Warehouse Nashbar Nashbar Price/Unit $1.75 $61.20 $159.41 $245.67 $0.64 $24.81 $0.51 $16.77 $52.61 $2.06 $0.15 $27.81 $151.36 $13.16 $4.05 $85.37 $15.98 $50.80 $366.91 Wheels Contact Points Groupset Accessories and tuning Wheels Wheels Accessories and tuning Contact Points Frameset Frameset Frameset Frameset Contact Points Frameset Contact Points Frameset 228.9 56.1 23.6 0.6 60.6 20 On Backorder Over-Stocked 17 44 344 28 6 32 41 11 Supplier Cycle Warehouse Nashbar Performance Bikes Plus Backcountry LBS 7.8 180.1 9.5 189 37 1 Y 957 14 The inventory sheet contains a list of all items stocked to make the bikes. The In Stock column indicates how many of each part are currently in stock. Where the stock level has fallen below the minimum shown in M3 it may already have been re-ordered, where this is the case a "Y" will show in the On Backorder Column. Please note that all values in this sheet are subject to change and so cell references must be used rather than typing values into formulas. If a stock item falls below the minimum stock level in M3 and is not on backorder (value in column His not Y), then we need to reorder it. In 14 create a calculation that will return "Y" if the item needs reordering and an empty string (nothing) if it does not. The quantity to reorder is determined by the price of the item as shown in the table in L9:M12, e.g. anything that costs under $50 we reorder 25 (these values can change). In J4 calculate the reorder quantity, return 0 if reorder not required. 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 in M5 and a No otherwise. In M15:M20 calculate how many part Codes are on Backorder for each supplier (in column L). M4 shows the Maximum Stock Level. In N15:N 20 calculate how many part codes are over-stocked for each supplier, remember that the value in M4 can change. (This is a challenge question.)