Answer To: Trying to get some assistance on completing this assignment
Shakeel answered on Mar 02 2021
Q1
For each multiple choice question, shade the correct answer yellow.
For fill in the blank questions, there is a yellow box that you should type your answer into.
1) Which best describes the optimal cost (i.e., the lowest possible cost at which you can order all 10,000 feet of molding)?
a) It is less than $20,000
b) It is greater than or equal to $20,000 but less than $22,000
c ) It is greater than or equal to $22,000 but less than $24,000
d) It is greater than or equal to $24,000 but less than $26,000
e) It is greater than or equal to $26,000
2) What is the optimal number of feet of molding (including all three types) that are ordered from Vendor B?
3,500
3) True or False: There is 0 slack for Vendor C.
4) Make a copy of your model into a new tab, and assume for this question only that the total max capacity for Vendor D decreases from 3,000 to 2,500 feet.
Given this decrease in the capacity, what is the optimal cost of ordering all 10,000 feet of molding?
$20,150
5) Make a copy of your model into a new tab, and assume for this question only that the cost per foot of Type 3 molding for Vendor B increases from $3.00 to $5.00 (capacity remains as in the original problem).
Given this unit cost increase, what is the optimal cost of ordering all 10,000 feet of molding?
$20,200
6) Given the new requirement, what is your optimal cost?
$20,100
7) Given the new requirement, what is the optimal quantity of Type 2 molding to order from Vendor D?
3,000 units
8) Given the new requirements, what is your optimal cost?
$19,150
9) Given the new requirements, what is the optimal total quantity of molding (including all types) that you will order from Vendor D?
2,500
10) In addition to the new requirements described above for Part 3, assume that Vendor D also has a capacity change.
They can now supply a max capacity of 1,500 for Type 1 and 1,500 for Type 3 (and they still cannot supply any amount for Type 2).
Given the new requirement, what is the optimal total quantity of molding (including all types) that you will order from Vendor D?
1,500
Ordering Crown Molding from Vendors *NOTE: Your work will be in other tabs, but you must fill in the questions at the bottom of this sheet.
In this scenario, you work at a construction company who builds homes. Crown molding is very popular these days and every month you need to place orders with vendors who can ship you various styles of custom molding. This month each of five possible vendors have provided you with 1.) a fixed cost for which you can purchase one foot of each type of molding, and 2.) the maximum number of feet that you can order from them due to their own capacity constraints. Assume that vendors will not sell you fractions of a foot of molding (in other words they will only sell you a whole number of feet, or an integer quantity).
This month, you have estimated that you will need to purchase exactly 10,000 feet of molding, which includes 2,500 feet of Type 1 molding, 5,000 feet of Type 2, and 2,500 feet of type 3. Your task is to determine how to purchase all 10,000 feet of molding at the lowest possible cost.
Your goal is to find the total number of feet of each of the three types of molding that you should purchase from each vendor in order to minimize your the total cost. You can use the problem formulation on the next sheet as a starting point.
Note: As usual, to ensure an optimal solution, your problem formulation should be linear.
Part 2: Copy your model (used in questions 1-3) into a new tab called Q1P2 and make the following modifications. For the following 2 questions, assume that you have been given a new requirement (in addition to the previous requirements from questions 1-3). You cannot order a total quantity (which includes all three types of molding) from Vendor C that is larger than that of Vendor D.
Part 3: Copy your original model (used in questions 1-3) into a new tab called Q1P3 and make the following modifications. For the following 2 questions, assume that you have been given a new requirement (in addition to the previous requirements from questions 1-3). Vendor D has discontinued their Type 2 molding, and you will no longer be able to purchase it from them. Because of this inconvenience, they have dropped their price for Type 3 molding from $3.00 to $2.50. They have not changed their overall capacity constraint.
Q1Start
Ordering Crown Molding
Cost per foot of molding:
Vendor A Vendor B Vendor C Vendor D Vendor E
Molding Type 1 $ 1.00 $ 2.00 $ 3.00 $ 3.00 $ 5.00
Molding Type 2 $ 2.00 $ 2.50 $ 1.80 $ 1.80 $ 1.90
Molding Type 3 $ 2.00 $ 3.00 $ 3.00 $ 3.00 $ 5.00
Ordering plan (in feet):
Sum Number of Feet to Be Ordered
Molding Type 1 - 0 2,500 - 0 - 0 - 0 2,500 2,500
Molding Type 2 - 0 - 0 1,000 3,000 1,000 5,000 5,000
Molding Type 3 1,500 1,000 - 0 - 0 - 0 2,500 2,500
Sum 1,500 3,500 1,000 3,000 1,000
Vendor Max Capacities (in feet and includes all three types of molding): 1,500 5,000 1,000 3,000 6,000
Total cost $20,100
Q1Start2
***This is an alternate starting format for Q1
Vendor A Vendor B Vendor C Vendor D Vendor E
Ordering plan (in feet): Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3
1500 0 0 1000 0 2500 0 1000 0 0 3000 0 0 1000 0 Total Cost
Cost: $ 1.00 $ 2.00 $ 2.00 $ 2.00 $ 2.50 $ 3.00 $ 3.00 $ 1.80 $ 3.00 $ 3.00 $ 1.80 $ 3.00 $ 5.00 $ 1.90 $ 5.00 $ 20,100.00
Amount to order
Molding Type 1 2500 2,500
Molding Type 2 5000 5,000
Molding Type 3 2500 2,500
Total Molding 10000 10,000
Vendor A 1500 1,500
Vendor B 3500 5,000
Vendor C 1000 1,000
Vendor D 3000 3,000
Vendor E 1000 6,000
Ans 4
***This is an alternate starting format for Q1
Vendor A Vendor B Vendor C Vendor D Vendor E
Ordering plan (in feet): Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3
1499.9998690328 0 0 1000.0001309672 0 2499.9998908606 0 1000 0 0 2500 0 0 1500.0000124176 0 Total Cost
Cost: $ 1.00 $ 2.00 $ 2.00 $ 2.00 $ 2.50 $ 3.00 $ 3.00 $ 1.80 $ 3.00 $ 3.00 $ 1.80 $ 3.00 $ 5.00 $ 1.90 $ 5.00 $ 20,150.00
Amount to order
Molding Type 1 2500 2,500
Molding Type 2 5000.0000124176 5,000
Molding Type 3 2499.9998908606 2,500
Total Molding 9999.9999032783 10,000
Vendor A 1499.9998690328 1,500
Vendor B 3500.0000218279 5,000
Vendor C 1000 1,000
Vendor D 2500 2,500
Vendor E 1500.0000124176 6,000
Ans 5
***This is an alternate starting format for Q1
Vendor A Vendor B Vendor C Vendor D Vendor E
Ordering plan (in feet): Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3
0 0 1499.9998850981 2499.999992142 0 0 0 0.0003637978 999.9997051201 0 2999.9999404125 0 0 1999.9997073796 0 Total Cost
Cost: $ 1.00 $ 2.00 $ 2.00 $ 2.00 $ 2.50 $ 5.00 $ 3.00 $ 1.80 $ 3.00 $ 3.00 $ 1.80 $ 3.00 $ 5.00 $ 1.90 $ 5.00 $ 20,200.00
Amount to order
Molding Type 1 2499.999992142 2,500
Molding Type 2 5000.0000115898 5,000
Molding Type 3 2499.9995902182 2,500
Total Molding 9999.9995939499 10,000
Vendor A 1499.9998850981 1,500
Vendor B 2499.999992142 5,000
Vendor C 1000.0000689178 1,000
Vendor D 2999.9999404125 3,000
Vendor E 1999.9997073796 6,000
Q1P2
***This is an alternate starting format for Q1
Vendor A Vendor B Vendor C Vendor D Vendor E
Ordering plan (in feet): Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3
1500 0 0 1000 0 2500 0 1000 0 0 3000 0 0 1000 0 Total Cost
Cost: $ 1.00 $ 2.00 $ 2.00 $ 2.00 $ 2.50 $ 3.00 $ 3.00 $ 1.80 $ 3.00 $ 3.00 $ 1.80 $ 3.00 $ 5.00 $ 1.90 $ 5.00 $ 20,100.00
Amount to order
Molding Type 1 2500 2,500
Molding Type 2 5000 5,000
Molding Type 3 2500 2,500
Total Molding 10000 10,000
Vendor A 1500 1,500
Vendor B 3500 5,000
Vendor C 1000 1,000
Vendor D 3000 3,000
Vendor E 1000 6,000
Q1P3
***This is an alternate starting format for Q1
Vendor A Vendor B Vendor C Vendor D Vendor E
Ordering plan (in feet): Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3
1500 0 0 1000 0 0 0 1000 0 0 0 2499.9998812564 0 4000 0 Total Cost
Cost: $ 1.00 $ 2.00 $ 2.00 $ 2.00 $ 2.50 $ 3.00 $ 3.00 $ 1.80 $ 3.00 $ 3.00 $ - 0 $ 2.50 $ 5.00 $ 1.90 $ 5.00 $ 19,150.00
Amount to order
Molding Type 1 2500 2,500
Molding Type 2 5000 5,000
Molding Type 3 2499.9998812564 2,500
Total Molding 9999.9998812564 10,000
Vendor A 1500 1,500
Vendor B 1000 5,000
Vendor C 1000 1,000
Vendor D 2499.9998812564 3,000
Vendor E 4000 6,000
Q1P3 (for Q.10)
***This is an alternate starting format for Q1
Vendor A Vendor B Vendor C Vendor D Vendor E
Ordering plan (in feet): Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3 Molding Type 1 Molding Type 2 Molding Type 3
1499.9999961979 0 0 999.9999949856 0 1000 0 1000.0000116369 0 0 0 1500 0 3999.9999883631 0 Total Cost
Cost: $ 1.00 $ 2.00 $ 2.00 $ 2.00 $ 2.50 $ 3.00 $ 3.00 $ 1.80 $ 3.00 $ 3.00 $ - 0 $ 2.50 $ 5.00 $ 1.90 $ 5.00 $ 19,650.00
Amount to order
Molding Type 1 2499.9999911835 2,500
Molding Type 2 5000 5,000
Molding Type 3 2500 2,500
Total Molding 9999.9999911835 10,000
Vendor A 1499.9999961979 1,500
Vendor B 1999.9999949856 5,000
Vendor C 1000.0000116369 1,000
Vendor D 1500 3,000
Vendor E 3999.9999883631 6,000
Q2
For each question, type your answer into the yellow box.
Question 1: Which best describes the optimal cost of a box of wine bottles (i.e. the optimal value of the objective function)?
a) It is less than $90
b) It is greater than or equal to $90 but less than $95
c) It is greater than or equal to $95 but less than $100
d) It is greater than or equal to $100 but less than $105
e) It is greater than or equal to $105
Question 2: How many items from winery 2 are included in this box of wines?
1
Question 3: How many bottles of Merlot are included in this box of wines?
1
Question 4: With the optimal assignment of wine bottles given this new requirement, what is total cost of a box of wine (i.e. the optimal value of the objective function)?
$88.00
Question 5: Which winery did not have any wines selected in the optimal solution?
Winery 5
Question 6: With the optimal assignment of wine bottles given this new requirement, what is...