Excel for Supply Chain Analysis: Solve Transportation Issues

Describe the transportation issue as a deterministic, cost-minimization problem with fixed demand and known distances, moving wind farms' units from three sources to three distribution centers within capacity limits.

Outline a transportation model in Excel by setting up demand, capacities, and miles between cities. Copy data, designate changing cells, and transpose capacities to prepare for analysis and cost calculations.

Define decision variables, constraints, and total cost in Excel for a transportation problem by using autosum checks, sumproduct with the distance matrix, and Solver to find the lowest cost solution.

Create and run a solver model in Excel to minimize transportation cost by selecting integer, nonnegative shipment quantities that meet demand and respect center capacities.

Describe the transshipment problem, routing goods through multiple stages from factories to distribution centers to wind farms with fixed demand, known distances, and conservation of flow.

Outline a transshipment problem in an Excel worksheet by creating changing cells for factory to distribution center and distribution center to wind farm flows, inbound and outbound distances and costs.

Define decision variables, constraints, and a cost in an Excel transshipment model. Enforce conservation of flow from factories to centers to customers, and compute inbound, outbound, and costs with sumproduct.

Minimize total cost in a transshipment network using Excel solver by defining demands, capacities, and conservation of flow, and setting objective, variable cells, and integer constraints with simplex LP.

Evaluate a transshipment solver model and compare its total cost to direct factory shipping, using goal seek to locate the break even cost per mile around $0.16 (target $0.15).

Decide which distribution centers to open to minimize total cost, balancing fixed opening costs and routing savings in a single-level transportation problem, using binary variables and a simplex-based solver.

Learn how to model a linking constraint in Excel using binary variables to decide which distribution centers stay open, incorporating fixed costs, capacities, and total demand in a transportation problem.

Implement linking constraints in Excel to force a distribution center to be open if any units flow, using a solver model and the 0303 linking file.

Create and run an Excel solver model to minimize total cost by altering binary and integer variables, applying capacity, demand, and linking constraints to decide facility openings, using simplex LP.

Minimize total miles to meet deterministic demand and level of service, using distance as a proxy and routing between wind farms and three distribution centers.

Outline level of service constraint analysis in Excel using a distance array between warehouses and wind farms, with outbound transport, capacity checks, and sumproduct-based total cost calculations.

Build a distance-check array (max 250 miles) and use sumproduct to compute level of service as the within-range demand divided by total demand, targeting 0.7.

Create and run a solver model to minimize total cost while meeting a level of service constraint, ensuring integer, non-negative shipments, capacity limits, and city demand requirements.

Solve a transportation problem by building and solving a worksheet to minimize costs for three bakeries serving nearby customers, using chapter one techniques, then test and adjust the model.

Discover how to model a transportation problem in Excel to minimize costs using a bakery-to-customer grid, sumproduct for total cost, and solver with integer, nonnegative, capacity, and demand constraints.

Analyze a transshipment problem to meet rising demand for Jenny's Biscuits by optimizing shipments from three bakeries to three distribution centers for the lowest cost. Review chapter two techniques.

analyze a three-layer transshipment problem by building an excel worksheet and solver model to minimize inbound and outbound transportation costs, enforce integer shipments, and satisfy bakery capacities and customer demands.

Analyze open, fixed costs and linking constraints to decide which distribution centers to open in a two-level transportation problem. Use Jenny's biscuits scenarios to minimize total cost while meeting demand.

Learn to build a solver model in Excel to minimize total cost in a two-facility transportation problem, using open/close binary variables, capacity constraints, and simplex LP.

Explore level of service constraints in an excel-based two-level bakery distribution model. Minimize cost while meeting demand and ensuring at least 80% of biscuits travel 11 miles or less.

Create an Excel solver model to minimize transportation costs under a level-of-service constraint, using three bakeries and ten customers, with distance-based service, integer decisions, and capacity and demand constraints.

Learn how to manually adjust demand and other parameters in a transportation problem to test business performance with a solver, and observe how shifts affect routing and capacity.

Adjust distances using factors to reflect delays in roads, detours, and construction, update the factor table, and rerun the solver to compare costs and identify robust routes.

explore extreme scenarios in a transportation model by tweaking demand and capacity, running the solver, and comparing solutions to see how drastic changes reshape the distribution network.

Set a target cost in a transportation problem using Excel solver. Relax the integer constraint to find near-optimal solutions and compare source shifts and total cost to the minimal solution.

Learn to use Excel's solver sensitivity report to see how constraint changes alter the transportation solution, interpret shadow prices, and assess capacity and demand effects on cost and shape.

Explore resources to deepen your understanding of supply chain transportation problems, including Chopra and Mendel's supply chain management, Hugo's essentials, and Wisner, Tan, and Long's principles, with most recent editions.