Excel Assignment #2 (THIS IS AN INDIVIDUAL ASSIGNMENT, SO PLEASE TURN IN YOUR OWN WORK USING YOUR OWN WORDING, ETC.) VA rm is analyzing their pricing and operations for next year based on the following anticipated cost structure, the given level of production and sales, and their target operating income margin of 10%. Units Produced and Sold 100,000 Sales Commission Rate 20% Direct Material Cost/Unit $20 Direct Labor Cost/Unit $15 Variable Manufacturing Overhead Cost/Unit $10 Total Fixed Manufacturing Overhead Costs $750,000 Total Fixed Selling & Administrative Costs $500,000 Target Operating Income Margin (Operating Income as a % of Sales) 10% Requirements: '1. Using Excel (Solver AddIn), determine the selling price necessary to obtain a 10% target operating income margin. Operating Income Margin is Operating Income/Sales. 'You will need to determine the appropriate Objective Function and the appropriate Constraints to use in Solver for calculating the selling price. In Solver under 'Select a Solving Method', you can select 'Simplex LP' in the dropdown. '2 Based on the information given generate the income statement both on a 'Per Unit' basis and on a 'Total Dollar ($)' basis using appropriate formulas Both of these income Hstatementsa ihould clearly show the Contribution Margin (CM) and Operating Income (01) calculations along with the CM and OI as a percentage of sales. Round to two decimal places for the 'Per Unit' income statement and round to the nearest dollar for the 'Total Dollar ($)' income statement. Contribution Margin Income Statement Line Item or Subtotal: Per Unit % Total Dollars % Notes: 1. You'll first need to enable the Solver Add-in (located in the same spot as the add-in we used for regression analysis under File > Options > Add-ins). 2. While the requirements are labeled 1 and 2, it would actually be beneficial for you to do them in conjunction with one another. Using the cells below the requirements, where I've got it started for you, create your income statement with everything you know so far (everything but selling price), and then plug in the information into Solver to find your selling price per unit. 3. In Solver, you'll need to do four things: set your objective referencing your target total, set your changing variable cells to reference what you're trying to find, set your constraints to reference your required percentage calculations, and select the solving method Simplex LP. 4. Make sure to complete the income statement under both versions (per unit and total dollars) as well as show your contribution margin and operating income ratios' 5. You can check your work on this problem by doing the calculation manually! \f