Hi, I am having trouble understanding these questions and what excel formulas to use. Any help with these questions would be appreciated.
A B D E G H Profit Volume Forecast 3 WB500 Sales Mix 60% Tax Rates State Tax 10% WB660 Sales Mix 40% 1,000 Federal Tax 5% Unit Increments 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) 18 19 Income Tax 20 State Tax 21 Federal Tax $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 22 Total Income Tax 23 24 $ Profit after Tax 25 26 % Profit (of Sales) 27 28 Break-Even Analysis Line Chart Data 29 Fixed Costs 30 Total Costs 31 Total $ Sales Value 32 33Section E The following are to be compieted in the Profit Volume Forecast Worksheet In this sheet we want to be able to forecast what our profits will look like with different sales volumes and different sales mixes. Click on the drop down in B3 and change it to 50%, the value in B4 adjusts accordingly. Now change it back to 60%. Click in B5 and you will see you can also select the increment [the amount we go up in) we want to model, for example we may want to see profit for 500, 1000, 1500 units or we may want to see prot for 1000, 2000, 3000 units. 19 E1 Click in B7 and you will see you can select the minimum sales volume you want to model. In C7 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 G7 and the total will increase by the value shown in B5 (and change if the value in B5 changes). Drag the formula across to G7. E2 In row 8 calculate the total sales revenue for each of the different Sales Volumes using the sales mix options selected in B3 and B4 (do not use the sales mix from the CVP sheet.) E3 Complete the variable costs table for the different sales volumes (B11:Gl4) using the sales mix options selected in B3 and B4. E4 In row 17 calculate Profit 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 B20 calculate the state tax incurred for each sales volume using the rate at the top ofthe 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. 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). E? In row 26 work out the profit for each sales volume as a percentage of sales. Complete the BreakEven Analysis Line Chart Data in 329 to 631 for each of the Unit volumes. (Note Total Costs includes Variable Costs, Fixed Costs and Income Tax) E9 Use the data in A29:631 to create a 2D Line Chart that plots the Total Fixed Costs, Total Costs 8!. Total $ Sales. Add the Sales Volume in Units figures as Horizontal Axis Labels and add the Chart Title: Prot Volume Forecast (mark given only if this exact title is used). Use chart tools (not drag and drop) to position the legend at the top of the chart. The completed chart should look something like this [although values will vary). The point where the lines . _ Profit Volume Forecast cross Is the break even pomt. l iled E05L5 lul.o| Cosh. lo'l.l| 5 Sales Value SUIUOLDO N.B. Do NOT use Pivot Tables or mm\"... Pivot Charts. Only include one Smxmxl chart, ensure it is on the Profit 5mm Volume Forecast Sheet and that 5600.000 it is a 2D Line Chart with no line 5mm markers (not a Scatter chart 5mm\" with smooth lines). 50 mo 1500 moo 2500 3000 3500 [:ICICICI
