1

I need to forecast budgeted hours for 2 departments to help them schedule staff as currently the best they can do is just wing it. When we receive a job proposal I'll have the person submitting the form input that job's budgeted hours into each department as float field.

Jobs can have hours budgeted for Dept. A, Dept. B, or both. Jobs have start and end dates. It's been decided by my supervisors that simply dividing the job hours evenly across all days will be a useful gauge. What I want to do is sum [all jobs] daily totals for Dept A and Dept B. Here's where it get's complex...

  • Dept. A works 7 days a week and Dept. B works regular business days.
  • Both departments need the flexibility to mark a day as closed and take the hours allocated to that day and push them to the remaining days.
  • But I also want to be able to put holidays in or events where we know far in advance we won't be open and instead of pushing the hours forward it distributes them back and forward. For example, Dept. A will never work on the day of a Superbowl.

I am looking for recommendations about how to approach this problem, specifically the days closed. I was thinking I might have a table in the database simply tracking closed days?

I'm not looking for help writing the code but rather help understanding how to design this process more generally.

1

I'd model a calendar (ie an array of 365 elements).

the difficulty using this system is that a block of days gets allocated as many individual days rather than a single entry, however I expect you'll be assigning a tag or some label to each logical entry that can be used to identify each block of days so that you can edit a single (logical) entry.

The advantage to storing everything daily is that you can pre-allocate weekends, holidays, team-meetings as you like, but also individual members holidays (or other absences from working) even if that means simply reducing the total hours available for a particular day.

Its also easy to understand, and reporting/searching on the model is easy too.

0

You're going to need a list of days closed and possibly even make it group specific. Writing an algorithm to predict the date of the Superbowl next year is just not worth it.

This allows you to create a list of all the days in a given time range in your code to exclude. Some database-centric solutions have a table with all the days already in it for a reasonable length of time: since the company started to 5 years in the future.), and then flag any days (weekends, holidays, other/Super Bowl) because it's easier to read/join data than create it.

The biggest hurdle may be getting someone to populate this table. Weekends and holidays that are always on the same day of the year, can be generated. You could flag individual groups/teams that always or never work weekends, but the exceptions will just make this more complicated. There will be exception.

  • I fully expect many of these days to be manually adjusted. I estimate 50-70% of the closings being unpredictable, like snow days or closed due to lack of need. For example, we have core staff that must fill weekday hours in Dept A. Such that it doesn't make sense to open both days on the weekend if we are low on work. – click here Jan 25 '16 at 15:28
0

So, it sounds like you want something relatively fluid. If yesterday was a snow day you could add it the day after and then rerun calculations to look at the workloads (theoretically) required to meet deadlines.

Just thinking on the fly here:

Jobs:
Job ID, Name, Start Date, End Date

Depts:
Dept ID, Name, WeekType

JobHours:
Job ID, Dept ID, Allotted Hours

Now, if you need to mess around with recalculations due to additional hours, deadlines extended, deadlines missed or other real life issues that intrude on simple solutions, you'll need a way to "recast" the project from a point in time.

You also have the flexibility, over time, to add additional departments. Maybe if a schedule can't be met third party developers would be scheduled and perhaps have different work days or hours per day expectations.

More pseudo-table thoughts:

JobDeptRecast:
Job ID, Dept ID, Recast Date, Hours Consumed, Hours Added, New End Date

In essence, for scheduling purposes nobody cares about the past after the recast (as you aren't trying to track hours worked or anything like that). The recast is conceptually a "new" project as of the recast date.

Now we get to the list of exceptions to the "expected" hours per day. I say expected as there could easily be days where hours are higher than the capacity to work for any number of reasons - including the ones you mention.

And more faux DB material:

WorkExceptions:
Job ID, Dept ID, Date, Hours Excepted, Reason

Sewing some of this together it would seem that you could do date math on the number of project days (adjusting for weekends whether by list, estimating weekends within the period, or just looping through days and looking for non-work days as the project won't be all that long) minus the number of hours of exceptions found between the start and end dates.

However, if you allow for a recast, you should first see if you have adjustments to the project that need to be taken into account.

I expect this model would be fairly fluid for a system only expected to spread hours between dates going forward without attempting to account for actual time usage or employee level details.

Basically, just run all the non-ended jobs from the last recast, if any, summing exceptions* in the past to get work missed and summing exceptions* going forward to reduce expected work days between now to deadline date. Unless you have a zillion jobs it should be very fast - and every exception added after the fact turns prior calculations into lies anyway.

For bonus points, because this situation exists in the company I work for, you may have a different number of hours in the workday for departments in different geographies. If you wanted to show the number of people needed to meet the planned work effort you could put the hours per day in the department table to do the proper calculation.

I hope that gives you some things to think about. Of course, I won't make any promises for a series of quick thoughts but i'd be willing to prototype based on the above if I had to the same task (assuming I interpreted your question correctly).

Hope that gives you some ideas.

[*] Don't forget to estimate, generate on the fly or load previously populated weekend exceptions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.