Excel and variable pricing based on volume

Question

Excel and variable pricing based on volume

Anonymous

Good morning,

I have a table of costs and a variable volume of thru-put to produce the monthly billing amount.

The table of costs is, for example:

1 - 10 @ $1

11 - 25 @ $1.5

26 - 50 @ $1.75

51+ @ $1.80

the volume varies each month and the pricing is stepped + cumulative, for example 27 items are billed as:

(10 x $1) + (15 @ $1.5) + (2 @ $1.75)

I have tried various IFS strings, and some of the time the answer calculated is correct, but often not. I think the reason it fails is 'cause the integer is applying to the (using above example) highest # of '51+', but my programing for the lower tiers is incorrect on the if <..., ...., >... (sorta).

I have played with ifs( ... < , ...> et cetera, however, I am thinking this is not a case for IFS, but I don't know what the function should be....

Hopefully someone out there understands my ramblings.

Any ideas people?

Simon

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Answer 1

This is a common problem, also seen in Tax Tables, and it is easily solved and maintained with a lookup table.

Create a table with three columns: Volume, BaseCost, and Cost per Unit

Volume	Base	Unit
0	$0.00	$1.00
10	$10.00	$1.50
25	$32.50	$1.75
50	$76.25	$1.80

The logic for the Base cost is that it represents the cost for items up to and including the Volume on that line. So with the CostTable in D1:F5, you can use this formula to fill out the Base Column in the table:

E2: $0.00

E3: =E2+(D3-D2)*F2

And select E3 and fill down as needed

Then, with your Volume in A1, the following formula will return the costs:

=VLOOKUP(A2,CostTable,2)+(A2-VLOOKUP(A2,CostTable,1))*VLOOKUP(A2,CostTable,3)

With the table, you can easily change the unit costs, add or subtract tiers, etc.

Answer 2

Ashish Mathur 101.8K Volunteer Moderator

Hi,

Try this

=SUMPRODUCT(--(A8>{0;10;25;50}), (A8-{0;10;25;50}), {1;0.5;0.25;0.05})

A8 has 27

You may refer to my solution to a similar problem at this link as well.

Hope this helps.

0 comments

Answer 3

Hi Simon,

Because you have said that the number will always exceed the 50 then I believe that it can be kept relatively simple.

Refer to the following screen capture and I will try to explain.

The 120 in red represents the total number for the calculation. The formula can be anywhere on the worksheet because I have used absolute referencing and the total number can be anywhere on the worksheet. The total number only appears in one place in the formula as $B$6 so only need to change that in the formula.

However, you will need to set up your table as I have done with the From and To columns (although the From column is not used in the formula) and replace all of the cell references throughout the formula to match the cell references of where you position your table on your worksheet.

The formula simply calculates the differences between the rows for the To column and multiplies the difference by the price for the row and then sums all of the results.

Formula below because you cannot copy it from the screen capture picture.

=SUM(($B$3-$B$2)*$C$3,($B$4-$B$3)*$C$4,($B$5-$B$4)*$C$5,($B$6-$B$5)*$C$6)

Hope it helps.

Answer 4

OssieMac,

there is only one item - it is an 'event' + always the same event.

the events are totaled at the end of the calendar month, the volume of the events looks at the table and based on the events, calculates a price for the total of the events for the period.

I am struggling with the calc for the steps in the pricing model.

the steps (between one number and another number) do not vary in the period either, so it's reasonably static. The variable is the number of events in the month, and over time the total of the events will always be greater than the maximum, so all months will have pricing for all of the four tiers.

the monthly expected volume is on the same tab as the pricing table.

I don't think it is too complex, it is more a case of the fact that my programing is poor due to the four steps of pricing.

does this clarify somewhat?

Simon

Answer 5

Hello Simon,

I believe that it can probably be achieved with a UDF (User Defined Function in VBA code). However, it will require a real sample of the table and it might even require a rehash of the table. eg 1 - 10, 11 - 25 etc is not as easy to work with as say 10, 25, 50 etc and individual numbers still provide enough information for the purpose.

How is the price tied to the item being looked up? I am assuming that it is in the same table because the UDF code needs to look up the item, get the prices and calculate total price.

Are all items priced in the same steps of 10, 25, 50 etc or do the steps vary depending on the item?

Therefore to answer these questions, can you upload an example workbook contain the table. If the items and prices are sensitive information then in the example replace the item names with dummy names like A, B, C etc.

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
Go to this link. https://onedrive.live.com
Use the same login Id and Password that you use for this forum.
Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
Right click the file on OneDrive and select Share.
Select "Get a Link" from the popup menu.
Click Copy the link and Paste into your reply on this forum.

Share via

Excel and variable pricing based on volume

9 answers