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

“

Vehicle Loading

”

as a column heading. Merge and

center the heading across the columns.

In row 14, column N, enter

“

Cost

”

as a centered column heading.

In row 15, column B, enter

“

Distance Table (from Memphis Plant)

”

as a column heading.

In row 15, column C, enter

“

Miles

”

as a column heading.