A formula that should be easy for forum members

[dhulqarten]

Hi,

I am trying to come up with a formula that will generate a price based on its cost. The only trick is that it can't be something constant like 20% or 30% more than the cost. It has to be a formula that adds a bigger profit on smaller numbers and lower profit on bigger numbers.

So on a straight forward 20% on top cost would be:

Cost*1.2=Price ---- so $1.00 cost would result $1.20 and $10.00 would result $12.00 and $100 cost would result $120.00

What i would like is smaller number costs from $0.50-$1.00 to be 150% profit, and 1.00 to 2.00 to be something like 100% profit, and 2.00-3.00 to be 75% profit and 5.00-10.00 to be 50% and 10.00-20.00 to be 25% and 20.00-50.00 to be 18% ---- except the curve the % change should be smooth in correlation to the increasing or decreasing of the cost price. So as the numbers are smaller (ex. $0.50 or $1.00) the percentage starts very high and decreases dramatically and levels out around $20.00-$80.00 and higher to 17-18%.

Any ideas on how to do this with a 1 line formula? Any help would be much appreciated.

-Raf

 

[Office_Shredder]

The first thing that comes to mind is an exponential function. Try and play around with the function e^-x to get something that does what you want

 

[dhulqarten]

Hi Shredder,

I am terrible at math. I need to do this in excel --- any idea or an example of what you are trying to tell me? If you give me an example then i can get an idea and start to play around with it?

Raf

 

[Office_Shredder]

Basically you're looking for a function which is decreasing, starts off fairly large and then levels off

http://www.wolframalpha.com/input/?i=graph+e^(-x)+1+from+0+to+4

Looking at the graph it satisfies your criteria. If it cost you c dollars to buy the product, you could try selling it for (e^-c+1)*c. For very large values of c you're making an arbitrarily small profit so you might want to shift the graph up by a little bit to guarantee a baseline profit. If you want to change how quickly your profit percentage drops, change the base of the exponent

http://www.wolframalpha.com/input/?i=graph+e^(-x)+1,+5^(-x)+1,+1.1^(-x)+1+from+0+to+4

All of those functions level out at a value of 1 for extremely large values of x, but they obviously do it at different rates

 

[dhulqarten]

Hi Shredder,

Thanks for the prompt response... I understand by looking at the curve that it is the correct representation of the strategy i am trying to go for.. The problem is my understanding of mathematics behind it. What does the E stand for? I need to plug this in a excel file that will spit out the right number for me. So if my cost is .50 --- it spits our 1.50 and the higher Cost goes, the lower the % difference between cost and the number it spits out.

How can I structure your formula so that it is excel friendly?

 

[DivisionByZro]

f(x)=e^x is called the exponential function. You can look it up on Wikipedia. In excel you can input it as exp(x), or in this case c*(exp(-c)+1). In nice print (Latex), it looks like this:

$$c\cdot( e^{-c}+1)$$

As Office Shredder suggested, you probably should add a base profit/cost:

$$c\cdot( e^{-c}+1) + k$$

 

[dhulqarten]

problem solved... thank you all so much!