Answered step by step
Verified Expert Solution
Question
1 Approved Answer
How do I use Excel Solver to figure out selling price? A nrm is analyzing their pricing and operations for next year based on the
How do I use Excel Solver to figure out selling price?
A nrm 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 8%. Units Produced and Sold Sales Commission Rate Direct Material Cost/Unit Direct Labor Cost/Unit Variable Manufacturing Overhead Cost/Unit Total Fixed Manufacturing (verhead Closts Total Fixed Selling & Administrative Costs 80,000 10% $15 $10 $8 $700,000 $400,000 Target Operating Income Margin (Operating Income as a % of Sales 8% Requirements: 1. Using Excel (Solver Add-In), determine the selling price necessary to obtain a 8% 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 statements should clearly show the Contribution Margin (CM) and Operating Income (OI) calculations along with the CM and Ol 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. A nrm 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 8%. Units Produced and Sold Sales Commission Rate Direct Material Cost/Unit Direct Labor Cost/Unit Variable Manufacturing Overhead Cost/Unit Total Fixed Manufacturing (verhead Closts Total Fixed Selling & Administrative Costs 80,000 10% $15 $10 $8 $700,000 $400,000 Target Operating Income Margin (Operating Income as a % of Sales 8% Requirements: 1. Using Excel (Solver Add-In), determine the selling price necessary to obtain a 8% 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 statements should clearly show the Contribution Margin (CM) and Operating Income (OI) calculations along with the CM and Ol 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 statementStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started