A matrix is a rectangular array of numbers. In Excel, or in any spreadsheet application program, we can represent a matrix as a range of cells with numerical entries. Actually, the elements of a matrix need not be numbers, but for our purposes, they always will be numbers.

Here's an example of a matrix:

Perhaps the best way to convey the usefulness of matrices is by example. Suppose that you're the manager of milk merchandising for a supermarket chain. You can represent your inventory as a matrix of brand by size. In the matrix, each row contains data for a particular brand of milk, and each column represents data for a given size. For example, the data for quarts of Bessie brand might be in the first cell of the third row. The half-gallon data might be in the second column of that same row. Analogous data for the Moo-Moo brand might be in the fifth row. This is a concise, manageable representation.

The dimensions of a matrix are given as a pair of integers, separated by an "x". For example, the matrix above has dimensions 4x3. We say that it's a 4x3 ("4 by 3") matrix. A matrix that has the same number of rows as columns is said to be "square." The (3,2) element of a matrix is the number that's in the second column of the third row.

The transpose of a matrix is a new matrix that's obtained by interchanging the rows and columns of the original matrix. For example, the transpose of the matrix above is:

The Excel worksheet function that produces the transpose of a matrix is TRANSPOSE. The notation for the transpose of a matrix A is A^t.

We can define a kind of product of two matrices that turns out to be very useful in business modeling applications. It's a little different from other kinds of products that you might know. It's called the matrix product. Here's how it works.

Let's compute the product of two matrices, A·B. We'll call A the first factor and B the second factor of the product. To form the matrix product of two matrices, you compute the (i, j) element of the product as the result of summing the ordinary arithmetic product, element by element, of row i of the first factor and column j of the second factor. You have to do this calculation for every element of the product matrix. The Excel worksheet function that implements matrix multiplication is MMULT.

For this product to be defined the number of rows of the second factor must be equal to the number of columns of the first factor.

Here's an example:

Check this. Notice that the (1 1) element of the result, 8, is equal to 2*1+3*2.

To make Excel compute the product of two matrices, A·B, follow these steps:

1. Verify for yourself that A has as many columns as B has rows. If not, the product is not defined.
2. Figure out the dimensions of the result. The result has as many rows as does the first factor, A, and as many columns as the second factor, B.
3. Select a range of that shape. If A has 6 rows, and B has 3 columns, you would select a 6x3 range.
4. Enter as an array formula =MMULT(A,B)

For examples of the use of matrices, see the demonstrations for session 2.

How on earth can such a complex thing as a matrix product be useful in business modeling? Its usefulness comes about because it so readily combines the pieces back together after you've decomposed a problem into parts. Let's go back to the milk department in the supermarket, and let's compute the projected total value of the inventory for Bessie Brand milk, week by week for 13 weeks. And let's suppose that there are three sizes of milk containers.

We first set up a price matrix. It has three rows, one for each size, and one column. So it's a 3x1 matrix of size by dollars. Next, set up a projected inventory matrix. Again it has three rows, one for each size. It has 13 columns, one for each week, so it's a 3x13 matrix of size by week.

The result we seek is a single row of 13 cells, indicating the total value of inventory for each week of the 13 weeks. It's a 1x13 matrix of dollars by week.

Concretely, let's say that quarts cost $1.02, half gallons cost $1.78, and gallons $2.24. Then the price matrix is P: .

And let's suppose that the projected inventory Q is:

The result we seek is P^t · Q:

[245.86 236.14 249.26 243.82 255.28 235.28 246.94 244.84 242.74 254.30 267.04 239.04 256.90]

Perhaps the most confusing step in any matrix computation is figuring out what matrices to multiply, in what order, and when to use the transpose. For most situations, you'll have available two or more matrices, and the puzzle will be:

1. What order do I multiply the matrices?
2. When do I have to use the transpose?

To solve these puzzles, you'll have to apply two sets of constraints:

1. The shapes of the matrix factors
2. The units of the matrix factors

Each matrix factor has a shape and an associated unit. For example, the matrix P above is shape 3x1 and has units of dollars. The units of matrix multiplications follow the same rules as the units of ordinary multiplication, and I won't say any more about them here. Read Dimensional Analysis for more details.

The rules for shapes are surprisingly simple. We can represent the shape of a matrix using the notation (Row-category x Column-category). For example, the Row-category of P above is Size. There's only one Column-category, so we'll call that 1. Thus, P is (Size x 1). P^t is (1 x Size). Q is (Size x Week).

The order in which you multiply the matrices is the order in which the Column-category of the left factor matches the Row-category of the right factor. Thus P^t · Q works because it's (1 x Size) · (Size x Week). P · Q doesn't work because it is (Size x 1) · (Size x Week).