New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Tiered Pricing Formula using SUMPRODUCT

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Tiered Pricing Formula using SUMPRODUCT

Postby kgoalie » Fri Jan 13, 2012 9:30 pm

I'm having trouble matching the answer to my formula to the correct total.

If a customer buys, a large quantity of product, I want to offer them tiered pricing instead of average pricing. The problem with selling them ample units using the average pricing model is that the price per unit goes down so quickly that eventually it ends up costing the customer less in total to buy more product. Eventually my discounted rate goes down so low, I'm giving it away!

Instead, the customer should pay $5 for 100 units, plus $4.90 for the next hundred units, plus $4.80 for the next and so on.


0-100 units =$5.00
101-200 units =$4.90
201-300 units = $4.80
301-400 units = $4.70
401-500 unites = $4.60
501 -600 units = $4.50

My formula is as follows, but I am not getting the correct total. Its close but needs to be exact. (It seems to be a rounding error somewhere?)

SUMPRODUCT(--(B3>={0,100,200,300,400,500,600}),B3-{0,100,200,300,400,500,600},{5.00,-0.10,-0.20,-0.30,-0.40,-0.50,-0.60})

B3 = total number of units sold

For the example: 367 units sold

Pricing would be:
100@ $5.00
100@ $4.90
100@ $4.80
67@ $4.70
Total: $1,784.90


When I apply the formula: SUMPRODUCT(--(B3>={0,100,200,300,400,500,600}),B3-{0,100,200,300,400,500,600},{5.00,-0.10,-0.20,-0.30,-0.40,-0.50,-0.60}) Total is : $1,754.80

What am I doing wrong?! Why is there a difference?

As I increase the units, the difference becomes larger, ie. 588 units sold, the error becomes larger. $2,796.00 versus formula answer of $2,608.00. When I attempt 202 units, where the answer is an obvious $509.80, the formula shows $999.40.
  • 0

kgoalie
Rookie
 
Posts: 3
Joined: Jan 13, 2012
Reputation: 0

Re: Tiered Pricing Formula using SUMPRODUCT

Postby Sisyphus » Sat Jan 14, 2012 1:02 am

Hi,
I teach my sons that "almost hit" equals "missed", and that sums up your formula. I have worked out where it goes wrong. Please refer to the attached worksheet for detail. The following formula lacks the elegance of your idea but it does have the advantage of producing the desired result accurately:
Code: Select all
=($B$3 * 5)+(SUMPRODUCT(--($B$3>={0,100,200,300,400,500,600}),--($B$3-(100*{1,2,3,4,5,6,7})>=0),{0,1,1,1,1,1,1},{0,0.1,0.2,0.3,0.4,0.5,0.6})*-100)+(MOD($B$3,100)*IF(INT($B$3/100)>5,5,INT($B$3/100))/-10)

Basically, this formula takes the total quantity at the standard price and deducts the discount. The discount is worked out in two steps, first for the full hundreds, then for the modulus. In this example the discount is a round fraction of the quantity increment that triggers the next discount level. I have refrained from simplifying the formula to take more advantage of this. For example, a major simplication could be possible if you would simply exclude quantities below 100 from being calculated at all because the discount is always zero. So, if you have other relationships between discount amount and discount trigger that part of the formula will be easy to adapt. In calculating the discount on the modulus I was less generous, and that part will have to be re-done if the same relationship isn't a given.

I haven't invested more time in this because my feeling is that many of the parameters now in the formula will be in referenced cells when you build your application. Depending upon how the reference table is constructed the way of looking up cells in them will also differ and the way of handling the data along with that.
  • 0

You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Tiered Pricing Formula using SUMPRODUCT

Postby kgoalie » Tue Jan 17, 2012 8:56 pm

Thank you Sisyphus. This is very helpful in understanding how the formula works and where the errors are occurring.

After building out the spreadsheet, I can first figure out the pricing I want for each tier by a simple chart: A=$5.00 B=A*.9, C=B*.9 (to offer discount on each tier of 100 units).

Then;

Once I have the $ Amount for each set of 100 units, I can use the SUM PRODUCT function to multiply out the price times the units at that price "SUMPRODUCT (AMOUNT IN THE FIRST TIER (to) AMOUNT IN THE LAST TIER (times) #UNITS IN THE FIRST TIER (to the )#UNITS IN THE LAST TIER)
1 2 3 4
A Units 0-100,101-200,201-300, 301-400
B Range 99, 100, 100, 26
C Price $5.00, $4.90, $4.80, $4.70

325 UNITS IS =SUMPRODUCT(C1:C4,B1:B4)
  • 0

kgoalie
Rookie
 
Posts: 3
Joined: Jan 13, 2012
Reputation: 0

Re: Tiered Pricing Formula using SUMPRODUCT

Postby kgoalie » Tue Jan 17, 2012 8:58 pm

I will now test out your formula and see if I get the same answer, as my method requires a bit more action in updating the final #units in the last tier.

=($B$3 * 5)+(SUMPRODUCT(--($B$3>={0,100,200,300,400,500,600}),--($B$3-(100*{1,2,3,4,5,6,7})>=0),{0,1,1,1,1,1,1},{0,0.1,0.2,0.3,0.4,0.5,0.6})*-100)+(MOD($B$3,100)*IF(INT($B$3/100)>5,5,INT($B$3/100))/-10)
  • 0

kgoalie
Rookie
 
Posts: 3
Joined: Jan 13, 2012
Reputation: 0

Re: Tiered Pricing Formula using SUMPRODUCT

Postby Sisyphus » Tue Jan 17, 2012 10:54 pm

Hi,
Such complicated formulae scare me. :mrgreen:
They are hell to produce and when you finally got them working the next change rolls around. :twisted:
Try something simpler:
Now you plan on a list where A2 = 5.00, B2 = 4.90, C2 = 4.80
You plan on referring to this list with your formula. So far so good.
Now, the truth is that the average price of the first 200 pcs. would be (A2 + B2) / 2 and the average price for the first 300 pieces would be (A2 + B2 + C2) / 3 and so on.
If you were to calculate these average prices, say in row 3, and if you were to refer to row 3 with your formula the remaining task would be only to find the price for the odd 67 (modulus of 367) which you find in Row 2.
Now, if you were to write your quantities in Row 1, say A1 = 100, B1 = 200, C1 = 300, D1 = 400,
then your entire show is reduced to two HLOOKUPs with a simple multiplication:
Code: Select all
(INT(367/100) * 100 * HLOOKUP(367, 3)) + (MOD(367/100) * HLOOKUP((INT(367/100) + 1) * 100, 2)

Transposed to a long list of items you would need two, perhaps even three rows for each but updating prices could be done by your teenage son and you won't need the internet to update the formula.
How does that sound to you? :D
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Tiered Pricing Formula using SUMPRODUCT

Postby Sisyphus » Sat Jan 21, 2012 8:36 am

Hi,
In reply to another question I just worked out a formula along the lines I suggested to you above. You may like to have a look at the thread.
http://www.excelkey.com/forum/viewtopic.php?f=2&t=288&p=1090#p1090


Your solution might look almost exactly the same way.
You may have noticed that there is a logical error in my earlier suggestion in this thread which was discovered in the process of working out the actual solution in the other thread. :oops:
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 66 guests