Matrices in OLS

Matrix Multiplication

If $A$ and $B$ is $3$ x $3$ matrix, then $AB$ is also a $3$ x $3$ matrix. However $AB \neq BA$ because matrix multiplication is not commutative.

As an illustration: matrixOLS.xlsm

$$A =  \begin{bmatrix} 1 & 2 &3 \\ 4 & 5 & 6 \\ 7 & 8 & 9 \end{bmatrix} \;\;\;\;\; B =  \begin{bmatrix} 11 & 12 &13 \\ 14 & 15 & 16 \\ 17 & 18 & 19 \end{bmatrix}$$
Then,
$$\begin{split} AB &= { \begin{bmatrix} 1*11+2*14+3*17 & 1*12+2*15+3*18 & 1*13+2*16+3*19 \\ 4*11+5*14+6*17 & 4*12+5*15+6*18 & 4*13+5*16+6*19 \\ 7*11+8*14+9*17 & 7*12+8*15+9*15 & 7*13+8*16+9*19 \end{bmatrix} }\\ &= \begin{bmatrix} 90 & 96 &102 \\ 216 & 231 & 246 \\ 342 & 366 & 390 \end{bmatrix} \end{split}$$
and
$$\begin{split} BA &= { \begin{bmatrix} 11*1+12*4+13*7 & 11*2+12*5+13*8 & 11*3+12*6+13*9 \\ 14*1+15*4+16*7 & 14*2+15*5+16*8 & 14*3+15*6+16*9 \\ 17*1+18*4+19*7 & 17*2+18*5+19*8 & 17*3+18*6+19*9 \end{bmatrix} }\\ &= \begin{bmatrix} 150 & 186 &222 \\ 186 & 231 & 276 \\ 222 & 276 & 330 \end{bmatrix} \end{split}$$

In Excel, matrix multiplication is easier.
1. Select a $n$ x $n$ where you will put your answer.
2. Use the function =MMULT(1st array, 2nd array) and
3. Type ctrl+shift+enter.

Matrices in OLS: 

 Sample1: on matrixOLS.xlsm

Given: $$\begin{split} 12&=7a-5b \\ 2&=7+9b\end{split}$$
Find: $b$

There are many ways to solve for b. Though the solution we made involves the theoretical concepts on matrices.


Given:
$$\begin{split} y_1&=a+bx_1 \\ y_2&=a+bx_2\end{split}$$
then in matrix form:
$$\hat{b} = \begin{bmatrix} a\\b \end{bmatrix} \;\;\;\;\;\; \hat{y} = \begin{bmatrix} y_1\\y_2 \end{bmatrix} \;\;\;\;\; \hat{x} = \begin{bmatrix} 1 & x_1\\ 1 & x_2 \end{bmatrix}$$
and
$$\hat{y} = \hat{x}\hat{b} \to \hat{b} = \hat{x}^{-1}\hat{y}$$

These are the steps applied to the example:

1. The linear equation was converted into its matrix form.
2. We got $\hat{x}^{-1}$ by using Excel's function =MINVERSE(array)
3. The formula $\hat{b} = \hat{x}^{-1}\hat{y}$ is then applied using the Excel's function =MMULT(array).

Sample2:  matrixOLS.xlsm

Given the following dataset, find $b$.

Solution 1: Matrix

Sample 2 involves a series of $x$'s and $y$'s. If that is the case, solving for $b$ would involve some transformation. 

$$\hat{b} = \hat{x}^{-1}\hat{y} \to \hat{b} = (\hat{x}^T \hat{x})^{-1} \hat{x}^T \hat{y}$$

The image below shows the solution for Sample2.

Solution 2: Graphing

1. Select the data to regress. $x$ must be on the first column followed by the $y$.
2. Click on "Insert" Ribbon -> "Charts" -> "Scatter" -> "Scatter with only Markers".
3. Right click on one of the data points of the Graph.
4. Select "Add Trendline" or "Format Trendline"
5. Check on the "Display Equation on Graph." The slope is $b$ and the y-intercept is $a$.