Problem 1. Government Services Administration (GSA)

The federal Government Services Administration (GSA)’s Facilities Operations office provides services for 1,523 federally owned facilities across the country. These services include electrical/mechanical maintenance, energy and water conservation, and custodial operations.[1] Having a central office for these services allows the GSA to deliver these services more efficiently. But this also means that the GSA needs to manage multiple contractors and keep track of the needs of the 1,523 facilities.

The Facilities Operations director learned that 345,000 light bulbs in 10 different facilities need to be replaced with energy-efficient bulbs. The GSA contracted with 6 different companies to purchase the light bulbs at a reduced price. However, in negotiating this reduced price, the contracts stipulated that the GSA is responsible for shipping costs. Build a linear program in Excel and use the Solver to minimize shipping costs.

The following summarizes the shipping costs per container, negotiated number of supplies, and light bulb demand for all light bulb distributors and federal facilities. Each row corresponds to 1 light bulb distributor (6 companies total), and each column corresponds to 1 federal facility (10 facilities need light bulbs). The last column (“Supplies (thousand units)”) lists how many light bulbs were purchased by the GSA from each distributor.  The last row (“Demand (thousand units)”) shows how many light bulbs are needed at each facility. The middle cells show the cost to ship a container of light bulbs from each distributor to each facility; for example, it costs $8 to ship a container of light bulbs from distributor #1 to facility #1, and it costs $16 to ship a container of light bulbs from distributor #3 to facility #3. Each container has 1,000 light bulbs, and assume they are indivisible (you cannot ship a fraction of a container).

How many containers should be shipped from each provider to each one of the warehouses to make the shipping costs as small as possible?

1. Write the objective function
2. Write down all linear constraints
3. Build the LP model in Excel
4. Use the Excel Solver to find the optimal solution

Problem 2. Red Cross Aid to the Caribbean

After Hurricane Matthew hit the Caribbean, the Red Cross is working on plans to send aid to Haiti, Cuba, and the Bahamas. The Red Cross infrastructure in these Caribbean islands consists of one warehouse on each island, with different maximum capacities (in weight and volume).

The Red Cross has the following supply of aid ready for delivery to these islands. Each item has a different size and population benefit.

In making its decision about how to allocate this aid across the islands, the Red Cross wants to also consider the following criteria:

• Each island should receive the same total amount of aid in tons, i.e. if the Bahamas receives two tons of aid, then Haiti and Cuba should also receive two tons of aid (equity consideration)
• Each island should receive at least one ton of each item (consideration of needing a diverse set of resources)
• The Red Cross wants to maximize the total population benefit (efficiency consideration)

How many tons of each item should be sent to each warehouse?

1. Write the objective function
2. Write down all linear constraints
3. Build the LP model in Excel
4. Use the Excel Solver to find the optimal solution

Attribution

By Luis F. Luna-Reyes, Erika Martin and Mikhail Ivonchyk, and licensed under  CC BY-NC-SA 4.0.