Facility Location Analysis: Birmingham

Cell Color Legend

Notes User input Auto calculate Answer Location Marker

Constraint Sign

$<=$ Less than or equal to

$=$ Equals $($add apostrophe first $-\text{'}=-$ to "tell" Excel this is not an equation$)$

$>=$ Greater than or equal to

Instructions:

Given the following problem statement:

$1.$ Define the variables in the model formulation steps.

$2.$ Determine and calculation intermediate values to support the model

$3.$ Construct and organize the model. Solve the model using Excel's "Solver."

$4.$ Interpret the model results and write result statements.

Variables

Problem Statement

"A company produces components at three production plants. These plants supply inventory to regional warehouses. The company is considering constructing an additional production plant in either Birmingham or Seattle to expand capacity. Using the following information, determine how many units are shipped from each plant to each warehouse and the total cost of production and shipping.

This analysis is for the proposed Birmingham location."

Table $1$

Demand and Supply Data

Warehouse Demand $($units$/$mo$)$ Production Plant Supply $($units$/$mo$)$ Production cost $($$$/$unit$)$

Detroit $10000$ Cincinnati $15000$ $$48\mathrm{.}00$

Dallas $12000$ Salt Lake City $6000$ $$50\mathrm{.}00$

New York $15000$ Pittsburgh $14000$ $$52\mathrm{.}00$

Los Angeles $9000$ Birminghama $11000$ $$49\mathrm{.}00$

a Site of proposed production plant.

Table $2$

Shipping Costs

From $\backslash$ To Detroit Dallas New York Los Angeles

Cincinnati $$25\mathrm{.}00$ $$55\mathrm{.}00$ $$40\mathrm{.}00$ $$60\mathrm{.}00$

Salt Lake City $$35\mathrm{.}00$ $$30\mathrm{.}00$ $$50\mathrm{.}00$ $$40\mathrm{.}00$

Pittsburgh $$36\mathrm{.}00$ $$45\mathrm{.}00$ $$26\mathrm{.}00$ $$66\mathrm{.}00$

Birminghama $$35\mathrm{.}00$ $$30\mathrm{.}00$ $$41\mathrm{.}00$ $$50\mathrm{.}00$

a Site of proposed production plant.

Model Formulation Steps

Note: identify each of the following for this problem. Type answer into green cell

$1.$ Define the decision variables

Proposed location: Birmingham

Xij$=$ # of units shipped from source I to destination j

TBD from analysis

cij$=$cost of one unit from source I to destination j

$(16)$ total cost cij

$2.$ Define the objective function

Total Cost $=$ sumproduct$($cij:Xij

$3.$ Define the constraints

si$=$ supply at source i

s$\_$cincinnati$=15000$

s$\_$Salt Lake$=6000$

s$\_$pittsburgh$=14000$

s$\_$birmingham$\_11000$

dj$=$demand at destination j

d$\_$detroit$=1000$

d$\_$dallas $=12000$

d$\_$new york $=15000$

d$\_$los angles $=9000$

Intermediate Calculations

Note: demonstrate one of each type of intermediate calculation

Type of calculation: Combined production and shipping cost for warehouse $/$ production pant pairing of Detroit $/$ Cincinnati

Demo: Cost $($cij$)=$ production $+$ shipping $=$ $$48+$$$25=$ $$73$

Model

Data Table

Destination Title

Costs Detroit Dallas New York Los Angeles Supply

Source Title Cincinnati $731038810815000$

Salt Lake City $8580100906000$

Pittsburgh $88977811814000$

Birmingham $8479909911000$

Demand $1000012000150009000$

Solver Table

Warehouse Locations

Shipments Detroit Dallas New York Los Angeles Supply

Production Plants Cincinnati $1000001000400015000$

Salt Lake City $01000050006000$

Pittsburgh $0014000014000$

Birmingham $0110000011000$

Demand $1000012000150009000$

Total Cost $3741000$

Result Statements

Note: Write result statements for the problem objective, production point of view, warehouse point of view.

Objective

Production POV

Warehouse POV

Interpreting the Results

Note: Please answer the following questions. Type the answer in the green box.

Questions Type answer in the box

$1.$ Briefly describe a qualitative reason that supports selecting Birmingham as the new production plant location.