I have attached a word document named OSCM-471571_Homework1_Sp23 which is the hw along with its excel template named hw1 template. I also attached solved practice problems along with their excel spreadsheets solved as well to be used as a sample. The hw needs to be submitted as a word doc.
OSCM 471/571 Optimization and Decision Support Modeling for Business
Homework 1, Spring 2023
Notice for Homework 1
Instructor: Seokjun Youn (
syoun@email.arizona.edu
)
·
Due date: Thursday 2/2, 11:59 pm
·
Please submit your files to D2L > Assignments > Homework 1
1.
A Word file (or PDF) with your answers combined into a single document.
2.
An Excel spreadsheet template with your answers (for some sub-questions).
·
This homework is made up of THREE questions (20 pts):
·
Q1: 10 sub-questions (8 pts)
·
Q2: 4 sub-questions (6 pts)
·
Q3: 2 sub-questions (6 pts)
·
Students may choose either handwriting or word processing (or both).
· Handwriting: please properly scan or take photos and organize them into one file before uploading in D2L.
·
Please write down your solutions step-by-step for partial credit.
·
You may use:
· Your textbook and notes from the class.
· Notes or sources from a related class or internet source.
· Discussion with the instructor.
· Voluntary, mutual, and cooperative discussion with other students currently taking the class.
·
You may not use:
· Solution manuals (printed or electronic).
· Copying from other students in this class, including expecting them to reveal their solutions in “discussion.”
· It is fine if your answer is not 100% correct. However, if you do not put enough effort to the assignment, your score for this homework will be lower than your expectation. So, please try to convince your logic to instructor.
Your Name:
1. The Whitt Window Company is a company with only three employees that makes two different kinds of handcrafted windows: a wood-framed and an aluminum framed window. They earn $60 profit for each wood-framed window and $30 profit for each aluminum-framed window. Doug makes the wood frames and can make 6 per day. Linda makes the aluminum frames and can make 4 per day. Bob forms and cuts the glass and can make 48 square feet of glass per day. Each wood-framed window uses 6 square feet of glass and each aluminum-framed window uses 8 square feet of glass.
The company wishes to determine how many windows of each type to produce per day to maximize total profit.
a.
Construct and fill in a table for this problem like the table of the Wyndor Glass Co. problem during the class, identifying both the activities and the resources.
Answer:
b. Identify verbally the decisions to be made, the constraints on these decisions, and the overall measure of performance for the decisions.
Answer:
c. Formulate a spreadsheet model for this problem. Identify the data cells, the changing cells, and the objective cell. Also show the Excel equation for each output cell expressed as a SUMPRODUCT function. Then use Solver to solve this model.
·
Please include the screenshot of your final spreadsheet model here.
Answer:
d. Indicate why this spreadsheet model is a linear programming model.
Answer:
e. Formulate this same model algebraically.
Answer:
f. Use the graphical method to solve this model.
Answer:
g. A new competitor in town has started making wood-framed windows as well. This may force the company to lower the price it charges and so lower the profit made for each wood-framed window. How would the optimal solution change (if at all) if the profit per wood-framed window decreases from $60 to $40? From $60 to $20?
Answer:
h. Doug is considering lowering his working hours, which would decrease the number of wood frames he makes per day. How would the optimal solution change if he only makes 5 wood frames per day?
Answer:
2. The Primo Insurance Company is introducing two new product lines: special risk insurance and mortgages. The expected profit is $5 per unit on special risk insurance and $2 per unit on mortgages.
Management wishes to establish sales quotas for the new product lines to maximize total expected profit. The work requirements are shown below:
a. Identify verbally the decisions to be made, the constraints on these decisions, and the overall measure of performance for the decisions.
Answer:
b. Convert these verbal descriptions of the constraints and the measure of performance into quantitative expressions in terms of the data and decisions.
Answer:
c. Formulate and solve a linear programming model for this problem on a spreadsheet.
·
Please include the screenshot of your final spreadsheet model here.
Answer:
d. Formulate this same model algebraically.
Answer:
3. The Learning Center runs a day camp for 6-10 year olds during the summer. Its manager, Elizabeth Reed, is trying to reduce the center’s operating costs to avoid having to raise the tuition fee. Elizabeth is currently planning what to feed the children for lunch. She would like to keep costs to a minimum, but also wants to make sure she is meeting the nutritional requirements of the children. She has already decided to go with peanut butter and jelly sandwiches, and some combination of apples, milk, and/or cranberry juice. The nutritional content of each food choice and its cost are given in the table that accompanies this problem.
The nutritional requirements are as follows. Each child should receive between 300 and 500 calories, but no more than 30 percent of these calories should come from fat. Each child should receive at least 60 milligrams (mg) of vitamin C and at least 10 grams (g) of fiber.
To ensure tasty sandwiches, Elizabeth wants each child to have a minimum of 2 slices of bread, 1 tablespoon (tbsp) of peanut butter, and 1 tbsp of jelly, along with at least 1 cup of liquid (milk and/or cranberry juice). Elizabeth would like to select the food choices that would minimize cost while meeting all these requirements.
a. Formulate and solve a linear programming model for this problem on a spreadsheet.
·
Please include the screenshot of your final spreadsheet model here.
Answer:
b. Formulate this same model algebraically.
Answer:
5/5
image2
image1
Q1-d
| Wood-framed | Aluminum-framed | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Unit Profit | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Square-feet | Used | Available | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Glass | <= | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Total Profit | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Units Produced | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Q2-c
| Special Risk | Mortgage | ||
| Work-Hours | |||
| Work-Hours per Unit | |||
| Underwriting | |||
| Administration | |||
| Claims | |||
| Sales Quota |
Q3-a
| Bread | Peanut Butter | Jelly | Milk | Juice | |||||||
| (slice) | (tbsp) | Apples | (cup) | ||||||||
| Unit Cost | |||||||||||
| Nutritional Data | Total in Diet | ||||||||||
| Calories | Needed | Maximum | |||||||||
| >= | |||||||||||
| Vitamin C (mg) | |||||||||||
| Fiber (g) | |||||||||||
| Total Cost | |||||||||||
| Diet (ounces) | |||||||||||
| Minimums | |||||||||||
| Fat Calories | of Total Calories | ||||||||||
| Milk and Juice |
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
1 / 11
Study Materials:
Lecture Note 2: LP Basic Concepts
1. This is your lucky day. You have just won a $20,000 prize. You are setting aside $8,000 for taxes and
partying expenses, but you have decided to invest the other $12,000. Upon hearing this news, two
different friends have offered you an opportunity to become a partner in two different
entrepreneurial ventures, one planned by each friend. In both cases, this investment would involve
expending some of your time next summer as well as putting up cash. Becoming a full partner in the
first friend’s venture would require an investment of $10,000 and 400 hours, and your estimated
profit (ignoring the value of your time) would be $9,000. The corresponding figures for the second
friend’s venture are $8,000 and 500 hours, with an estimated profit to you of $9,000. However, both
friends are flexible and would allow you to come in at any fraction of a full partnership you would like.
If you choose a fraction of a full partnership, all the above figures given for a full partnership (money
investment, time investment, and your profit) would be multiplied by this same fraction.
Because you were looking for an interesting summer job anyway (maximum of 600 hours), you
have decided to participate in one or both friends’ ventures in whichever combination would
maximize your total estimated profit. You now need to solve the problem of finding the best
combination.
a. Describe the analogy between this problem and the Wyndor Glass Co. problem discussed in
Section 2.1. Then construct and fill in a table like Table 2.1 for this problem, identifying both
the activities and the resources.
Answer:
As in the Wyndor Glass Co. problem, we want to find the optimal levels of two activities that
compete for limited resources.
Let x1 be the fraction purchased of the partnership in the first friends venture.
Let x2 be the fraction purchased of the partnership in the second friends venture.
The following table gives the data for the problem:
Resource Usage
per Unit of Activity
Amount of
Resource 1 2 Resource Available
Fraction of partnership in
first friends venture
1 0 1
Fraction of partnership in
second friends venture
0 1 1
Money
$10,000 $8,000 $12,000
Summer Work Hours 400 500 600
Unit Profit $9,000 $9,000
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
2 / 11
b. Identify verbally the decisions to be made, the constraints on these decisions, and the overall
measure of performance for the decisions.
Answer:
The decisions to be made are how much, if any, to participate in each venture. The
constraints on the decisions are that you can’t become more than a full partner in either
venture, that your money is limited to $12,000, and time is limited to 600 hours. In
addition, negative involvement is not possible. The overall measure of performance for the
decisions is the profit to be made.
c. Convert these verbal descriptions of the constraints and the measure of performance into
quantitative expressions in terms of the data and decisions.
Answer:
First venture: (fraction of 1st) ≤ 1
Second venture: (fraction of 2nd) ≤ 1
Money: 10,000 (fraction of 1st) + 8,000 (fraction of 2nd) ≤ 12,000
Hours: 400 (fraction of 1st) + 500 (fraction of 2nd) ≤ 600
Nonnegativity: (fraction of 1st) ≥ 0, (fraction of 2nd) ≥ 0
Profit = $9,000 (fraction of 1st) + $9,000 (fraction of 2nd)
d. Formulate a spreadsheet model for this problem. Identify the data cells, the changing cells,
and the objective cell. Also show the Excel equation for each output cell expressed as a
SUMPRODUCT function. Then use Solver to solve this model.
Answer:
Data cells: B2:C2, B5:C6, F5:F6, and B11:C11
Changing cells: B9:C9
Objective cell: F9
Output cells: D5:D6
e. Indicate why this spreadsheet model is a linear programming model.
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
3 / 11
Answer:
This is a linear programming model because the decisions are represented by changing cells
that can have any value that satisfy the constraints. Each constraint has an output cell on the
left, a mathematical sign in the middle, and a data cell on the right. The overall level of
performance is represented by the objective cell and the objective is to maximize that cell.
Also, the Excel equation for each output cell is expressed as a SUMPRODUCT function where
each term in the sum is the product of a data cell and a changing cell.
f. Formulate this same model algebraically.
Answer:
Let x1 = share taken in first friend’s venture
x2 = share taken in second friend’s venture
Maximize P = $9,000×1 + $9,000×2,
subject to x1 ≤ 1
x2 ≤ 1
$10,000×1 + $8,000×2 ≤ $12,000
400×1 + 500×2 ≤ 600 hours
and x1 ≥ 0, x2 ≥ 0.
g. Identify the decision variables, objective function, nonnegativity constraints, functional
constraints, and parameters in both the algebraic version and spreadsheet version of the
model.
Answer:
Algebraic Version
decision variables: x1, x2
functional constraints: x1 ≤ 1
x2 ≤ 1
$10,000×1 + $8,000×2 ≤ $12,000
400×1 + 500×2 ≤ 600 hours
objective function: Maximize P = $9,000×1 + $9,000×2,
parameters: all of the numbers in the above algebraic model
nonnegativity constraints: x1 ≥ 0, x2 ≥ 0
Spreadsheet Version
decision variables: B9:C9
functional constraints: D4:F7
objective function: F9
parameters: B2:C2, B5:C6, F5:F6, and B11:C11
nonnegativity constraints: “Make Unconstrained Variables Nonnegative”
in Solver
h. Use the graphical method by hand to solve this model. What is your total estimated profit?
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
4 / 11
Answer:
Optimal solution = (x1, x2) = (0.667, 0.667). P = $12,000.
2. Independently consider each of the following changes in the Wyndor problem. In each case, apply the
graphical method by hand to this new version of the problem, describe your conclusion, and then
explain how and why the nature of this conclusion is different from the original Wyndor problem.
a. The unit profit for the windows now is $200.
Answer:
When the unit profit for Windows is $300, there are multiple optima, including (2 doors, 6
windows) and (4 doors and 3 windows) and all points inbetween. It is different than the
original unique optimal solution of (2 doors, 6 windows) because windows are now more
profitable, making the solution of (4 doors and 3 windows) equally profitable.
b. To justify introducing these two new products, Wyndor management now requires that the
total number of doors and windows produced per week must be at least 10.
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
5 / 11
Answer:
There is no feasible solution with the added requirement that there must be a total of 10
doors and/or windows.
c. The functional constraints for Plants 2 and 3 now have been inadvertently deleted from the
model.
Answer:
If the constraints for plant 2 and plant 3 are inadvertently removed, then the solution is
unbounded. There is nothing left to prevent making an unbounded number of windows, and
hence making an unbounded profit.
3. Weenies and Buns is a food processing plant that manufactures hot dogs and hot dog buns. They grind
their own flour for the hot dog buns at a maximum rate of 200 pounds per week. Each hot dog bun
requires 0.1 pound of flour. They currently have a contract with Pigland, Inc., which specifies that a
delivery of 800 pounds of pork product is delivered every Monday. Each hot dog requires 1/4 pound
of pork product. All the other ingredients in the hot dogs and hot dog buns are in plentiful supply.
Finally, the labor force at Weenies and Buns consists of five employees working full time (40 hours
per week each). Each hot dog requires three minutes of labor, and each hot dog bun requires two
minutes of labor. Each hot dog yields a profit of $0.20, and each bun yields a profit of $0.10.
Weenies and Buns would like to know how many hot dogs and how many hot dog buns they
should produce each week so as to achieve the highest possible profit.
a. Identify verbally the decisions to be made, the constraints on these decisions, and the overall
measure of performance for the decisions.
Answer:
The decisions to be made are how many frankfurters and buns should be produced. The
constraints are the amounts of flour and pork available, and the hours available to work. In
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
6 / 11
addition, negative production levels are not possible. The overall measure of performance
for the decisions is the profit to be made.
b. Convert these verbal descriptions of the constraints and the measure of performance into
quantitative expressions in terms of the data and decisions.
Answer:
flour: 0.1 (# buns) ≤ 200
pork: 0.25 (# frankfurters) ≤ 800
work hours: 3 (# frankfurters) + 2 (# buns) ≤ 12,000
Nonnegativity: (# frankfurters) ≥ 0, (# buns) ≥ 0
Profit = $0.40 (# frankfurters) + $0.20 (# buns)
c. Formulate and solve a linear programming model for this problem on a spreadsheet.
Answer:
d. Formulate this same model algebraically.
Answer:
Let F = # of frankfurters to produce
B = # of buns to produce
Maximize P = $0.40F + $0.20B,
subject to 0.1B ≤ 200
0.25F ≤ 800
3F + 2B ≤ 12,000
and F ≥ 0, B ≥ 0.
e. Use the graphical method to solve this model. Decide yourself whether you would prefer to
do this by hand or by using the Graphical Linear Programming and Sensitivity Analysis module
in your Interactive Management Science Modules.
Answer:
Optimal Solution: (F, B) = (x1, x2) = (3200, 1200) and P = $1520.
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
7 / 11
4. Nutri-Jenny is a weight-management center. It produces a wide variety of frozen entrées for
consumption by its clients. The entrées are strictly monitored for nutritional content to ensure that
the clients are eating a balanced diet. One new entrée will be a beef sirloin tips dinner. It will consist
of beef tips and gravy, plus some combination of peas, carrots, and a dinner roll. Nutri-Jenny would
like to determine what quantity of each item to include in the entrée to meet the nutritional
requirements, while costing as little as possible. The nutritional information for each item and its cost
are given in the following table.
The nutritional requirements for the entrée are as follows: (1) it must have between 280 and 320
calories, (2) calories from fat should be no more than 30 percent of the total number of calories, and
(3) it must have at least 600 IUs of vitamin A, 10 milligrams of vitamin C, and 30 grams of protein.
Furthermore, for practical reasons, it must include at least 2 ounces of beef, and it must have at least
half an ounce of gravy per ounce of beef.
a. Formulate and solve a linear programming model for this problem on a spreadsheet. by using
the Excel Solver.
Answer:
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
8 / 11
b. Formulate this same model algebraically.
Answer:
Let B = ounces of beef tips in diet,
G = ounces of gravy in diet,
P = ounces of peas in diet,
C = ounces of carrots in diet,
R = ounces of roll in diet.
Minimize Z = $0.40B + $0.35G + $0.15P + $0.18C + $0.10R
subject to 54B + 20G + 15P + 8C + 40R ≥ 280
54B + 20G + 15P + 8C + 40R ≤ 320
19B + 15G + 10R ≤ 0.3(54B + 20G + 15P + 8C + 40R)
15P + 350C ≥ 600
G + 3P + C ≥ 10
8B + P + C + R ≥ 30
B ≥ 2
G ≥ 0.5B
and B ≥ 0, G ≥ 0, P ≥ 0, C ≥ 0, R ≥ 0.
5. You are given the following linear programming model in algebraic form, with x 1 and x 2 as the
decision variables:
Minimize 𝐶𝑜𝑠𝑡 = 40𝑥 + 50𝑥
subject to
Constraint 1: 2𝑥 + 3𝑥 ≥ 30
Constraint 2: 𝑥 + 𝑥 ≥ 12
Constraint 3: 2𝑥 + 𝑥 ≥ 20
and 𝑥 ≥ 0 𝑥 ≥ 0
a. Use the graphical method to solve this model.
Answer:
Optimal Solution: (x1, x2) = (7.5, 5) and C = 550.
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
9 / 11
b. How does the optimal solution change if the objective function is changed to 𝐶𝑜𝑠𝑡 = 40𝑥 +
70𝑥 ?
Answer:
Optimal Solution: (x1, x2) = (15, 0) and C = 600.
c. How does the optimal solution change if the third functional constraint is changed to 2𝑥 +
𝑥 ≥ 15?
Answer:
Optimal Solution: (x1, x2) = (6, 6) and C = 540.
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
10 / 11
d. Now incorporate the original model into a spreadsheet and use Solver to solve this model.
Answer:
e. Use Excel to do parts b and c.
Answer:
Module 2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
11 / 11
Part b)
Part c)
>Q -d
00
$9,000 Resource ,000
,000
,000
$12,000 0
0
0
00
<=
600
First Friend 0.667 $12,000 <=
<=
1
2
1
First Friend
Second Friend
Unit Profit
$9,
0
Resource
Resource Usage
Used
Available
Money
$
10
$
8
$
12
<=
Work Hours
40
5
6
Second Friend
Total Profit
Share
0.667
1
Q
-c
| Frankfurters | Buns | |||||
| $0.40 | $0. | 20 | ||||
| Flour | 0.1 | 120 | 200 | |||
| Pork | 0.25 | 800 | ||||
| Decision | 3,200 | 1,200 | $1,520 |
Q4-a
| Beef | Gravy | Peas | Carrots | Roll | ||||||||||||||||
| Unit Cost | $0.35 | $0. | 15 | $0. | 18 | $0.10 | ||||||||||||||
| (per ounce) | ||||||||||||||||||||
| Nutritional Data (per ounce) | Total in Diet | Needed | Maximum | |||||||||||||||||
| Calories | 54 | 320 | 96 | >= | 280 | |||||||||||||||
| Fat Calories | 19 | 95.9999999969 | ||||||||||||||||||
| Vitamin A (IU) | 3 | 50 | ||||||||||||||||||
| Vitamin C (mg) | 12.38 | |||||||||||||||||||
| Protein (g) | 29.9999999984 | 30 | ||||||||||||||||||
| Total Cost | ||||||||||||||||||||
| Diet (ounces) | 2.94 | 1.47 | 3.11 | 1.58 | 1.82 | $2.62 | ||||||||||||||
| Minimums | ||||||||||||||||||||
| 96.0000000029 | 30% | of Total Calories | ||||||||||||||||||
| 50% | of Beef |
Q5-d
| Activity 1 | Activity 2 | ||||||||||
| Totals | Limit | ||||||||||
| Constraint 1 | |||||||||||
| Constraint 2 | 12.5 | ||||||||||
| Constraint 3 | |||||||||||
| 7.5 | 550 |
Q5-e(b)
| 70 |
Q5-e(c)
| 540 |
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
