Microsoft Office Tutorials and References
In Depth Information
USING EXCEL SOLVER
￿
In column F, enter the values for the Available Fleet, which is the number of each type of
vehicle your company owns or leases.
You can update the Constants section as the company adds more products to its offerings or adds
vehicles to its fleet.
NOTE
The column headings in the Constants section contain two or three lines to keep the columns from becoming too wide. To
create a multiple-line column heading, hold down the Alt key and press Enter when you need to create a new line.
Now is a good time to save your workbook. Use a descriptive filename so you can find it easily later
—CV
Fitness Trucking Problem.xlsx should work well.
Calculations and Results Section
The structure and format of your Calculations and Results section will vary greatly depending on the nature
of the problem you need to solve. In some Solver models, you might need to maximize income, which means
you might also have an Income Statement section. In other Solver models, you may want to have a separate
Changing Cells section that contains cells Solver will manipulate to obtain a solution. However, in this tutorial
you want to minimize shipping costs while meeting the product demand of your stores. You can accomplish
this task by building a single unified table that includes the distances to the stores, the product demand for
each store, and the shipping alternatives and costs.
A unified Calculations and Results section makes sense in this model for several reasons. First, it
simplifies writing and copying the formulas for the needed shipping volumes, the vehicle capacity totals, and the
shipping costs to each destination. Second, a well-organized table allows you to easily identify the Changing
Cells, which Solver will manipulate to optimize the solution, as well as the Total Cost (or Optimization Cell).
Finally, a unified table allows your management team to visualize the problem and its solution.
When creating a complex table, it is often a good idea to sketch the table
s structure first to see how you
want to organize the data. Format the table structure, then enter the data you are given for the problem.
Write the cells that contain the formulas last, starting with all the formulas in the first row. If you do a good
job structuring your table, you will be able to copy the first-row formulas to the other rows.
Build the blank table shown in Figure D-6. A discussion of the rows and columns follows the figure.
FIGURE D-6
Blank table for Calculations and Results section
￿
In row 13, enter
Calculations and Results Section:
as the title of the table.
￿
In row 14, columns B and C, enter
Distance/Demand Table
as a column heading. Merge and
center the heading in the two columns.
￿
In row 14, columns D, E, and F, enter
Store Demand
as a column heading. Merge and center
the heading in the three columns.
￿
In row 14, columns G through M, enter
as a column heading. Merge and
center the heading across the columns.
￿
In row 14, column N, enter
Cost
￿
In row 15, column B, enter
Distance Table (from Memphis Plant)