Variable pricing based on volumes

Hi, I wonder if someone might help me?

I am creating a spreadsheet that allows me to offer a potential customer some ideas on future pricing.

The scenario is that, if a customer processes a number of transactions per month, then the price "per transaction" will vary within pre-defined "bands" once certain criteria have been met.

So, if a customer processes up to 500 transactions per month, the cost per transaction will be - let's say - 80p. If they process between 501 and 2,000 transactions, then the transaction costs for that particular tranche will be lower at, say, 70p. So, in a scenario that a client processes - say - 1,500 - total costs will be 500 x 80p PLUS 1,000 x 70p.

What I'd like to do is to insert in one cell the total number of transactions in any particular month, and then have another cell that works out the total cost, based on the critieria that I set for transaction costs in each of the - probably - six "bands" that will offer reducing prices.

I'm sure this can be used using IF and THEN formulae and, for most of you, it will be an easy answer! But, I'm being very slow here and just cannot figure out the correct way of laying out the formula!

I hope someone can help, and look forward to a reply!

Thank you

Mike

Please see if attached file helps.

Thanks, I appreciate your help. But, I'm not sure it is what I would ideally like...

The ideal is for data to be entered into a single cell (say A3) that represents the total volume. In a seperate cell (say B3), the
return would show the total cost for what is entered into A3 (lets say 2,500 for example) based on a cost "per banding" (possible up to 8 bands).

So, for example, we put 2,500 into A3. The return in B3 should be £1,700, based on banding of 500x80p+1000x70p+1000+60p.

Ideally, all the spreadsheet would show (other than additional information which is not relevant to this calculation) are cells A3 (for manual data entry of volumes) and B3 (for calculated return of cost based on data in A3 and desribed as above.)

Does that help?

Thats perfect!

Many thanks to you both!!

Mike

Hi - I thought we'd solved this, but perhaps I can bother you again....

Just to expand on the original, I am now tryin to give a "month by month" cost projection based on 12 months and using the fab formuale you;ve kindly provided. It might help if you were able to spare some time to look at the attached - I've taken your formulas and made some modest changes so that, in the workbook attached, the sheet "Calc" essentially repeats things 12 times and the sheet "Display" generates results based on the Calc sheet.

So, if you now enter data into the "Volumes" column, it returns a cost based on the variables in "Calc".

As you'll see, I've changed the original variable costs to new figures and extended the range, so the costs now alter in bands of First 500 / 40p, 501-2500 / 30p, 2501 - 5,000 / 20p, 5001 - 10000 / 15p, 1o,000+ / 10p.

The problem is that the calculations are not coming through accurately. For example, if I manuall calculate the cost of 10,001 transactions, the answer should be £2049.45 (based on 500x40p + 1999x30p + 2499x20p + 4999x10p + 1x10p) but the "Display" sheet brings the total across at £2,300.

I'm sure the answer is very simple but I'm blowed if I can find it!

Do you think you can help again?

Thank you.

Variable cost v3.xls

Thanks guys, so much!

JetE - your solution is great thanks. I've replicated what you've done in month 1 for each month, and checked some random calculations manually and it all now looks like it is working. I appreciate the gentle heads-up as well....

I'll mark as "solved" now.....thanks everyone who's taken the time and trouble to reply and offer solutions!