Help with Optimization and Decision Support Modeling for Business HW2

Module 3-1 Practice Problems

OSCM 471/571 Optimization and Decision Support Modeling for Business

Study Materials:

Lecture 3-1: LP Formulation and Applications

1. (Resource-Allocation Problem) A cargo plane has three compartments for storing cargo: front, center, and back. These compartments have capacity limits on both weight and space, as summarized below.

Furthermore, the weight of the cargo in the respective compartments must be the same proportion of that compartment’s weight capacity to maintain the balance of the airplane.

The following four cargoes have been offered for shipment on an upcoming flight as space is available.

Any portion of these cargoes can be accepted. The objective is to determine how much (if any) of each cargo should be accepted and how to distribute each among the compartments to maximize the total profit for the flight.

a. Formulate and solve a linear programming model for this mixed problem on a spreadsheet.

Answer:

b. Express the model in algebraic form.

Answer:

Let xij = tons of cargo i stowed in compartment j ( i = 1,2,3,4; j = F, C, B) Maximize Profit = $320( x1F + x1C + x1B) + $400( x2F + x2C + x2B) + $360( x3F + x3C + x3B) + $290( x4F + x4C + x4B) subject to x1F + x2F + x3F + x4F ≤ 12 tons x1C + x2C + x3C + x4C ≤ 18 tons x1B + x2B + x3B + x4B ≤ 10 tons x1F + x1C + x1B ≤ 20 tons x2F + x2C + x2B ≤ 16 tons x3F + x3C + x3B ≤ 25 tons x4F + x4C + x4B ≤ 13 tons 500 x1F + 700 x2F + 600 x3F + 400 x4F ≤ 7,000 cubic feet 500 x1C + 700 x2C + 600 x3C + 400 x4C ≤ 9,000 cubic feet 500 x1B + 700 x2B + 600 x3B + 400 x4B ≤ 5,000 cubic feet ( x1F + x2F + x3F + x4F) / 12 = ( x1C + x2C + x3C + x4C) / 18 ( x1F + x2F + x3F + x4F) / 12 = ( x1B + x2B + x3B + x4B) / 10 and x1F ≥ 0, x1C ≥ 0, x1B ≥ 0, x2F≥ 0, x2C ≥ 0, x2B ≥ 0, x3F ≥ 0, x3C ≥ 0, x3B ≥ 0, x4F ≥ 0, x4C ≥ 0, x4B ≥ 0.

2. (Cost-Benefit-Tradeoff-Problem) Web Mercantile sells many household products through an online catalog. The company needs substantial warehouse space for storing its goods. Plans now are being made for leasing warehouse storage space over the next five months. Just how much space will be required in each of these months is known. However, since these space requirements are quite different, it may be most economical to lease only the amount needed each month on a month-by-month basis. On the other hand, the additional cost for leasing space for additional months is much less than for the first month, so it may be less expensive to lease the maximum amount needed for the entire five months. Another option is the intermediate approach of changing the total amount of space leased (by adding a new lease and/or having an old lease expire) at least once but not every month.

The space requirement and the leasing costs for the various leasing periods are as follows.

The objective is to minimize the total leasing cost for meeting the space requirements.

a. Indicate why this is a cost–benefit–trade-off problem by identifying both the activities and the benefits being sought from these activities.

Answer:

The activities are leasing space in each month for a number of months. The benefit is meeting the space requirements for each month.

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 space to lease and for how many months. The constraints on these decisions are the minimum required space. The overall measure of performance is cost which is to be minimized.

c. Formulate a spreadsheet model for this problem. Identify the data cells, the changing cells, the objective cell, and the other output cells. Also show the Excel equation for each output cell expressed as a SUMPRODUCT function. Then use Solver to solve the model.

Answer:

Data cells: B4:P8, B10:P10, and S4:S8 Changing cells: B13:P13 Objective cell: S13 Output cells: Q4:Q8

d. Summarize the model in algebraic form.

Answer:

Let xij = square feet of space leased in month i for a period of j months. for i = 1, ... , 5 and j = 1, ... , 6- i. Minimize C = $650( x11 + x21 + x31 + x41 + x51) + $1,000( x12 + x22 + x32 + x42) +$1,350( x13 + x23 + x33) + $1,600( x14 + x24) + $1,900 x15 subject to x11 + x12 + x13 + x14 + x15 ≥ 30,000 square feet x12 + x13 + x14 + x15 + x21 + x22 + x23 + x24 ≥ 20,000 square feet x13 + x14 + x15 + x22 + x23 + x24 + x31 + x32 + x33 ≥ 40,000 sq. feet x14 + x15 + x23 + x24 + x32 + x33 + x41 + x42 ≥ 10,000 square feet x15 + x24 + x33 + x42 + x51 ≥ 50,000 square feet and xij ≥ 0, for i = 1, ... , 5 and j = 1 , ... , 6- i.

3. (Mixed Problem) Comfortable Hands is a company that features a product line of winter gloves for the entire family—men, women, and children. They are trying to decide what mix of these three types of gloves to produce.

Comfortable Hands’s manufacturing labor force is unionized. Each full-time employee works a 40-hour week. In addition, by union contract, the number of full-time employees can never drop below 20. Nonunion, part-time workers also can be hired with the following union-imposed restrictions: (1) each part-time worker works 20 hours per week and (2) there must be at least two full-time employees for each part-time employee.

All three types of gloves are made out of the same 100 percent genuine cowhide leather. Comfortable Hands has a longterm contract with a supplier of the leather and receives a 5,000-square-foot shipment of the material each week. The material requirements and labor requirements, along with the gross profit per glove sold (not considering labor costs), are given in the following table.

Each full-time employee earns $13 per hour, while each part-time employee earns $10 per hour. Management wishes to know what mix of each of the three types of gloves to produce per week, as well as how many full-time and part-time workers to employ. They would like to maximize their net profit —their gross profit from sales minus their labor costs.

a. Formulate and solve a linear programming model for this problem on a spreadsheet.

Answer:

b. Summarize this formulation in algebraic form.

Answer:

Let M =number of men’s gloves to produce per week, W = number of women’s gloves to produce per week, C = number of children’s gloves to produce per week, F = number of full-time workers to employ, PT = number of part-time workers to employ. Maximize Profit = $8M + $10 W + $6 C – $13(40) F – $10(20) PT subject to 2 M + 1.5 W + C ≤ 5,000 square feet 30 M + 45 W + 40 C ≤ 40(60) F + 20(60) PT hours F ≥ 20 F ≥ 2 PT and M ≥ 0, W ≥ 0, C ≥ 0, F ≥ 0 (and integer), PT ≥ 0 (and integer).

4. (Transportation Problem: Producing and Distributing AEDs) Heart Start produces automated external defibrillators in each of two different plants (A and B). The unit production costs and monthly production capacity of the two plants are indicated in the table below. The automated external defibrillators are sold through three wholesalers. The shipping cost from each plant to the warehouse of each wholesaler along with the monthly demand from each wholesaler are also indicated in the table. The management of Heart Start now has asked their top management scientist (you) to address the following two questions. How many automated external defibrillators should be produced in each plant, and how should they be distributed to each of the three wholesaler warehouses so as to minimize the combined cost of production and shipping? Formulate and solve a linear programming model in a spreadsheet.

	Unit Shipping Cost	Unit	Monthly
	Warehouse 1	Warehouse 2	Warehouse 3	Production Cost	Production Capacity
Plant A	$22	$14	$30	$600	100
Plant B	$16	$20	$24	$625	120
Monthly Demand	80	60	70

Answer:

This is a transportation problem as described in Section 3.5 of the text. The Solver information and solved spreadsheet are shown below.

Thus, from Plant A they should ship 40 to Warehouse 1 and 60 units to Warehouse 2, from Plant B they should ship 40 units to Warehouse 1 and 70 units to Warehouse 3, giving an overall total cost of $132,790.

5. (Transportation Problem) The Childfair Company has three plants producing child push chairs that are to be shipped to four distribution centers. Plants 1, 2, and 3 produce 12, 17, and 11 shipments per month, respectively. Each distribution center needs to receive 10 shipments per month. The distance from each plant to the respective distribution centers is given below.

The freight cost for each shipment is $100 plus 50 cents/mile. How much should be shipped from each plant to each of the distribution centers to minimize the total shipping cost? Formulate this problem as a transportation problem on a spreadsheet and then use Solver to obtain an optimal solution.

Answer:

6. (Transportation Problem) The Onenote Co. produces a single product at three plants for four customers. The three plants will produce 60, 80, and 40 units, respectively, during the next week. The firm has made a commitment to sell 40 units to customer 1, 60 units to customer 2, and at least 20 units to customer 3. Both customers 3 and 4 also want to buy as many of the remaining units as possible. The net profit associated with shipping a unit from plant i for sale to customer j is given by the following table.

Management wishes to know how many units to sell to customers 3 and 4 and how many units to ship from each of the plants to each of the customers to maximize profit. Formulate and solve a spreadsheet model for this problem.

Answer:

7. (Assignment Problem) Consider the assignment problem having the following cost table.

The optimal solution is A-3, B-1, C-2, with a total cost of $10. Formulate this problem on a spreadsheet and then use Solver to obtain the optimal solution identified above.

Answer:

8. (Assignment Problem: Bidding for Classes) In the MBA program at a prestigious university in the Pacific Northwest, students bid for electives in the second year of their program. Each student has 100 points to bid (total) and must take two electives. There are four electives available: Management Science, Finance, Operations Management, and Marketing. Each class is limited to 5 students. The bids submitted for each of the 10 students are shown in the table below.

Student	Management Science	Finance	Operations Management	Marketing
George	60	10	10	20
Fred	20	20	40	20
Ann	45	45	5	5
Eric	50	20	5	25
Susan	30	30	30	10
Liz	50	50	0	0
Ed	70	20	10	0
David	25	25	35	15
Tony	35	15	35	15
Jennifer	60	10	10	20

a. Formulate and solve a spreadsheet model to determine an assignment of students to classes so as to maximize the total bid points of the assignments.

Answer:

The Solver information and solved spreadsheet are shown below.

Thus, the 1’s in Assignment (C18:F27) show the assignments that should be made, achieving a total of 705 points.

b. Does the resulting solution seem like a fair assignment?

Answer:

No. For example, Eric did not get into Management Science despite bidding 50 points, while Ann got in with only 45 points. Also, Eric got into classes worth only 45 total bid points to him while Liz got classes worth 100 bid points to her.

c. Which alternative objectives might lead to a fairer assignment?

Answer:

Perhaps maximizing the minimum total number of bid points achieved by each student.

2 / 10