- Solve questions C and D. In particular pay attention to carefully developing the mathematical formulation before putting everything into Excel Solver.

**SUBMIT Homework 1 – Part II – see the instructions above and below**

1) Submit your write-up file as a "pdf" file. Make sure to convert your document files to pdf before submitting. Pay attention to the deliverables outlined in the syllabus. Hand-written solutions will NOT be accepted.

2) Submit your Excel Solver solutions. There are two problems in this assignment so you should be submitting two Excel files.

DS 655– Sustainable Supply Chain Management Instructor: Leyla Ozsen

HOMEWORK 1

NOTES: To get any points, you will have to attempt all questions (per syllabus). To get full marks, follow the guidelines provided on the deliverables in the syllabus.

PROBLEM A

1. Refer to the Product-Mix Problem (Juice Problem) that we discussed in the first lecture. Both the mathematical formulation and the Excel Solver solution to that problem are posted on iLearn. Now assume that the products are produced in one-gallon containers instead. All other parameters of the problem stay the same except the retail price for each type of product, given in Table 1 below:

Product Retail Price Per Gallon

Orange Juice $3.00 Grapefruit juice $2.70 Pineapple juice $2.40

All-in-One $3.30

Table 1: Retail price for each juice product sold in gallon size containers.

Please note that the retail price of the new gallon-size products is three times the price of the old quart-size products. The cost per gallon of each type of juice however stays the same. Do you expect to make more profit or less profit? Explain your answer without re-solving the problem. This step allows you to do some sanity check on the optimal solution that you will obtain later on, using Excel Solver.

2. Now, assume that the recipe for the all-in-one juice is also modified such that half of it is orange juice, one eights is grapefruit juice, and three eights is pineapple juice. Modify the Excel worksheet to address all the modifications explained thus far.

HINT 1: The Excel Solver solution to the original problem will be made available on iLearn so you can choose to modify that one. HINT 2: You will need to hit solve button every time you make modifications to the worksheet for Excel solver algorithm to run and obtain the optimal solution.

Once done organizing the worksheet then copy paste the table from Excel into Word showing the setup of your worksheet. DO NOT turn in the EXCEL worksheet. All homework answers should be in one file formatted as pdf.

3. What is the optimal solution? (Use solver to determine the solution) Does the solution make sense?

4. What is the optimal objective function value? (Use solver to determine the optimal objective function value) Does the new optimal profit make sense?

PROBLEM B Refer to the transportation problem that we discussed in the second lecture. The mathematical

formulation is provided in the Linear Programming Examples Solutions file on iLearn. Use Excel Solver or Open Solver to obtain the optimal solution to this problem. For ease, a template for the problem is already provided on iLearn. I recommend that you use the worksheet, named, short form. If you are having trouble with that form, start with the long form first and then attempt the short form. Once done organizing the worksheet then copy paste the table from Excel into Word showing the setup of your worksheet; the optimal solution and objective function values should be visible. DO NOT turn in the EXCEL worksheet. All homework answers should be in one file and formatted as pdf.

For problems C and D, address each of the following parts:

a. Define the decision variables using complete sentences b. Write down the objective function in words c. Write down the constraints using complete sentences d. Express the objective function mathematically using the decision variables defined in part a. e. Express the constraints mathematically using the decision variables defined in part a; see

slides for an example. f. Use Excel Solver or Open Solver to obtain the optimal solution. Once done organizing

the worksheet then copy paste the table from Excel into Word showing the setup of your worksheet; the optimal solution and objective function values should be visible. DO NOT turn in the EXCEL worksheet. All homework answers should be in one file and formatted as pdf.

PROBLEM C: Resource Allocation Problem A small firm makes three similar products, which all follow the same three-step process, con-

sisting of milling, inspection, and drilling. Product A requires 12 minutes of milling, 5 minutes for inspection, and 10 minutes of drilling per unit; product B requires 10 minutes of milling, 4 minutes for inspection, and 8 minutes of drilling per unit; product C requires 8 minutes of milling, 4 min- utes for inspection, and 16 minutes of drilling. The department has 20 hours available during the next period for milling, 15 hours for inspection, and 24 hours for drilling. Product A contributes $2.4 per unit to profit, product B contributes $2.5 per unit, and product C contributes $3.0 per unit. Determine the optimal mix of products in terms of maximizing contribution to profits for the period. Then, find the range of optimality for the coefficient of each variable.

PROBLEM D: Housing Development Problem The seaside city of Finike is faced with a severe budget shortage. Seeking a long-term solution,

the city votes to improve the tax base by replacing an underutilized housing area with a modern development. The project involves two phases: (1) demolishing substandard houses to provide land for the new development, and (2) building the new development. The city needs to identify the development plan with the best tax collection scheme. The following is a summary of the situation. (a) As many as 300 houses can be demolished. Each demolition costs $2000 and frees up a 0.25 acre lot for development. (b) Lot sizes needed for the new single-, double-, and triple-family homes (units) are 0.18, 0.28 and 0.40 acre respectively. (c) In the new development the single-family units should be at least 20% of the total. The ratio of triple-family units to double-family units should be at most 1 to 2. (d) The tax levied per unit for single, double, and triple units is $1000, $1900, and $2700, respectively. (e) The construction cost per unit for single, double, and triple units is $50,000, $70,000, and $130,000, respectively. Financing through a local bank can amount to a maximum of $15 million.

,

IKEA CHAIR PROBLEM | |||||||

Decision Variables | IVOR | BROR | Total Profit | ||||

Values | 10 | 10 | 250 | ||||

Objective Function Coefficients | 10 | 15 | |||||

Constraints | Coefficients | LHS | RHS | ||||

Demand | 1 | 1 | 20 | <= | 20 | ||

Labor | 5 | 10 | 150 | <= | 150 | ||

Raw Mat'l | 10 | 6 | 160 | <= | 180 |

Microsoft Excel 10.0 Sensitivity Report | |||||||

Worksheet: [LP Examples.xls]IKEA | |||||||

Report Created: 10/7/2003 10:26:39 AM | |||||||

Adjustable Cells | |||||||

Final | Reduced | Objective | Allowable | Allowable | |||

Cell | Name | Value | Cost | Coefficient | Increase | Decrease | |

$C$5 | Values IVOR | 10 | 0 | 10 | 5 | 2.5 | |

$D$5 | Values BROR | 10 | 0 | 15 | 5 | 5 | |

Constraints | |||||||

Final | Shadow | Constraint | Allowable | Allowable | |||

Cell | Name | Value | Price | R.H. Side | Increase | Decrease | |

$E$11 | Demand LHS | 20 | 5 | 20 | 1.4285714286 | 5 | |

$E$12 | Labor LHS | 150 | 1 | 150 | 50 | 25 | |

$E$13 | Raw Mat'l LHS | 160 | 0 | 180 | 1E+30 | 20 |

DECISION VARIABLES | ||||||||

OJ | GJ | PJ | AIO | Total Profit | ||||

Price per gallon | $ 3.00 | $ 2.70 | $ 2.40 | $ 3.30 | $ 1,230.00 | |||

Cost per gallon | $ 2.00 | $ 1.60 | $ 1.40 | $ 1.72 | ||||

Profit per gallon | $ 1.00 | $ 1.10 | $ 1.00 | $ 1.58 | ||||

Number of containers | 133.3333333333 | 233.3333333333 | 0 | 533.3333333333 | ||||

CONSTRAINTS | LHS | RHS | ||||||

OJ availability | 1 | 0 | 0 | 0.50 | 400 | <= | 400 | |

GJ availability | 0 | 1 | 0 | 0.13 | 300 | <= | 300 | |

PJ availability | 0 | 0 | 1 | 0.38 | 200 | <= | 200 | |

GJ percentage | -0.3 | 0.7 | -0.3 | -0.30 | -36.6666666667 | <= | 0 | |

OJ/PJ ratio | 5 | 0 | -7 | 0 | 666.6666666667 | >= | 0 |

,

INPUTS | OBJECTIVE FUNCTION | |||||||||

Cost | $681,010 | |||||||||

Unit Shipment Cost | Warehouses | |||||||||

CA | TX | OH | Supply | |||||||

Plants | CO | 1268 | 1083 | 1331 | 250 | |||||

SC | 2843 | 1194 | 605 | 400 | ||||||

Demand | 225 | 240 | 185 | |||||||

DECISION VARIABLES | ||||||||||

Amount Shipped | Warehouses | SUPPLY CONSTRAINT | ||||||||

CA | TX | OH | Total Shipment | Constraint Sign | RHS | |||||

Plants | CO | 225 | 25 | 0 | 250 | <= | 250 | |||

SC | 0 | 215 | 185 | 400 | <= | 400 | ||||

DEMAND CONSTRAINT | ||||||||||

Total Shipment | 225 | 240 | 185 | |||||||

Constraint Sign | >= | >= | >= | |||||||

RHS | 225 | 240 | 185 |

We are a professional custom writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework.

Yes. We have posted over our previous orders to display our experience. Since we have done this question before, we can also do it for you. To make sure we do it perfectly, please fill our Order Form. Filling the order form correctly will assist our team in referencing, specifications and future communication.

1. Click on the “**Place order** tab at the top menu or “**Order Now**” icon at the
bottom and a new page will appear with an order form to be filled.

2. Fill in your paper’s requirements in the "**PAPER INFORMATION**" section
and click “**PRICE CALCULATION**” at the bottom to calculate your order
price.

3. Fill in your paper’s academic level, deadline and the required number of pages from the drop-down menus.

4. Click “**FINAL STEP**” to enter your registration details and get an account
with us for record keeping and then, click on “**PROCEED TO CHECKOUT**”
at the bottom of the page.

5. From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.

**Why Hire Safehomework.com writers to do your paper?**

- Quality- We are experienced and have access to ample research materials.
- We write plagiarism Free Content
- Confidential- We never share or sell your personal information to third parties.
- Support- Chat with us today! We are always waiting to answer all your questions.