Pricing - Sliding Scale Price Markup

[Ed]

In Excel, I am looking for a formula to apply a sliding scale price markup
using interpolation.

The size of the percentage markup would decrease as cost increases and would
increase as cost decreases.

I have 200 products and the cost ranges from $1.50 (approx. 60% markup) to
$35.00 (approx. 15% markup)

Avg. cost is $6.00 (50% markup)


Thank you.

Ed.

 

[Spiky]

In Excel, I am looking for a formula to apply a sliding scale price markup
using interpolation.

The size of the percentage markup would decrease as cost increases and would
increase as cost decreases.

I have 200 products and the cost ranges from $1.50 (approx. 60% markup) to
$35.00 (approx. 15% markup)

Avg. cost is $6.00 (50% markup)

Thank you.

Ed.

Guess it depends how you want to do it. You need to select an amount
where there is a change in the markup %. It may be every $1, or a true
sliding scale would use your smallest possible dollar increment on
your cost list. So maybe $.01 or $.001.

And, I'd probably find the best would be to make a simple table,
although you could cram all this into a formula. Personally, I'd want
the table to have for printing or visually checking a markup on
occasion, anyway.

List all your prices and the markup in 2 columns. Then use INDEX/MATCH
in your invoice worksheet to get the proper markup. If you use more of
a jump than your smallest increment, make sure to put $35 at the top
and $1.50 at the bottom to allow the formula to work best. By $.25
increments, and rounded for sanity:
...A...........B
35.00....15.00%
34.75....15.34%
34.50....15.67%
34.25....16.01%

Lookup function to pull the markup, assuming E2 is your price:
=INDEX($A$1:$B$135,MATCH(E2,$A$1:$A$135,-1),2)

And the formula to figure out the difference is just simple division.
Well, 2 of them. Using my $.25 increment:
(60%-15%)/((35-1.50)/.25)=.003358209

Add that to each level of markup. So the first dollar level has 15%
markup, the 2nd is 15.34%, 15.67%, etc. (rounded, of course)



Or you could go truly nuts and base it on percentage of products above
and below your average cost of $6. Have it be a weighted sliding
scale. That would be more complex and you didn't give enough data to
play with that.

 

[Gord Dibben]

John McGimpsey show how to calculate a variable rate.

May be something you could use.

http://www.mcgimpsey.com/excel/variablerate.html


Gord Dibben MS Excel MVP

 

[Ed]

Thank you so much for your help.

Ed.

Guess it depends how you want to do it. You need to select an amount
where there is a change in the markup %. It may be every $1, or a true
sliding scale would use your smallest possible dollar increment on
your cost list. So maybe $.01 or $.001.

And, I'd probably find the best would be to make a simple table,
although you could cram all this into a formula. Personally, I'd want
the table to have for printing or visually checking a markup on
occasion, anyway.

List all your prices and the markup in 2 columns. Then use INDEX/MATCH
in your invoice worksheet to get the proper markup. If you use more of
a jump than your smallest increment, make sure to put $35 at the top
and $1.50 at the bottom to allow the formula to work best. By $.25
increments, and rounded for sanity:
..A...........B
35.00....15.00%
34.75....15.34%
34.50....15.67%
34.25....16.01%

Lookup function to pull the markup, assuming E2 is your price:
=INDEX($A$1:$B$135,MATCH(E2,$A$1:$A$135,-1),2)

And the formula to figure out the difference is just simple division.
Well, 2 of them. Using my $.25 increment:
(60%-15%)/((35-1.50)/.25)=.003358209

Add that to each level of markup. So the first dollar level has 15%
markup, the 2nd is 15.34%, 15.67%, etc. (rounded, of course)



Or you could go truly nuts and base it on percentage of products above
and below your average cost of $6. Have it be a weighted sliding
scale. That would be more complex and you didn't give enough data to
play with that.

 

[Ed]

Thank you so much for your help.

Ed.

John McGimpsey show how to calculate a variable rate.

May be something you could use.

http://www.mcgimpsey.com/excel/variablerate.html


Gord Dibben MS Excel MVP

 

[Ed]

I found the following formula, using interpolation. It will start by
assigning a 50% markup to $1.00 and the percentage will go down from there
until it reaches $39.99; and will start at 12% for anything above $40.00.

=IF(A1<=39.99,(-A1/100+0.50+1/100),0.12)

I would like to know how I can add to the formula so that it starts and ends
at 14 different intervals:

$1.00-$1.99 assign 50%
$2.00-$2.99 assign 49%
$3.00-$3.99 assign 48%
$4.00-$4.99 assign 47%
$5.00-$5.99 assign 46%
$6.00-$6.99 assign 45%
$7.00-$7.99 assign 44%
$8.00-$8.99 assign 43%
$9.00-$9.99 assign 42%
$10.00-$10.99 assign 41%
$11.00-$11.99 assign 40%
$12.00-$12.99 assign 39%
$13.00-$13.99 assign 38%
$14.00-$14.99 assign 37%
$15.00-$15.99 assign 36%
$16.00-$16.99 assign 34%
$17.00-$17.99 assign 32%
$18.00-$18.99 assign 30%
$19.00-$19.99 assign 25%
$20.00-$24.99 assign 20%
$25.00-$29.99 assign 18%
$30.00-$34.99 assign 16%
$35.00-$39.99 assign 14%
$40.00+ assign 12%

Thank you.

Ed.

 

[Spiky]

I found the following formula, using interpolation. It will start by
assigning a 50% markup to $1.00 and the percentage will go down from there
until it reaches $39.99; and will start at 12% for anything above $40.00.

=IF(A1<=39.99,(-A1/100+0.50+1/100),0.12)

I would like to know how I can add to the formula so that it starts and ends
at 14 different intervals:

Did you mean 24 percent groups since you listed 24? The ones you
listed are not linear or predictable in any mathematical way, so one
formula won't cut it, you'll need several to get all those different
percents. BTW, that formula will not give you exactly the right
amount, you'd need to add a rounding. IE: $7.50 cost equals a percent
of 43.5, not 44 even. So:
=IF(A1<=39.99,ROUND(-A1/100+0.50+1/100,2),0.12)

However, unless you want a long series of IF statements for a formula,
why not use a lookup table? Put the table on a separate worksheet, out
of the way of your invoice. Or use the "table within a formula" as
shown in Gord's link, but in a LOOKUP function:
=LOOKUP(A1,{1;2;3;4,etc},{0.5;0.49;0.48;0.47,etc})

For an actual table:
You can just enter the table like you have typed here (you would need
to take out the ranges, just use $1,2,3...), and use the Index/Match
formula I gave for a lookup. Although I designed it with the highest
cost number at the top. So:

40.......12%
35.......14%
30.......16%
etc

 

[Ed]

Thank you for all your help.

I actually would like to exact percentage (not rounded off). I should've
made it more clear. Yes, there are 24 percent groups.

What would be the formula for a long series of IF statements? Also, is
there a limitation to the number of IF statements that you can include
within a formula (24 percent groups).

Thank you.

Ed.

 

[Spiky]

Thank you for all your help.

I actually would like to exact percentage (not rounded off). I should've
made it more clear. Yes, there are 24 percent groups.

What would be the formula for a long series of IF statements? Also, is
there a limitation to the number of IF statements that you can include
within a formula (24 percent groups).

Thank you.

Ed.

You can have only 7 nested IF statements, I believe. You don't
actually need 24, though. In looking at your list, I see you can use
your above formula for everything from $1.00-15.99. $16.00-18.99 is
also a steady range, so if you can alter your calculation to jump 2%
each $1, you should be able to stay within the 7 IF requirement.
Continuing like this, I see 5 sections. Something like this:

=IF(A1<16,(-A1/100+0.51),IF(A1<19,(2nd calc),IF(A1<25,(3rd
calc),IF(A1<40,(4th calc),0.12)

Sorry, my brain isn't working well on figuring out those formulas
offhand. Calculate those formulas for each section and plug them in.

 

[Ed]

Thank you again for all your help.

Ed.

You can have only 7 nested IF statements, I believe. You don't
actually need 24, though. In looking at your list, I see you can use
your above formula for everything from $1.00-15.99. $16.00-18.99 is
also a steady range, so if you can alter your calculation to jump 2%
each $1, you should be able to stay within the 7 IF requirement.
Continuing like this, I see 5 sections. Something like this:

=IF(A1<16,(-A1/100+0.51),IF(A1<19,(2nd calc),IF(A1<25,(3rd
calc),IF(A1<40,(4th calc),0.12)

Sorry, my brain isn't working well on figuring out those formulas
offhand. Calculate those formulas for each section and plug them in.