In B14 calculate the Total Fixed Costs.

In E5, using the information in the BoM, calculate the Direct Material Cost per unit specified in B3. (Note Do NOT use the value in BoM!K9 as this can be for another product, and ensure the calculation works regardless of sort order in the BoM.)

In E6 use the information in the Labour Costs sheet to calculate the Direct Labour Cost per unit. Note if the values in the changes this figure should update.

In E9 calculate the Variable Manufacturing Cost per unit.

In E14 calculate the Variable Selling & Admin Cost per unit (as a %).

In H4 calculate the Contribution Margin per Unit (this is the Unit Sales Price less the Variable Manufacturing Cost per unit and the Variable Selling & Admin Cost per unit).

In H5 calculate your break-even volume (in units) rounded up to 0 decimal places. (This answer is very important so double check it, multiply it by the variable cost figures and add the fixed costs back in and unrounded it should give you 0).

In H6 multiply the Break-Even Volume in Units by the Unit Sales Price to calculate the Break-Even Sales Value.

In H9 to H14 calculate the Break-Even Variable Costs using the calculated figures in H5 or H6 (where appropriate). Note, each answer is worth mark which will only be awarded if the answer is correct

In H15 calculate the Total Break-Even Variable Costs. (Answer must be correct to get mark.)

In H17 calculate the Break-Even Contribution Margin.

In H18 calculate the Profit Before Tax. (If we hadn't rounded this should be 0).

PLEASE PROVIDE AN EXPLAINATION during each step

Bill of Materials (BoM)
Product	Part Code	Quantity	Category	Product Decsription	Supplier	Unit Price	Total Price
ZF300	GR1751	2	Groupset	CNC brakes 200g Brakes	Cycle Warehouse	$96.05	$192.10
X-800	AC1914	2	Accessories and tuning	Nashbar Alpe d Huez Pedals	Nashbar	$15.12	$30.24
ZF300	CO2357	1	Contact Points	RockBros CrMo Saddle	Cycle Warehouse	$20.47	$20.47
X-800	FR1235	1	Frameset	J&L Fork	Bikes Plus	$202.40	$202.40
X-800	CO1469	180	Contact Points	Generic Foam Tape Tape	Cycle Warehouse	$0.22	$39.60
ZF300	GR2009	1	Groupset	Ultegra Cranks	Nashbar	$143.10	$143.10
X-800	WH1100	2	Wheels	XR-270/Bitex Wheel	Cycle Warehouse	$152.48	$304.96
X-800	CO1099	1	Contact Points	Nashbar Carbon Bar	Nashbar	$83.52	$83.52
ZF300	CO1469	180	Contact Points	Generic Foam Tape Tape	Cycle Warehouse	$0.22	$39.60
ZF300	FR2360	1	Frameset	J&L Plug	Bikes Plus	$10.40	$10.40
X-800	GR1770	1	Groupset	Nashbar RD	Nashbar	$51.15	$51.15
X-800	WH2942	2	Wheels	Titanium QR Skewers	Cycle Warehouse	$5.15	$10.30
ZF300	FR1934	1	Frameset	Headset	Bikes Plus	$0.00	$0.00
ZF300	GR1322	1	Groupset	Ultegra RD	Backcountry	$52.00	$52.00
ZF300	FR1235	1	Frameset	J&L Fork	Bikes Plus	$202.40	$202.40
ZF300	GR1172	1	Groupset	Ultegra FD	Nashbar	$31.61	$31.61
ZF300	CO2904	2	Contact Points	Custom Bar Plugs	Cycle Warehouse	$0.99	$1.98
ZF300	FR1080	1	Frameset	J&L Top Cap & Screw	Cycle Warehouse	$4.90	$4.90
ZF300	GR1215	1	Groupset	Dura Ace Chain	Nashbar	$35.84	$35.84
X-800	WH2373	2	Wheels	Veloplugs Rim tape	LBS	$4.86	$9.72
X-800	CO2904	2	Contact Points	Custom Bar Plugs	Cycle Warehouse	$0.99	$1.98
X-800	WH1260	2	Wheels	CNC V18 Ultra Wheels	Cycle Warehouse	$188.80	$377.60
X-800	WH1573	2	Wheels	Forte Light Tubes	Performance	$6.16	$12.32
ZF300	CO1099	1	Contact Points	Nashbar Carbon Bar	Nashbar	$83.52	$83.52
X-800	FR1934	1	Frameset	Headset	Bikes Plus	$0.00	$0.00
ZF300	FR1323	1	Frameset	Nashbar Carbon Frame	Nashbar	$499.20	$499.20
X-800	CO2143	5	Contact Points	Spacers	Bikes Plus	$0.00	$0.00
X-800	AC2716	220	Accessories and tuning	Grease	Cycle Warehouse	$0.07	$15.40
X-800	FR1323	1	Frameset	Nashbar Carbon Frame	Nashbar	$499.20	$499.20
ZF300	GR1836	1	Groupset	Ultegra Cassette	Nashbar	$42.14	$42.14
ZF300	WH2778	2	Wheels	CNC V16 Super Slim Wheels	Cycle Warehouse	$183.60	$367.20
ZF300	AC1508	4	Accessories and tuning	Fairing M5 Bolts	Cycle Warehouse	$0.41	$1.64
ZF300	GR2589	2	Groupset	Ultegra Levers	Nashbar	$99.51	$199.02
X-800	CO2357	1	Contact Points	RockBros CrMo Saddle	Cycle Warehouse	$20.47	$20.47
X-800	AC1945	2	Accessories and tuning	Alloy CR Bolts	Cycle Warehouse	$2.23	$4.46
X-800	GR1550	4	Groupset	Ultegra Cables	Nashbar	$0.00	$0.00
X-800	GR1432	2	Groupset	Nashbar Levers	Nashbar	$121.20	$242.40
ZF300	AC2964	1	Accessories and tuning	Generic FD Adapter	Cycle Warehouse	$2.58	$2.58
X-800	AC2733	1	Accessories and tuning	Titanium Stem Bolts	Cycle Warehouse	$10.20	$10.20
X-800	CO1649	1	Contact Points	KCNC Scandium Seatpost	Cycle Warehouse	$85.44	$85.44
X-800	GR1751	2	Groupset	CNC brakes 200g Brakes	Cycle Warehouse	$96.05	$192.10
X-800	FR2360	1	Frameset	J&L Plug	Bikes Plus	$10.40	$10.40
ZF300	WH1573	2	Wheels	Forte Light Tubes	Performance	$6.16	$12.32
ZF300	AC2716	200	Accessories and tuning	Grease	Cycle Warehouse	$0.07	$14.00
ZF300	AC1945	2	Accessories and tuning	Alloy CR Bolts	Cycle Warehouse	$2.23	$4.46
X-800	AC1508	4	Accessories and tuning	Fairing M5 Bolts	Cycle Warehouse	$0.41	$1.64
X-800	CO2202	1	Contact Points	Uno 7050 Stem	Cycle Warehouse	$22.80	$22.80
X-800	WH2856	2	Wheels	Forte LCR Tyres	Performance	$20.93	$41.86
ZF300	FR1081	1	Frameset	Mr. Ride Seat Clamp	Cycle Warehouse	$14.88	$14.88
X-800	GR2009	1	Groupset	Ultegra Cranks	Nashbar	$143.10	$143.10
ZF300	WH1100	2	Wheels	XR-270/Bitex Wheel	Cycle Warehouse	$152.48	$304.96
ZF300	GR1406	1	Groupset	Ultegra BB	Backcountry	$19.55	$19.55
X-800	FR1080	1	Frameset	J&L Top Cap & Screw	Cycle Warehouse	$4.90	$4.90
ZF300	WH2856	2	Wheels	Forte LCR Tyres	Performance	$20.93	$41.86
X-800	FR1081	1	Frameset	Mr. Ride Seat Clamp	Cycle Warehouse	$14.88	$14.88
ZF300	GR1550	4	Groupset	Ultegra Cables	Nashbar	$0.00	$0.00
X-800	GR1836	1	Groupset	Ultegra Cassette	Nashbar	$42.14	$42.14
ZF300	CO2143	3	Contact Points	Spacers	Bikes Plus	$0.00	$0.00
ZF300	AC1914	2	Accessories and tuning	Nashbar Alpe d Huez Pedals	Nashbar	$15.12	$30.24
ZF300	WH2373	2	Wheels	Veloplugs Rim tape	LBS	$4.86	$9.72
ZF300	CO1649	1	Contact Points	KCNC Scandium Seatpost	Cycle Warehouse	$85.44	$85.44
X-800	AC2964	1	Accessories and tuning	Generic FD Adapter	Cycle Warehouse	$2.58	$2.58
ZF300	WH2942	2	Wheels	Titanium QR Skewers	Cycle Warehouse	$5.15	$10.30
X-800	GR1406	1	Groupset	Ultegra BB	Backcountry	$19.55	$19.55
X-800	GR1966	1	Groupset	Nashbar FD	Nashbar	$32.00	$32.00
X-800	GR1215	1	Groupset	Dura Ace Chain	Nashbar	$35.84	$35.84
ZF300	AC2733	1	Accessories and tuning	Titanium Stem Bolts	Cycle Warehouse	$10.20	$10.20
ZF300	CO2202	1	Contact Points	Uno 7050 Stem	Cycle Warehouse	$22.80	$22.80

Cost Volume Profit Analysis
Product Name	X-800		Variable Costs per Unit			Break-Even Analysis
Unit Sales Price	$ 5,500.00		Variable Manufacturing Costs			Contribution Margin per Unit
Total Units Sold	350		Direct Material			Break Even Volume in Units
			Direct Labour			Break Even Sales Value
			Factory Overhead	$ 120.00
Fixed Cost Items			Other Variable Manufacturing Costs	$ 80.00		Break Even Variable Costs
Rent	$ 50,000.00		Total Variable Manufacturing Costs			Direct Material
Utilities	$ 14,300.00					Direct Labour
Admin Costs	$ 22,800.00		Variable Selling and Admin Costs			Variable Factory Overhead
Loan Repayments	$ 68,280.00		Sales Commissions	5.0%		Other Variable Manufacturing Costs
Insurance	$ 12,500.00		Variable Admin Costs	1.5%		Sales Commissions
Total Fixed Costs			Total Variable Selling and Admin Costs			Variable Admin Costs
						Total Variable Costs
			Income Tax
			State Tax	10.0%		Contribution Margin
			Federal Tax	5.0%		Profit Before Income Tax