Hi tutors, I am having trouble knowing what to do in excel for these PVF questions. Any help is appreciated.

If it is easier to access through google drive I can do that.

B m G Profit Volume Forecast 3 WB500 Sales Mix 60% Tax Rates WB660 Sales Mix 40% State Tax 10% 5 Unit Increments 1,000 Federal Tax 5% 6 Sales Volume in Units 1000 8 Total $ Sales Value 9 10 Variable Costs 11 Direct Material 12 Direct Labor 13 Other Vary-with-Unit Costs 14 Sales Commissions 15 Total Variable Costs $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 16 17 $ Profit Before Tax (Contribution Margin - Fixed Costs) BT 19 Income Tax 20 State Tax 21 Federal Tax 22 Total Income Tax $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 23 24 $ Profit after Tax 25 26 % Profit (of Sales) 27 Break-Even Analysis Line Chart Data 29 Fixed Costs 30 Total Costs 31 Total $ Sales Value 322 3 4 5 6 7 8 9 Product Information Unit Sales Price Units Manufactured and Sold Sales Mix (% oftotal units sold) Vary with Unit Costs Direct Material Direct Labor Other Vary-with-Unit Costs Total Vary with Unit Costs Break Even Analysis Contribution Margin Per Unit Break Even Point in Units/Product Break Even in Sales Dollars 957 638 $301,382.88 $309,355.97 $179,208.00 Vary-with-Revenue Costs Sales Commissions 3.5% Weighted Average Contribution Margin $112.38 Break Even Units (weighted) 1595 Break Even Variable Costs Direct Material Direct Labor Other Vary-with-Unit Costs Sales Commissions Total Variable Costs $277,517.82 $66,973.97 $27,392.35 $21,375.86 $393,260.01 Break Even Contribution Margin $217,478.84 Break Even Prot Before Tax $1.21 wmummthH H 0 Fixed Costs Fixed Cost Items Quarter 1 Quarter 2 Quarter 3 Quarter 4 Quarterly Average 96 of Total Rent $ 30,400.00 5 30,400.00 5 3 1,464.00 $ 31,464.00 5 30,932.00 69% Utilities 5 3,730.00 5 3,730.00 5 3,730.00 $ 3,730.00 S 3,730.00 8% Ad min Costs S 6,990.00 $ 7,200.00 5 7,200.00 S 7,130.00 S 7,130.00 16% Insurance $ 3,120.00 $ 2,900.00 5 3,120.00 $ 2,900.00 $ 3,010.00 7% Quarterly Totals 5 44,240.00 44,230.00 45,514.00 $ 45,224.00 Total Fixed Costs Average Monthly Rent: Rent 96 Increase: Highest Quarterly Admin Costs: 179,208.00 7,200.00 10,310.67 3.5% 30398965036 B D E IN W Product Part Code Quantity Product Decsription Price Each Total Price WB660 PX1004683 DESCRIPTION $13.19 $13.19 WB660 PX242979 4 DESCRIPTION $1.83 $7.32 WB660 PX373195 DESCRIPTION $2.49 $2.49 WB660 PX374089 DESCRIPTION $53.11 $53.11 WB660 PX374561 DESCRIPTION $2.74 $2.74 9 WB660 PX577199 8 DESCRIPTION $1.99 $15.92 10 WB660 PX723206 DESCRIPTION $1.99 $7.96 11 WB660 PX723541 DESCRIPTION $1.66 $1.66 12 WB500 PX723542 DESCRIPTION $1.99 $1.99 13 WB660 PX723557 DESCRIPTION $2.49 $9.96 14 WB500 PX723566 DESCRIPTION $2.29 $2.29 15 WB660 PX723593 DESCRIPTION $1.91 $15.28 16 WB500 PX723594 IN DESCRIPTION $2.39 $4.78 17 WB500 PX723596 DESCRIPTION $2.49 $9.96 18 WB660 PX723742 DESCRIPTION $1.74 $1.74 19 WB500 PX723743 DESCRIPTION $2.29 $4.58 20 WB660 PX723745 DESCRIPTION $3.11 $12.44 21 WB500 PX723820 DESCRIPTION $2.49 $9.96 22 WB500 PX725125 DESCRIPTION $2.19 $8.76 23 WB660 PX792653 8 DESCRIPTION $2.08 $16.64 24 WB500 PX796490 DESCRIPTION $2.49 $9.96 25 WB660 PX797516 DESCRIPTION $1.8 $3.66 26 WB660 PX807530 DESCRIPTION $2.99 $2.99 27 WB660 PX807531 DESCRIPTION $1.66 $3.32 28 WB660 PX807532 DESCRIPTION $1.74 $3.48 29 WB500 PX807539 DESCRIPTION $18.69 $37.38 30 WB660 PX807540 DESCRIPTION $3.33 $6.66 NNHNH HOO NN NN HN 31 WB660 PX809486 DESCRIPTION $2.08 $16.64 32 WB660 PX820261 DESCRIPTION $12.99 $12.99 33 WB500 PX820262 DESCRIPTION $13.19 $13.19 34 WB500 PX820263 DESCRIPTION $2.49 $4.98 5 WB500 X820266 DESCRIPTION $10.99 $10.99 36 WB660 PX820728 DESCRIPTION $2.19 $4.38 37 WB500 PX820728 DESCRIPTION $2.19 $4.38 3 WB500 PX820834 DESCRIPTION $1.99 $1.99 39 WB660 PX823385 DESCRIPTION $1.66 $6.64 40 WB660 PX823386 8 DESCRIPTION $1.74 $13.92 41 WB660 PX823978 DESCRIPTION $26.24 $26.24 42 WB500 PX823978 DESCRIPTION $26.24 $26.24 43 WB500 PX860085 2 DESCRIPTION $2.19 $4.38 44__ .____' _____ _'_______ __, _. __ E1 Click In B? and you will see you can select the minimum sales volume you want to model. In C? create a formula to add the Increment In BS to the volume in B7. Use appropriate cell referencing so that the formula can be dragged across to G? and the total will increase by the value shown in B5 (and change ifthe value in BS changes). Drag the formula across to G7. E2 E3 In row 8 calculate the total sales revenue for each of the different Sales Volumes using the sales mix options selected in BB and B4 (do not use the sales mix from the CVP sheet.) Complete the variable costs table for the different sales volumes (311:614) using the sales mix options selected in 33 and B4. E4 In row 17 calculate Prot Before Tax (Contribution Margin - Fixed Costs) for each sales volume (this will return a negative value where the unit volume is less than the break-even). E5 In 320 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 0. (Do NOTjust 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. E6 In row 24 calculate the profit after factoring in the income tax (this may return a negative value where the unit volume is less than or close to the break-even). E7 In row 25 work out the profit for each sales volume as a percentage of sales. E3 Complete the Break-Even Analysis Line Chart Data in B29 to 631 for each of the Unit volumes. (Note Total Costs includes Variable Costs, Fixed Costs and Income Tax)