Showing a time crunch in a spreadsheet?
May 24, 2013 8:11 AM   Subscribe

How do I represent the fact that my team and I experience time-crunches caused by late submissions of documents to us on a spreadsheet showing the amount of work required of myself and my team for the coming year?

I have been asked to fill in a spreadsheet showing the amount of work required of myself and my team for the coming year to complete 14 major projects (most of which are large-scale meetings involving the highest ranks of our organization). The instructions were clear: don't just take the total time available to the team and allocate it, show the real amount of time the projects will require, in order to determine potential gaps between the time required and the resources available.

The situation is this: a clear, well-thought-out process exists that allocates enough time for us to do our work, which is to organize and run the meetings, and do the editing, translation and QA on documents going forward to the meetings. However, people providing documentation to us are usually several days to a week late, forcing us up against our own (immovable) deadlines. This puts us in serious crunch mode, forcing us into long hours and lots of overtime, and sometimes even preventing us from doing the detailed kind of QA that the senior executives want. This has been getting me in hot water with my boss (B) and his boss recently (BB).

Calling in B and BB to help us push people into providing the documents in a timely way hasn't worked (BB favours the doc providers and prefers to put us in crunch mode over forcing them to work to their timelines). Explaining the time-crunch to B and BB hasn't worked - BB thinks we're just lazy and need to work harder. Looking for ways to modify the process to account for the time lag hasn't worked either - BB is a control-freak who wants full approvals on everything before the meeting.

Tl;dr - I need a way to show a time crunch on my spreadsheet. If people don't get their work to us on time, we feel the crunch and must work up against hard deadlines to get the work done. How do I make it clear to the boss the impact this tardiness has on us from a time-spent perspective?
posted by LN to Work & Money (11 answers total) 6 users marked this as a favorite
How do I make it clear to the boss the impact this tardiness has on us from a time-spent perspective?

Start missing deadlines. If you feel you have made it sufficiently clear to your employer that tardiness on the part of others is causing your group to not be able to work optimally and if you feel you are optimally utilizing the time available to you and that you still can't make the time work, then there's nothing else you can do. Start ignoring deadlines and quoting time with respect to receipt of documents.

From an employer's perspective, an employee/group that magically makes deadlines work all the time and doesn't require any extra resources/money to do so is a great employee/group to manage. Until you stop working around your coworkers' delays, "the boss" is not really interested in what you need to do to make things happen. After all, why should he/she?
posted by saeculorum at 8:18 AM on May 24, 2013

Make a Gantt chart. Here's how to do one in Excel.

Make sure you put in entries to indicate when the upstream work was due to your group and when you actually received it.
posted by jamaro at 8:22 AM on May 24, 2013 [5 favorites]

Are you and your team salaried or hourly? Because if you are hourly than you could provide the costs for each of the two scenarios: doc providers are on time, your team needs no OT; and doc providers are late and your team requires OT. Looking at the yearly OT costs would certainly catch my attention.
posted by emkelley at 8:28 AM on May 24, 2013

You may want to consider looking at the critical path and identifying work effort associated with the work, dependencies and potential float available per task. Here is an example in excel.

This will show which tasks have zero slack available (aka do not delay those activities) and will therefore impact your final delivery. Not all activities will be on your critical path and may not impact your schedule. It depends on the dependencies.

I've done these manually before and they are a crap ton of work but provide valuable information. I haven't done them in excel before. Does anyone have a project management tool available for use? I can't imagine having to do this for 14 projects by hand.

Or yes, miss deadlines and say it takes x hours to complete this. If you turn it over two days late, we lose 16 available hours on our plan. To make up for it, we need OT or a schedule adjustment. But I imagine you are being asked for this to avoid that issue
posted by polkadot at 8:33 AM on May 24, 2013

I too like the Gantt Chart.

Another option is to present a chart wherein multiple due date possibilities are possible.

For example:

If documents received by mm/dd/yy---X hours

If documents recevied by mm/dd/yy---XX hours

If documents received by mm/dd/yy---XXX hours
posted by Ruthless Bunny at 8:35 AM on May 24, 2013

Yes, a Gannt chart.

The items are users, beginning/end timestamps and duration between start and end point of work. Cull that data from your users, get it mapped on a calendar then create a chart representing of the rise and fall of time needed to complete a task. How you display it is up to you. However, having that initial raw data is the key.

Armed with that data, you can also apply associated costs to give you a value in whatever currency you use.

You can go in a bunch of different directions, but the tedious work of getting the basic dataset is the first - and really biggest - step. The reporting end is easy.
posted by lampshade at 8:36 AM on May 24, 2013

nthing Gantt chart.

Also, putting things in financial terms. If you can show that there is a negative financial impact to this crunch, this may persuade B & BB to put pressure in the right place.

However, given that you are pre-planning -- I think looking at the critical path is helpful. Is there any way you can get the doc providers to start earlier to build in their delays? Is the amount of time they are given reasonable or will they miss any deadline?
posted by hrj at 8:46 AM on May 24, 2013

The joy of spread sheets is that if you write a parameter in you can vary them I would write a spread sheet with a timing parameter and use it to create optimal n sub optimal outcomes.
posted by BenPens at 9:12 AM on May 24, 2013

Here's what I would do. I would have one table (Table1) that has minimum six columns by 365 rows: a full range of dates for a calendar year, a "IsWeekend" flag (True/False) that can be calculated with IF and WEEKDAY functions. The third column is a "IsHoliday" function that is manually entered. The fourth column is total hours available for the team, it is 0 for weekends/holidays and a calculation that divides the sum of hours available for the year (see Table2) by total workdays in year (calculated as 365-weekends-holidays). The fifth column is projected hours - ideal dates worked by the team next to it. The ideal hours work should be a calculated column. The sixth column is projected hours worked - expected dates. These are calculated with help from Table3. There may be scratch or helper columns for intermediate calculations.

The table that you present to management is a pivot table on top of Table1 summarizing team load, calculating % overtime days and % slack days, etc.

There would be a second table (Table2) indicating team availability. This would have 1 row per team member. This is name, status (full time/part time), vacation time available, sick time available, final calculated column is sum of hours available for the year. You may have a single named cell that sums the total hours for the whole team for the year. Keep it simple, assigning dates to their vacations for now.

There is a third table (Table3) with 14 rows, one row per project. This is the fun table.

First column is due date of project, sorted by date. Second column is project name. Third column is total hours required for project, this is calculated. There should be a fourth column for total hours before documents are returned, and a fifth column for total hours after documents returned, total of these two should add to the third column. There should be columns with project specific attributes that you think is helpful to calculate total hours required - for example if there are project types with different sets of tasks or different times required, include the project type here. If you want to break down a common set tasks for each project, this should be done in a separate table and referenced in to facilitate calculation. There should be column with expected date of document hand-in, if the document review team consistently hands in at last possible second (say 3 days before due date), calculate Due Date - 3 working days.

With this info you can in Table3 identify if there is team overwork for a single project with expected hand-in dates assuming team is 100% allocated to a single project, as you can figure out how many hours are required before hand-in (from previous columns) and then show hours per day worked. Now you can go back to Table1 and calculate the post document hand-in across all projects, probably by VLOOKUP in Table3 and SUM hours per day worked before hand-in. This will show overage in the crunch time.

Now here's the really fun part. You have to calculate the ideal date for hand-in to get a balanced 100% load across the year. Unfortunately due to time constraint I leave this as an exercise for you. However this calculation can be done somewhere in Table3, you can calculate the start date of project assuming 100% allocation for a single project across days available. Then go back to Table1, do a similar calculation as previous to see if there is overage when 100% allocating per project. You can then manually go back and forth between Table3 and Table1, probably easiest way is to now enter guess for ideal start dates for each individual project in a new column (next to the calc one) and going back to Table1 to see if there is 100% balanced load yet. Once you have 100% balanced load, calculate the ideal hand-in date from the start date.

And then from there, you would have to go back and calculate load with expected hand-in date. I would show the slack by always starting at ideal start date, averaging out the time before hand-in (thus showing slow hours per day), then using crunch calculation as previous and sending back to Table1.

Table3 is tedious work but should be do-able with appropriate supporting tables.
posted by crazycanuck at 10:09 AM on May 24, 2013 [3 favorites]

Make sure that your spreadsheet vividly represents the opportunity costs (in terms of staff hours and analysis quality) due to your team not being able to work because the documents haven't been provided.
posted by neutralmojo at 11:21 AM on May 24, 2013

You say you already have a clear well thought out process, so I suspect you may already be Gantt-charted up, with deadlines already clearly set and communicated. It seems that you have a problem with the credibility of those deadlines, and this seems to be for a combination of two reasons: lack of clarity over the impact of the deadlines, and a lack of respect for how they were determined.

Take these one at a time.

To illustrate the impact of a missed submission deadline, keep it really clear and really simple. You need the papers x days before the meeting because your team needs to expend y hours over z days to process them. If you get the papers on day x+1, you still need to expend y staff hours, but you only have z-1 days. A simple bar chart will show how staff hours required per day increase the closer you get to the meeting: you put "deadline missed by (x) days" on the x axis and "total staff hours needed per day remaining" up the y axis. Turn the columns amber when you need to start incurring overtime to deliver on time, and red when, even with overtime, you can't complete the processing by the deadline. Simple, clear, and unarguable.

I'd probably put this in a callout box for each key deadline beside the project plan, though after the first couple, people will get the message. (Oh, and incidentally, this analysis would also enable you to illustrate the cost of missed deadlines, which should help get BB's attention.)

But, your second problem seems to be that there isn't a strong consensus around the amount of procesing time that you need in the first place (BB thinks we're just lazy and need to work harder). Firstly, start seeing that as a separate problem and present it separately. If this is really the root of your problems you're going to have to go right back to the original time budget that led to your original "hours needed" calculation. Review your own assumptions and satisfy yourself there isn't any justification for BB's scepticism, and then make sure it's set out as clearly as it can be. I'd probably put this in an appendix to my report so it's there if BB wants to challenge the underlying analysis that your "impact" charts are based on, but it doesn't interfere with the clarity of your argument for others who accept that you've done your sums right.
posted by genesta at 2:13 AM on May 25, 2013

« Older You're perfect, I love you, now change.   |   If I Told You, Would They Have to Kill Me, You, or... Newer »
This thread is closed to new comments.