Please, follow the following instructions. Failure to do so will result in a 0 in those parts that do not comply with them.
Your submission will consist of the following files:
- A computer-typed Word file that contains the solution to the problems in this assignment. For equations, use Microsoft Word’s equation editor. Use figures as needed. This Word file must include the LP formulation required in Problem 1a (including definition of variables objective function, constraints, and final model, as seen in class) and the numerical solutions and interpretations of all other problems or subproblems in this assignment.
- An Excel file that contains three tabs (Problem 1b, Problem 2, Problem 3) with Solver’s solution to these problems. Note that it is imperative that Excel file and Solver are set up so the grader can run the model with Solver and obtain a solution immediately.
- In both files, the names of all the group members must appear.
Problem 1 (35 pt)
Bowman & Kinkins (B&K) is a hi-fi speaker manufacturer. They proudly engineer and manufacture their speakers in the United Kingdom. To celebrate their 75-year anniversary, they will release a Limited Edition of their most well-known speakers from the acclaimed 6000 Series, the floorstanding speaker B&K 6003.
B&K has three manufacturing plants in the UK (Manchester, Edinburgh, Southampton) that will be in charge of producing a first batch of these hi-fi limited-edition speakers. Their production will be sent to B&K’s distribution centers in other continents. More concisely, these distribution centers are located in Santiago de Chile (Chile), Atlanta (USA), Frankfurt (Germany), and Shanghai (China). The cost of producing these speakers varies with the location they are produced at. Likewise, transporting them to the company’s distribution centers has a variable transportation cost that depends on the route. Production costs and capacities for the next month are given in Table 1. The amount that needs to be put in inventory in each distribution center is shown in Table 2. The transportation costs (in USD/speaker) are given in Table 3.
| Production cost (per speaker) | Capacity (in speakers) | |
| Manchester | $100 | 100 |
| Edinburgh | $75 | 100 |
| Southampton | $90 | 80 |
Table 1
| Santiago | Atlanta | Frankfurt | Shanghai | |
| Demand (tons) | 80 | 70 | 60 | 40 |
Table 2
| Shipping cost (per speaker) | Santiago | Atlanta | Frankfurt | Shanghai |
| Manchester | 11 | 8 | 4 | 15 |
| Edinburgh | 12 | 9 | 4 | 12 |
| Southampton | 10 | 8 | 4 | 13 |
Table 3
- (18pt) Problems in B&K’s procurement of raw materials has resulted in shortage of their production capacity. Consequently, their new production capacities are shown in Table 4. Formulate an LP model that can at least tell B&K how to better distribute the production they can come up with.
- (17pt) Solve this model with Excel and interpret its solution.
| Production cost (per speaker) | Capacity (in speakers) | |
| Manchester | $100 | 45 |
| Edinburgh | $75 | 70 |
| Southampton | $90 | 55 |
Table 4
Problem 2 (30 pt)
Consider the network below:
If the numbers by the edges indicate distance, use Excel’s Solver to find the shortest path between nodes 1 and 7. Note that arcs can be traversed in both directions. Clearly indicate this path.
Problem 3 (35 pt)
Consider the network below:
If the numbers by the edges indicate capacity, use Excel’s Solver to find the maximum flow that can be sent between nodes s and t. Clearly indicate the value of this flow and the way it would traverse the network.