Math Problem Statement

Transportation Problem - Airport.pdf

113.57 KB

Create a excel sheet for this problem

Solution

It seems the problem you're working on involves solving a transportation problem for car rental logistics between airports, where cars need to be relocated to balance demand. Based on the PDF, the goal is to set up and solve this problem in Excel. Here's how I would create the necessary components for the Excel sheet:

Steps to create the Excel Sheet:

  1. Define Decision Variables:

    • Create a matrix where rows represent the origin cities (from where cars are moved) and columns represent the destination cities (where cars are needed).
    • The cell values will be the number of cars moved between the cities.
  2. Objective Function:

    • The objective is to minimize the cost of transporting the cars.
    • Include transportation costs in the matrix. Each cell will represent the cost of moving a car from one city to another.
    • Add a row or column to calculate the total cost of transportation.
  3. Constraints:

    • Supply constraints: Each origin city has a limited number of cars that can be moved.
    • Demand constraints: Each destination city has a specific number of cars it needs.
  4. Set up the Solver:

    • Use Excel's Solver to minimize the total transportation cost by adjusting the number of cars moved between cities, subject to the supply and demand constraints.
  5. Sensitivity Report:

    • After running Solver, you can generate a Sensitivity Report to understand the shadow prices and mandatory routes, as required.

Structure of the Excel Sheet:

  1. Sheet 1: Data Input

    • Columns for Cities: Origin cities in rows, destination cities in columns.
    • Cost Matrix: Fill this matrix with the transportation cost per car for each route.
    • Supply and Demand: Add an extra row and column for supply (cars available at each origin) and demand (cars needed at each destination).
  2. Sheet 2: Results

    • Optimal Assignment of Cars: This will show the optimal number of cars to move between each origin and destination.
    • Total Cost: The total cost of transportation, which should match the provided target of $922.
  3. Sheet 3: Sensitivity Report

    • After solving the problem, this sheet will show the sensitivity analysis, including shadow prices and slack variables for each constraint.

Would you like me to create a template Excel sheet for you, or would you like further clarification on how to set it up?

5 Related Questions:

  1. How do you set the decision variables in Excel's Solver for a transportation problem?
  2. What constraints should be considered in the context of car rental relocation?
  3. How is the total transportation cost calculated in the objective function?
  4. What insights can be drawn from the sensitivity analysis in this problem?
  5. How do shadow prices influence the transportation decisions?

Tip:

When working with Solver, make sure to correctly set the constraints (equalities and inequalities) to prevent Solver from giving infeasible or incorrect results.

Ask a new question for Free

By Image

Drop file here or Click Here to upload

Math Problem Analysis

Mathematical Concepts

Transportation Problem
Linear Programming
Optimization

Formulas

Minimize Z = Σ (cost_ij * x_ij)
Supply Constraints: Σ x_ij ≤ Supply_i
Demand Constraints: Σ x_ij ≥ Demand_j

Theorems

Linear Programming Theorem
Shadow Price Theory in Sensitivity Analysis

Suitable Grade Level

Undergraduate/Graduate Level