Please read the attached document for instructions. MGMT5360 – Decision Making Techniques for Managers Winter, 2006
MGMT5260 – Decision Making Techniques for Managers
Rules and Instructions.
1. Exam time: Thursday, February 8, 6:00 pm – 10:00 pm CT
2. This is “Open Book – Close Everything Else” exam.
3. There are four problems in the exam and you need to create a single Excel file with four worksheets (tabs) – one tab per problem.
4. Name your test file as YourLastName, FirstName_MidTerm (e.g., Smith,John_MidTerm).
5. You are advised to save frequently.
6. When you are done
a. Save your file and close Excel.
b. Submit your solution through Blackboard.
7. Your submission should be completed before 10:00 pm CT.
Special comment for Problem 4. This is a nonlinear model and you are recommended to use the “Multistart” option in Solver to get the best solution. When you create your model and invoke the Solver, click on “Options” button, select “GRG nonlinear” tab, then check the “Use Multistart” checkbox, then OK. Problem 1 (25 points)
Problem 2 (25 points)
Problem 3 (25 points)
Problem 4 (25 points) . Use Multistart option in Solver.
Grading Rubric:
| Problem number | Items for grading | Maximum points |
| #1 | Formula for Copies made | 8 |
| Formula for Total Cost | 5 | |
| Formula for Total Revenue | 3 | |
| Break -even | 3 | |
| Using DataTable | 3 | |
| Graph | 3 | |
| #2 | Formulas for Contancts made | 8 |
| Formulas for total number of calls made | 3 | |
| Formulas for total cost | 3 | |
| Constratint for <=40% evening calls | 5 | |
| Constrain for nuber of calles answered by various groups | 3 | |
| Changing cells, min, non-negative, linear model | 3 | |
| #3 | Formulas for Total Used and Produced | 2 |
| Formula for Cans Used | 2 | |
| Formulas for Actual | 6 | |
| Formulas for Required | 6 | |
| Formula for Profit | 3 | |
| Constraints | 3 | |
| Changing cells, max, non-negative, linear model | 3 | |
| #4 | Values for a and b for Plant 2 | 3 |
| Formula for Total | 4 | |
| Formula for Cost | 8 | |
| Formula for Revenue | 5 | |
| Solver: changing cells, constraints, GRG nonlinear, MultiStart | 5 | |
| Total | 100 |
Created by: Dr. Levon Hayrapetyan Page 1 of 6
Profit per canMeow ChowBow Chow
Blending Plan
Meow ChowBow ChowUsedAvailable
Horse meat
Ground fish
Cereal additive
Total produced
Cans available
Cans used
Fish constraintHorse meat constraint
ActualActual
RequiredRequired
Profit
Problem 3. Bark’s Pet Company produces canned cat food called Meow Chow and
canned dog food called Bow Chow. The company produces the pet food by blending
horse meat, ground fish, and a cereal additive. Each week the company has 600
pounds of horse meat, 800 pounds of ground fish, and 1,000 pounds of cereal
additive available to produce both kinds of pet food. Meow Chow must be at least
half fish, and Bow Chow must be at least half horse meat. The company has 2,350
one pound cans available each week. A can of Meow Chow earns $1.10 in profit,
and a can of Bow Chow earns $1.40 in profit.
The company wants to create a model that will help to maximize its weekly profit.
Costs of producing x units at each plant are of form ax
b
, where
Plant 1Plant 2
a125
b1/2
Unit selling prices
Plant 1Plant 2
Number produced
Plant 1Plant 2TotalMax sales
<=
<=<=
Capacity
Cost
Revenue
Profit
Problem 4.
Widgetco produces widgets at plants 1 and 2. It costs
125x
1/2
dollars to produce x units at plant 1 and 235x
1/3
dollars to
produce x units at plant 2. Each plant can produce up to 500 units.
Each unit produced can be sold for $10. At most 800 widgets can
be sold.
Determine how Widgetco can maximize its profit.
Inputs
Yearly rental cost
Other monthly fixed costs
Cost per copy
Charge per copy
Days per year
Copies per copier per year
Daily demand (trial value)
Total (i.e., annual) demand
Copies made
Total cost
Total revenue
Total profit
Daily demandProfit
50
75
100
125
150
175
200
225
250
275
Problem 1. You are thinking of opening a small copy shop and rent a copier. It costs
you $1,000 to rent a copier for a year and it costs you $0.03 per copy to operate a
copier. Other fixed costs of running the store amount to $150 per month. You are
planning to charge $0.10 per copy. You are open 365 days per year. A copier can
make up to 80,000 copies per year.
(a) Given these assumptions, what level of daily demand will enable you to break even?
(b) Calculate the annual profits for specified (in this template) daily demands and graph
them.
PercentagesDaytimeEvening
Wife30%30%
Husband10%30%
Single male10%15%
Single female10%20%
Cost/call
Max % of evening calls40%
DaytimeEveningTotal
Calls made
<=
ContactsMadeRequired
Wife
Husband
Single male
Single female
Total cost
Problem 2. For a telephone survey, your department needs to contact at least 600
wives, 480 husbands, 400 single adult males, and 440 single adult females. It costs $3.20
to make a daytime call and $6.00 to make an evening call. The table below lists the
results that can be expected. For example, 30% of all daytime calls are answered by
wives, and 15% of all evening calls are answered by single males. Because of a limited
staff, at most 40% of all phone calls can be evening calls.
Create a model to determine how to minimize the cost of completing the survey.
Max evening calls:
