In: Operations Management
Jo Anna will bake some brownies for the next Work day.
She will make two different kinds: oatmeal and chocolate chip. Everyone knows
that the chocolate is a crowd favorite, so she wants the number of chocolate chip
cookies to be twice as much the number of oatmeal cookies. Additionally, she
needs at least eight dozen cookies. She estimates she will spend $0.25 per
oatmeal cookie and $0.35 per chocolate chip cookie. Additionally, she has
limited amount of butter, sugar, and flour in her kitchen and she does not have
time to go back to the grocery store. She estimates the amount of material
required for each cookie and measures her available material:
Amount Required in grams Availability in grams
oatmeal chocolate
Butter 9.46 6.31 700
Sugar 8.33 5.55 1000
Flour 5.00 10.00 850
(she is comfortable with the Standard!)
a. (Linear Programming) How many cookies of each type she should bake to
minimize her cost?
b. (Integer Programming) If she has to batch her cookies as a dozen, how
many dozen of each type she should bake?
Please show in exel show formulas and use solver
(a)
Excel model:
Solver inputs:
Solution:
Oatmeal cookies | Chocolate cookies | ||||
Dozens of | 2.49 | 5.51 | |||
Cost | $3.00 | $4.20 | $30.61 | ||
s.t. | |||||
Ratio | -2 | 1 | 0.521 | >= | 0 |
Min total | 1 | 1 | 8.000 | >= | 8 |
Butter | 113.52 | 75.72 | 700.000 | <= | 700 |
Sugar | 99.96 | 66.60 | 615.971 | <= | 1000 |
Flour | 60.00 | 120.00 | 810.413 | <= | 850 |
So,
The no. of oatmeal cookies = 2.49 doz = 29.88 units
The no. of chocolate cookies = 5.51 doz = 66.12 units
(b)
Keep everything else the same and only add the integer constraint in Solver as follows:
Solution:
Oatmeal cookies | Chocolate cookies | ||||
Dozens of | 2 | 6 | |||
Cost | $3.00 | $4.20 | $31.20 | ||
s.t. | |||||
Ratio | -2 | 1 | 2.000 | >= | 0 |
Min total | 1 | 1 | 8.000 | >= | 8 |
Butter | 113.52 | 75.72 | 681.360 | <= | 700 |
Sugar | 99.96 | 66.60 | 599.520 | <= | 1000 |
Flour | 60.00 | 120.00 | 840.000 | <= | 850 |
So,
The dozens of oatmeal cookies = 2
The dozens of chocolate cookies = 6