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.