Regressive Logarithmic Retail Markup

[Fibonacci]

Hello,

I am having a lot of trouble trying to figure out a way to formulate 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 regressive curve by adjusting 1 or 2 cells and but
adjusting them arrive at a curve that I like.

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

Best Regards,

Fibonacci

 

[Jim Cone]

For an arithmetic approach...

In B912 enter the column titles and the initial numbers...
Cost Markup Price
$1.00 6.00 $6.00
$2.00 5.00 $10.00
$3.00 4.00 $12.00

In A10 enter "= 2/47"
In B13 enter "4"
In C13 enter "=C12-$A$10"
In D13 enter "=C13*B13"
Format the cells.

Fill B1313 down to row 59
------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Fibonacci" <[email protected]>
wrote in message Hello,
I am having a lot of trouble trying to figure out a way to formulate 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 regressive curve by adjusting 1 or 2 cells and but
adjusting them arrive at a curve that I like.
Could anyone offer a solution to my problem? It would be most
appreciated.
Best Regards,
Fibonacci

 

[Fibonacci]

For an arithmetic approach...

In B912 enter the column titles and the initial numbers...
Cost Markup Price
$1.00 6.00 $6.00
$2.00 5.00 $10.00
$3.00 4.00 $12.00

In A10 enter "= 2/47"
In B13 enter "4"
In C13 enter "=C12-$A$10"
In D13 enter "=C13*B13"
Format the cells.

Fill B1313 down to row 59

Thanks very much Jim, that puts me on the right track.

 

[Pete_UK]

Please don't double-post - you have another answer elsewhere.

Pete

 

[Fibonacci]

Perhaps it was not exactly the answer i was looking for but I didn't
want to seem ungratefull for your efforts.

But since you feel the urge to play that age-old game of usenet
policeman, I offer you this advice: get a life and/or mind your
business.

 

[Dana DeLouis]

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

... $1.00 -> 500%
... $3.00 -> 300%
...$50.00 -> fall to around 100%.

Don't know if you would want to go this route.
The limit of the Hyperbolic Cotangent function tends towards 1 as the input
increases.
Perhaps you could adjust it with the decreasing exponential function also.
Maybe a custom function similar to this...

Function MyFit(x)
MyFit = 2.10169200448472 / Exp(5.55641007649304E-02 * x) _
+ 0.869379917043942 * Coth(0.297090954276678 * x)
MyFit = CSng(MyFit)
End Function

Function Coth(x)
Coth = (Exp(x) + Exp(-x)) / (Exp(x) - Exp(-x))
End Function

Some tests...

?MyFit(1)
5
?MyFit(3)
3
?MyFit(50)
1

And as the input increases, it very slowly drops below 1.

?MyFit(1000)
0.8693799

 

[Fibonacci]

Thanks Dana,

That is exactly what I was hoping for!

Best Regards,

Fibonacci