Excel math problem question
Question:
Write the answers to each question in this word file. For the mathematical models either use Word Equations, or write down the formulas on a paper, take a photo, and copy the photo in this word file. When submitting the assignment, upload this word file and an Excel workbook, in which each question is solved in a separate worksheet. Only one member of the group should submit the assignment. Make sure to write the group number and team members in the files while submitting your assignments
Consider the retailer of Question 1. After a few years, the company becomes more established, and the demand of its stores has increased. So, to meet the increased demand, in addition to the current available warehouses, the retailer wants to build 1 or 2 new warehouses to increase the total supply capacity. The retailer is considering two potential locations for each of these new warehouses. (the potential locations are shown as orange triangles in the map below.)
The cost of building a warehouse in location 3 is 2000 and the cost of building a warehouse in location 4 is 3000. The new demand, supply and cost information are summarized in the table below.
Store |
||||||
A |
B |
C |
D |
E |
Supply |
|
Warehouse 1 |
3 |
4 |
7 |
20 |
16 |
250 |
Warehouse 2 |
14 |
15 |
17 |
6 |
3 |
400 |
Location 3 |
4 |
3 |
6 |
15 |
18 |
350 |
Location 4 |
14 |
19 |
19 |
6 |
6 |
350 |
Demand |
181 |
158 |
191 |
250 |
193 |
-
a) Formulate a mathematical optimization model that helps the retailer decide in which location a new warehouse should be built. The objective function is to minimize the total costs which includes the cost of building the new warehouse(s) and the shipment costs. Clearly specify the decision variables, objective function, and constraints.
-
b) Use Excel to solve the model. In which location(s) a new warehouse should be built? What is the minimum total cost?
DETAILED ASSIGNMENT