Simple “Predictive” Analytics

What if your company is not on the analytics kick or does not make enough money to afford those fancy software tools advertising predictive analytics or some kind of analytics? What if your peers simply do not have any interest in exploring how to use data and find valuable information?

You might still be able to do some kind of simple analytics, but it would just be of a form used before all of the buzz words came out. Today we have decision trees, cluster models, regression models and time series. But maybe before the advent of specialized computers and big data, we had simpler techniques to deploy for analysis. Regression models and time series – the kind I’m thinking of – might have dominated and possibly deployed within Excel.

I didn’t use regression models, but I think I used some kind of time series (again, the kind I’m thinking of for finance). What I did was not complicated; it just required some creativity to think of using those “predictive” analysis.

Instead of putting my forefinger into the air to gauge which way the winds were blowing during forecast, I did deploy some kind of analysis. Since it was financial forecasting, it was generally a time series type of analysis.

In the PDF below, I explained about a situation when I was managing 100+ projects with a team of 3 other people. We were starting to miss when projects overran the contract budgets. We will simply just trying to keep our heads above water with all of the tasks it takes to financially run those projects.

Those misses were starting to impact our revenue and gross margins.

I came up with a very simple method of flagging which project might run out of money by the end of the month. With that advance notice, we were able to reach out to the project managers and warn them and help them do the necessary next steps to keep the project viable. That simple method was averaging.

Yeah, everybody knows how to use averages, so it is a simple form of analytics.

Check out the PDF that describes in detail on how this was done.

Okay, I’m having problems getting the PDF to set up properly, so I’m going to add select pages but enlarged. Click on the images to “blow them up” into a new page and hopefully the numerical details will become sharper.

This first image shows the original layout of the Excel spreadsheet with the remaining budget left to spend under column R, and the actual weekly expenditure under columns C through L. These numbers were pulled in probably via VLOOKUPs or array formulas from some data download.

To get a better image, especially of the numbers, click on that image below and the numbers will show up more clearly. Why? I don’t know.

To back out of the image and return to the web page, click on the back arrow in upper left-hand corner of your screen or web page.

The next page of the slide, or the image below, are the end result after working to add the predictive aspects. The changes/additions are outlined in red. Click on the image to get a clearer look at the numbers and the back arrow to return to this page.

This next image discusses some elements of the added changes. Here I’m talking about how many number of weeks I want to average the weekly spend rate for each project. In the example below, I had 8 weeks of spending to arrive at the weekly average for each project. That 8 is a hard-coded number. Depending on how fast things changes, you might want to use 4 or 6 instead.

The section labeled as number 2 is showing which weeks are being included in the averaging. Section 3 shows the specific formula for those x’s.

Again, if you need clarity, just click on the image.

The next image displays how the average formula was created. Column D is the column where the averaging was calculated, and the formula bar depicts the formula used. Note how the formula incorporates the x’s.

The next image is slightly more complicated. If I were trying to do a predictive analysis at the beginning of the month, then I would use 4 weeks to calculate 4 weeks of spending using the weekly average. The 4 weeks is roughly comparable to a month. If I’m midway through the month, then I would use 2 weeks as a rough estimate.

The next image depicts the formula to generate “!!!” when the forecasted spending for the month will use up all remaining budget money. The remaining budget is found in column R, so column C is compared to column R, and whenever C is greater than R, a warning “!!!” is shown in column B.

Hopefully, this example will give you some ideas of how to do some simple analysis to improve your business if you don’t have the money for the fancy stuff or you are not in an environment that encourages trying to move into the future.

[Note: By the way, I haven’t felt like doing much blogging because I’m trying to do some stuff that is taking up a lot of time and on top of that, the heat is infernal. I’m trying to learn Python and I’m taking the CS50 course from Harvard and the problem sets they have just takes so long to figure out. Between that and the heat and everything else, my posting here may be reduced. I just haven’t felt like doing anything. We’ll see.]