Performance measurement spreadsheet help needed please

[Joy29]

I am an excel newbie and have been asked to create a spreadsheet which measures if we are meeting our targets of responding to enquiries within an alloted time, something along the following:

Date enquiry received
Date response given to enquiry also number of days taken - colour fomat if within time limit - show percentage of on time or late.
Number of enquiries which were won - job secured - against total number of enquiries

Will need a monthly total as well as a running total and if a pivot chart could be created that would be superb

Can anyone give me any assistance / guidelines as to how I might be able to create something like this?

Many thanks
Joy

 

[Faseeh]

Hi Joy,

Do you have any specific layout for your sheet?? Here is a link to one of call center dashboard i found

http://exceldashboardschool.com/excel-kpi-dashboard/customer-kpi-dashboard-excel/

 

[Joy29]

Hi Joy,

Do you have any specific layout for your sheet?? Here is a link to one of call center dashboard i found

http://exceldashboardschool.com/excel-kpi-dashboard/customer-kpi-dashboard-excel/

Many thanks for replying so quickly.

I would need to list date a sales lead was received, person's name, date of first contact and probably colour green if within 48 hrs, red if over that time, date quoted, again green if within x amount of days (prob 21) and red if over - then finally if sales lead was converted into a job - green if yes, red if no. As well as this I would like to create some sort of dashboard which shows monthly / ongoing analysis of if the above targets where met.

I have looked at the kpi dashboard but to be honest haven't got a clue where to start.

Thanks again
Joy

 

[eibi]

Joy,

Welcome to the forums! You've come to the right place. Chandoo is the first place I go when I have an excel question.

As I see it, you've asked for two items: (1) a data table with conditional formatting and (2) a dashboard to summarize that data.

I've put together a sample for the Item 1. We need to get it set up right before proceeding on to Item 2.

But there is an ambiguity in your request:

- Is the 21 days to quote based on the original inquiry date or the initial response date? (I assumed that it is based on the original inquiry date...)

Please take a look at attached.

I've put in "dummy" data, based on my own imagination...But if this is the direction you want to go, we can dress it up just the way you want.

Before I can be any more help, please populate a similar table with the samples of the data you actually want to use.

All best.

 

[Joy29]

Joy,

Welcome to the forums! You've come to the right place. Chandoo is the first place I go when I have an excel question.

As I see it, you've asked for two items: (1) a data table with conditional formatting and (2) a dashboard to summarize that data.

I've put together a sample for the Item 1. We need to get it set up right before proceeding on to Item 2.

But there is an ambiguity in your request:

- Is the 21 days to quote based on the original inquiry date or the initial response date? (I assumed that it is based on the original inquiry date...)

Please take a look at attached.

I've put in "dummy" data, but this is based on my own imagination...But if this is the direction you want to go, we can dress it up just the way you want.

Before I can be any more help, please populate a similar table with the samples of the data you actually want to use.

All best.

Many thanks for the sample data, this looks very similar to what i had in mind, I have added some details and a few extra columns -these will probably need to be put in a more logical order. You are correct that the 21 days to quote is based on the original enquiry date - ideally our targets are contact made within 2 days of enquiry and quote sent within 21 days from date of enquiry.

I have attached the file and really appreciate your help.

Thanks
Joy

https://www.dropbox.com/s/mdsmauj1f3k3uw2/joy1 copy 2.xlsx?dl=0

 

[eibi]

Joy,

I've put together a rough dashboard...see attached. You can pick any two dates and the dashboard will compile the information you have requested for the inquiries you received during those dates. Maybe this will be a helpful starting point?

Spend a few weeks or months collecting the data and then come back with a real-world data set and we can figure out what works and what doesn't...what errors to work out...what questions your boss is asking that aren't answered here...

The goal is to get the right framework built before you spend time decorating it.

Now the ball is in your court. Make changes, make mistakes, ask questions. This forum isn't the place where other people can do your work, we can only help each other as we each run into issues that need answers.

All best...