**Excel Basics -- A Brief Refresher**

Excel is a popular *spreadsheet* program. Spreadsheets are the
most common way of performing calculations on personal computers, and among
the most widespread personal computer applications programs.

Spreadsheets perform calculations on a two-dimensional *grid* of
*cells*.
Customarily, the grid rows are labeled by numbers (1,2,3,...) and the columns
by letter codes (A through Z, then AA through AZ, then BA through BZ, and
so forth).

A | B | C | D | E | F | |

1 | ||||||

2 | ||||||

3 | ||||||

4 | ||||||

5 | ||||||

6 |

In Excel, the grid is actually three-dimensional: each Excel file is a "book" consisting of two-dimensional "sheets" stacked on top of one another. Typically, we will try to do most of our calculations on a single sheet (usually called "Sheet1").

Each cell in the grid can either be empty, contain a number, contain
text, or contain a
*formula*. To enter or change the contents of a
cell, click on it.

Formulas start with the "=" sign. Formulas specify that the contents
of the cell are formed by a calculation that may involve other cells. For
example:

Formula |
Specifies |

= A5+B7 | The sum of cells A5 and B7 |

= 1/3 | 1 divided by 3, that is, 0.33333333... |

=A2*C12 | The product of cells A2 and C12 |

=C6/(D4-E4) | Cell C6 divided by the difference of D4 and E4 |

Ranges of cells can be specified with a ":" -- for example, "B3:D5" denotes the 3-by-3 block of cells with B3 in the upper left corner, and D5 in the lower right. Excel also provides a variety of mathematical functions that operate on ranges, including SUM, PRODUCT, AVERAGE, and SUMPRODUCT. For example, the formula =SUM(A1:A12) denotes the sum of cells A1 through A12. SUMPRODUCT is bit more obscure, but will be quite useful in this course. You specify two ranges of cells of the same shape. SUMPRODUCT multiplies together the corresponding cells in the two ranges, and then adds all the resulting products into a single number. Thus, =SUMPRODUCT(A1:C1,A4:C4) is equivalent to =A1*A4+B1*B4+C1*C4.

When typing formulas, you don’t have to type in cell coordinates or ranges with the keyboard; instead you can just point or drag with the mouse.

When you build large mathematical models with spreadsheets, it becomes
tiresome to type in all the formulas one-by-one. Excel’s *Copy* and
*Paste*
functions (on the *Edit* menu) provide a fast way to build up large
spreadsheets. When you *Copy* and *Paste* a formula from one
cell to another, Excel’s usual behavior is to treat its references as *relative*.
For example, if cell A2 contains the formula =A1+1, the "A1" really means
"the cell one above this one". If we were to copy the formula to cell D5,
it would appear as "=D4+1". The same thing applies to the column part of
each cell reference: consider the following spreadsheet fragment:

A | B | C | D | E | F | |

1 | 1 | =A1*2 | ||||

2 | ||||||

3 | ||||||

4 | ||||||

5 | ||||||

6 |

The "A1" in cell B1 is relativing, meaning that it really refers to
"the cell one to the left of me." Copying the contents of cell B1 to cells
C1 through F1 yields

A | B | C | D | E | F | |

1 | 1 | =A1*2 | =B1*2 | =C1*2 | =D1*2 | =E1*2 |

2 | ||||||

3 | ||||||

4 | ||||||

5 | ||||||

6 |

and Excel displays

A | B | C | D | E | F | |

1 | 1 | 2 | 4 | 8 | 16 | 32 |

2 | ||||||

3 | ||||||

4 | ||||||

5 | ||||||

6 |

You can make the row and/or column parts of a cell reference *absolute*
by inserting $ signs. For example, "$A12" will always refer to column A
wherever you copy the formula to, but the row part of the reference remains
relative. On the other hand, "A$12" will always refer to row 12, but the
column part of the reference (A) remains relative. Finally, "$A$12" will
always refer to cell A12, no matter where you paste it.

Let’s say we wanted to build a 5-by-5 multiplication table. We can start
by entering

A | B | C | D | E | F | |

1 | 0 | =A1+1 | ||||

2 | =A1+1 | =B$1*$A2 | ||||

3 | ||||||

4 | ||||||

5 | ||||||

6 |

which displays as

A | B | C | D | E | F | |

1 | 0 | 1 | ||||

2 | 1 | 1 | ||||

3 | ||||||

4 | ||||||

5 | ||||||

6 |

We now copy cell B1 to cells C1 through F1, and cell A2 to cells A3
through A6, yielding

A | B | C | D | E | F | |

1 | 0 | =A1+1 | =B1+1 | =C1+1 | =D1+1 | =E1+1 |

2 | =A1+1 | =B$1*$A2 | ||||

3 | =A2+1 | |||||

4 | =A3+1 | |||||

5 | =A4+1 | |||||

6 | =A5+1 |

which displays as

A | B | C | D | E | F | |

1 | 0 | 1 | 2 | 3 | 4 | 5 |

2 | 1 | 1 | ||||

3 | 2 | |||||

4 | 3 | |||||

5 | 4 | |||||

6 | 5 |

Finally we copy B2 to the remaining empty cells. Consider what happens
when cell B2 gets copied to, say, cell D3. In the "B$1" in cell B2, the
"B" is relative, that is, it mean "this row". So, when we copy to cell
D3, it changes to "D". However, the "$1" is an absolute (non-changing)
reference to row 1, so it stays the same. Similarly, the "$A" in "$A2"
means "always column A" and stays the same, but the "2" is relative and
means "this row." Thus, when copied to D3, the formula becomes "=D$1+$A3",
which is the same as 3*2, or 6. After copying the contents of B2 to all
the empty cells, the entire grid of formulas becomes

A | B | C | D | E | F | |

1 | 0 | =A1+1 | =B1+1 | =C1+1 | =D1+1 | =E1+1 |

2 | =A1+1 | =B$1*$A2 | =C$1*$A2 | =D$1*$A2 | =E$1*$A2 | =F$1*$A2 |

3 | =A2+1 | =B$1*$A3 | =C$1*$A3 | =D$1*$A3 | =E$1*$A3 | =F$1*$A3 |

4 | =A3+1 | =B$1*$A4 | =C$1*$A4 | =D$1*$A4 | =E$1*$A4 | =F$1*$A4 |

5 | =A4+1 | =B$1*$A5 | =C$1*$A5 | =D$1*$A5 | =E$1*$A5 | =F$1*$A5 |

6 | =A5+1 | =B$1*$A6 | =C$1*$A6 | =D$1*$A6 | =E$1*$A6 | =F$1*$A6 |

which displays as

A | B | C | D | E | F | |

1 | 0 | 1 | 2 | 3 | 4 | 5 |

2 | 1 | 1 | 2 | 3 | 4 | 5 |

3 | 2 | 2 | 4 | 6 | 8 | 10 |

4 | 3 | 3 | 6 | 9 | 12 | 15 |

5 | 4 | 4 | 8 | 12 | 16 | 20 |

6 | 5 | 5 | 10 | 15 | 20 | 25 |

So, we were able to make the table by entering only "0" and three formulas,
and then doing some copying and pasting. In general, this type of trick
is very useful for creating large spreadsheets.

*Modeling in Excel*

In this course, we will use Excel to build *mathematical models*
of business situations. Much as pointed out in Chapter 1 of the text, mathematical
models tend to have four basic components:

**Controllable inputs**, also called **decision variables**: these
are numbers that the decision maker can control, within limits, such as
the amount of a particular product to build this month. Typically, each
decision variable will have its own spreadsheet cell.

**Mathematical relationships and intermediate calculations**: these
are based on both kinds of inputs.

**Outputs**: the "interesting" results of the calculations.

Many models also contain **constraints** which specify limits on,
or relationships between, certain inputs, intermediate calculations, or
outputs. When building a mathematical model in Excel, one typically wants
to express constraints as requirements that the contents of certain cells
fall into a specified range. The most common and intuitive example is a
requirement that certain cells not contain negative numbers.

In order for others to comprehend you spreadsheets, or even for you to understand them yourself after putting them aside for more than a week or so, it is important that they be well organized. Spreadsheets are easier to create than computer programs, but like computer programs, forethought and planning in their construction almost always pays off in a result that is easier to use and modify.

The best organization depends on what you’re modeling, but a general rule of thumb is to partition your spreadsheet into distinct areas for uncontrollable inputs, controllable inputs, intermediate calculations, and outputs. Further grouping within these areas, for example grouping together similar data or constraints, is another good idea. It is generally hard to read a spreadsheet in which inputs, calculations, and outputs are jumbled together.

*Using Solver*

Once you’ve created a spreadsheet model of a business situation, you can start playing "what if" games with it. You can try different values for the controllable inputs, and see what happens to the outputs, and check if all the contraints are satisfied. Spreadsheets were originally developed with this kind of process in mind.

Modern spreadsheets can sometimes do better. They give you a way of
telling the spreadsheet enough about the structure of your model that it
can, using MS/OR technology, attempt to recommend the best possible values
of the controllable inputs. Excel calls this mechanism *Solver*. To
use it, you select *Solver* from the *Tools* menu. The *Solver
Parameters* dialog box then appears.

*Solver* calls the objective function of your model the "target
cell". In the *Target Cell* section of the *Solver Parameters*
box, you specify the coordinates of a single cell, and whether you want
to maximize (*Max*) or minimize (*Min*) that cell.

In solver, controllable inputs are called "changing cells". In the *Changing
Cells* section of the *Solver Parameters* box, you can type a list
of cells and/or ranges that represent decision variables. You can also
specify these cells/ranges by clicking or dragging the mouse.

The *Solver Parameters* box also lets you specify constraints.
It displays a list of constraint conditions to which you can *Add*,
*Change*,
or *Delete*. **Note:** try to avoid embedding controllable input
data into your constraint conditions. Suppose that cell E5 is a decision
variable representing the number of crates of a product that can be shipped
through one of your distributors each month. The distributor can only handle
up to 1000 crates/month, so it is tempting to add the constraint E5 <=
1000 to the model. But then the uncontrollable input "1000" is buried in
the Solver parameters, and not immediately obvious on the spreadsheet itself.
It's better to put the constant value 1000 into another cell, say G5, and
use the contraint E5 <= G5.

We will concentrate on *linear models*. If your model is
linear, it is best to click the *Options* button in the *Solver
Parameters* box, and click on "*Assume Linear Model*".

In the *Solver Parameters*, if you now click *Solve*, *Solver*
attempts to find the values of the changing cells that produce either the
maximum or minimum value in the target cell (depending on which you asked
for), subject to all the constraints being satisfied. For linear models,
it will generally succeed if the model has been properly constructed and
is not too immense. When it is done, a dialog box will pop up. If
you click OK, the spreadsheet will reappear, with optimal values placed
in all the changing cells.