Use Excel for regressions

- [Instructor] It's time to use a hedonic prediction. I'm in the 03_04 folder looking at the begin employee theft file. We've got a basic question. Jack has assigned employees one, three, six and seven to work today in the store. Manager three is also working. What should we expect the over/under on the register to be at the end of the day? In other words, should we expect the register to have more cash in it versus sales? Or should we expect the register to have less cash? We're going to use the regression that we ran previously to answer this question.

So, I'm going to set up our variables as follows. Cash in register is going to be the variable that we're trying to predict. Employee one, employee three, employee six and employee seven are all working. And manager three is working. Now, I've got my values.

I'm going to use my Y and my Xs here. In this case, employee one, three, six and seven and manager three are all working so each of them get a one. I'm not showing this, but if we wanted to add, say, manager two, we could. Manager two is not working so we'd simply put in a zero for manager two. We could do the same thing with the other employees. We're leaving them out to simplify this as much as possible. Now we need to look at the coefficient for each of these employees.

So the coefficient on employee one is 7.403. The coefficient on employee number three is negative $1.74. It's positive 33 cents for employee six. And $6.66 for employee seven.

Manager three has an impact of negative 2.52. And again manager two is not working but we'll include them nonetheless in here. Now, to figure out our hedonic prediction, we just need to multiply the X values by the coefficient. So these are our coefficients. So I'm just labeling my variables and my coefficients. Now I've multiplied the coefficient by the variable in question and I'm going to drag and drop from here.

Now to get my hedonic pricing model, I'm simply going to add up all of these multiplied values and this tells me that my predicted over/under is $10.09. $10.10 for the day. So given all of these employees are working, we would expect the cash register, on average, should have a positive balance of $10.10 at the end of the day.

In other words, these particular employees, on average, over time, will not only not take money from the register, but they will leave money there any time a customer gives them spare change or they will deter theft by other employees, et cetera. This is called a hedonic model. All we've done is take the coefficients in the regression and then multiply them by our X variables in question.

This type of model is widely used in industry. For example, commercial real estate pricing is often based on hedonic models. Investing in the stock market is sometimes done with hedonic models. This is a very powerful tool that you can use in a variety of circumstances. Next time, we'll take a look at how we can use this type of analysis in forecasting.