Question
B1: Go to the Inventory Sheet. This sheet contains a list of all the items the company keep in stock to build the bikes. Each
B1: Go to the Inventory Sheet. This sheet contains a list of all the items the company keep in stock to build the bikes. Each part is identified by a unique part code. Convert the Range A3:J43 to a Table. Name the table Inventory
B2: Go to the Bill of Materials (BoM) Worksheet. This sheet contains a list of which parts (and how many) are needed to make two different types of bicycles, the ZF300 (no longer in production) and the X-800, the model currently produced. The table is incomplete as it doesnt have the rest of the information about each part. Use appropriate formulas to lookup the corresponding Category, Product Description, Supplier and Unit Price for each of the Part Codes from the Inventory table. (Note: Both the BoM and the Inventory are frequently re-sorted, so the formulas must work regardless of sort order.)
B3: Still in the BoM sheet, click into K3. Note the dropdown that allows you to select a product. In K4 create a formula to calculate the total price of parts of category Groupset for that product. Copy the formula down to K8 to get a breakdown of Product Costs by Category.
B4: In K9 calculate the total cost to make one unit of the selected product.
PLEASE PROVIDE AN EXPLAINATION during each step
INVENTORY
Inventory | |||||||||
Part Code | Category | Product Description | Supplier | Unit | Price/Unit | Weight (g) | Weight (lbs) | In Stock | Reorder Qty |
FR1323 | Frameset | Nashbar Carbon Frame | Nashbar | item | $499.20 | 1166 | 47 | ||
FR1235 | Frameset | J&L Fork | Bikes Plus | item | $202.40 | 444 | 28 | ||
FR2360 | Frameset | J&L Plug | Bikes Plus | item | $10.40 | 7.3 | 29 | ||
FR1934 | Frameset | Headset | Bikes Plus | item | $0.00 | 69 | 38 | ||
FR1080 | Frameset | J&L Top Cap & Screw | Cycle Warehouse | item | $4.90 | 7.5 | 31 | ||
FR1081 | Frameset | Mr. Ride Seat Clamp | Cycle Warehouse | item | $14.88 | 8.5 | 39 | ||
WH1100 | Wheels | XR-270/Bitex Wheel | Cycle Warehouse | item | $152.48 | 630 | 31 | ||
WH2373 | Wheels | Veloplugs Rim tape | LBS | item | $4.86 | 3.9 | 35 | ||
WH2942 | Wheels | Titanium QR Skewers | Cycle Warehouse | item | $5.15 | 22.3 | 17 | ||
WH2856 | Wheels | Forte LCR Tyres | Performance | item | $20.93 | 171.1 | 28 | ||
WH1573 | Wheels | Forte Light Tubes | Performance | item | $6.16 | 58 | 20 | ||
CO2357 | Contact Points | RockBros CrMo Saddle | Cycle Warehouse | item | $20.47 | 214.2 | 40 | ||
CO1649 | Contact Points | KCNC Scandium Seatpost | Cycle Warehouse | item | $85.44 | 159.6 | 34 | ||
CO2143 | Contact Points | Spacers | Bikes Plus | item | $0.00 | 3.3 | 41 | ||
CO2202 | Contact Points | Uno 7050 Stem | Cycle Warehouse | item | $22.80 | 106.7 | 33 | ||
CO1099 | Contact Points | Nashbar Carbon Bar | Nashbar | item | $83.52 | 231 | 48 | ||
CO1469 | Contact Points | Generic Foam Tape Tape | Cycle Warehouse | cm | $0.22 | 0.4 | 6 | ||
CO2904 | Contact Points | Custom Bar Plugs | Cycle Warehouse | item | $0.99 | 2 | 37 | ||
GR2589 | Groupset | Ultegra Levers | Nashbar | item | $99.51 | 178.5 | 7 | ||
GR1322 | Groupset | Ultegra RD | Backcountry | item | $52.00 | 185.3 | 37 | ||
GR1172 | Groupset | Ultegra FD | Nashbar | item | $31.61 | 96.1 | 48 | ||
GR1836 | Groupset | Ultegra Cassette | Nashbar | item | $42.14 | 220.5 | 23 | ||
GR1215 | Groupset | Dura Ace Chain | Nashbar | item | $35.84 | 228.7 | 42 | ||
GR1751 | Groupset | CNC brakes 200g Brakes | Cycle Warehouse | item | $96.05 | 87 | 17 | ||
GR2009 | Groupset | Ultegra Cranks | Nashbar | item | $143.10 | 646.7 | 33 | ||
GR1406 | Groupset | Ultegra BB | Backcountry | item | $19.55 | 61.7 | 1 | ||
GR1550 | Groupset | Ultegra Cables | Nashbar | item | $0.00 | 47.7 | 23 | ||
AC1508 | Accessories and tuning | Fairing M5 Bolts | Cycle Warehouse | item | $0.41 | 1.1 | 27 | ||
AC1945 | Accessories and tuning | Alloy CR Bolts | Cycle Warehouse | item | $2.23 | 0.3 | 10 | ||
AC2733 | Accessories and tuning | Titanium Stem Bolts | Cycle Warehouse | item | $10.20 | 8.1 | 23 | ||
AC2964 | Accessories and tuning | Generic FD Adapter | Cycle Warehouse | item | $2.58 | 17 | 20 | ||
AC1914 | Accessories and tuning | Nashbar Alpe d Huez Pedals | Nashbar | item | $15.12 | 101.3 | 3 | ||
AC2716 | Accessories and tuning | Grease | Cycle Warehouse | grams | $0.07 | 0.2 | 20 | ||
GR1432 | Groupset | Nashbar Levers | Nashbar | item | $121.20 | 201.9 | 37 | ||
GR1770 | Groupset | Nashbar RD | Nashbar | item | $51.15 | 216.5 | 41 | ||
GR1966 | Groupset | Nashbar FD | Nashbar | item | $32.00 | 98.8 | 31 | ||
GR2655 | Groupset | Nashbar Cassette | Nashbar | item | $44.10 | 199.3 | 40 | ||
AC2170 | Accessories and tuning | RockBros S60 Pedals | Cycle Warehouse | item | $30.52 | 100.1 | 27 | ||
WH1260 | Wheels | CNC V18 Ultra Wheels | Cycle Warehouse | item | $188.80 | 644 | 29 | ||
WH2778 | Wheels | CNC V16 Super Slim Wheels | Cycle Warehouse | item | $183.60 | 680 | 20 | ||
CO1688 | Contact Points | Specialised Foam Tape Tape | Cycle Warehouse | cm | $0.22 | 0.5 | 11 |
Minimum Slock Level | 10 |
Re-Order Quantities | |
Item | 50 |
cm | 5000 |
grams | 2500 |
BILL OF MATERIALS
|
Cost Brakdown for: | X-800 |
Groupset | |
Accessories and tuning | |
Contact Points | |
Frameset | |
Wheels | |
Total Cost per Unit |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started