Transportasi Menggunakan Excel

Summary

In this tutorial, the process of solving a transportation problem using Excel is explained in detail. The video guides viewers through checking if the problem is balanced by comparing total supply and demand. It then demonstrates how to create tables for supply, demand, and shipping costs, followed by using Excel's Solver tool to minimize shipping costs while meeting all constraints. The step-by-step instructions help users understand how to configure Solver for optimal shipping distribution and how to interpret the results for the most cost-effective solution.

Takeaways

• 😀 Understanding the transportation problem: The script introduces the concept of solving transportation problems using Excel, focusing on distributing goods from multiple distributors to different stores.
• 😀 Checking for balance: The first step in solving the problem is to check if the supply and demand are balanced by summing up both values. If they are equal, the case is balanced, and no adjustments are needed.
• 😀 Setting up the table: To begin solving the problem, set up a table with sources (distributors) and destinations (stores), including their supply and demand values.
• 😀 Inputting supply and demand: The supply values for distributors (A, B, C, D) and the demand values for stores (1, 2, 3) are input into the table for further calculation.
• 😀 Using Excel's Solver tool: After setting up the table with costs, supply, and demand, Excel’s Solver tool is used to calculate the minimum cost of transportation.
• 😀 Calculating transportation costs: The cost of transportation between each source and destination is input into the table, which is then used to optimize the transport plan.
• 😀 Formatting the table for clarity: To make the data clear and organized, the table is formatted with colors and borders. This makes the table visually easier to read.
• 😀 Excel formulas for efficiency: Formulas such as SUM and product calculations are used in Excel to automate and optimize the process of solving the transportation problem.
• 😀 Running the Solver function: Once all data is correctly inputted, Solver is run to minimize the cost of transportation while ensuring all demands and supplies are met.
• 😀 Interpreting the results: After running Solver, the distribution plan with the minimum cost is displayed, showing how much each source sends to each destination, along with the transportation cost breakdown.
• 😀 Conclusion and final remarks: The tutorial ends with a reminder that the case assumes balanced supply and demand, and that Excel's Solver is an effective tool for solving transportation problems.

Q & A

What is the main objective of this tutorial?

The main objective of this tutorial is to teach how to solve a transportation problem using Excel, where goods are distributed from multiple suppliers (distributors) to different stores, with the goal of minimizing transportation costs.

How do you determine if the transportation problem is balanced or unbalanced?

To check if the transportation problem is balanced, you compare the total supply from all distributors with the total demand from all stores. If both totals are equal, the problem is balanced. In this case, the total supply and demand are both 1600, so the problem is balanced.

What are the four distributors and three stores in this problem?

The four distributors are A, B, C, and D, and the three stores (or destinations) are 1, 2, and 3.

Why is it important to create a table with both supply and demand values?

The table with both supply and demand values helps to organize the data clearly and ensures that the correct distribution of goods from each distributor to the stores is calculated. It also simplifies the process of solving the transportation problem using Excel.

What is the purpose of the Solver tool in Excel in this case?

The Solver tool in Excel is used to find the optimal solution to the transportation problem by minimizing the total transportation cost. It adjusts the number of goods shipped from each distributor to each store based on the given supply, demand, and cost constraints.

How do you set up the Solver tool for this transportation problem?

To set up Solver, you need to define the objective (minimizing the transportation cost), specify the decision variables (the number of goods shipped from each distributor to each store), and add constraints for supply and demand. The Solver tool will then find the optimal shipping plan.

What does the formula `=SOMPROD` in Excel do in the context of this tutorial?

The formula `=SOMPROD` (or SUMPRODUCT in English Excel) calculates the total transportation cost by multiplying the amount of goods shipped from each distributor to each store by the corresponding shipping cost, and then summing these products.

What happens if the transportation problem is unbalanced?

If the transportation problem is unbalanced, meaning total supply does not equal total demand, you would need to either add a dummy supplier or a dummy store to balance the totals. This would allow you to solve the problem using the same method.

How do you enter the shipping costs in the Excel table?

You enter the shipping costs in the table by creating a cost matrix, where each cell represents the cost of shipping goods from a specific distributor to a specific store. For example, from distributor A to store 1, the cost is 5, from A to store 2 is 3, and so on.

What is the final result after running the Solver in this problem?

The final result shows the minimum transportation cost (5800 in this case) along with the optimal distribution of goods from each distributor to each store. For example, distributor A ships 300 units to store 2, distributor B ships 200 units to store 3, and so on.