Complete these by using cell references
Section F 14 F1 3 F2 3 The following are to be completed 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 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: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 F5 3 Inventory Inventory Management In Stock On Backord: Reorder? Reorder Qy Min Slock Level Max Slock Level Reorder A Reorder Due 20 40 22 Y Y 6 38 9 22 1 17 G 40 23 4 41 Y Y Y Y Price Bracket 0 50 100 200 Re-order Amount 25 20 15 10 Y On Backorder Over-Stocked 0 g 18 Y Y Y Y Y Supplier Cycle Warehouse Nashbar Performance Bikes Plus Backcountry LBS GR2855 Part Code Product Description AC 1945 Alloy CR Belt GR 1751 CNC brakes 2009 Brake WH2778 CNC V18 Super Grip Wheels WH 1200 CNC V1 Ultra Wheels CO2904 Custom Bar Plugs GR 1215 Dura Ace Chain AC 150B Fairing M5 Bolls WH256 Forte LCR Tyres WH 1573 Forle Light Tubes AC2964 Generic FD Adapter CO1469 Generic Foam Tape Tape FR 1904 Headset FR 1235 J&L Fork FR2360 J&L Plug FR 1080 J&L Top Cap & Screw CO1649 KCNC Scandum Seatpost FR1081 Mr. Ride Seat Clamp CO1099 Nashbor Carbon Bar FR1323 Nasher Carbon Frame Nashber Cassette GR 1966 Naaber FD GR 1432 Naster Levers GR 1770 Nashbar RD CO2357 RockBros CrMo Saddle AC 1914 RockBros SR Pedas C02143 Spacers CO168 Specialised Foam Tape Tape WH2912 Titanium QR Skewers AC2733 Titanium GR 1408 Ullegra DB GR 1150 Ultegra Cables GR 1836 Ultegra Coselle GR2009 Ultegra Cranks GR 1172 UllegaFD GR21589 Ultegra Lovers GR 1322 Ultegra RD C02202 Uno 7000 Stem WH2373 Veloplugs Rim se WH1100 XR-270 Bitex Wheel AC 1919 Bikes Plus Deluxe Pedale AC2174 Rockgrip Alped Huez Pecals C01104 Rockglider Carbon Bar CO1650 Diamond Seatpost C00203 BACycle 5000 Stem CO2381 Diamond Ultra Saddle FR 1082 Finn Cap & Screw FR 1088 RockBros Seat Clamp FR 1240 Firstlite Fork FR1324 RockBros Carbon Frame Category Accessories and tuning Groupset Wheels Wheels Contact Points Groupset Accessories and during Whools Wheele Accessories and tuning Contact Pointe Frameset Framesel Frameset Frameeel Contact Paints Frameset Contact Paints Frameset Groupset Grousset Groupset Groupset Contact Points Accessories and tuning Contact Points Contact Paints Wheels Accessories and tuning Groupel Grousset Groupeet Groupset Groupeet Groupset Groupeet Contact Paints Wheels Wheels Accessories and tuning Accessories and turing Contact Points Contact Paints Contact Points Contact Paints Frameset Supplier Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycia Warehouse Cycle Warehouse Nashhar Cycle Warehouse Performance Performance Cycle Warehouse Cycle Warehouse Bikes Plus Bikes Plus Bikes Plus Cycle Warehouse Cycle Warehouse Cycle Warehouse Nachbar Nachbar Nashbar Nashbar Nashbar Nashbar Cycle Warehouse Nashher Bikes Plus Cycia Warehouse Cycle Warehouse Cycia Warehouse Backcountry Nashher Nashbar Nashhar Nashbat Nashbar Beckoountry Cycle Warehouse LES Cycle Warehouse Bikes Plus Cycle Warehouse Nashher Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycle Warehouse Bikes Plus Nashbar Prie/Une Weight (9 $2.33 568.65 84 $166.31 $ 778.1 $294.19 588 SO.B6 22 S24.32 2176 S0.48 0.8 S18.23 229.9 S60.89 56.1 $2.50 23.6 S0.18 0.6 S28.62 60.6 $174.21 344 . S11.7 6 59.71 78 SB3.02 180.1 $14.40 8.5 S88.93 189 $123.38 957 S40.39 1972 $95.16 863 $121.18 199.8 538.87 2028 S17.22 210.6 73.25 1316 $23.00 2.4 $0.15 as 015 S5.11 28.8 $9.33 8.7 S13.45 51 S24.84 49.1 $19.05 178 $11523.59 6849 523.89 91.7 S77.45 216.8 S10.57 2018 $15.58 130.9 54.85 4 $11144 595 527 25 1316 S25.24 110.3 562.75 189 S71.27 100.1 $18.53 1309 S19.20 210.6 $3.98 78 S14.11 9.5 $183.014 344 $143.52 957 Y Y Y Y Sim Baits 8 19 31 42 21 37 28 9 6 35 23 11 16 32 42 38 G 13 39 19 29 25 18 41 9 15 43 21 20 23 3 2 31 Y Y Y Y Y Y Frames Y Y Frameset Frameset Information Fixed Costs March Sales Labour Costs BOM CVP Analysis Profit Volume Forecast Inventory Category Prica/Unit Weight (g) In Stock Reorder Qty K L M On Backorder Reorder Y Y 6 35 23 11 18 32 Y Y Y 523.25 $22.90 $0.15 S5.11 59.33 $13.45 $24.84 S49.05 $153.59 $23.69 S77.45 S40.67 $15.58 S4.95 $111.44 $27 25 Y Y Y Y 50524 Y Y Accessories and turning Contact Points Contact Points Wheels Accessories and tuning Groupset Groupeet Groupset Groupset Groupset Groupset Groupset Contact Points Wheels Wheels Accessories and tuning Accessories and turing Contact Points Contact Points Contact Paints Contact Points Frameset Frameset Frameset Frameset Frameset Groupset Groupeet Groupset Groupset Groupset Groupset Groupset Groupset Groupset Groupset Groupset Groupeet Groupset Wheels Wheels Wheels Part Code Product Description wwwwwwwwww 28 AC 1914 RockBros S60 Pedals 29 CO2143 Spacers 30 CO1688 Specialised Foam Tape Tape 31 WH2942 Titanium QR Skewers el 32 AC2733 Titanium Stem Bolts 33 GR 1406 Ultegra BB 34 GR 1550 Ultegra Cables 35 GR 1836 Ultegra Cassette 36 GR 2009 Ultegra Cranks 37 GR 1172 Ultegra FD 38 GR2589 Ultegra Levers 39 GR 1322 Ultegra RD 40 C02202 Uno 7050 Stem 41 WH2373 Veloplugs Rim tape 42 WH1100 XR-270 Bitnx Wheel 43 AC 1919 Bikee Plus Delucce Pedals 44 AC2174 Rockgrip Alpe d Huez Pedals 45 c01104 Rockglider Carbon Bar 46 CO1650 Diamond Seatpost 47 C02203 BBCycle 5000 Stern 4B CO2361 Diamond Ultra Saddle 49 FR 1082 Finn Cap & Screw 50 FR 1086 RockBros Seal Clamp 51 FR 1240 Firstlite Fork 52 FR1324 RockBros Carbon Frame 53 FR 1937 Giga Headset 54 GR 1177 Firma FD 55 GR1218 Magneto Chain 56 GR1325 Firma RD 57 GR 1407 Firma BB 58 GR1435 Rockgrip Levers 59 GR 1554 Firma Cables 60 GR 1753 Firma Brakes 61 GR1754 Rockgrip RD 62 GR 1755 Firma Cassette 63 GR 1756 Rockgrip FD 64 GR1757 Firma Cranks 65 GR 1758 Firma Levers 66 GR 1759 Rockgrip Cassette 67 GR1760 Ultrazon 5 Wheel 68 GR1761 Firma Inner Tubes 69 GR1782 Ultra Rock Tyres 70 71 72 73 74 75 76 77 78 79 80 Information Supplier wwwwwwwwww Nashbar Bikes Plus Cycle Warehouse Cycle Warehouse Cycle Warehouse Backcountry Nashber Nashbar Nashbar Nashber Nashbar Backcountry Cycle Warehouse LBS Cyale Warehouse Bikes Plus Cycle Warehouse Nashbar Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycle Warehouse Bikes Plus Nashber Bikes Plus Nashbar Nashbar Backcountry Backcountry Nashbar Nashbar Cycle Warehouse Nashber Nashber Nashbar Nashber Nashber Nashbar Cycle Warehouse Performance LRS M 131.6 2.4 05 26.8 8.7 51 49.1 178 604.9 91.7 216.8 204.8 130.9 4 995 131.6 110.3 189 180.1 130.9 210.6 7.8 9.5 344 957 60.6 91.7 217.6 204.8 51 198,8 49.1 84 202.9 176 88.3 49 216.8 1972 995 56.1 2289 Y Y 38 6 13 39 19 39 25 19 41 9 15 43 21 20 23 3 2 31 30 20 34 2 37 6 14 37 7 14 18 21 18 37 5 40 18 $42.75 S7127 S18.53 S19 20 $3.98 S14.11 $183,04 $443.52 $12.18 $26.90 $21.64 539.84 S16.08 $119.99 58.91 589 29 $46.40 $15.04 S2820 $16237 S73.33 $34.45 $143.55 55124 $32.86 Y Y Y Y Y Y Y Y Y Y Y Y Y Y % Fixed Costs March Sales Labour Costs CVP Analysis Profit Volume Forecast Inventory + Section F 14 F1 3 F2 3 The following are to be completed 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 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: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 F5 3 Inventory Inventory Management In Stock On Backord: Reorder? Reorder Qy Min Slock Level Max Slock Level Reorder A Reorder Due 20 40 22 Y Y 6 38 9 22 1 17 G 40 23 4 41 Y Y Y Y Price Bracket 0 50 100 200 Re-order Amount 25 20 15 10 Y On Backorder Over-Stocked 0 g 18 Y Y Y Y Y Supplier Cycle Warehouse Nashbar Performance Bikes Plus Backcountry LBS GR2855 Part Code Product Description AC 1945 Alloy CR Belt GR 1751 CNC brakes 2009 Brake WH2778 CNC V18 Super Grip Wheels WH 1200 CNC V1 Ultra Wheels CO2904 Custom Bar Plugs GR 1215 Dura Ace Chain AC 150B Fairing M5 Bolls WH256 Forte LCR Tyres WH 1573 Forle Light Tubes AC2964 Generic FD Adapter CO1469 Generic Foam Tape Tape FR 1904 Headset FR 1235 J&L Fork FR2360 J&L Plug FR 1080 J&L Top Cap & Screw CO1649 KCNC Scandum Seatpost FR1081 Mr. Ride Seat Clamp CO1099 Nashbor Carbon Bar FR1323 Nasher Carbon Frame Nashber Cassette GR 1966 Naaber FD GR 1432 Naster Levers GR 1770 Nashbar RD CO2357 RockBros CrMo Saddle AC 1914 RockBros SR Pedas C02143 Spacers CO168 Specialised Foam Tape Tape WH2912 Titanium QR Skewers AC2733 Titanium GR 1408 Ullegra DB GR 1150 Ultegra Cables GR 1836 Ultegra Coselle GR2009 Ultegra Cranks GR 1172 UllegaFD GR21589 Ultegra Lovers GR 1322 Ultegra RD C02202 Uno 7000 Stem WH2373 Veloplugs Rim se WH1100 XR-270 Bitex Wheel AC 1919 Bikes Plus Deluxe Pedale AC2174 Rockgrip Alped Huez Pecals C01104 Rockglider Carbon Bar CO1650 Diamond Seatpost C00203 BACycle 5000 Stem CO2381 Diamond Ultra Saddle FR 1082 Finn Cap & Screw FR 1088 RockBros Seat Clamp FR 1240 Firstlite Fork FR1324 RockBros Carbon Frame Category Accessories and tuning Groupset Wheels Wheels Contact Points Groupset Accessories and during Whools Wheele Accessories and tuning Contact Pointe Frameset Framesel Frameset Frameeel Contact Paints Frameset Contact Paints Frameset Groupset Grousset Groupset Groupset Contact Points Accessories and tuning Contact Points Contact Paints Wheels Accessories and tuning Groupel Grousset Groupeet Groupset Groupeet Groupset Groupeet Contact Paints Wheels Wheels Accessories and tuning Accessories and turing Contact Points Contact Paints Contact Points Contact Paints Frameset Supplier Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycia Warehouse Cycle Warehouse Nashhar Cycle Warehouse Performance Performance Cycle Warehouse Cycle Warehouse Bikes Plus Bikes Plus Bikes Plus Cycle Warehouse Cycle Warehouse Cycle Warehouse Nachbar Nachbar Nashbar Nashbar Nashbar Nashbar Cycle Warehouse Nashher Bikes Plus Cycia Warehouse Cycle Warehouse Cycia Warehouse Backcountry Nashher Nashbar Nashhar Nashbat Nashbar Beckoountry Cycle Warehouse LES Cycle Warehouse Bikes Plus Cycle Warehouse Nashher Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycle Warehouse Bikes Plus Nashbar Prie/Une Weight (9 $2.33 568.65 84 $166.31 $ 778.1 $294.19 588 SO.B6 22 S24.32 2176 S0.48 0.8 S18.23 229.9 S60.89 56.1 $2.50 23.6 S0.18 0.6 S28.62 60.6 $174.21 344 . S11.7 6 59.71 78 SB3.02 180.1 $14.40 8.5 S88.93 189 $123.38 957 S40.39 1972 $95.16 863 $121.18 199.8 538.87 2028 S17.22 210.6 73.25 1316 $23.00 2.4 $0.15 as 015 S5.11 28.8 $9.33 8.7 S13.45 51 S24.84 49.1 $19.05 178 $11523.59 6849 523.89 91.7 S77.45 216.8 S10.57 2018 $15.58 130.9 54.85 4 $11144 595 527 25 1316 S25.24 110.3 562.75 189 S71.27 100.1 $18.53 1309 S19.20 210.6 $3.98 78 S14.11 9.5 $183.014 344 $143.52 957 Y Y Y Y Sim Baits 8 19 31 42 21 37 28 9 6 35 23 11 16 32 42 38 G 13 39 19 29 25 18 41 9 15 43 21 20 23 3 2 31 Y Y Y Y Y Y Frames Y Y Frameset Frameset Information Fixed Costs March Sales Labour Costs BOM CVP Analysis Profit Volume Forecast Inventory Category Prica/Unit Weight (g) In Stock Reorder Qty K L M On Backorder Reorder Y Y 6 35 23 11 18 32 Y Y Y 523.25 $22.90 $0.15 S5.11 59.33 $13.45 $24.84 S49.05 $153.59 $23.69 S77.45 S40.67 $15.58 S4.95 $111.44 $27 25 Y Y Y Y 50524 Y Y Accessories and turning Contact Points Contact Points Wheels Accessories and tuning Groupset Groupeet Groupset Groupset Groupset Groupset Groupset Contact Points Wheels Wheels Accessories and tuning Accessories and turing Contact Points Contact Points Contact Paints Contact Points Frameset Frameset Frameset Frameset Frameset Groupset Groupeet Groupset Groupset Groupset Groupset Groupset Groupset Groupset Groupset Groupset Groupeet Groupset Wheels Wheels Wheels Part Code Product Description wwwwwwwwww 28 AC 1914 RockBros S60 Pedals 29 CO2143 Spacers 30 CO1688 Specialised Foam Tape Tape 31 WH2942 Titanium QR Skewers el 32 AC2733 Titanium Stem Bolts 33 GR 1406 Ultegra BB 34 GR 1550 Ultegra Cables 35 GR 1836 Ultegra Cassette 36 GR 2009 Ultegra Cranks 37 GR 1172 Ultegra FD 38 GR2589 Ultegra Levers 39 GR 1322 Ultegra RD 40 C02202 Uno 7050 Stem 41 WH2373 Veloplugs Rim tape 42 WH1100 XR-270 Bitnx Wheel 43 AC 1919 Bikee Plus Delucce Pedals 44 AC2174 Rockgrip Alpe d Huez Pedals 45 c01104 Rockglider Carbon Bar 46 CO1650 Diamond Seatpost 47 C02203 BBCycle 5000 Stern 4B CO2361 Diamond Ultra Saddle 49 FR 1082 Finn Cap & Screw 50 FR 1086 RockBros Seal Clamp 51 FR 1240 Firstlite Fork 52 FR1324 RockBros Carbon Frame 53 FR 1937 Giga Headset 54 GR 1177 Firma FD 55 GR1218 Magneto Chain 56 GR1325 Firma RD 57 GR 1407 Firma BB 58 GR1435 Rockgrip Levers 59 GR 1554 Firma Cables 60 GR 1753 Firma Brakes 61 GR1754 Rockgrip RD 62 GR 1755 Firma Cassette 63 GR 1756 Rockgrip FD 64 GR1757 Firma Cranks 65 GR 1758 Firma Levers 66 GR 1759 Rockgrip Cassette 67 GR1760 Ultrazon 5 Wheel 68 GR1761 Firma Inner Tubes 69 GR1782 Ultra Rock Tyres 70 71 72 73 74 75 76 77 78 79 80 Information Supplier wwwwwwwwww Nashbar Bikes Plus Cycle Warehouse Cycle Warehouse Cycle Warehouse Backcountry Nashber Nashbar Nashbar Nashber Nashbar Backcountry Cycle Warehouse LBS Cyale Warehouse Bikes Plus Cycle Warehouse Nashbar Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycle Warehouse Cycle Warehouse Bikes Plus Nashber Bikes Plus Nashbar Nashbar Backcountry Backcountry Nashbar Nashbar Cycle Warehouse Nashber Nashber Nashbar Nashber Nashber Nashbar Cycle Warehouse Performance LRS M 131.6 2.4 05 26.8 8.7 51 49.1 178 604.9 91.7 216.8 204.8 130.9 4 995 131.6 110.3 189 180.1 130.9 210.6 7.8 9.5 344 957 60.6 91.7 217.6 204.8 51 198,8 49.1 84 202.9 176 88.3 49 216.8 1972 995 56.1 2289 Y Y 38 6 13 39 19 39 25 19 41 9 15 43 21 20 23 3 2 31 30 20 34 2 37 6 14 37 7 14 18 21 18 37 5 40 18 $42.75 S7127 S18.53 S19 20 $3.98 S14.11 $183,04 $443.52 $12.18 $26.90 $21.64 539.84 S16.08 $119.99 58.91 589 29 $46.40 $15.04 S2820 $16237 S73.33 $34.45 $143.55 55124 $32.86 Y Y Y Y Y Y Y Y Y Y Y Y Y Y % Fixed Costs March Sales Labour Costs CVP Analysis Profit Volume Forecast Inventory +
