+ Reply to Thread
Results 1 to 9 of 9

Attempting to Predict Future Employee Attendance Occurrences

  1. #1
    Registered User
    Join Date
    11-14-2018
    Location
    Lufkin, TX
    MS-Off Ver
    2007, 2016, Office 365
    Posts
    8

    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.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Attempting to Predict Future Employee Attendance Occurrences

    Hello again druchek.

    Since you are not able to post a link to the thread you refer to at this time I'll take the liberty of posting it for you. Others can be working on it in the meantime as well.

    Attendance Tracker with a rolling six month period?
    Dave

  3. #3
    Registered User
    Join Date
    11-14-2018
    Location
    Lufkin, TX
    MS-Off Ver
    2007, 2016, Office 365
    Posts
    8

    Re: Attempting to Predict Future Employee Attendance Occurrences

    Thank you very much, Dave!

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Attempting to Predict Future Employee Attendance Occurrences

    You are welcome.

    Also in the meantime there is something you can do at this time and that is to post a sample workbook that illustrates what you are working with and what you want to happen.

    If you are not familiar with how to do this (save yourself some aggravation and time ... the 'paperclip' icon has not worked for a while.) follow these steps.

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    The file name will appear at the bottom of your reply.

  5. #5
    Registered User
    Join Date
    11-14-2018
    Location
    Lufkin, TX
    MS-Off Ver
    2007, 2016, Office 365
    Posts
    8

    Re: Attempting to Predict Future Employee Attendance Occurrences

    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!

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Attempting to Predict Future Employee Attendance Occurrences

    Though not stated explicitly, if I understand correctly the focus of efforts are in Sheet1.

    While I know how TREND and FORECAST functions work I am not always confident in how to apply them in statistically sound ways in all situations. This is one of those.

    Three things I note:
    1. If you are not aware of it in Q16 the array formula is returning 5 values in memory. To see them click the formula bar and hit the F9 function key. Which of these are of interest?
    2. In column O there is an equation that contains some constants. What is the significance of that formula and where do the constants come from?
    3. In column G starting at G20 there is a TREND function that uses relative range referencing. I don't know enough about the proactive mission or use of TREND to know if this is sound application.

    At this point I don't even know what questions to ask.

    That said I am going to consult with the forum community. We have some business/statistics saavy members. Perhaps a few of these will come to our rescue.

  7. #7
    Registered User
    Join Date
    11-14-2018
    Location
    Lufkin, TX
    MS-Off Ver
    2007, 2016, Office 365
    Posts
    8

    Re: Attempting to Predict Future Employee Attendance Occurrences

    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!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Attempting to Predict Future Employee Attendance Occurrences

    This may or may not be helpful, as I am neither business nor statistics savvy.
    I looked at this from the viewpoint of focusing on an employees behaviors, as in chronically late.
    Overall historical data for attendance violations is displayed in rows 1:7 using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Historical data per violation (chosen in cell B9) is displayed in B10:I14 using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Projected behavior (per violation), based on the previous eight months historical (and/or projected) data is displayed in J10:U14 using: =SUM(B11:I11)/8
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    11-14-2018
    Location
    Lufkin, TX
    MS-Off Ver
    2007, 2016, Office 365
    Posts
    8

    Re: Attempting to Predict Future Employee Attendance Occurrences

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] data validation and predict future costs
    By paulunh11 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-11-2017, 11:16 AM
  2. [SOLVED] predict future costs
    By paulunh11 in forum Excel General
    Replies: 1
    Last Post: 04-06-2017, 11:47 AM
  3. Need Help - AutoCalculate Attendance Points for employee attendance records
    By switzd0d in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-17-2015, 03:00 PM
  4. [SOLVED] Predict future value and date in a table
    By bajdr47 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-07-2013, 04:39 AM
  5. Predict a Future Date?
    By Brook963 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-14-2011, 09:05 AM
  6. How do I forecast/predict future values ?
    By new2all in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2007, 08:07 AM
  7. [SOLVED] Formula to predict a future date
    By Bill Eagle eye in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2005, 10:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1