Forecast By Sales Rep

[Ang Ang]

Hi Excel Expert,

I was wondering if there is a easy way to lookup up by sales rep & tier table and figure out the forecast %.

For example: Bob has a sales attainment of 73.16% (Sales/Quota)= .25%(Forecast Rate Tier). Need to find out if there is a way to look up by attainment % and by Sales rep.

Attached is a copy of my excel file.

Sales Rep Total Sales Quota Attainment Forecast Rate
Bob $ 643,772.99 $ 880,000.00 73.16% 0.25%


Forecast Tier (Min) Forecast Tier (Max) Bob Rate Tier
0% 50% 0.00% 0.00%
50% 80% 0.21% 0.26%
80% 99% 0.33% 0.53%
100% 100% 1.05% 1.05%
115% 115% 1.14% 1.14%
116% 116% 1.35% 1.35%
129% 129% 1.42% 1.42%
130% 130% 1.61% 1.61%
150% 150% 1.74% 1.74%
200% 200% 1.74% 1.74%


Thanks,

 

[NARAYANK991]

Hi ,

See if this works :

=FORECAST(D3,OFFSET($C$14:$L$23, MATCH(D3, $A$14:$A$23, 1)-1, MATCH(A3, $C$13:$L$13, 0)-1, 1,2), INDEX($A$14:$B$23, MATCH(D3, $A$14:$A$23, 1), ))

Enter this in E3 and copy down.

Narayan

 

[Ang Ang]

Hi,

When I change the attainment to 113% the formula is not working. Help!

Thanks,

 

[Ang Ang]

Hi, Can someone help me look at why the formula does not work for Mike when I changed his attainment to 130%.

 

[p45cal]

The values in column A and B are the same, especially in the case (red border) for 130%, but for any of the light blue highlighted values:

You might also want to put different values in the pairs of columnsto theright too.

The reason E7 isn't showing an error is because the formula uses the wrong row for 130%; row 15 instead of row 21.

 

[Ang Ang]

Thank you!