Description

This downloadable virtual product contains a warehouse facility allocation Excel Solver model. The model demonstrates how Excel Solver may be used for facility allocation problems. The Excel Solver model decides how many warehouses should be operated and how warehouses supply customers and source product from suppliers. A scenario with just one single product is considered. Net inventory remains unchanged.

In short, the model decides:

• How many and which warehousing facilities to install and operate?
• Which warehouse does a customer receive products from?
• Which supplier does a given warehouse source product from?

In detail, this virtual product contains:

• PDF file with a presentation of the warehouse facility planning problem.
• Excel sheet with Excel Solver model implementing warehouse facility planning.
• Instructions on how to setup and solve the problem in Excel Solver.

This case study in Excel Solver is an example of mixed-integer optimization.

Who will benefit from warehouse allocation Excel Solver model?

This downloadable product suits supply chain analysts, operations researchers, transport planners, and students that want to:

• Get a hands-on example on a network design optimization problem.
• Learn how warehouse allocation problems are solved using mathematical programming.
• Obtain a linear mixed-integer optimization template in Excel Solver.
• Learn Excel Solver for supply chain design.
• Obtain a formula for distance calculation in miles, based on latitude and longitude data.

Brief case study description

For a given set of 30 major customers and 5 suppliers, all with defined demand and supply capacities, a supply chain network must be installed. 5 potential warehouse locations have been identified. All have different throughput capacities and fixed costs. In addition, transport costs between warehouse and customers as well as suppliers must be considered. Transportation costs grow linearly with the transport distance and the amount of product shipped for the given distance.

The problem is to minimize total supply costs subject to supply and demand constraints, considering the known customer, warehouse and supplier locations.

Relevant KPIs traced and outputted by this model         

The model contained by this product tracks relevant KPIs.

The following KPIs supported by the model.

• Total supply chain costs [USD].
• Which warehouses to install?
• Material flows between warehouses and customers.
• Material flows between suppliers and warehouses.

Learn more about network design and warehouse facility planning

You can find several other contributions on SCDA related to network design and warehouse facility planning.

Here are some exemplary articles for you to read:

• Link: Monte-carlo simulation in R for warehouse location risk assessment
• Link: Locating multiple warehouses at center of mass using R