Help With Solver

Indigent1

New Member
Joined
Aug 9, 2011
Messages
3
Hi there. I am a student and i am trying to learn how to use Solver in order to answer part B of the following question. anyone willing to help? thank you.

5.*Gloddy Company makes three products in a single facility. These products have the following unit product costs:

Products
A B C
Direct material $24.90 $25.70 $26.60
Direct labor (variable) $13.30 $17.10 $15.70
Variable manufacturing overhead $2.50 $2.80 $3.10
Fixed manufacturing overhead $19.80 $27.70 $21.00
Unit product cost $60.50 $73.30 $66.40
Additional data concerning these products are listed below:
Mixing minutes per unit 2.50 1.70 1.60
Selling price per unit $71.50 $87.90 $83.00
Variable selling cost per unit $2.30 $1.90 $3.80
Monthly maximum demand in units 1,000 3,000 3,000

The mixing machines are potentially the constraint in the production facility. A total of 10,800
minutes are available per month on these machines. Direct labor is a variable cost in this company.

Required - use linear programing with excel solver to find the solutions as to how many units of each project
can be produced given the constraints in demand for each product and the limited monthly machine minutes.
Save the three reports available in the solver.

a. How many minutes of mixing machine time would be required to satisfy demand for all three products?

Mixing minutes per unit 2.50 1.70 1.60
Monthly maximum demand in units 1,000 3,000 3,000
TOTAL Mixing minutes per unit 2,500 5,100 4,800
TOTAL MINUTES FOR ALL PRODUCTS=2,500 + 5,100 + 4,800= 12,400
Maximum capacity = 10,800

b. How much of each product (rounded) should be produced to maximize net operating income?

Products
Relevant costs A B C
Selling price per unit $71.50 $87.90 $83.00

Direct material $24.90 $25.70 $26.60
Direct labor (variable) $13.30 $17.10 $15.70
Variable manufacturing overhead $2.50 $2.80 $3.10
Variable selling cost per unit $2.30 $1.90 $3.80
Total variable cost per unit $43.00 $47.50 $49.20

Contribution margin $28.50 $40.40 $33.80

Calculation by minutes:
Mixing minutes per unit 2.50 1.70 1.60
Contribution margin per minute $11.40 $23.76 $21.13

TOTAL Mixing minutes per month 2,500 5,100 4,800
Total contribution margin per unit per month $28,500 $121,200 $101,400

Check and balance:
Monthly maximum demand in units 1,000 3,000 3,000
Contribution margin $28.50 $40.40 $33.80
Contribution margin by unit calculation $28,500 $121,200 $101,400






Constraint = Maximum capacity = 10,800

c. Up to how much should the company be willing to pay for one additional hour of mixing
machine time if the company has made the best use of the existing mixing machine capacity?

No more than the lowest amount of contribution margin. Since the lowest amount is $11.40 per
minute, the company should not pay more than $11.40 X 60, or $684 per hour
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Indigent1

New Member
Joined
Aug 9, 2011
Messages
3
Re: Help With Solver ---CORRECTED

Hi there. I am a student and i am trying to learn how to use Solver in order to answer part B of the following question. anyone willing to help? thank you.

5.*Gloddy Company makes three products in a single facility. These products have the following unit product costs:

Products
A B C
Direct material $24.90 $25.70 $26.60
Direct labor (variable) $13.30 $17.10 $15.70
Variable manufacturing overhead $2.50 $2.80 $3.10
Fixed manufacturing overhead $19.80 $27.70 $21.00
Unit product cost $60.50 $73.30 $66.40
Additional data concerning these products are listed below:
Mixing minutes per unit 2.50 1.70 1.60
Selling price per unit $71.50 $87.90 $83.00
Variable selling cost per unit $2.30 $1.90 $3.80
Monthly maximum demand in units 1,000 3,000 3,000

The mixing machines are potentially the constraint in the production facility. A total of 10,800
minutes are available per month on these machines. Direct labor is a variable cost in this company.

Required - use linear programing with excel solver to find the solutions as to how many units of each project
can be produced given the constraints in demand for each product and the limited monthly machine minutes.
Save the three reports available in the solver.


b. How much of each product (rounded) should be produced to maximize net operating income?

Products
Relevant costs A B C
Selling price per unit $71.50 $87.90 $83.00

Direct material $24.90 $25.70 $26.60
Direct labor (variable) $13.30 $17.10 $15.70
Variable manufacturing overhead $2.50 $2.80 $3.10
Variable selling cost per unit $2.30 $1.90 $3.80
Total variable cost per unit $43.00 $47.50 $49.20

Contribution margin $28.50 $40.40 $33.80

Calculation by minutes:
Mixing minutes per unit 2.50 1.70 1.60
Contribution margin per minute $11.40 $23.76 $21.13

TOTAL Mixing minutes per month 2,500 5,100 4,800
Total contribution margin per unit per month $28,500 $121,200 $101,400

Check and balance:
Monthly maximum demand in units 1,000 3,000 3,000
Contribution margin $28.50 $40.40 $33.80
Contribution margin by unit calculation $28,500 $121,200 $101,400






Constraint = Maximum capacity = 10,800 minutes
Maximum demand = 7000 units

how to maximize profit??????
 

Watch MrExcel Video

Forum statistics

Threads
1,130,205
Messages
5,640,836
Members
417,172
Latest member
Matias_Luna

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top