Hello,

I am having a lot of trouble trying to figure out a way to create a
spreadsheet to automate my retail markup process. Here is what I am
trying to do:

Let's say for items costing me $1.00 I would like to have a 500%
markup. A $3.00 item, a 300% markup. By the time an item reaches a cost
of $50.00 I would like the markup to fall to around 100%.

I am guessing this would involve some kind of logarithmic function, I
am not sure.

I would like to have a spreadsheet that allows me to adjust the
properties of this curve by adjusting 1 or 2 cells, changing it's shape
to find one that fits my needs.

I have had some success, but the can never seem to adjust the rate of
decay and its position in the curve as I would like.

Could anyone offer a solution to my problem? It would be most
appreciated.

Best Regards,

Fibonacci

You could do it by setting up a table of costs and markups, and then
you could insert however many costs you feel you need. The three
examples you quote would give you a table like this:

0.00 500%
3.00 300%
50.00 100%

and the formula I shall give you shortly will regard the costs in the
first column as being a range - the above table means if the range of
price is from $0 to $2.99 then use a 500% markup, if the range is from
$3.00 to $49.99 then use a 300% markup, and if the cost is from $50.00
upwards then use a 100% markup. Hopefully you can see how you can
introduce other values - here's an example:

0.00 500%
1.50 400%
3.00 300%
7.50 250%
20.00 200%
50.00 100%

Let's assume that you type this table in cells X1 to Y6, and that your
cost price is in cell A1. This formula will give you the appropriate
markup:

=VLOOKUP(A1,X$1:Y$6,2)

or if you want your selling price, amend it to this:

=VLOOKUP(A1,X$1:Y$6,2) * A1

The formula can be copied down the column for as many cost prices as
you have in column A.

Hope this helps.

Pete
Fibonacci wrote:
Hello,

I am having a lot of trouble trying to figure out a way to create a
spreadsheet to automate my retail markup process. Here is what I am
trying to do:

Let's say for items costing me $1.00 I would like to have a 500%
markup. A $3.00 item, a 300% markup. By the time an item reaches a cost
of $50.00 I would like the markup to fall to around 100%.

I am guessing this would involve some kind of logarithmic function, I
am not sure.

I would like to have a spreadsheet that allows me to adjust the
properties of this curve by adjusting 1 or 2 cells, changing it's shape
to find one that fits my needs.

I have had some success, but the can never seem to adjust the rate of
decay and its position in the curve as I would like.

Could anyone offer a solution to my problem? It would be most
appreciated.

Best Regards,

Fibonacci

Thanks very much Pete, very helpful.

Best Regards,

Fibonacci


Pete_UK wrote:
You could do it by setting up a table of costs and markups, and then
you could insert however many costs you feel you need. The three
examples you quote would give you a table like this:

0.00 500%
3.00 300%
50.00 100%

and the formula I shall give you shortly will regard the costs in the
first column as being a range - the above table means if the range of
price is from $0 to $2.99 then use a 500% markup, if the range is from
$3.00 to $49.99 then use a 300% markup, and if the cost is from $50.00
upwards then use a 100% markup. Hopefully you can see how you can
introduce other values - here's an example:

0.00 500%
1.50 400%
3.00 300%
7.50 250%
20.00 200%
50.00 100%

Let's assume that you type this table in cells X1 to Y6, and that your
cost price is in cell A1. This formula will give you the appropriate
markup:

=VLOOKUP(A1,X$1:Y$6,2)

or if you want your selling price, amend it to this:

=VLOOKUP(A1,X$1:Y$6,2) * A1

The formula can be copied down the column for as many cost prices as
you have in column A.

Hope this helps.

Pete
Fibonacci wrote:
Hello,

I am having a lot of trouble trying to figure out a way to create a
spreadsheet to automate my retail markup process. Here is what I am
trying to do:

Let's say for items costing me $1.00 I would like to have a 500%
markup. A $3.00 item, a 300% markup. By the time an item reaches a cost
of $50.00 I would like the markup to fall to around 100%.

I am guessing this would involve some kind of logarithmic function, I
am not sure.

I would like to have a spreadsheet that allows me to adjust the
properties of this curve by adjusting 1 or 2 cells, changing it's shape
to find one that fits my needs.

I have had some success, but the can never seem to adjust the rate of
decay and its position in the curve as I would like.

Could anyone offer a solution to my problem? It would be most
appreciated.

Best Regards,

Fibonacci

Thanks for feeding back.

Pete

Fibonacci wrote:
Thanks very much Pete, very helpful.

Best Regards,

Fibonacci


Pete_UK wrote:
You could do it by setting up a table of costs and markups, and then
you could insert however many costs you feel you need. The three
examples you quote would give you a table like this:

0.00 500%
3.00 300%
50.00 100%

and the formula I shall give you shortly will regard the costs in the
first column as being a range - the above table means if the range of
price is from $0 to $2.99 then use a 500% markup, if the range is from
$3.00 to $49.99 then use a 300% markup, and if the cost is from $50.00
upwards then use a 100% markup. Hopefully you can see how you can
introduce other values - here's an example:

0.00 500%
1.50 400%
3.00 300%
7.50 250%
20.00 200%
50.00 100%

Let's assume that you type this table in cells X1 to Y6, and that your
cost price is in cell A1. This formula will give you the appropriate
markup:

=VLOOKUP(A1,X$1:Y$6,2)

or if you want your selling price, amend it to this:

=VLOOKUP(A1,X$1:Y$6,2) * A1

The formula can be copied down the column for as many cost prices as
you have in column A.

Hope this helps.

Pete
Fibonacci wrote:
Hello,

I am having a lot of trouble trying to figure out a way to create a
spreadsheet to automate my retail markup process. Here is what I am
trying to do:

Let's say for items costing me $1.00 I would like to have a 500%
markup. A $3.00 item, a 300% markup. By the time an item reaches a cost
of $50.00 I would like the markup to fall to around 100%.

I am guessing this would involve some kind of logarithmic function, I
am not sure.

I would like to have a spreadsheet that allows me to adjust the
properties of this curve by adjusting 1 or 2 cells, changing it's shape
to find one that fits my needs.

I have had some success, but the can never seem to adjust the rate of
decay and its position in the curve as I would like.

Could anyone offer a solution to my problem? It would be most
appreciated.

Best Regards,

Fibonacci