Schedule Variance Formula

[JHaugland]

Does anyone have a simple excel tool to calculate schedule performance based on the number of days in a project, hours estimated and actual hours to date to show if a project is tracking to schedule, ahead or behind?

 

[6StringJazzer]

Actual vs. estimated hours won't tell you the schedule variance, it will tell you a cost variance. For schedule variance you need to know an objective measure of progress.

What information do you have about your tasks and schedule?

I have decades of experience in using Excel and project scheduling tools to do schedule and cost variance analysis, but it's hard to answer your question concretely because it's pretty high-level.

Using Earned Value Management System, you would determine the work that should be complete at a point in time based on your baseline schedule, then determine the work that is actually complete based on progress. So if it costs $1 to make a widget, and you are supposed to make 100 widgets by now and you have only made 80, you have a schedule variance of -$20. It seems odd to express a schedule variance as dollars instead of time, but that actually makes a lot of sense--your productivity is $20 less than it should have been in this amount of time.

If you go strictly by schedule, then you have to look at a breakdown of subtasks and see what's been completed, and you could express a variance in days.

But without knowing more about what you're doing and how you schedule it, I'm not sure how to answer.

 

[JHaugland]

You sound like the expert I need!

I hope I can explain this correctly.

I am looking to create a simple chart, graph, dashboard to give a visual representation of how we are tracking to schedule.

Example: Start date = 1/21. Completed date = 2/22. Estimated hours = 140. Actual hours to-date = 0 but will change daily as the project progresses. We work M-F, weekdays.

I want to have a tool that I can enter the actual hours that creates a visual indicator to show if we are tracking to schedule. This will be posted as a visual KPI on a project board. I do not want the estimated, remaining or actual hours posted....just the 'On Schedule' or 'Behind Schedule'

I will be updating the actual hours weekly and displaying a very high level 'Schedule Performance Indicator'.

I hope this makes sense. I appreciate your help.

Thank you!

 

[6StringJazzer]

It's probably not going to be complex to do this but my concern is that you are measuring hours, instead of measuring how much work got done. Let me give you an example that I actually saw.

A manager was working on a small project (one or two people for four week). He reported to me after the first two weeks that his project was 50% complete. I said, "How do you know you are 50% complete?" He said, "It's a four-week project, and two weeks have gone by." But he could get to the end of four weeks and realize that he still had another week to go based on how much work was actually done.

What kind of work are you doing? Can the work be measured?

Do you have any kind of Excel file to start with? If so is there anywhere you can publicly post a copy (like Dropbox)?

 

[JHaugland]

Our estimated hours are pretty spot on so to measure by hours completed is a good start.

I have 10 days to complete 300 hours on this project. At day five, 160 hours have been billed to the job. We are on schedule.

I have 10 days to complete 300 hours on this project. At day five, 130 hours have been billed to the job. We are behind schedule.

It really just needs to be a green = on schedule, red = behind schedule

 

[6StringJazzer]

OK, that's pretty straightforward. You can use conditional formatting for that, although the formula will depend on what your data looks like. Do you have a column for the hours for each day? Or just one column that gives the total hours so far?

The idea is that you will use a formula for where you should be on this day:

(hours it should take) * (days it should take) / (total days gone by) = (hours that should have been billed)

If that number is > (hours actually billed) then you are red, otherwise you are green.

But without knowing how to find those four pieces of data on your worksheet I can't actually give you a formula.

 

[JHaugland]

Let’s use this scenario:

Start date: 1/21
Completion date: 2/22 (workdays only)
Estimated hours: 180
Billed hours: 35

That is the only data I will be inputting.

Start date. Completion date. Estimated hours. Billed (actual to-date) hours.

Does this work?

Can there be a green/red indicator and/or an expected burn-down and actual burn-down graph?

Thank you!

 

[6StringJazzer]

Here's an example that shows the green/red but I do not know what you envision your graph looking like.

 

[JHaugland]

https://drive.google.com/file/d/1N56Wm2lt5IE6ZWC9clI8bFiF5RgZDfwv/view?usp=sharing

I think we are close.

How about something like this?

Green shows where we are at. Red shows where we should be. If we are really close, they partially overlap to represent. If we are ahead of schedule, we fully overlap and the chart is all green?

 

[Mahesh5277]

Hi StringJazzer,

I was referring to your example which you have provided on the link above. How is the billed hours formula derived as =ROUND(H2*(0.8+0.4*RAND()),0)

Can you help me understand please?

Thanks,
Mahesh

Here's an example that shows the green/red but I do not know what you envision your graph looking l