ACCG200 Excel Assignment

BikePro Ltd want you to perform a Cost Volume Profit Analysis for their High-End Racing Bike product, the X-800. You will need to calculate the labour costs, material costs, fixed costs and variable costs and then work out the break-even volume. You have also been asked to show a forecast for different sales volumes and produce a break-even analysis line chart.

HAVING TROUBLE, PLS HELP!!

Product Name Unit Sales Price Total Units Sold Variable Costs per Unit Variable Manufacturing Costs Direct Material Direct Labour Factory Overhead Other Variable Manufacturing Costs Total Variable Manufacturing Costs Break-Even Analysis Contribution Margin per Unit Break Even Volume in Units Break Even Sales Value X-800 5,100.00 350 1,159.94 145.3350 $741,208.47 1,875.53 1,533.03 ei Fixed Cost Items Rent Utilities Admin Costs Loan Repayments Break Even Variable Costs Direct Material 80.00 $272,580.14 $222,802.78 $51,000.00 $14,300.00 $22,500.00 $68,280.00 $12,500.00 $168,580.00 3,608.56 Variable Selling and Admin Costs Sales Commissions Variable Admin Costs Total Variable Selling and Admin Costs Variable Factory Overhead 5.0% 1.5% 6.5% Sales Commissions Variable Admin Costs Total Variable Costs Total Fixed Costs $572,628.47 Income Tax State Tax Federal Tax Contribution Margin Profit Before Income Tax $405,979.31 $237,399.31 10.0% 5.0%