Projected vs Actual

[sdexman]

Excel 2013
I have a sheet that has projected hours for my team. I want to be able to enter in the actual hours used at a later time and get a sense of how accurate are projections are vs actual hours used. I would like to have the comparisons on a different spreadsheet so as not to clutter our projected hours sheet. I'm not sure what the best way is to calculate this or how to make it look good. If anyone has experience with something similar I would be interested in how they do it.
So far the only thing I can think is to have a new sheet, put in projected and in the next column put in actual but I feel like then I have to add a 3rd column to show difference and it will just be difficult to read. Here's what I was kind of thinking, but not happy with it, the first chart is our actual projected hours the second would be a way to show projected vs actual. For formula's I assumed I would just pull the data from projected and enter in the data for the actual then create a 3rd column to calculate difference. Is there a better way that is easier to read? I'm open to a chart as well.
Thanks in advance!!

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

Have you ever looked at "Earned Value". That is a program management technique that will display how on track a project is. It is a step up from comparing plans to actuals, though.

The problem with just looking at planned costs versus actual costs is that you might be following the plan exactly but you might be doing no work! Earned Value makes you describe packets of work (possibly milestones) that can be recognised when done and flagged as complete. Then you check off the milestones as they occur. That way you know the plan, the actuals and what you have actually done. All metrics are reported in financial terms so all charts are plots of money against time.

All three lines will be superimposed if the project is perfect (some chance!) and any differences can be interpreted to determine the nature of the problem.

The method is well-known and you should be able to find lots of examples of the standard graphs it produces.

Starter link: Earned value management - Wikipedia, the free encyclopedia

 

[sdexman]

Thanks for the suggestions! The Earned Value looks great and is similar to what I'm looking for but I don't think it would work in this case. We aren't assigning any cost to the hours planned we actually use this more as a scheduling tool and we want to start seeing how accurate our projections are. What happens is that even though we have scheduled time for specific projects, we have other things that come in and we end up shifting time around and we're trying to see if there is a pattern to this or if we can improve upon our projections in general.

 

[RickXL]

Hi,

OK, you could still use similar graphs though.

Plot cumulative hours against time and plot both planned and actuals on the same graph. The space between the lines will be the difference.

The cumulative figures will give you a true impression of the total discrepancy at any point in time. Just plotting the hours without cumulating them can hide some problems.

(Also, if you ever need some to use earned value at some point, you will just need to multiply your times by an arbitrary cost rate and the charts will be in money - just waiting for the EV line to be added.)