top of page
Rock Formation

Regression Analysis

In the previous section, I have already touched on the subject of statistics. Now, we are going in hardcore by looking at regression analysis! You will have come across it at school, and it is one of these topics well worth keeping! It is of good use in the real world and another essential tool in our finance and accounting toolbox.

The nitty gritty

Let us consider the following situation:

You are working for a company that keeps guessing what ultimately drives its sales! The sales director asks you for help as he heard you are a data Wizz! You are more than happy to help and get right to work with your newly acquired knowledge about the importance of data and regression analysis!

First and foremost, like with so many things, it is essential to collect data because we need data to perform meaningful analysis! Luckily our company Jolly Brew Ltd. now the leading brewer of Brewlandia is serious about its data and has collected the following information for us to make sense of.

 

 

 

 

 

 

 

 

They want to know what drives their sales, and we are happy to advise them with a bit of regression analysis. Regression analysis gives us a mathematical way of sorting through this data to establish what impacts our sales! However, a bit of vocabulary first: The sales are our dependent variable and the number of sunny days and advertising expenses are so-called independent variables.

Regression data_edited.jpg
Source_Spieworks_edited.jpg

To your left side, you see a graphical example of what regression analysis tries to achieve. To use our example, on the y-axis you plot the number of sales (the dependent variable), and the x-axis is the total advertisement cost. Each blue dot represents one month’s data—how much the company spent on advertisement and how many sales the company made that same month. After plotting all the data points you try to establish a relationship between the dependent and independent variable by trying to draw a line through the data points.

The so-called regression line is actually not really drawn by yourself but by a statistical program. No need to worry; excel can do the job just fine. For excel to perform it you have to load the "Analysis ToolPak add-in" which you can easily do by typing this into your excel search bar and and then tick the option. Afterwards your excel should look a little bit like this: 

Excel linear regression_edited.jpg

When you click on data the "Data Analysis" function should appear on your right side. When you click on it and scroll down the regression option should appear. The following window (see above) should appear where you simply select the input range for the y-axis (the dependent variable, aka beer sales) and then the x-axis (independent variable, aka advertisement costs). The last thing to select is to either display the results in a new workbook or the same worksheet underneath your data. If you like to have it displayed in the same worksheet tick the "Output Range" option and then select any cell below your data.

The results will look as follows:

bottom of page