# Formulate this transportation problem algebraically and use Excelâ€™s Solver to determine the o

ASSIGNMENT # 2

Transportation and Integer/Binary Programming Problems

ADM2302 students are reminded that submitted assignments must be typed (i.e. can NOT be hand written), neat, readable, and well-organized. Assignment marks will be adjusted for sloppiness, poor grammar, spelling mistakes, technical errors as well as wrong formats such as PDF files. Submitted assignment solutions (if applicable) must include â€œmanagerial statementsâ€ that communicate the results of the analyses in plain language.

The assignment is to be submitted electronically as a single Word Document file via Brightspace by the NEW updated due date of Thursday November 19th prior to 23:59. The front page of the Word document has to include the title of the assignment, the course code and section, and the student name and number. The second page is the SIGNED Statement of Integrity.

Note: Each student must provide an individual original submission of completed Assignment #2. Please also note: Assignment #2 copies that are submitted jointly (i.e., by more than one author) will not be graded.

E-mail questions related to the assignment should be sent to the Teaching Assistant or posted on the Brightspace course website â€œDiscussion pageâ€ (viewed by all).

Section A: Parisa Keshavarz (pkesh064@uottawa.ca)

Section C: Afshin Kamyabniya (akamy007@uottawa.ca)

Section D: Josianne Absi (jabsi022@uottawa.ca)

Section E : Scotia Haig (shaig013@uottawa.ca)

Problem 1 (25 points)

A soft drink manufacturer, Sara Soda, Ltd., has recently begun negotiations with brokers in the areas where it intends to distribute its products. Before finalizing the agreements, however, Manager Dave Pepper wants to determine shipping routes and costs. The firm has three plants with capacities as shown below:

Capacity

Plant                           (cases per week)

Metro (M)                   40,000

Ridge (R)                    30,000

Colby (C)                    25,000

Estimated demands in each of the warehouse localities are:

Demand

Warehouse                 (cases per week)

RS1                                         24,000

RS2                                         22,000

RS3                                         23,000

RS4                                         16,000

RS5                                         10,000

The estimated per unit shipping costs per case for the various routes are:

 To: From: RS1 RS2 RS3 RS4 RS5 Metro Ridge Colby .80 .75 .70 .75 .80 .75 .60 .85 .70 .70 .70 .80 .90 .85 .80

1. Formulate this transportation problem algebraically and use Excelâ€™s Solver to determine the optimal shipping plan that will minimize total shipping cost (Provide the corresponding â€œExcel Spreadsheetâ€ and the â€œAnswer Reportâ€). Include â€œmanagerial statementsâ€ that communicate the results of the analyses, (i.e. describe verbally the results). (20 points)

1. Adjust the formulation when Route Ridge-RS4 is unacceptable. What is the new optimal shipping plan under this condition? (3 points)

1. What is the additional cost of the Ridge-RS4 route not being acceptable? (2 points)

Problem 2 (30 points)

The J. Mehta Companyâ€™s production manager is planning a series of one-month production periods for stainless steel sinks. The forecasted demand for the next four months is as follows:

 Month Demand for Stainless Steel Sinks 1 100 2 160 3 240 4 120

The Mehta firm can normally produce 100 stainless steel sinks in a month. This is done during regular production hours at a cost of \$100 per sink. If demand in any one month cannot be satisfied by regular production, the production manager has three other choices:

• He/she can produce up to 30 more sinks per month in overtime but at a cost of \$130 per sink;
• He/she can purchase a limited number of sinks from a friendly competitor for resale (the maximum number of outside purchases over the four-month period combined is 200 sinks, at a cost of \$150 each);
• Or, he/she can fill the demand from his/her on-hand inventory (i.e. beginning inventory). The inventory carrying cost is \$10 per sink per month (i.e. the cost of holding a sink in inventory at the end of the month is \$10 per sink).

A constant workforce level is expected. Back orders are NOT permitted (e.g. orders taken in period 3 to satisfy the demand for period 2 is not permitted). Inventory on hand at the beginning of month 1 is 30 sinks (i.e. beginning inventory in month 1 is 30 sinks)

1. Set up and formulate algebraically the above â€œproduction schedulingâ€ problem as a TRANSPORTATION Model to minimize cost. (18 points)

1. Solve using Excelâ€™s solver (Provide the corresponding â€œExcel Spreadsheetâ€ and the â€œAnswer Reportâ€). Also include a managerial statement that describes verbally the results. (10 points)

1. Does this problem have an alternate optimal solution? Justify your answer. (2 points)

Note: This problem can be formulated as a multi-period production scheduling LP problem. However, if you try to formulate it this way then you will get ZERO as the problem requirement is to formulate it as a transportation problem.

Problem 3 (25 points)

The Toys-R-U Company has developed two new toys for possible inclusion in its product line for the upcoming Christmas season. Setting up the production facilities to begin production would cost \$50,000 for toy1 and \$80,000 for toy 2. Once these costs are covered, the toys would generate a unit profit of \$10 for toy 1 and \$15 for toy 2.

The company has two factories that are capable of producing these toys. However, to avoid doubling the start-up costs, just one factory would be used, where the choice would be based on maximizing profit. For administrative reasons, the same factory would be used for both new toys if both are produced.

Toy 1 can be produced at the rate of 50 per hour in factory 1 and 40 per hour in factory 2. Toy 2 can be produced at the rate of 40 per hour in factory 1 and 25 per hour in factory 2. Factory 1 and 2, respectively, have 500 hours and 700 hours of production time available before Christmas that could be used to produce these toys.

It is not known whether these toys would be continued after Christmas. Therefore, the problem is to determine how many units (if any) of each new toy should be produced before Christmas to maximize the total profit.

1. Formulate this problem as a mixed BIP problem algebraically. Define the decision variables, objective function, and constraints. (17 points)

1. Formulate this same linear programming problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding â€œExcel Spreadsheetâ€ and the â€œAnswer Reportâ€). Include â€œmanagerial statementsâ€ that communicate the results of the analyses. (i.e. describe verbally the results). (8 points)

Problem 4: Set covering problem (20 points)

A troop of Canadian soldiers has recently been sent to a foreign country to support a defense mission. They were assigned to defend a region with many hills. As the hills could block the sight of the areas beneath them, the troops decided to place sentries on the hills. They were provided the following geographical map.

They partitioned the map into five areas, AREA I to V, and have identified five spots on the hills, A to E (the red dots) that are good for placing sentry. A sentry if placed at one of the spots can watch over the areas right beside the spot (the areas that have the spot over their boundaries). The troops have to figure out the minimum number of sentry they need to ensure each area is supervised by at least one sentry.

1. Formulate this problem algebraically (i.e. define the decision variables, objective function, and constraints). Use Excelâ€™s Solver to determine the optimal solution. (Provide the corresponding â€œExcel Spreadsheetâ€ and the â€œAnswer Reportâ€). Include â€œmanagerial statementsâ€ that communicate the results of the analyses, (i.e. describe verbally the results). (18 points)

1. Suppose there is an additional requirement: If a sentry is placed at the spot A, then there must also be a sentry placed at the spot B or spot D. Write down the constraint algebraically. (2points)

## Why US?

##### 100% Confidentiality

Information about customers is confidential and never disclosed to third parties.

##### Timely Delivery

No missed deadlines – 97% of assignments are completed in time.

##### Original Writing

We complete all papers from scratch. You can get a plagiarism report.

##### Money Back

If you are convinced that our writer has not followed your requirements, feel free to ask for a refund.