• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare Actual vs Planned Labor Hours over time

test

New Member
Hi,
I don't know why but I am having difficulty even getting started on this task.

I was asked to chart an employee's planned work effort against the employee's actual work effort over an entire year. As an example, employee A states they have the following planned work effort. There are 40 hours in a week so 160 hours in a month:
Job 1: 5% of their weekly hours from January 1, 2021 - December 31, 2021
Job 2: 25% of their weekly hours from January 1, 2021 - December 31, 2021
Job 3: 15% of their weekly hours from January 1, 2021 - December 31, 2021
Job 4: 40% of their weekly hours from January 1, 2021 - December 31, 2021
Job 5: 15% of their weekly hours from January 1, 2021 - April 15, 2021
Job 6: 15% of their weekly hours from March 15, 2021 - September 28, 2021

During the period of March 15 - April 15 the employee has plans to work more than 100%
After September 28 the employee has plans to work less than 100% because the job ends.
It is possible another job is assigned to get the employee back to 100% or above
One of the goals is to determine when an employee does have less than 100%.

But, reality is often different than plans. So, actual work effort needs to be compared against the plan. As an example, the employee may have the following actual work effort:
Job 1: 6% of their weekly hours from January 1, 2021 - December 31, 2021
Job 2: 31% of their weekly hours from January 1, 2021 - December 31, 2021
Job 3: 15% of their weekly hours from January 1, 2021 - December 31, 2021
Job 4: 34% of their weekly hours from January 1, 2021 - December 31, 2021
Job 5: 14% of their weekly hours from January 1, 2021 - April 15, 2021
Job 6: 15% of their weekly hours from March 15, 2021 - September 28, 2021

The above needs to be shown in one chart. I have tried stacked column charts, clustered bar charts, and made my own GANTT charts. However, none of them show the data clearly, concisely, or correctly. I envision months on the X-axis, and jobs on the Y-axis with two horizontal data sets; planned & actual. But, when doing this I get a planned bar spanning January thru December, and an actual bar showing year to date work effort. But, it needs to show the percent for the month vs the actual. Hmmm...maybe I need to have a start & stop date for each month vs. the entire year. ...Hmmm Let me go try that but in the meantime if anyone can help I would appreciate it. Can anyone help get me unstuck?

Thank you.
 
Last edited:

vletm

Excel Ninja
test
You should have some kind of visual image - what do You have in Your mind ...?
After that, You should upload here a sample data.
... or You could get something like this.
Screenshot 2021-03-10 at 18.54.46.png
 

test

New Member
I like your chart and I am going to attempt to duplicate it. Could you also provide the instructions in case I am unsuccessful?

However, what I was envisioning is below. Is my idea possible?

Job #Planned Work PercentStartEndNote
Job #1
5%​
1/1/2021​
12/31/2021​
Job #2
20%​
1/1/2021​
12/31/2021​
Job #3
15%​
1/1/2021​
12/31/2021​
Job #4
30%​
1/1/2021​
6/15/2021​
Job #5
18%​
3/15/2021​
6/30/2021​
Job #6
7/1/2021​
12/31/2021​
12% planned from 7/1/21 - 9/15/21 then 30% from 9/16/21 - 12/31/21

From 1/1/21 until 3/14/21 the employee is only assigned 70% of their time: there is 30% free time available to work

From 3/15/21 - 6/14/21 the employee is assigned 88% of their time

From 6/15/21 - 6/30/21 the employee is assigned 58% of their time

From 7/1/21 - 9/15/21 the employee is assigned 52% of the time

From 9/15/21 - 12/31/21 the employee is assigned 58% of the time

The yellow bar details the duration of the job
The green bar is the actual time worked
The blue bar is the actual time planned to be worked

73727
 

Attachments

vletm

Excel Ninja
test
Are those Your blue and green bars as Your given values?
... where comes the lowest right 18% value?
... Your text let me understand about 30%.
Here my version with those values.
Screenshot 2021-03-12 at 19.14.27.png
What were those Your five 'from ...'-sentences?
 
Top