please
Section 14 F1 3 3 F2 3 3 The following are to be compierea in the inventory worksheet 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 H is 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 34 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 MS 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:N20 calculate how many part codes are over-stocked for each supplier, remember that the value in M4 can change. (This is a challenge question.) F3 3 F4 2 2 F5 3 3 A B C D F G H E Inventory 1 2 3 Part Cod Product Description 4 AC 1945 Alloy CR Bolts 5 GR1751 CNC brakes 200g Brakes 6 WH2778 CNC V16 Super Grip Wheels 7 WH1260 CNC V18 Ultra Wheels 8 C02904 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 C01649 KCNC Scandium Seatpost 20 FR1081 Mr. Ride Seat Clamp 21 c01099 Nashbar Carbon Bar 22 FR1323 Nashbar Carbon Frame 23 GR2655 Nashbar Cassette 24 GR 1966 Nashbar FD 25 GR 1432 Nashbar Levers 26 GR 1770 Nashbar RD 27 C02357 RockBros CrMo Saddle 28 AC1914 RockBros S60 Pedals 29 C02143 Spacers 30 C01688 Specialised Foam Tape Tape 31 WH2942 Titanium QR Skewers 32 AC2733 Titanium Stem Bolts 33 GR 1406 Ultegra BB 34 GR1550 Ultegra Cables 35 GR1836 Ultegra Cassette 36 GR2009 Ultegra Cranks 37 GR 1172 Ultegra FD Information Fixed costs Ready Accessibility Inwestoste 9.5 Category Supplier Price/Unit Weight (g Accessories and tuning Cycle Warehouse $1.75 0.3 Groupset Cycle Warehouse $72.04 84 Wheels Cycle Warehouse $169.74 778.1 Wheels Cycle Warehouse $250.26 588 Contact Points Cycle Warehouse $0.82 2.2 Groupset Nashbar $27.24 217,6 Accessories and tuning Cycle Warehouse $0.51 0.8 Wheels Performance $20.60 228.9 Wheels Performance $55.32 56.1 Accessories and tuning Cycle Warehouse $2.59 23.6 Contact Points Cycle Warehouse $0.15 0.6 Frameset Bikes Plus $26.73 606 Frameset Bikes Plus $184.80 344 Frameset Bikes Plus $13.04 6 Frameset Cycle Warehouse $4.20 7.8 Contact Points Cycle Warehouse $78,32 180.1 Frameset Cycle Warehouse $15.12 Contact Points Nashbar $53 19 189 Frameset Nashbar $387 07 957 Groupset Nashbar $45 54 1972 Groupset Nashbar $33.58 86.3 Groupset Nashbar $111.67 199.8 Groupset Nashbar $44.73 202.8 Contact Points Cycle Warehouse $1902 210.6 Accessories and tuning Nashbar $26.00 1316 Contact Points Bikes Plus $28.56 2.4 Contact Points Cycle Warehouse $0.14 0.5 Wheels Cycle Warehouse $5.29 26.8 Accessories and tuning Cycle Warehouse $9.15 8.7 Groupset Backcountry $15.79 51 Groupset Nashbar S21 39 49 1 Groupset Nashbar $37.90 176 Groupset Nashbar $136.04 694.9 Groupset Nashbar $24.80 91.7 March Sales Labour Costs BOM CVP Analysis Profit Volume Forecast In Stock - On Backord 1 26 8 43 32 25 23 9 1 44 30 37 28 3 24 26 8 4 41 17 19 32 27 15 Y 41 39 26 12 28 13 35 9 Y 42 28 Inventory H K N M Inventory Management ek On Backord Reorder? Reorder Q Min Slock Level Max Slock Level Reorder At Reorder Due 20 40 22 Price Bracket Re-order Amount 0 25 50 20 100 15 200 10 On Backorder Over-Stocked Supplier Cycle Warehouse Nashbar Performance Bikes Plus Backcountry LBS Y Y ory Section 14 F1 3 3 F2 3 3 The following are to be compierea in the inventory worksheet 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 H is 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 34 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 MS 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:N20 calculate how many part codes are over-stocked for each supplier, remember that the value in M4 can change. (This is a challenge question.) F3 3 F4 2 2 F5 3 3 A B C D F G H E Inventory 1 2 3 Part Cod Product Description 4 AC 1945 Alloy CR Bolts 5 GR1751 CNC brakes 200g Brakes 6 WH2778 CNC V16 Super Grip Wheels 7 WH1260 CNC V18 Ultra Wheels 8 C02904 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 C01649 KCNC Scandium Seatpost 20 FR1081 Mr. Ride Seat Clamp 21 c01099 Nashbar Carbon Bar 22 FR1323 Nashbar Carbon Frame 23 GR2655 Nashbar Cassette 24 GR 1966 Nashbar FD 25 GR 1432 Nashbar Levers 26 GR 1770 Nashbar RD 27 C02357 RockBros CrMo Saddle 28 AC1914 RockBros S60 Pedals 29 C02143 Spacers 30 C01688 Specialised Foam Tape Tape 31 WH2942 Titanium QR Skewers 32 AC2733 Titanium Stem Bolts 33 GR 1406 Ultegra BB 34 GR1550 Ultegra Cables 35 GR1836 Ultegra Cassette 36 GR2009 Ultegra Cranks 37 GR 1172 Ultegra FD Information Fixed costs Ready Accessibility Inwestoste 9.5 Category Supplier Price/Unit Weight (g Accessories and tuning Cycle Warehouse $1.75 0.3 Groupset Cycle Warehouse $72.04 84 Wheels Cycle Warehouse $169.74 778.1 Wheels Cycle Warehouse $250.26 588 Contact Points Cycle Warehouse $0.82 2.2 Groupset Nashbar $27.24 217,6 Accessories and tuning Cycle Warehouse $0.51 0.8 Wheels Performance $20.60 228.9 Wheels Performance $55.32 56.1 Accessories and tuning Cycle Warehouse $2.59 23.6 Contact Points Cycle Warehouse $0.15 0.6 Frameset Bikes Plus $26.73 606 Frameset Bikes Plus $184.80 344 Frameset Bikes Plus $13.04 6 Frameset Cycle Warehouse $4.20 7.8 Contact Points Cycle Warehouse $78,32 180.1 Frameset Cycle Warehouse $15.12 Contact Points Nashbar $53 19 189 Frameset Nashbar $387 07 957 Groupset Nashbar $45 54 1972 Groupset Nashbar $33.58 86.3 Groupset Nashbar $111.67 199.8 Groupset Nashbar $44.73 202.8 Contact Points Cycle Warehouse $1902 210.6 Accessories and tuning Nashbar $26.00 1316 Contact Points Bikes Plus $28.56 2.4 Contact Points Cycle Warehouse $0.14 0.5 Wheels Cycle Warehouse $5.29 26.8 Accessories and tuning Cycle Warehouse $9.15 8.7 Groupset Backcountry $15.79 51 Groupset Nashbar S21 39 49 1 Groupset Nashbar $37.90 176 Groupset Nashbar $136.04 694.9 Groupset Nashbar $24.80 91.7 March Sales Labour Costs BOM CVP Analysis Profit Volume Forecast In Stock - On Backord 1 26 8 43 32 25 23 9 1 44 30 37 28 3 24 26 8 4 41 17 19 32 27 15 Y 41 39 26 12 28 13 35 9 Y 42 28 Inventory H K N M Inventory Management ek On Backord Reorder? Reorder Q Min Slock Level Max Slock Level Reorder At Reorder Due 20 40 22 Price Bracket Re-order Amount 0 25 50 20 100 15 200 10 On Backorder Over-Stocked Supplier Cycle Warehouse Nashbar Performance Bikes Plus Backcountry LBS Y Y ory
