Calculate supervisor to rep ratio

[bammons]

I'm in need of a repeatable excel calculation/formula to calculate supervisor to rep ratio based on supervisor time spent on coaching and administrative tasks. Completely stumped on how to create this. Can anyone help?

 

[Royzer]

Can you upload a workbook showing an example of the data and the result you're looking for?

 

[bammons]

Thx for taking a look Royzer! I'm unable to attach the workbook but heres the data (in time) I have to consider to arrive at a ratio. The time is monthly. I'm trying to create something (formula/calculation) I can plug in the different time variables and it calculates the ratio needed to complete all tasks during the month (160 hours) per supervisor.
[TABLE="width: 561"]
<TBODY>[TR]
[TD]Task/Action</SPAN>[/TD]
[TD="colspan: 2"]</SPAN>Supervisor
[/TD]
[/TR]
[TR]
[TD]Hours</SPAN>[/TD]
[TD]% of Time</SPAN>[/TD]
[/TR]
[TR]
[TD]Call Monitoring</SPAN>[/TD]
[TD]60</SPAN>[/TD]
[TD]15%</SPAN>[/TD]
[/TR]
[TR]
[TD]Customer Experience Calibrations</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]1%</SPAN>[/TD]
[/TR]
[TR]
[TD]Coaching Preparation (Performance/Data Review & Analysis)</SPAN>[/TD]
[TD]15</SPAN>[/TD]
[TD]5%</SPAN>[/TD]
[/TR]
[TR]
[TD]Performance Discussion & Coaching (MAP)</SPAN>[/TD]
[TD]22.5</SPAN>[/TD]
[TD]11%</SPAN>[/TD]
[/TR]
[TR]
[TD]Leadership Observations</SPAN>[/TD]
[TD]NA</SPAN>[/TD]
[TD]NA</SPAN>[/TD]
[/TR]
[TR]
[TD]MBWA</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD]4%</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]IM/Email/Calls</SPAN>[/TD]
[TD]48</SPAN>[/TD]
[TD]18%</SPAN>[/TD]
[/TR]
[TR]
[TD]T&L, FMLA, Attendance</SPAN>[/TD]
[TD]20</SPAN>[/TD]
[TD]7%</SPAN>[/TD]
[/TR]
[TR]
[TD]Exceptions Management</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD]4%</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Planning/Task Prioritization</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD]4%</SPAN>[/TD]
[/TR]
[TR]
[TD]Reactive Time</SPAN>[/TD]
[TD]20</SPAN>[/TD]
[TD]7%</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Management Meetings (team, market, all leadership)</SPAN>[/TD]
[TD]16</SPAN>[/TD]
[TD]6%</SPAN>[/TD]
[/TR]
[TR]
[TD]Service 2 Sales Huddles (20 min) plus preparation</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]1%</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Special Projects</SPAN>[/TD]
[TD]20</SPAN>[/TD]
[TD]7%</SPAN>[/TD]
[/TR]
[TR]
[TD]Back up duties/POC </SPAN>[/TD]
[TD]12</SPAN>[/TD]
[TD]4%</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]HR Issues</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD]4%</SPAN>[/TD]
[/TR]
[TR]
[TD]Interviewing</SPAN>[/TD]
[TD]TBD</SPAN>[/TD]
[TD]TBD</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Training Attendance</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD]2%</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[Royzer]

To make sure I understand: If you total the hours in the hours column, do you want to know what percent of the total time is taken by each task?

 

[bammons]

Yes. Thats correct. % of time taken by each task. Thats not as important though compared to getting the actual sup to rep ratio based on time spent on the tasks. Thx!

 

[Royzer]

Sorry, but I'm having trouble identifying which data is which, I guess. I need you to get me started. Using your example above, please tell me what would qualify as "sup" and what would qualify as "rep".

 

[bammons]

No prob. Hopefully this will help to add a little color... All of the data above is Supervisor (Sup) data. These are the daily tasks they perform broken down by 1) the average time (hours) it takes them to complete on a monthly basis, and 2) the % of time each task takes from thier time overall for the month. There is no data specific to the reps. I'm trying to build an exel model that I can input the time (hours) taken by the Sups on the tasks which will in turn output the best Supervisor to Rep ratio for team size to accomplish the tasks in 160 hour (1 month). The 160 hrs is the amount of time 1 Sups works in a month.

The following tasks from the data above are directly impacted by the Supervisors time spent with thier Reps. So the number of reps a Supervisor has on their team great impacts thier ability to complete all tasks. The tasks below have the highest priority for the Supervisor. All other tasks from the data above are pretty much fixed times but still must be completed by the Supervisor regardless of team size.

Call Monitoring
Coaching Preparation
Coaching Discussion
Exceptions Management
HR Issues

 

[Royzer]

I'm sorry, but I just can't envision how this should look without an example of how it would look, Sorry I couldn't help.

 

[bammons]

No problem Royzer. Thanks for giving it a look though!