Logarithmic Retail Markup Worksheet
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 |
Logarithmic Retail Markup Worksheet
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 |
Logarithmic Retail Markup Worksheet
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 |
Logarithmic Retail Markup Worksheet
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 |
All times are GMT +1. The time now is 01:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com