F H $ 162.438 co $94.688 9,375 $ 104,063 58,375 B D E F 1 TheZone - Apparel Division - TZSwimSkin 2 Projected Profitability and Break-Even Analysis 3 For the Year Ended Dec 31, 2016 4 5 Assumptions: Projections: 6 7 Revenues Sales in units (depends on Mktg Exp, see table below) 1,250 Sales Revenue 9 Variable Costs 10 Selling price per unit $ 129.95 Manufacturing 11 Variable Costs per Unit Distribution 12 Variable Manufacturing Costs $ 75.75 Total Variable Costs 13 Distribution Costs $ 7.50 Contribution margin 14 Fixed Costs Fixed Costs. 15 Marketing $50,000 Marketing 16 Fixed Manufacturing Costs $ 60,000 Manufacturing 17 Selling and Administrative Costs $ 12,000 Selling and Administrative 18 Total Fixed Costs 19 Profit before taxes if Mktg Exp 20 is: Then Units Sold is: 21 S 22 5 25,000 500 23 5 50,000 1.250 24 $ 75.000 2.750 25 $ 100,000 5,000 26 $ 125,000 6,500 27 28 29 30 31 $50,000 60,000 12,000 $ 122 000 (63,625) 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 17 Using Data Tables and Excel Scenarios for What Analysis Chapter 8 Steps To Success: Level 1 Richard and other managers agree that the TZSwimSkin product is promising. Richard wants to conduct another profitability analysis with more sophisticated sales and marketing data. The marketing group has conducted research and determined the likely sales volume at various levels of marketing expenses. Richard has started to create a revised break-even and sensitivity analysis in a workbook named TZSwimSkin, which contains input assumptions and an output section in the form of an income statement, as shown in Figure 8.25 Figure 8.25: TZSwimSkin workbook TA Year 350 3182400 w DC Towe Co SO Site St 68.00 TO 12 311 0 135 000 . 30 21 Richard asks you 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. He also asks you to create 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 named TZSwimSkin.xlsx located in the Chapter 8 folder, and save the file with the name TZSwimSkin Analysis.xlsx. 2. Switch to the SwimSkin Projections worksheet. Below the Projections section of the worksheet, create the structure for the one variable data table. The table's input values should be marketing expenses of $25,000 to S125,000 in increments of $25,000. The table's 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 table's input values to the Marketing expense cell in the worksheet's input section 4. Add headings and format the data table so it is appealing and professional and marginal income before taxes results cells. 3. Complete the one-variable data table using the Data Table dialog box. Relate the table's input values to the Marketing expense cell in the worksheet's input section 4. Add headings and format the data table so it is appealing and professional 511 COM www www.my Chapter 8 Using Data Tables and Excel Scenarios for Wharf Analysis 5. A few rows below the one-variable data table, create the structure for the two variable data table. One set of the table's input values should be marketing expenses of $25,000 to $125,000 in increments of $25,000. The other set of the table's input values should be sales prices of $99.95 to $149.95 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. Rclate the table's input values to the marketing expense cell and the selling price cell in the worksheet's input section. 7. Add headings and format the data table so it is appealing and professional 8. Sayr and close the TZSwimSkin Anal the workbook mamma