Attempting to Predict Future Employee Attendance Occurrences

In a separate thread, a few contributors, namely FlameRetired, helped a great deal in creating a spreadsheet that functions as an Attendance Points Tracker based on certain occurrences, like coming in late, leaving early, no call/no show, etc.

The establishment I work for uses this in what I consider a largely reactionary way. Once an employee reaches a certain number of attendance points they will hit certain corrective milestones, and as such receive the corresponding corrective action.

What I'm seeking to do is try to use the historical data for each employee that essentially builds up in these spreadsheets to try and predict or forecast when an employee will hit a corrective milestone.

To me, this would be beneficial in that it would make the system usable in a proactive manner, where a supervisor could use this data to coach an employee and alert them to the behavior and give them the opportunity to address the issue before it results in corrective action. On the other hand, it is also beneficial to the supervisors, in that this same data can help them prepare for when an employee may need to be replaced, in the event said employee chooses not to correct the issue, ultimately resulting in their termination.

So, with all that in mind, I would greatly appreciate any help anyone can give me in this pet project of mine.

Also, I have already played around a bit with the FORECAST and TREND functions, which seem to be somewhat accurate in predicting the number of occurrences may happen in a given month, but the margin of error is a bit greater than I would like and it doesn't really accomplish the goal of what I stated above.

Thank you very much, Dave!

This is the Attendance Point Tracker from the other post, with a few edits and additions by me so it would work well with my company's needs.
What I'm trying to do is use the data available in this spreadsheet to attempt to forecast with some accuracy about how many occurrences are likely to happen for a given agent for each month.
The idea driving it is to be able to provide an agent with a data representation of their attendance behavior in an attempt to fix any issues with said behavior before it leads to corrective action for the agent. To try and make the system proactive and not just reactive.

Any help with this is greatly appreciated.

And again, big shout out to Dave, aka FlameRetired, for being as helpful as he has up to this point, especially with me being new to the forum. His Forum Guru title is well-earned and deserved, indeed!

I do apologize, that should've been stated explicitly in my posting of the file.

Yes, the focus of my efforts thus far have been in Sheet 1. Not to mention, I share your same concerns about the TREND and FORECAST functions, as I am not sure precisely how it is calculating or what all mechanisms are being utilized to arrive at the answer displayed.

To address the other things of note:
1. Q16 was a test to see if the relative cell referencing could produce some kind of numeric constant, as a way of perhaps using it in some way to tailor a forecast to a specific employee. This stems from my logic of each employee behaves in a somewhat different way from the others, which would result in a different overall attendance pattern. Unfortunately, I'm not enough of a mathematician to really determine if this was a sane or logical method by which to tackle that assumption, or if even the assumption I was making was a valid one.
2. Column O was the result of doing a bit of research through some of the financial market websites as well as future profit analysis. That column was merely introducing the constants I found through that research to insure I was properly constructing the equation and yielding the same results as those websites, then applying it to my test data set in Column J. It's what led me to trying to use the TREND function to trying to see if there was a discoverable constant within the historical data that could potentially serve to tailor the calculation, as stated above.
3. Again, Column G was a utilization of the TREND function in a fashion to see if that would accurately predict future occurrences, wholly separate from everything in Column J on. I am also unsure if this is a sound application method for this function.

Aside from all that, I greatly appreciate you taking a crack at this, Dave, as well as communicating with other members more business/statistics savvy than either of us, and sincerely hope they come to our rescue as well!

JeteMC:

Thank you for the information and the insight! I am definitely going to give that a look and see if it will work. I've been beating my head against my keyboard trying to figure out a way to try and tailor a Linear Regression Model to do this. In theory, this is something an LRM should absolutely be able to do, I'm just having trouble figuring out how it would:
A. Be constructed in Excel, if it can.
B. Actually tailor the model, i.e.; setting the parameters, determining the independent value, and setting the appropriate error terms.

So basically, I have an idea of what I would need to do, just not very certain how to go about it.

Thank you again for this setup though, Jete. I'm going to try it out and will probably report back to you after the Christmas holidays.