ES mato ud O CVP USING EXCEL The Power Tool Division of ABC Hardware selle one product, Jig Saw, and has the following data for the second quarter! Units of output Price per unit Variable cost per unit Total fixed costs 1.200 units SISU 90 48,000 Create a worksheet in Excel using the example provided. Using this worksheet, etter the data from above and calculate the amounts below. Just a note: all the shaded cells in the example MUST USE FORMULAS, FUNCTIONS, AND CELL REFERENCES for this project to be the most efficient Determine (to the nearest dollar or unit or to the nearest hundredth of a percent): 1. The pretax income profit when 1.200 units are sold. 2. Break-even volume in units and sales dollars 3. Contribution margin ratio 4. Sales dollars and units needed to generate a pretax income of $57.000 5. Calculate the margin of safety in dollars and percent at a pretax income of $57.000). Name this worksheet Initial Calculations. Create a second worksheet in the Excel workbook by copying the initial Calculations worksheet (right click on tab). In this sheet, assume the price of the product increased to $175. Recalculate the five requirements above. Name this worksheet Increased Sales Price Create a third worksheet in the Excel workbook. In this sheet, assume that rather than increasing the price of the product, the Power Tool Division cut variable costs by 20%. Recalculate the five requirements above. Name this worksheet Decreased Variable Costs. Create a fourth worksheet in the Excel workbook. In this sheet, assume that the Power Tool Division moved its store to a new location that increased the rent $2,000 a quarter and decreased the shipping costs $2 per unit. Recalculate the five requirements above. Nume this worksheet Increased Fixed Costs. Select which method yields you the best alternative for earning income. Color this tab red. CVP ANALYSIS UNITS 1200 1200 $ per unit 150 90 Sales Variable costs Contribution Margin Fixed Costs Pretax income Total 180000 108000 72000 48000 0 60 Contribution Margin Ratio 0.4 Breakeven point in units Breakeven point in sales dollars 800 120000 57000 740 48950 179200 179933.3333 Target pretax income 5 Sales (units) to achieve pretax income Sales (dollars) to achieve pretax income 3 Margin of Safety (in dollars) Margin of Safety (in percent) 1 2 3 4 5 6 7 28 29 30 31 32 33 34 35 Sheet1 Type here to search