• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Productivity and Charting

S

saumyamukherjeee

New Member
I need some help in creating a data from below raw data, scenario is like this.


ACCOUNT SERVICES

Date 01-Oct-12 02-Oct-12 03-Oct-12 04-Oct-12 05-Oct-12

Collections Schedule Updation 1243 956 789 1967 2100

Collection Updation - Manual Cases 23 43 25 29 32

Branch PDD 765 265 386 950 1250

Account Closure 50 47 85 43 93

NOC Blocking / Deblocking 32 54 45 56 43

Storage 50 65 40 55 37

Retrival 10 56 32 5 23

Refilling 45 32 12 12 34


BBO Activities

Date 01-Oct-12 02-Oct-12 03-Oct-12 04-Oct-12 05-Oct-12

BBO Reschedulement 265 386 25 29 32

BBO Files Processed 47 85 386 950 1250

BBO NOC Blocked 54 45 85 43 93

BBO NOC DEBLOCKED 65 40 45 56 43


now for each activities I've to publish MIS of daily productivity compared to last day and in weekends where the data shows which day was most productive.

I've tried applying indexed charts but it doesn't worked well for me.


Please help in creating this data for me with good charts to show off the data


Thanks...Saumya
 
Saumyamukherjeee


Firstly, Welcome to the Chandoo.org forums.


I'm unclear what you are wanting to chart

Can you possibly draw a sketch of what your after ?

You post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Dear Hui,


You may refer this excel file i've attached with google drive/docs


https://docs.google.com/open?id=0BwzKuUvuZlkcYUJ3SkVzVl9TdVU


There are various activities like ACCOUNT SERVICES & BBO ACTIVITIES. Now in account services sub activities are there with date wise count of work done. Also count of pendency for that particular activity has been mentioned in pending column.Also I'm facing one issue is live some activities doesn't happen everyday so mandatorily I'm putting "0" in that particular cell and this is returning me #DIV/0! error for calculation of productivity and pendency %age ( Divide by zero error)..


Now all i want is a good way to expose the pending and productivity data, be it either by chart, pivot or any other tools. NO MACRO'S AND VBA'S PLEASE.


Please guide and help me


Thanks..Saumya
 
Saumya


You've got me stumped


Apart from the few formulas you've got I still don't understand what you require as I don't see any relationships between the tables either numerically or via headings :(


Can you try and be more explicit about exactly what you require, assume the r4eader knows nothing about the business your in.


You can put the formulas which result in error messages in something like:

=Iferror(Existing Formula, 0)
 
Hui


Let me explain this time..


Suppose we're running a finance company back-end operations, where lots of activities are happening department wise. Now departments may be categorized as ACCOUNT SERVICES / BBO ACTIVITIES and so on. Since the list of department is large so in the example file I've used only two departments.Under each department there are various activities happening and that has been categorized as sub departments as collections updation, branch pdd, account closure. No numbers are the count of files processed under that particular activity and has been segregated date wise which i call daily productivity.


Now suppose a manager wants to see productivity of each activity, assuming only count of files processed on that day is not being possible.


my job starts from here, i want to show management about our productivity on each activities basis these figures and also I've to publish MIS of daily productivity compared to last day and in weekends where the data shows which day was most productive.


Please let me know if any more data/query/info's you want, to make this excel beautiful.


Thanks.. Saumya
 
Hui


I've added concept of Full Time Equivalent ( FTE ) and got something like this


https://docs.google.com/open?id=0BwzKuUvuZlkcYUJ3SkVzVl9TdVU


But still don't you think something lacks, please review.
 
Hi Saumya,


Can you clarify on Pending column is that pending for whole week.


If it is whole week better to put individual day then we can calculate average prod for the team.


if you provide the 2 weeks data then we can show a graph with expected (High /Low)volumes in coming weeks on a specific day.


I hope this will work if you need in a excel format i can send you that.


Regards,

Satish.
 
Hi Satish,


Thanks a ton for revert, actually pending columns are the count of work left pending as on last day or may be better explained "pending as on date"


Would be great if you could share the excel format ( since i'm still stuck with this :)


Thanks...Saumya
 
You must log in or register to reply here.
Back
Top