Business Analytics Assignment, Uottawa ADM 2302

profilekirby613
1.Assignment2Fall20201.docx

ADM2302 Section A, B, C, D and E Assignment 2

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 ([email protected])

Section B: Niki Khorasanizadeh ([email protected])

Section C: Afshin Kamyabniya ([email protected])

Section D: Josianne Absi ([email protected])

Section E : Scotia Haig ([email protected])

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

a. 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)

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

c. 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:

(1) He/she can produce up to 30 more sinks per month in overtime but at a cost of $130 per sink;

(2) 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);

(3) 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)

a. Set up and formulate algebraically the above “production scheduling” problem as a TRANSPORTATION Model to minimize cost. (18 points)

b. 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)

c. 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.

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

b. 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.

AREA I

AREA II

AREA III

AREA IV

AREA V

B

A

C

D

E

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.

a. 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)

b. 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)

Fall 2020 Page 4