Please provide all formulas for cells E1-E8. Cells B25 and B6 are drop down menus, so please provide the cell references with the formula so when the value is changed, the answer in the cells changes too.

Section E 16 E1 1 The following are to be completed 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 of the product selected in the CVP Analysis. Click into B4 and note that you can select how many units you want the sales volumes to go up by, 25, 50, 75 or 100. In B6 you can choose how many units you want to start from. In C6 create a calculation to add the value in B4 to B6. Adjust the cell referencing so that as you drag the formula across to G6 the results increase by the value selected in B4, e.g. 50, 100, 150, 200 etc. In row 7 calculate the total sales revenue for each of the different Sales Volumes in row 6. Using the unit costs from the CVP, complete the variable costs table for the different sales volumes (B10:15). (Each row is worth / Mark, all answers in row must be correct to get marks). In row 18 calculate the Contribution Margin for each sales volume. E2 1 E3 3 E4 1 E5 In row 19 calculate Contribution Margin less Fixed Costs for each sales volume. 1 Page 3 E6 3 In B22, using the tax rate in G3, calculate the state tax incurred for the sales volume in B6. If the profit was zero or below, the calculation should return 0 but do NOT just type 0 as the volumes can change. Adjust the cell referencing so the formula can be dragged down to B23 and then across to G23 and return the correct answers. (Full marks awarded only if cell referencing and solutions correct). In row 26 work out the profit for each sales volume as a percentage of the total sales value. Complete the Break-Even Analysis Line Chart Data in B29 to G32 for each of the Unit volumes. (Note Total Costs includes Variable Costs, Fixed Costs, and Income Tax) E7 1 E8 2 Strata Bikes - Profit Volume Forecast State Tax: Federal Tax: 10% 5% Unit Volume Increments 25 50 Sales Volume in Units Total Sales Value Variable Costs Direct Labor Direct Material Factory Overhead Other Vary-with-Unit Costs Sales Commissions Other Vary-with-Revenue Costs Total Variable Costs $ $ $ Contribution Margin Contribution Margin - Fixed Costs Income Tax State Tax Federal Tax Total Income Tax $ $ $ % Profit of Sales Value Break-Even Analysis Line Chart Data Sales Volume in Units Fixed Costs Total Costs Total Sales Value Cost Volume Profit Analysis for Strata Bikes $ $ Product Name Unit Sales Price Unit Sales Estimate Total Fixed Costs Strata 4,265.00 300.00 108,600.00 Break-Even Analysis Contribution Margin Per Unit Break Even Volume in Units Break Even Sales Value 1,513.97 72 307,080.00 $ Variable Costs per Unit Vary with Unit Costs Direct Labor Direct Material Factory Overhead Other Vary-with-Unit Costs Total Vary with Unit Costs $ $ $ $ $ 1,232.32 1,304.93 144.16 51.62 2,733.03 Break Even Variable Costs Direct Labor Direct Material Factory Overhead Other Vary-with-Unit Costs Sales Commissions Other Vary-with-Revenue Costs Total Variable Costs $ $ $ $ $ $ $ 88,726.84 93,954.96 10,379.52 3,716.64 972.00 324.00 198,073.96 Vary-with-Revenue Costs Sales Commissions Other Vary-with-Revenue Costs Total Vary with Revenue Costs 4.5% 1.5% 6.0% Contribution Margin Profit Before Tax $ $ 109,006.04 406.04