You work for a company named BHOutdoors. Your manager decides to conduct a profitability analysis with sophisticated sale and marketing data. The marketing group of BHOutdoors has conducted research and determined the likely sales volume at various levels of marketing expenses. Your manager has you start to create a revised break-even and sensitivity analysis in a workbook named BHOutdoors, which contains input assumptions and an output section in the form of an income statement (see BHOutdoors.xlsx).

Your manager asks your team to finish the analysis by creating a one-variable data table to show the effects of the marketing expense levels on sales, gross profit, contribution margin, and marginal income before taxes. Additionally, you are tasked with creating a two-variable data table that shows the effects of the interaction of various marketing expenses and sales prices on income before taxes. Complete the following:

1. Open the workbook BHOutdoors.xlsx. Rename it as BHOutdoors Analysis.xlsx.

2. View the Projections worksheet. Below the Projections section of the worksheet, create the structure for the one-variable data table. The tables input values should be marketing expenses of $25,500 to $125,500 in increments of $25,000. The tables output formulas should refer to the sales, contribution margin, and marginal income before taxes results cells.

3. Complete the one-variable data table using the Data Table dialog box. Relate the tables input values to the Marketing expense cell in the worksheets input section.

4. Add headings and format the data table so it is appealing and professional.

5. A few rows below the one-variable data table, create the structure for the two-variable data table. One set of the tables input values should be marketing expenses of $25,500 to $125,500 in increments of $25,000. The other set of the tables input values should be sales prices of $99.99 to $149.99 in increments of $5.00. The tables output formula should refer to the marginal income before taxes cell.

6. Complete the two-variable data table using the Data Table dialog box. Relate the tables input values to the marketing expense cell and the selling price cell in the worksheets input section.

7. Add headings and format the data table so it is appealing and professional.

8. Save and close BHOutdoors.xlsx.

A B I 1 E Clothing Division - BHOutdoors Product Projected Profitability and Break-Even Analysis For the Year Ended Dec 31, 2018 Projections: 1,250 $ 162,437.50 Sales in units (depends on Mktg Exp; see table below) Selling price per unit $ 129.95 94,687.50 9,375.00 Variable Manufacturing Costs Distribution $ 75.75 $ 7.50 104,063 58,375.00 2 3 4 5 Assumptions: 6 7 Revenues 8 9 10 11 Variable Costs per Unit 12 13 14 Fixed Costs 15 16 17 18 19 20 If Mktg Exp is: 21 $ 22 $ 25,500 23 $ 50,500 24 $ 75,500 25 $ 100,500 26 $ 125,500 Sales Revenue Variable Costs: Manufacturing Distribution Total Variable Costs Contribution margin Fixed Costs: Marketing Manufacturing Selling and Administrative Total Fixed Costs Profit before taxes Marketing Payroll Estimated overhead $ 50,500 $ 65,000 $ 13,000 $ 50,500 65,000 13,000 128,500.00 $ (70,125.00) Then Units Sold is: 500 1,250 2,750 5,500 6,500 A B I 1 E Clothing Division - BHOutdoors Product Projected Profitability and Break-Even Analysis For the Year Ended Dec 31, 2018 Projections: 1,250 $ 162,437.50 Sales in units (depends on Mktg Exp; see table below) Selling price per unit $ 129.95 94,687.50 9,375.00 Variable Manufacturing Costs Distribution $ 75.75 $ 7.50 104,063 58,375.00 2 3 4 5 Assumptions: 6 7 Revenues 8 9 10 11 Variable Costs per Unit 12 13 14 Fixed Costs 15 16 17 18 19 20 If Mktg Exp is: 21 $ 22 $ 25,500 23 $ 50,500 24 $ 75,500 25 $ 100,500 26 $ 125,500 Sales Revenue Variable Costs: Manufacturing Distribution Total Variable Costs Contribution margin Fixed Costs: Marketing Manufacturing Selling and Administrative Total Fixed Costs Profit before taxes Marketing Payroll Estimated overhead $ 50,500 $ 65,000 $ 13,000 $ 50,500 65,000 13,000 128,500.00 $ (70,125.00) Then Units Sold is: 500 1,250 2,750 5,500 6,500