Math Genius? Formula to figure price

[mljohn]

I need help figuring a math formula to figure my price.

I have been doing it in my head somehow but now I am creating a website and I need a formula.

Right now I look at the cost versus the Retail price and see the amount of difference. My price needs to beat the Retail price so I look at the difference between cost and Retail price and then figure the least amount of discount I can give the customer that would be a good deal for them and me.

Example
A. Cost: $43.05
B. Retail Price: $238.99
C. My Price: $180.00
D. Customer Saved: $58.99
E. Profit: $136.95

I need a formula that is dynamic. When there is lots of difference between Cost and Retail I give a bigger discount but when there is not as much difference I give a smaller discount.

Here is a link to more data: http://www.box.net/shared/oyxchhpv3a

Thanks

Matt

 

[Ralajer]

Could you expand on your problem a little more, quantizing dynamic and good deal. Without more detail any formula would be arbitrary.

 

[asad]

It looks like you wanted to give app. 30% saving to customers and 70% for yourself. So I am just using these figures and it will calculate the price for you and for customers as soon as put in original cost and retail price in columns A and B.
Here is a copy:

Excel Workbook
	A	B	C	D	E
1	Cost	Retail Price	My Price	Customer Saving	My Profit
2	43.05	238.99	180	58.99	136.95
3				30%	70%
4	65	120	103.5	16.5	38.5
5	42	300	222.6	77.4	180.6
6			0	0	0
Sheet1

 

[mljohn]

Please look at the information in the Box.net link:
http://www.box.net/shared/oyxchhpv3a

I need a math formula that takes into account the difference between the Cost and Retail. Sometimes there is more room to play with and sometimes there is less.

If I do a static percentage across the board then sometimes it kills my profit and I go negative.

I'm thinking that the math formula to figure out my price might be something like a percentage increase of Cost (or percentage decrease of Retail), based on the percentage of the cost-Retail.

Matt

 

[shg]

Seems to me that all you need it is this:

       ---B--- --C--- -D- ---E----
   2   C% of R Markup GM  "U Save"
   3       18%   150% 60%      55%
   4       20%   145% 59%      51%
   5       22%   140% 58%      47%
   6       24%   135% 57%      44%
   7       26%   130% 57%      40%
   8       28%   125% 56%      37%
   9       30%   120% 55%      34%
  10       32%   115% 53%      31%
  11       34%   110% 52%      29%
  12       36%   105% 51%      26%
  13       38%   100% 50%      24%
  14       40%    95% 49%      22%
  15       42%    90% 47%      20%
  16       44%    85% 46%      19%
  17       46%    80% 44%      17%
  18       48%    75% 43%      16%
  19       50%    70% 41%      15%

You fill out col C (the markup) as a function of the cost percentage of retail price.

D3 and down is

=C3/(1+C3)

E3 and down is

=1-(1+C3)*B3

 

[mljohn]

I made some changes.

http://www.box.net/shared/oomc2p6pr0

I times the "Dif" by 90% to come up with the Price. Seems to work on some comparing it to the "MyPrice" but on others the savings is too great and my profit too small.

Is there a math formula that would adjust the price when the savings is too great compared to the profit?

Matt

 

[shg]

You're trying to do this in one step, but it's a two-step process.

The only independent variable is the ratio of cost to retail price.

The only dependent variable is your markup of the cost to become your sales price.

Once you enter values in col C that show acceptable Gross Margin in col D and an attractive "U Save" value in col E, you can use a simple lookup formula to price any item using just columns B:C.

For example, if the values shown were acceptable, then to price an item that cost $25 and was being retailed for $100,

your price = 25 * (1 + vlookup(25/100, $B$3:$C$19, 2)) which would return $58.75