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:
-
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.
-
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.
-
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.
-
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.
-
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:
-
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).
-
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.
-
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:
- How do you set the decision variables in Excel's Solver for a transportation problem?
- What constraints should be considered in the context of car rental relocation?
- How is the total transportation cost calculated in the objective function?
- What insights can be drawn from the sensitivity analysis in this problem?
- 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
Related Recommendation
Optimal Transportation: Charlotte to Washington Shipping
Minimizing Vehicle Rental Costs for Transporting Goods Using Linear Programming
Optimal Vehicle Rental Strategy for Minimal Cost
Optimal Solution for Transportation Problem with Factory and Market Constraints
Solving a Transportation Problem with Linear Programming: Case Study of Four Automobile Plants and Nine Markets