Dynamic Revenue Per Hour Tool

[dwcjmilo]

Hey Everyone,

I work for a call center of Inbound Sales agents and I'm looking to set up a tool that will allow our leadership team to project what the Sales agent's Month-to-Date Revenue will be based on their current performance during that month so far. This will assist them in their one-on-ones with their employees to convey what they need to do to get back on track if they are falling behind. However it takes a couple of reporting programs to pull the data I need and manual calculation to get the end numbers. One of the reporting programs can give me the KPI's of Handle Time and Month-to-Date Revenue.

Based on those two KPI's, I can theoretically calculate all of the following bullet points, but I'm struggling with the excel formula on a couple of them.

- Revenue Per Hour: = SUM(Revenue/Hours Worked)
- Hours Worked: = SUM((Handle Time/60)/60)
- Shrinkage Value: = (How much time the employee's Shrinkage is valued at based on their Revenue Per Hour)
- Projected MTD Revenue: = (What we are projecting the employee to finish out the Month with in their Revenue based on Revenue Per Hour)
- Hours Needed: = How many hours of Overtime the employee would need to sign up for beyond their scheduled hours to meet their MTD Revenue goal based on their Revenue per Hour performance

It's the Shrinkage Value formula I am getting hung up on the most. I'm trying to calculate that with just the Handle Time and MTD Revenue. Since we do our reporting on a MTD basis, the number of days each employee has to meet their MTD Revenue goal may be different based on their scheduled days off. To further complicate this, we have variable schedules that can literally have any combination of days off and scheduled hours as long as they are 40 hours per week.

Here is a basic table of the above and I'd love to know if I am just over-thinking this or if there is a resource like this in excel that I just haven't discovered yet. Whether it's Excel Formulas or VBA doesn't matter to me. Any assistance would be greatly appreciated!

Agent	Rev Per Hour	Hours Worked	Shrinkage Value	MTD Revenue	Projected MTD Rev	Hours Needed
Agent 1				$ 211,080.17
Agent 2				$ 71,210.58
Agent 3				$ 14,745.69
Agent 4				$ 137,729.85
Agent 5				$ 164,260.40

<tbody>
</tbody>

Thank you!

 

[James006]

Hello,

Would be great if you could expand on the actual definition of the "Shrinkage Value" ... :wink:

may be with an example of the final value ...along with the process which leads you to this value ...