Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

Bill of Materials
Product Part Code Quantity Category Product Decsription Supplier Unit Price Total Price
ZF300 GR1751 2
X-800 AC1914 2
ZF300 CO2357 1
X-800 FR1235 1
X-800 CO1469 180
ZF300 GR2009 1
X-800 WH1100 2
X-800 CO1099 1
ZF300 CO1469 180
ZF300 FR2360 1
X-800 GR1770 1
X-800 WH2942 2
ZF300 FR1934 1
ZF300 GR1322 1
ZF300 FR1235 1
ZF300 GR1172 1
ZF300 CO2904 2
ZF300 FR1080 1
ZF300 GR1215 1
X-800 WH2373 2
X-800 CO2904 2
X-800 WH1260 2
X-800 WH1573 2
ZF300 CO1099 1
X-800 FR1934 1
ZF300 FR1323 1
X-800 CO2143 5
X-800 AC2716 220
X-800 FR1323 1
ZF300 GR1836 1
ZF300 WH2778 2
ZF300 AC1508 4
ZF300 GR2589 2
X-800 CO2357 1
X-800 AC1945 2
X-800 GR1550 4
X-800 GR1432 2
ZF300 AC2964 1
X-800 AC2733 1
X-800 CO1649 1
X-800 GR1751 2
X-800 FR2360 1
ZF300 WH1573 2
ZF300 AC2716 200
ZF300 AC1945 2
X-800 AC1508 4
X-800 CO2202 1
X-800 WH2856 2
ZF300 FR1081 1
X-800 GR2009 1
ZF300 WH1100 2
ZF300 GR1406 1
X-800 FR1080 1
ZF300 WH2856 2
X-800 FR1081 1
ZF300 GR1550 4
X-800 GR1836 1
ZF300 CO2143 3
ZF300 AC1914 2
ZF300 WH2373 2
ZF300 CO1649 1
X-800 AC2964 1
ZF300 WH2942 2
X-800 GR1406 1
X-800 GR1966 1
X-800 GR1215 1
ZF300 AC2733 1
ZF300 CO2202 1

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Financial Management Of Health Care Organizations

Authors: William N. Zelman, Michael J. McCue, Noah D. Glick, Marci S. Thomas

5th Edition

1119553849, 9781119553847

More Books

Students also viewed these Finance questions