Small optimization task in Excel (Solution in AMPL )

MistaMista

New Member
Joined
May 18, 2021
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Hello! I want to create this model in Excel to find the optimal solution with 2 constraints (Labor Hours and Material Available pr. week), below you will see the description and the solution in AMPL. For my own learning in other courses, I wish to make this in Excel using Solver.

"Packing can be done in three types of packages, called PACKon1, Gladwrap2 and Uberware3.
Each week, 200 hours of labor and 180 m2 of plastic is available.
The assembly of each type of package requires amounts of plastic and labor as shown in the table below.

Package TypeLabor (hours)Plastic(m2)
PACKon134
Gladwrap223
Uberware364

The fixed cost for each is $200 pr week for Packon, $160 pr week for Gladwrap, and $100 for Uberware. NOTE THAT this fixed cost is for the rent of equipment, as is ONLY considered if it is being produced.

The variable cost pr item is as follows:

Frame TypeSelling Price ($)Variable Cost ($)
PACKon1126
Gladwrap284
Uberware3158
Solve this problem optimally = Maximize profit, within the constraints.
(Don't worry if we don't produce certain products, we will build on this model later and change numbers.)
There is no min/max demand.
In this optimal solution, how many of the different packages are being produced, and whats the expected profit.


THIS IS THE AMPL SOLUTION
reset;
var x1 >=0 integer;
var x2 >=0 integer;
var x3 >=0 integer;
var y1 binary;
var y2 binary;
var y3 binary;

maximize z: 6*x1-200*y1+4*x2-160*y2+7*x3-100*y3;

subject to c1: 3*x1+2*x2+6*x3 <= 200;
subject to c2: 4*x1+3*x2+4*x3 <= 180;
subject to f1: x1 <= y1 * 100;
subject to f2: x2 <= y2 * 100;
subject to f3: x3 <= y3 * 100;

option solver cplex;
solve;

display z;
display x1, x2, x3;
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,215,049
Messages
6,122,864
Members
449,097
Latest member
dbomb1414

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