Demand Profile graph for Monthly efforts on a specific activity

[MaheshK5277]

Dear Experts,

I need help to create a Demand profile graph for a data (which you can see in the attached sheet).

So basically, the scenario goes like this:
We have a 1 person working for our client on a Project with 5 days per week & 9 hours/day of efforts. The Project comprises of various sub-projects and at any given point of time you may see the person is working on >5 sub projects.

Out of this sub-projects, one area is the requests handling for a specific activity done on a software tool.
But, the no. of requests are varying in numbers and also, they can get carried forward to the next month (if not completed within the same month).
So, effectively, the total no. of requests which the person works is a sum of (new requests for that month + pending requests carried forward from previous month).

Further, the data in sheet shows, how much % of the time out of the Total efforts spent in a month were spent on the requests. There is no linear co-orelation between how much time is required to execute 1 request. For easy calculation, I have kept the Total Monthly efforts available to spend in a month as 190 (i.e. avg. 21 days/month).

Our client wants to see the Demand profile of the requests only (month over month) & observe the trend. Hence this request.

Any help or guidance on how to plot a graph representing the data in table, will be much appreciated.

Thanks,
Mahesh

[MaheshK5277]

Dears,

Anyone can hep please?

[MrShorty]

What kind of help do you need? Are you mostly asking about the mechanics of how to create a chart in Excel, or is your question something more generic -- like what chart type to choose? We are usually pretty good at helping with the Excel mechanics kinds of questions, but we aren't always able to help with questions that are not specific to Excel.

If your question is about the mechanics of chart creation, I would expect to:

1) Select the desired range. In your spreadsheet, it looks like A3:Q9 would be the range. For whatever reason, Excel wants the upper left cell of the chart range to be blank to help it parse the data series definitions, so I might clear A3 before inserting the chart (you can add the text in A3 back in after the chart is created and finalized).
2) Look at the default data series definitions and see if Excel correctly interpreted what you wanted. You may need to execute a "switch row/columns" command if the data series are mostly right just going the wrong direction. Or you may need to go into the "select data" dialog and remove all the data series and build the data series manually (when Excel does a particularly horrible job of guessing the data series).
3) Add/remove and format chart elements until you are satisfied with your chart.

That should allow you to create a chart, how can we help you further?

[MaheshK5277]

Thanks MrShorty,

Your explanation was quite helpful. Actually, I am not very familiar with creating graphs which include more than 2 variables and i was precisely looking for help on the same. Anyways, i could make one with some self-help and i did refer your guidance as well.

I was looking to establish a relation between 'No. of requests' and the 'Effort hours per request'; which i knew was not linear and hence wanted to plot this on a graph showing the variability(or complexity) of the requests. As you can see in the attached file, i used custom combination for this graph. I am happy that i could come reasonably close to where i wanted to be.


Thanks,
Mahesh