Help with Optimization and Decision Support Modeling for Business HW1
Redhand
OSCM-471571_Homework1_Sp23.docxOSCM 471/571 Optimization and Decision Support Modeling for Business
Homework 1, Spring 2023
Notice for Homework 1
Instructor: Seokjun Youn ( [email protected] )
· 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.
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
