|
|
|
|
|
|
|
|
| Producing
Frames at Monet |
|
|
| The Monet
Company produces 4 types of picture frames, which are labeled 1,2,3,4 |
|
| the four
types of frames fiffer with respect to size, shape, and materials used. |
|
|
|
Skilled |
|
Selling |
|
|
Labor |
Metal |
Glass |
Price |
|
| |
|
|
|
|
|
| Frame 1 |
2 |
4 |
6 |
$28.50 |
|
| Frame 2 |
1 |
2 |
2 |
$12.50 |
|
| Frame 3 |
3 |
1 |
1 |
$29.25 |
|
| Frame 4 |
2 |
2 |
2 |
$21.50 |
|
| |
|
|
|
|
|
|
| Monet
has the abiltiy to purchase up to 4000 hours of skilled labor, |
|
|
| 6000
ounces of metal, and 10,000 ounces of glass. The unit cost per labor |
|
| are
$8.00 per labor hour, in wich .50$ per ounce is metal and $.75 per ounce
is |
|
| glass.
The company wishes to maximize its weekly profit. |
|
|
|
|
| Maz Z= 6x1 + 2x2 + 4x3 + 3x4 |
Profit objective |
|
| ST |
|
| 2x1 + x2 +3x3 + 2x4 <=4000 |
Labor constraint |
|
| 4x1 + 2x2 + x3 +2x4<=6000 |
Metal Constraint |
|
| 6x1 + 2x2 + x3 + 2x4 <=10000 |
glass constraint |
|
| x1<=1000 |
frame 1 sales constraint |
|
| x2<= 2000 |
frame 2 sales constraint |
|
| x3<=500 |
frame 3 sales constraint |
|
| x4<=1000 |
frame 4 sales constraint |
|
| x1,x2,x3,x4>=0 |
nonnegativity constraints |
|
|
| By using
Solver, you can check the sensitivity of the |
|
| optimal
profit and optimal prodcut mix associated with |
|
| changes
to the number of labor hours and the cost per metal. |
|
|
| Sensitivity
of optimal solution to number of labor hours |
|
|
|
Frames produced |
|
|
| Labor hours |
1 |
2 |
3 |
4 |
Total profit |
Increase |
|
| [1] |
$B$16 |
$C$16 |
$D$16 |
$E$16 |
$F$32 |
|
|
| 2500[2] |
1000[3] |
500 |
0 |
0 |
$7,000 |
|
|
| 2750 |
1000[4] |
750 |
0 |
0 |
$7,500 |
$500 |
|
| 3000 |
1000[5] |
1000 |
0 |
0 |
$8,000 |
$500 |
|
| 3250 |
1000[6] |
950 |
100 |
0 |
$8,300 |
$300 |
|
| 3500 |
1000[7] |
900 |
200 |
0 |
$8,600 |
$300 |
|
| 3750 |
1000[8] |
850 |
300 |
0 |
$8,900 |
$300 |
|
| 4000 |
1000[9] |
800 |
400 |
0 |
$9,200 |
$300 |
|
| 4250 |
1000[10] |
750 |
500 |
0 |
$9,500 |
$300 |
|
| 4500 |
1000[11] |
500 |
500 |
250 |
$9,750 |
$250 |
|
| 4750 |
1000[12] |
250 |
500 |
500 |
$10,000 |
$250 |
|
| 5000 |
1000[13] |
0 |
500 |
750 |
$10,250 |
$250 |
|
|
| Sensitivity
of optimal solution to unit cost of metal (per oz.) |
|
|
|
Frames produced |
|
|
|
| Cost (per oz.) of metal |
1 |
2 |
3 |
4 |
Total profit |
Decrease |
|
| [14] |
$B$16 |
$C$16 |
$D$16 |
$E$16 |
$F$32 |
|
|
| $0.30[15] |
1000[16] |
800 |
400 |
0 |
$10,400 |
|
|
| $0.50 |
1000[17] |
800 |
400 |
0 |
$9,200 |
$1,200 |
|
| $0.70 |
1000[18] |
800 |
400 |
0 |
$8,000 |
$1,200 |
|
| $0.90 |
1000[19] |
500 |
500 |
0 |
$6,800 |
$1,200 |
|
| $1.10 |
1000[20] |
0 |
500 |
250 |
$5,750 |
$1,050 |
|
| $1.30 |
1000 |
0 |
500 |
250 |
$4,750 |
$1,000 |
|
|
|
|
| Part2 |
|
|
| Situation
Description and Overall Description |
|
|
| Postal
Employee Scheduling |
|
| A post office requires different numbers of
full-time employees on different days of the week. |
| Union
rules state that that each full-time employee are required to work 5
consecutive |
|
| days
and then 2 days off. |
|
|
| |
Min.
Employees Req'd |
|
| Monday |
17 |
|
|
| Tuesday |
13 |
|
|
| Wednesday |
15 |
|
|
| Thursday |
19 |
|
|
| Friday |
14 |
|
|
| Saturday |
16 |
|
|
| Sunday |
11 |
|
|
|
| Variables
and Measures |
|
|
| The
variables are the number of employees starting work on each day of the week, |
|
| the number of employees working each day,
and the total number of employees. |
|
|
| Number
starting their five-day shift on various days |
|
| Mon |
6 |
|
| Tue |
6 |
|
| Wed |
0 |
|
| Thu |
7 |
|
| Fri |
0 |
|
| Sat |
4 |
|
| Sun |
0 |
|
|
| Mathematical
Model Formulation |
|
| *
the objective of the model is to
minimize |
|
| the
number of full-time employees to be
hired. |
|
|
|
|
|
|
|
|
|
|
| Development
of Spreadsheet Model |
|
|
| |
Mon |
Tue |
Wed |
Thu |
Fri |
Sat |
Sun |
| Mon |
6 |
6 |
6 |
6 |
6 |
|
| Tue |
|
6 |
6 |
6 |
6 |
6 |
|
| Wed |
|
0 |
0 |
0 |
0 |
0 |
| Thu |
7 |
|
7 |
7 |
7 |
7 |
| Fri |
0 |
0 |
|
0 |
0 |
0 |
| Sat |
4 |
4 |
4 |
|
4 |
4 |
| Sun |
0 |
0 |
0 |
0 |
|
0 |
| Available |
17 |
16 |
16 |
19 |
19 |
17 |
11 |
|
>= |
>= |
>= |
>= |
>= |
>= |
>= |
| Min required |
17 |
13 |
15 |
19 |
14 |
16 |
11 |
|
|
| Total employees |
23 |
|
| |
|
|
|
|
|
|
| Results |
|
|
| When you
use Solver, it is possible to generate multiple optimal solutions in which
some |
|
| numbers
may appear as fractions when all employees are full-time. To escape this,
the |
|
| utilization
of Solver is necessary along with adding Integer Constraints. As for
Sensitivity |
|
| Analysis,
it can be used to examine how the work schedule and total number of employees |
| change as
the number of employees required each day changes. |
|
|
| DSS guidelines |
|
| Using my model is quite simple. First thing
is that you need to know what your objective |
|
| is. Then
you establish a agenda that will cater to that goal. By using decision
analysis tools |
| such
as Solver, you can easily perform a sensitivity analysis on the input values
and create a |
| schedule
that is accomodating to your employees/clients. |
|
|
|
|
|
|
|
|
|
