I'm making this harder than it needs to be....

[Metro0120]

I am an experienced Excel user but my brain is not working this week and I am turning this spreadsheet into a major production and have lost my objectivity with it.

I am trying to accomplish the following:


Based on my personal averages in the past year for the following categories, I am attempting to determine how many from each category I need to hit my sales objective of 300K.


Dials
Contacts
New Appointments
Total Appointments
Cases (closed business)
GDC (commisionable Gross)

Example: My Dial to Contact ratio is 19% so I know if I call 100 people, I will get 19 contacts. But how many contacts do I need to get based on my ratio that turn into appointments and so on down the line?

I also have a couple of rows where I track my real time percentages and I would like the above categories to move appropriately as my averages move higher or lower (i.e. if my closing ratio goes up the number of appointments I need to hit my goal adjust downwards accordingly.

I would truly appreciate any help as my patience is burning and I am spending valuable time attempting to correct this so I may ensure my best sales year to date.

Thank you for your consideration and any help you may offer.

Michael

 

[oldbrewer]

we need your other ratios and to know what you consider a significant shift in performance - by this I mean if you are averaging 10 per day, a score of 8 MAY NOT be significantly different from 10

 

[Metro0120]

My Current ratios as of today (though they adjust daily are as follows:

Contacts to Dials = 19%
Contacts to New appointments = 82%
Total Appointments (This is one I'm having trouble figuring out how to calculate as new appointments turn into old/repeat appointments)
Appointments to Closing ratio (Cases) = 54%

These are last years over all ratios.

You will see below the sheet itself (I dont know if i can attach the actual file or how to in this forum) you will see year to date that the averages I have are off due to a low data sample thus far.

As you can see it is nt calculating as desired.

Goals</SPAN>	Per Year</SPAN>	Per Month</SPAN>	Per Week</SPAN>	Per Day</SPAN>
Dials</SPAN>	5,425.0</SPAN>	452.1</SPAN>	108.5</SPAN>	21.7</SPAN>
Contacts</SPAN>	620.0</SPAN>	51.7</SPAN>	12.4</SPAN>	2.5</SPAN>
New Appointments</SPAN>	56.4</SPAN>	4.7</SPAN>	1.1</SPAN>	0.2</SPAN>
Total Appoinments</SPAN>	348.7</SPAN>	29.1</SPAN>	7.0</SPAN>	1.4</SPAN>
Cases</SPAN>	5</SPAN>	0.4</SPAN>	0.1</SPAN>	0.0</SPAN>
GDC @ $1,800.00 per</SPAN>	9,223.06</SPAN>	768.59</SPAN>	184.46</SPAN>	26.35</SPAN>
Goal Trends YTD</SPAN>	Per Year</SPAN>	Per Month</SPAN>	Per Week</SPAN>	Per Day</SPAN>
Dials</SPAN>	2.58%</SPAN>	30.97%</SPAN>	129.03%</SPAN>	645.17%</SPAN>
Contacts</SPAN>	2.58%</SPAN>	30.97%</SPAN>	129.03%</SPAN>	645.17%</SPAN>
New Appointments</SPAN>	15.97%</SPAN>	191.61%</SPAN>	798.39%</SPAN>	3991.97%</SPAN>
Total Appointments</SPAN>	3.15%</SPAN>	37.85%</SPAN>	157.71%</SPAN>	788.54 </SPAN>
Cases</SPAN>	19.52%</SPAN>	421551.90%</SPAN>	0.00%</SPAN>	0.00%</SPAN>
GDC</SPAN>	0.01%</SPAN>	0.00%</SPAN>	0.00%</SPAN>	0.00%</SPAN>

<TBODY>
</TBODY>

Totals YTD</SPAN>	140</SPAN>	16</SPAN>	9</SPAN>	11</SPAN>	1</SPAN>	$1,800.00</SPAN>
% YTD</SPAN>		11.43%</SPAN>	56.25%</SPAN>	122.22%</SPAN>	9.09%</SPAN>	0</SPAN>
	Dials </SPAN>	Contacts</SPAN>	New Appts.</SPAN>	Total Appts.</SPAN>	Cases</SPAN>	Average Case</SPAN>
Totals MTD</SPAN>	140</SPAN>	16</SPAN>	9</SPAN>	11</SPAN>	0</SPAN>	0</SPAN>
	Dials </SPAN>	Contacts</SPAN>	New Appts.</SPAN>	Total Appts.</SPAN>	Cases</SPAN>	Average Case</SPAN>

<TBODY>
</TBODY>