Help, how to use Excel Solver Tool

L

LauraForiero

New Member
Joined
Oct 6, 2013
Messages
18
How do I use Excel's Solver problem to solve this problem??? All the fictional data necessary is below. Thanks in advance.[TABLE="width: 584"]
<tbody>[TR]
[TD="class: xl93, width: 256, bgcolor: transparent, colspan: 4"]On hand - Cannot use more than this
[/TD]
[TD="class: xl95, width: 128, bgcolor: transparent"]Each batch of Cherry tarts requires (kg)
[/TD]
[TD="class: xl95, width: 141, bgcolor: transparent"]Each batch of Apricot tarts requires (kg)
[/TD]
[TD="class: xl95, width: 130, bgcolor: transparent"]Each batch of Lemon tarts requires (kg)
[/TD]
[TD="class: xl96, width: 123, bgcolor: transparent"]Totals
[/TD]
[/TR]
[TR]
[TD="class: xl97, bgcolor: transparent"]Flower
[/TD]
[TD="class: xl98, bgcolor: transparent"] 2,000
[/TD]
[TD="class: xl97, bgcolor: transparent"]kg
[/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl99, bgcolor: transparent"] 6.50
[/TD]
[TD="class: xl99, bgcolor: transparent"] 4.50
[/TD]
[TD="class: xl99, bgcolor: transparent"] 4.95
[/TD]
[TD="class: xl100, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl97, bgcolor: transparent"]Sugar
[/TD]
[TD="class: xl98, bgcolor: transparent"] 2,100
[/TD]
[TD="class: xl97, bgcolor: transparent"]kg
[/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl99, bgcolor: transparent"] 5.25
[/TD]
[TD="class: xl99, bgcolor: transparent"] 4.75
[/TD]
[TD="class: xl99, bgcolor: transparent"] 6.00
[/TD]
[TD="class: xl100, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl97, bgcolor: transparent"]Salt
[/TD]
[TD="class: xl98, bgcolor: transparent"] 95
[/TD]
[TD="class: xl97, bgcolor: transparent"]kg
[/TD]
[TD="class: xl97, bgcolor: transparent"][/TD]
[TD="class: xl99, bgcolor: transparent"] 0.20
[/TD]
[TD="class: xl99, bgcolor: transparent"] 0.19
[/TD]
[TD="class: xl99, bgcolor: transparent"] 0.22
[/TD]
[TD="class: xl100, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl94, bgcolor: transparent, colspan: 5"][/TD]
[TD="class: xl94, bgcolor: transparent"][/TD]
[TD="class: xl94, bgcolor: transparent"][/TD]
[TD="class: xl93, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl94, bgcolor: transparent, colspan: 5"]Unlimited supply of Cherries & Apricots & Lemons
[TABLE="width: 778"]
<tbody>[TR]
[TD="colspan: 5"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="colspan: 5"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="colspan: 5"] Additional constraints and information [/TD]
[TD="align: right"]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 584"]
<colgroup><col width="64" style="width: 48pt;" span="4"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <col width="141" style="width: 106pt; mso-width-source: userset; mso-width-alt: 5156;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;"> <col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <tbody>[TR]
[TD="class: xl103, width: 256, bgcolor: transparent, colspan: 4"]Minimum number of full batches of each type of tart per baking. Note: You cannot bake partial batches.[/TD]
[TD="class: xl97, width: 128, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl97, width: 141, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl97, width: 130, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl98, width: 123, bgcolor: transparent"] 300 [/TD]
[/TR]
[TR]
[TD="class: xl103, width: 256, bgcolor: transparent, colspan: 4"]Maximum number of full batches of each type of tart per baking.[/TD]
[TD="class: xl97, bgcolor: transparent, align: right"]175[/TD]
[TD="class: xl97, bgcolor: transparent, align: right"]175[/TD]
[TD="class: xl97, bgcolor: transparent, align: right"]175[/TD]
[TD="class: xl98, bgcolor: transparent"] 525 [/TD]
[/TR]
[TR]
[TD="class: xl103, width: 256, bgcolor: transparent, colspan: 4"]Maximum number of tart batches per baking.[/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl98, bgcolor: transparent"] 450 [/TD]
[/TR]
[TR]
[TD="class: xl99, bgcolor: transparent, colspan: 3"]Profit per batch of tarts[/TD]
[TD="class: xl101, bgcolor: transparent"] [/TD]
[TD="class: xl102, bgcolor: transparent, align: right"]$8.00[/TD]
[TD="class: xl102, bgcolor: transparent, align: right"]$7.36[/TD]
[TD="class: xl102, bgcolor: transparent, align: right"]$8.25[/TD]
[TD="class: xl102, bgcolor: transparent, align: right"]$23.61[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl94, bgcolor: transparent"][/TD]
[TD="class: xl94, bgcolor: transparent"][/TD]
[TD="class: xl93, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 584"]
<colgroup><col width="64" style="width: 48pt;" span="4"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <col width="141" style="width: 106pt; mso-width-source: userset; mso-width-alt: 5156;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;"> <col width="123" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4498;"> <tbody>[TR]
[TD="class: xl97, width: 128, bgcolor: transparent, colspan: 2"][/TD]
[TD="class: xl98, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl98, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl100, width: 128, bgcolor: transparent"][/TD]
[TD="class: xl100, width: 141, bgcolor: transparent"][/TD]
[TD="class: xl100, width: 130, bgcolor: transparent"][/TD]
[TD="class: xl100, width: 123, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl97, width: 128, bgcolor: transparent, colspan: 2"]Solver Solution[/TD]
[TD="class: xl98, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl98, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl100, width: 128, bgcolor: transparent"]# of batches of Cherry tarts per baking[/TD]
[TD="class: xl100, width: 141, bgcolor: transparent"]# of batches of Apricot tarts per baking[/TD]
[TD="class: xl100, width: 130, bgcolor: transparent"]# of batches of Lemon tarts per baking[/TD]
[TD="class: xl100, width: 123, bgcolor: transparent"]Total # of batches of tarts per baking[/TD]
[/TR]
[TR]
[TD="class: xl105, width: 256, bgcolor: transparent, colspan: 4"]Number of full batches of each type of tart for a given baking. [/TD]
[TD="class: xl101, bgcolor: #F79646"] 100.00 [/TD]
[TD="class: xl101, bgcolor: #F79646"] 100.00 [/TD]
[TD="class: xl101, bgcolor: #F79646"] 100.00 [/TD]
[TD="class: xl99, bgcolor: transparent"] 300 [/TD]
[/TR]
[TR]
[TD="class: xl105, width: 256, bgcolor: transparent, colspan: 4"]Flower used (kg)[/TD]
[TD="class: xl102, bgcolor: transparent"] 650.00 [/TD]
[TD="class: xl102, bgcolor: transparent"] 450.00 [/TD]
[TD="class: xl102, bgcolor: transparent"] 495.00 [/TD]
[TD="class: xl99, bgcolor: transparent"] 1,595 [/TD]
[/TR]
[TR]
[TD="class: xl105, width: 256, bgcolor: transparent, colspan: 4"]Suger used (kg)[/TD]
[TD="class: xl102, bgcolor: transparent"] 525.00 [/TD]
[TD="class: xl102, bgcolor: transparent"] 475.00 [/TD]
[TD="class: xl102, bgcolor: transparent"] 600.00 [/TD]
[TD="class: xl99, bgcolor: transparent"] 1,600 [/TD]
[/TR]
[TR]
[TD="class: xl105, width: 256, bgcolor: transparent, colspan: 4"]Salt used (kg)[/TD]
[TD="class: xl102, bgcolor: transparent"] 20.00 [/TD]
[TD="class: xl102, bgcolor: transparent"] 19.00 [/TD]
[TD="class: xl102, bgcolor: transparent"] 22.00 [/TD]
[TD="class: xl103, bgcolor: transparent"] 61.00 [/TD]
[/TR]
[TR]
[TD="class: xl98, bgcolor: transparent"][/TD]
[TD="class: xl98, bgcolor: transparent"][/TD]
[TD="class: xl98, bgcolor: transparent"][/TD]
[TD="class: xl98, bgcolor: transparent"][/TD]
[TD="class: xl102, bgcolor: transparent"] [/TD]
[TD="class: xl102, bgcolor: transparent"] [/TD]
[TD="class: xl102, bgcolor: transparent"] [/TD]
[TD="class: xl103, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl95, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl95, bgcolor: transparent, colspan: 2"]Total Profit[/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl96, bgcolor: transparent"][/TD]
[TD="class: xl104, bgcolor: transparent"] 800.00 [/TD]
[TD="class: xl104, bgcolor: transparent"] 736.00 [/TD]
[TD="class: xl104, bgcolor: transparent"] 825.00 [/TD]
[TD="class: xl104, bgcolor: transparent"] 2,361.00 [/TD]
[/TR]
</tbody>[/TABLE]
 
You must log in or register to reply here.

Similar threads

N
  • Question Question
How to Create Multiple Reports from One Sheet
Replies
1
Views
3K
Logit
Logit
S
  • Question Question
Conditional Formating Required for changing Dates based on 2 different cell dates
Replies
1
Views
812
Toadstool
Toadstool
S
  • Question Question
Formula Help
Replies
4
Views
640
Fluff
Fluff
subrosumon
  • Question Question
Multiple Sheet pivot table
Replies
1
Views
859
steve the fish
steve the fish
N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff

Share this page

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
Back
Top