Script for Answer (1)



Script for Answer (1).

Before we can use Excel Solver to find a solution to C&A’s location decision problem, we need to set up three tables: the transportation table, the candidate solution table, and the cost calculation table.

Let us show you how to use the worksheet called “Answer (1)” to set up the column and row headings of the transportation table.

Double click on the “Sheet1” tab and rename it to “Answer (1)”

We will label the sources of demand, which are customers 1, 2, 3, and 4 as the four column headings of the transportation table in cells B1 to E1.

Then the label for the sources of supply, which are warehouses A, B, and C/D as the three row headings of the transportation table in cells A2 to A4.

The remaining labels are “From/To” in cell A1, “Supply” in cell F1, and “Demand” in cell A5.

Next, we will fill in the demand of each customer in cells B5 to E5.

Enter “400” in cell B5 showing customer 1’s demand.

Enter “900” in cell C5 indicating that’s customer 2’s demand.

Enter “200” in cell D5 which is customer 3’s demand.

Enter “500” in cell E5 which is customer 4’s demand.

We then fill in the available product supply at warehouse A and B in cells F2 and F3.

Enter “500” in cell F2 showing warehouse A’s supply.

Enter “700” in cell F3 showing warehouse B’s supply.

Question 1 asks us about the amount of unmet supply which is the amount to be entered in cell F4. We can compute that by subtracting the total supply from warehouses A and B from the total demand of customers 1, 2, 3 and 4. Enter the formula =sum(B5:E5) – sum(F2:F3) in cell F4.

Thus, the minimum amount to be supplied from the new warehouse is 800 units

[pic]

Script for Answer (C).

Let us copy the transportation table in worksheet “Answer (1)” to a new worksheet called “Answer (C)”. To do so, select Edit and then Move or Copy Sheet from the Excel Menu. Make sure the “Create a copy” option is checked in the “Move or Copy” window. Click “OK”. Double Click the worksheet tab and rename it to “Answer (C)”.

Fill in the cells of the transportation table with the cost of supplying a unit of the product to each customer from each warehouse.

Enter “12” in cell B2, showing that it costs $12 to ship a unit of product from warehouse A to customer 1.

Enter “13” in cell C2, showing that the unit cost of supply from warehouse A to customer 1 is $13.

Enter “4” in cell D2, “6” in cell E2 and so on to complete the set up of the transportation table.

[pic]

Next we will do the set up for the solution table. Enter “Solution” in cell A7. The headings for this table are the same as the transportation table so you can copy them from the previous table if you like.

The cells in B8:E10 will be filled in by Excel to show us the optimal shipping schedule so we will leave them empty.

The shipping schedule given by Excel needs to satisfy each customer’s demand without exceeding each warehouse’s supply. To do so, Enter “=sum(B8:B10)” in cell B11. Copy the formula to cells C11 to E11.

Enter “=sum(B8:D8) in cell F8. Copy this formula to cells F9 and F10.

[pic]

Finally, we will set up the cost table. Enter “Cost” in cell A13. Enter the labels for our customers in cells B13 to E13. Enter the labels for our three warehouses in cells A14 to A16. Enter “Total Cost” in cell E17.

The cells in B14 to E16 will be filled in by Excel to show us the detailed shipping cost based on the shipping schedule shown in cells B8 to E10.

Enter “=B8*B2” in cell B14. Recall that cell B2 contains the unit cost of supplying customer 1 from warehouse A. Cell B8 contains the amount to be supplied from warehouse A to customer 1. Thus, we will multiply the contents of these two cells to give the total cost of supplying customer 1 from warehouse A.

Copy the formula from cell B14 to cells C14 to E14, then to cells B15 to E16.

Enter “=sum(B14:E16)” in cell F17 to compute the total transportation costs of the entire shipping schedule with C being the new warehouse.

[pic]

After these tables are set up, we can then start Excel Solver.

Select Tools and then Solver from the Excel menu. Select Tools and then Add-in from the Excel menu if Solver is not found in that location.

The “Solver Parameters” window will pop up.

Click on the “Set Target Cell:” parameter and select cell F17 from the worksheet. This means that we want Excel to find the total transportation cost if C is the location for C&A’s new warehouse.

Since we are given the per unit cost of supplying each customer from each warehouse, we will ask Excel to find the minimum total transportation cost. Click on the “Min” option of the “Equal To:” parameter.

The “By Changing Cells:” parameter is the location of the shipping schedule in the solution table. Thus, enter “B8:E10” for this parameter.

Finally, we will add to constraints in the “Subject to the Constraints:” parameter. The first constraint indicates that the total demand from the cost table (i.e., B11 to E11) has to be exactly equal to each customer’s demand (i.e., B5 to E5). This demand constraint is entered by clicking the “Add” button to open the “Add Constraint” window. The “Cell Reference:” parameters are set by selecting cells B11 to E11. Change the logical operator parameter to “=”. Set the “Constraint:” parameter to cells “B5:E5”. Then click “OK”.

The second constraint requires that the total supply from the cost table (i.e., F8 to F10) cannot exceed the available supply at each warehouse (i.e., F2 to F4). This demand constraint is entered by clicking the “Add” button to open the “Add Constraint” window. The “Cell Reference:” parameters are set by selecting cells F8 to F10. Change the logical operator parameter to “ ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download