Actual vs Target data

[Lacer75]

Hi, I'll trying to find a solution for producing a graph or chart to show performance against actual date or project commencing vs target date but for multiple project types and completed mixed start and agreed dates. I'm only an average excel user and understand if the data was consistent I guess it would be easier. But any help would be appreciated to guide me in the right way. Thank you in advance.

[6StringJazzer]

The chart part shouldn't be hard, but it's darn near impossible without knowing what your data looks like. Can you provide a sample of the data you want to chart?

[Lacer75]

Thanks for your reply and reaching out to try and help. I understand it must be difficult to try and help without the data. The data involved is sesnsitive work stuff that I wouldn't be allowed to share but I can try and better describe. Essentially column A would be "Project name", column B would be "Target start date" & column C would be "Actual start date" but all projects are different and each would have a different target and actual date (although some coincidently could be the same), hope this makes a little more sense and you would be able to help, if not I could maybe draft a false spreadsheet from my laptop but unfortunately not home at the moment. Thanks in advance.

[6StringJazzer]

That's pretty clear. What ultimately are you trying to show? Does the chart need to actually show the dates, or just show how close it came to meeting its planned vs. actual?

Here is a quick and dirty example to see if this helps. If I were a manager, I would want to see variance, not dates.

[Lacer75]

Thanks for your help and support. This is indeed along the lines that I'm looking for. I think the trick is that I didn't have before is to have a variance column so that the graph works, as the data i'm working from doesn't have that. Would that be accurate?

Then I suppose once you have that then you can generate a graph/table fairly simple through the excel tools?

Thanks again.

[6StringJazzer]

Yes, that's the idea. Graphing challenges are almost always resolved by setting up the data the way you need it first. Let me know if you run into issues implementing it in your own file.

[Lacer75]

Yes, that's the idea. Graphing challenges are almost always resolved by setting up the data the way you need it first. Let me know if you run into issues implementing it in your own file.

yeah I seemed to have got it working the graph is tiny as i believe I'm expecting it to include much data so going to have to narrow it down, e.g. rather than reviewing whole year, maybe review whole month. Would work fine with less projects I guess. I'll let you know how I get on. Thabks again.

[Lacer75]

yeah I seemed to have got it working the graph is tiny as i believe I'm expecting it to include much data so going to have to narrow it down, e.g. rather than reviewing whole year, maybe review whole month. Would work fine with less projects I guess. I'll let you know how I get on. Thabks again.

I have applied your advice to my data and can get it working fine but I have been asked to not neccessarily include the project names but show the variance over a period of time. If I'm honest not sure how best to approach as i know you'd need 2 data points to create a table unless you could advice another way? Thanks so much in advance

[6StringJazzer]

The variance over a period of time? Like variance by end date? Not sure what you are envisioning but look at this, which uses the Actual as the x axis rather than the project names.

Another way to get really fancy with this is to treat it as statistical process control, where you plot the variances over time to determine whether your schedule management process is under control. If you are not familiar with SPC then this may not be the time to start.

[Lacer75]

Apologies for the late reply, didn't get a notification of reply. Definitely along the lines of what I was thinking and need as from that I can generate averages over a select period of time I guess? I think the challenge will be how to automate it and just pull data from our existing spreadsheets. I'll be in touch if I require further assistance but honestly can't thank you enough for taking the time out to help, it really goes a long way.