Schedule Variance Formula

JHaugland

New Member
Joined
Mar 9, 2016
Messages
15
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?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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!
 
Last edited:
Upvote 0
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)?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top