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