In this video, the instructor discusses using Excel to forecast employee behavior and financial performance.
- [Narrator] We've got an emergency. Jack has just discovered some suspicious cash shortages at a company outlet store. He was in the process of beginning his sales forecasting when he discovered we've got some missing cash. In fact, theft and cash handling are major issues at many firms, so Jack is not alone here. But, the key question he's coming up with is could an employee be stealing from the firm? Let's find out. I'm in the 03_02 folder in the file labeled Begin_EmployeeTheft.
What we've done is gone through and gathered data that Jack put together for us that covers various cash shortages that have occurred over the last year. And, in fact, Jack has found 41 days over the last year where there was either a cash shortage or a cash overage. There are eight different employees and three different managers who work in this particular outlet store. What we need to figure out is are one or more of these people stealing from the company? Even if we don't think they're stealing, perhaps it's just careless cash handling, and we need to go through and retrain all of the employees on cash handling procedures.
Whatever the answer, it's clear that we are missing cash from the register, and this is a problem we need to get fixed now. Our job is to figure out which employees or managers might be responsible for these problems. How could we go about doing this? Well, what I've done is gone through and labeled each employee and each manager with a one or a zero for each day when there was cash missing. So, for example, on this first day where we had a loss of $15.10, we had $15.10 missing from the register, employee one, two, three, four, and five all worked on that particular day.
And, manager one also worked on that day. The next day where we had a major cash loss we had employee one, two, four, six, and manager three working. In order to try and isolate which particular employee might be responsible for these cash losses, we could go through, and we could see if maybe there was just one employee that was working all the days when there was a cash loss. Jack's already done that for us, and he's discovered that it doesn't look like there was just one employee who was working only on those days where there were losses.
That could mean one of two things, though. It could mean either A, there's multiple employees having problems with cash handling, or B, and more likely, because we have lots of different customers coming in and out, and there are lots of employees coming in and out and using this same register, we have some degree of natural variation. In other words, a penny or two gets accidentally handed out or taken in in any given transaction. Jack's gone through and isolated only the most egregious examples, but we still don't know for, say, the $15.10 loss, how much of this is due to poor cash handling versus how much of it is due to outright theft or negligence on the part of the employees.
To figure out the answer to this question, we need a regression analysis. In order to run a regression analysis in Excel, we're going to need to go through, and we're going to need to download what's called the Analysis ToolPak. This is a special edition or add-on to Excel that'll let us do some very creative and useful analysis. So, I'm going to use Google, and I'm going to type in analysis toolpak, and I'm looking for this from Excel.
So, now we've found the instructions that are going to let us load and use the Analysis ToolPak. So, I'm going to go File, Options, Add-ins, now I'm going to load the Analysis ToolPak, click Manage Excel Add-ins, check this file, Analysis ToolPak, then click OK. Now, you should see under our Data tab, we've got the Data Analysis tool that has appeared.
And, if we click Data Analysis, and scroll down, we now have Regression available to us. Our Y input range is the value that we care about. In this case, it's going to be all of our cash losses. Our Xs are going to be all of the employees that were working across all of these days. And, I'm going to check Labels, so that my output will contain information on which employees we're talking about, and I'm also going to check this box, Constant is Zero.
The reason I'm checking this is because I would expect that if the business is being well-run, there should be no cash overages or cash shortages. So, my constant should be zero, that is, there should be no difference between what I expect to be in the register and what actually is in the register. Now, I'm going to click OK. And, it'll take just a moment, but we'll see that on a new spreadsheet we've populated with this regression.
Now, I'm going to widen my columns slightly to make this easier to read, and I'm going to remove these last two columns, which are duplicates. I'm also going to come down here, and I'm going to change my numbers to make them easier to read. Now, what we see looking at this data is the R squared tells us that our regression can capture roughly 65% of any of the losses that are expected.
In other words, this isn't a perfect regression. There are still going to be days where one of our employees accidentally hands out an extra quarter when making change, or accidentally hands out an extra $1 bill, or where a customer gives us a little bit too much money, and then walks out. A transaction might be for $25, and they give us $24.75, for example. But, we're going to capture most of the variation in our data here. Now, looking at this, who do you think is the problematic employee? We see positive and negative numbers on coefficients.
Employee one, Employee six, seven, and eight all have positive numbers on their coefficients. Employee two through five have negative numbers. The biggest negative number though, is employee number two. The way to interpret this is that on days where employee number two works, all else equal, the register comes up $23.74 short. We can look at the P value over here in column E and see that that is a statistically significant outcome.
The interpretation on that P value is that there is only a 1.2% chance that this is random. In fact, it looks like employee two, either through theft or carelessness, is responsible for significant losses at the store. The other employees that have negative numbers, we see that those P values are considerably higher, so it might just be random chance that they happened to be working when cash goes missing.
The one person we can conclude this is not random chance for is employee number two. This is very powerful. In fact, it gives us a way to look at many different employees across a long period of time and identify which employees are probably most responsible for our cash losses. Now, Jack can take corrective action with employee number two. What's more, this same tool that we've just used could be used more broadly.
Rather than think about this in a negative context, like shrinkages or theft, for example, we could think about this as the opposite. Perhaps we've got particular employees who help customers and upsell them on new products. We could run a similar analysis that would tell us which employees were most effective at promoting new sales or helping us to grow our revenue. Wouldn't that be a useful tool for you and your firm?
Join Professor Michael McDonald and discover how to use predictive analytics to forecast key performance indicators of interest, such as quarterly sales, projected cash flow, or even optimized product pricing. All you need is Microsoft Excel. Michael uses the built-in formulas, functions, and calculations to perform regression analysis, calculate confidence intervals, and stress test your results. You'll walk away from the course able to immediately begin creating forecasts for your own business needs.
- Understanding big data and predictive analytics
- Gathering financial data
- Cleaning up your data
- Calculating key financial metrics
- Using regression analysis for business-specific forecasts
- Performing scenario analysis
- Calculating confidence intervals
- Stress testing