Tiered prorata pricing

Hello All,


I am a noob in Excel and need help. I am a freelance trainer and need to send pricing based on number of participants. I need a calculator designed which will address the following.

Program A EQ
Cost per candidate 1000
Minimum Candidates 50
Expected Participants 51
Tiered Pricing
Service Tax 12.50%
Total Cost


Minimum Candidates 50: This is a non editable field

Tiered Pricing: This is a entry field which will be blank for the client to enter a number between 51-75. The pricing for the 75th participant will be 800. So a prorata from the 51st participant to the 75th participant should fall within the 200 bracket. So on and so forth.

The tiers are as follow.
1-50: 1000
51-75: 800 (Keeping in mind the distribution of 200 on the number of participants prorate basis)
76-100:600 (Keeping in mind the distribution of 200 on the number of participants prorate basis)
101-150: 400 same as above
151-200: 200

Expected Participants: This is a entry field which will be blank for the client to enter any number
Service Tax: This is fixed.
Total Cost: Sum of Service Tax and Tiered Pricing

Kindly help me address this issue. Will be highly obliged........ Thanks in advance

Thanks for your reply and query.

Here is how I envision the calculator. I intend to send this calculator attached to the business proposal so that the client get a sense of pay for what you use services. My clients requirement for training can range from 10-300+.

1) If there is a minimum of 50 candidates needed, why have a price per candidate at all? Why not just have a base fee of 50000, then every subsequent participant be applied to a tier? - I am fine with a base of 50000 for 50 participants. I thought if it could reflect on the calculator wherein even if the client enters a value below 50, the cost remains 50000 only.

2) Do the tiers take into account the minimum amount of candidates? That is if there are 80 participants, 50 of which fall into the minimum, does that mean there are 30 that fall into "tier 1" (1000), or does that fall into "tier 3" (600)? ---- No, the tiers do not take into account the minimum no of participants. However, for candidates within the bracket of 51 - 75, the prices will range between 200 on a prorata basis (1000 all the way down to 800 for the 75th candidate).

The tiers are as follows:
1-50: 1000
51-75: 800 (Cummulative or prorata basis to fall within 200 and not less than 800 for the 75th candidate.

So, for 75 participants, the fee will be 50000 (for the 1st 50) plus 2000 (800*25). The discounted amount of 5000. However, this value will change if it is for 74, 73,72, 71 etc....

Now from the 76th participant to the 100th participant range the bracket will be 600. So for the 100th participant, the pricing will be 50000 + 20000 + 15000 = 85000

So on and so forth. All the way for the ranges mentioned below
1-50: 1000
51-75: 800
76-100:600
101-150: 400
151-200: 200

After 200 participants, the fee will be the same. The trick here is the fee pricing for between the ranges like 51-74, 76-99, 101-124 etc....
Hope this clarifies..... I assume this should not be difficult for gurus in this forum... This is a great place.... Thanks again.

Shg,

I appreciate your initiative to help me with my query.

However, for candidates within the bracket of 51 - 75, the prices will range between 200 on a prorata basis (1000 all the way down to 800 for the 75th candidate).

The tiers are as follows:
1-50: 1000
51-75: 800 (Cummulative or prorata basis to fall within 200 and not less than 800 for the 75th candidate.

So, for 75 participants, the fee will be 50000 (for the 1st 50) plus 20000 (800*25). The discounted amount of 5000. However, this value will change if it is for 74, 73,72, 71 etc....

Now from the 76th participant to the 100th participant range the bracket will be 600. So for the 100th participant, the pricing will be 50000 + 20000 + 15000 (600*25)= 85000

So on and so forth. All the way for the ranges mentioned below
1-50: 1000
51-75: 800
76-100:600
101-150: 400
151-200: 200

After 200 participants, the fee will be the same. The trick here is the fee pricing for between the ranges like 51-74, 76-99, 101-124 etc....
Hope this clarifies..... I assume this should not be difficult for gurus in this forum... This is a great place.... Thanks again.

Thanks for your reply......

1-50 = 50000
for 75 candidates, the fee will be 50000+20000 ( this will change for the range between 71-74, the 75th candidate should be charged at 80000. In other words this will be a tier as well)
In your calculation, it shows 72400.

Going by my requirement, for 100 candidates, the pricing will be 95000 (50000+20000+15000). Hence 100 candidates will be costing 95000. The range between 76-99 will be the determining factor for this..

Hope this helps...

Sorry....
Going by my requirement, for 100 candidates, the pricing will be 95000 (50000+20000+15000). Hence 100 candidates will be costing 85000. The range between 76-99 will be the determining factor for this..

Sorry....
Going by my requirement, for 100 candidates, the pricing will be 95000 (50000+20000+15000). Hence 100 candidates will be costing 85000. The range between 76-99 will be the determining factor for this..

Shg,


Sorry, got the numbers all jumbled up.... Hope the table below summarizes.....


Range fixed In between
1 to 50 1000 1 to 50 fixed at 50000
51-75 800 51 - 74 for 75 participants, not to exceed 70000 ie; 50000+20000.
76-100 600 76-99 for 100 participants, not to exceed 85000 ie; 50000+20000+15000
101-150 400 101-149 for 150 participants, not to exceed 95000 ie; 50000+20000+15000+10000
151-200 200 151-199 for 200 participants, not to exceed 100000 ie; 50000+20000+15000+10000+5000

The prices should vary for the values in between the ranges.....

Thanks for your patience........

Shg,

Halleluia.... God be praised...... You nailed it.... All this while I was under the assumption that ExcelForum was sick of my queries after confusing them and wasting their time... Great to see your work... All credits to you....

Just slight modification, the ranges are in increments of 5, 10 etc.. in your work..... I have explained my desired result in the attached workbook.

Great work....

Shg.

While this is excellent and is on par with what I had on mind, I have a few questions...
a. Will I be able to change the values for the ranges?
b. Am I expected to drag and drop the formula or is it auto populated? Cos I don't understand the F column and its functions....

I am glad that you nailed my requirement....

Thanks again..

Ooopss... What a dumb person I am... I realized that I had to copy the formula from f column to the reference cells and VIOLA its working fine........


SHG.... You are my saviour..... You have saved my day.... from where I am now it is the early morning....... Good way to start the day....

SHG,,,


If at all it helps, I would like to give you more stars... Unfortunately, your system wants me to share with others....

Hi I need excel help in formula spreading sales which is full year number . over the period of jan-decc by month. also there is what if scenarios if function of spreading sales. can anyone help in formula.
Full year sales
Product A 100000
Product B 50000
ProductC 95000

hi shg,

I guess you will help me again with my earlier post which was already solved by you..

I tried copying the formula from the F column to the cell references, and they do not seem to work. Am I missing something here?

I have the need to use this formula for pricing after a long time.

Need your help again. Could you please put it in a excel sheet? I have been trying to copy the formula to no avail.

Thanks again.