Calculate the state tax incurred for each sales volume using the rate at the top of the sheet. If the profit was zero or below, the calculation should return O. (Do NOT just type 0 as the volumes can change.) Apply appropriate cell referencing so that the formula can be dragged down and then across to complete the tax table.

 

B E F Profit Volume Forecast 2 3 WB500 Sales Mix 4 WB660 Sales Mix 5 Unit Increments 40% Tax Rates 60% State Tax 10% 1,000 Federal Tax 5% 6 7 Sales Volume in Units 8 Total $ Sales Value 1000 2,000 3,000 4,000 5,000 6,000 $403,000.00 $806,000.00 $1,209,000.00 $1,612,000.00 $2,015,000.00 $2,418,000.00 10 Variable Costs 11 Direct Material 12 Direct Labor 13 Other Vary-with-Unit Costs 14 Sales Commissions 15 Total Variable Costs $218,318.00 $52,320.00 $17,800.00 $14,105.00 $302,543.00 $436,636.00 $654,954.00 $873,272.00 $209,280.00 $1,091,590.00 $1,309,908.00 $104,640.00 $156,960.00 $261,600.00 $313,920.00 $35,600.00 $28,210.00 $605,086.00 $53,400.00 $71,200.00 $89,000.00 $70,525.00 $106,800.00 $42,315.00 $907,629.00 $56,420.00 $1,210,172.00 $84,630.00 $1,815,258.00 $1,512,715.00 16 17 S Profit Before Tax (Contribution Margin - Fixed Costs) -$83,610.60 $16,846.40 $117,303.40 $217,760.40 $318,217.40 $418,674.40 18 19 Income Tax 20 State Tax 21 Federal Tax 22 Total Income Tax 23 24 $ Profit after Tax $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 25 26 % Profit (of Sales) 27 28 Break-Even Analysis Line Chart Data 29 Fixed Costs 30 Total Costs 31 Total $ Sales Value 32