Assignment #3

ASSIGNMENT #3 TEAM-BASED PROBLEMS 1. Classic Construction Company specializes in constructing buildings with a period-correct architectural style. Three of their current projects, one in Dallas, one in St. Louis, and one in Atlanta, all require a particular reddish “scratch-faced brick,” which can only be purchased from two suppliers, one based in Newkirk, Ohio and one based in Tulsa, Oklahoma. The unit purchase costs and the availability of bricks at the two locations are given in the table below: Unit Costs (per brick) Available Supply Newkirk $0.57 95,000 (bricks) Tulsa $0.64 78,000 (bricks) The quantities (“demand”) needed by Classic Construction are given in the table below. Dallas St. Louis Atlanta 57000 44,000 68000 Suppose that shipping costs are assessed per truck . Each truck can carry up to 10,000 bricks. However, if you send a truck with only 5000 bricks from Newkirk to Atlanta, Classic Construction still pays $4000 for the half- loaded truck. The shipping costs per truck and route are given below: Shipping costs (per Truck) Dallas St. Louis Atlanta Newkirk $3000 $2500 $4000 Tulsa $1000 $1500 $2500 A. Formulate an Integer Linear Program (ILP) that meets the construction needs of Classic Construction and minimizes the cost of purchasing and shipping. B. Solve your formulation in Excel and report the optimal purchasing/shipping plan as well as the optimal cost. (Set Optimality Gap to .1%) Hint : You will need a brick quantity variable and a truckload quantity variable for each potential route. The truckload variable captures the (integer) number of trucks you will need to ship the brick quantity. Expect to use 12 decision variables (total). 2. Bonus 10 points (Courtesy of Brent Gette, EMBA 2018) A company needs to buy at least 5000 individual storage devices (SIM cards) with a total storage capacity of at least 1,250,000 MB. The devices are to be divided into two distinct “buckets,” with each bucket having at most 3000 of the individual devices. Moreover, the average cost per MB cannot exceed $0.0075 across all devices purchased (both buckets). The costs and MB of the devices are given in the table below. Device MB Cost ($)

100 MB 6.5 1000 MB 10 3000 MB 20 5000 MB 30 7000 MB 40 10000 MB 55 A. Formulate an optimization model that helps Brent accomplish the company’s goals at minimum cost. (Set Optimality Gap to .1%) B. What if the company insisted on the average blended price being no more than .006 per MB? What about .0065? (Set Optimality Gap to .1%). Warning : .0065 took me forever. See if you can get it (if not, do not worry). Below is the actual email correspondence! From: Gette, Brent Sent: Tuesday, September 12, 2017 9:50 AM To: Semple, John <jsemple@mail.cox.smu.edu> Subject: Decision modeling problem Working on a DM problem for a friend at [Fortune 100 Company] Below are the constraints he has sent me. Constraints: Need a total of 5,000 devices with a minimum total of 1,250,000 total mbs. (1.25 terabyte) Must have 2 buckets of devices, not to exceed 3,000 total in each bucket. ex. Can have 3k and 2k. 2.5k and 2.5k etc. Total blended cost across buckets must not exceed .0075 per mb. So one bucket can exceed the .0075 as long as together they both come in under. Trying to find the most cost efficient way of pricing this solution. Cost of service is: Size Cost CPMb 100 MB $6.50 $0.065 1000 mb $10.00 $0.010 3000 mb $20.00 $0.006667 5000 mb $30.00 $0.006000 7000 mb $40.00 $0.005714 10000 mb $55.00 $0.005500 From: Gette, Brent Sent: Tuesday, September 12, 2017 10:09 AM To: Semple, John <jsemple@mail.cox.smu.edu> Subject: Re: Decision modeling problem Can be over 5000 devices. --Brent Gette Hint #1: I Used 12 variables. B11 = number of devices in bucket 1 of type 1 (100 MB), B12 = number of devices in bucket 1 of type 2 (1000 MB), …….., B21 = number of devices in bucket 2 of type 1 (100 MB), B22 = number of devices in bucket 2 of type 2 (1000 MB),……. Hint #2: Don’t forget that the average cost condition can be handled using a blending constraint. Recall what you learned from your previous blending problems (fractional constraint, convert to linear, etc.). INDIVIDUAL PROBLEMS 3. This problem was taken from Decision Modeling with Microsoft Excel by Moore & Weatherford, 6 th edition, Prentice Hall, 2001.