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

#### Metro0120

##### New Member
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
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.

Michael

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### oldbrewer

##### Well-known Member
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

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

<TBODY>
</TBODY>

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

<TBODY>
</TBODY>

Replies
4
Views
332
Replies
0
Views
262
Replies
0
Views
582
Replies
3
Views
1K
Replies
2
Views
456

1,195,657
Messages
6,010,951
Members
441,577
Latest member
Alonshow

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back