MistaMista
New Member
- Joined
- May 18, 2021
- Messages
- 14
- Office Version
- 2019
- Platform
- Windows
- 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.
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:
(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;
"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 Type | Labor (hours) | Plastic(m2) |
---|---|---|
PACKon1 | 3 | 4 |
Gladwrap2 | 2 | 3 |
Uberware3 | 6 | 4 |
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 Type | Selling Price ($) | Variable Cost ($) |
---|---|---|
PACKon1 | 12 | 6 |
Gladwrap2 | 8 | 4 |
Uberware3 | 15 | 8 |
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;