Macro/Program to calculate adjusted sale price

[Slpygry]

There is a website, Selling Price Calculator, that will run a calculation for Sale Price where the sales price includes fees. There have been many questions on this forum regarding a “formula” for this function, and with all the research I’ve done, I think I’ve realized that a simple “formula” isn’t possible; (unless you’re getting the sales price one at a time, then there is an Excel Function). However, I need the calculation on thousands of items at once and I think it needs to be a program or macro that cycles through incrementing sale prices to reach the desired percent margin. Is this the right assumption?

I have already created an Excel Power Query that has a row for each item I’m trying to sell. Each row has (among other things) the % Margin I want, as well as the cost for the item (at this time there is only one cost, (shipping you may be thinking is another cost, but I’m going to charge shipping at actual cost, so it will not affect the % margin)). I was hoping to run a Macro that will “spit out” or calculate and display the sale price for each row, on the row it belongs to.

I’m assuming I’ve done my math correctly. If % Margin = Profit / Cost, then the formula’s I need to use are as follows:

For the sell price of $250 or less: % margin = ((sell $) - (cost + (sell $ * .06))) / (cost + (sell $ * .06))

For the sell price of $250.01 or more: % Margin = ((sell $) – (cost + $15 + ((sell $ – 250) * 0.06))) / (cost + ((sell $ – 250) * 0.06))

I know enough about Macro’s to be dangerous but not effective, so I’m not sure what other information needs to be provided; nor am I positive that a Macro is the solution. Please let me know, if you can help, what you need from me. Thank you for your time, Karri

 

[StephenCrump]

Welcome to the Forum!

I can see that @EricW mentioned Solver / macro solutions here: Formula for calculating eBay selling price

But the requirements you've now posted seem very simple ...

If you want to measure margin on cost (that's up to you) then Sell Price = Cost Price * (1+required margin), as shown in the first option below. You could code this into a macro, but why bother?

It's not clear why you have a different formula above $250: $15 + 6% of (Sell - $250) is the same as 6% of Sell.

There are other ways you may want to define your margin. The second option shown is based on a margin expressed as a % of Sell Price, rather than a % of Cost Price.

	A	B	C	D	E	F
1
2		Cost price	Required margin on cost	Sell price	Profit	Profit as % Cost
3
4		$10	20%	$12	$2	20%
5		$25	12%	$28	$3	12%
6		$300	6%	$318	$18	6%
7
8
9		Cost price	Required margin on sell price	Sell price	Profit	Profit as % Sell price
10
11		$10	20%	$12.50	$2.50	20%
12		$25	12%	$28.41	$3.41	12%
13		$300	6%	$319.15	$19.15	6%
Sheet1

Cell Formulas
Range	Formula
D4:D6	D4	=B4*(1+C4)
E4:E6,E11:E13	E4	=D4-B4
F4:F6	F4	=E4/B4
D11:D13	D11	=B11/(1-C11)
F11:F13	F11	=E11/D11

 

[Slpygry]

Thank you for welcoming me to the Forum. I am part of the discussion with @EricW in the thread you attached, towards the bottom of that thread, and he suggested that I pose my question in a separate thread.

Also, I apologize, I made a mistake when posing my initial question. For a sell price that is over $250.00 the formula should read:
% Margin = ((sell $) – (cost + $15 + ((sell $ – 250) * 0.035))) / (cost + ((sell $ – 250) * 0.035))

The spreadsheet in the thread you attached does a great job of providing the sell price if my fees are constant (6% for the fee and 8% for the margin), however, it does not work with the added 3.5% fee on the sell price that is over $250.00.

As an example, if my sell price is $1040.39, then my fee is: ($250 * 6%) + (($1040.39 - $250) * 3.5%) = $42.66

Therefore, the sell price of $250.01 or more: Sell $ = (cost + 15 + ((sell $ - 250) * .035)) / (1 - % margin)

I can use the formula in the attached if the sales price will be less than $250. However, is there is a formula that solves for the sell price over $250?

 

[StephenCrump]

Thanks for clarifying. And sorry, my first post oversimplified. Is this closer to what you're looking for?

	A	B	C	D	E	F	G	H
1		Fees:	$250	6.0%
2			then	3.5%		CHECK:
3		Cost price	Required margin on sell price	Sell price		Fees	Margin	Margin as % Sell price
4
5		$50	20%	$67.57		$4.05	$13.51	20%
6		$100	12%	$121.95		$7.32	$14.63	12%
7		$250	6%	$283.15		$16.16	$16.99	6%
8		$300	10%	$354.05		$18.64	$35.40	10%
9		$500	15%	$621.17		$27.99	$93.17	15%
10
Sheet1

Cell Formulas
Range	Formula
F5:F9	F5	=D$2*D5+(D$1-D$2)*MIN(D5,C$1)
G5:G9	G5	=D5-F5-B5
H5:H9	H5	=G5/D5
D5:D9	D5	=IF(B5<=C$1*(1-D$1-C5),B5/(1-D$1-C5),(B5+C$1*(D$1-D$2))/(1-D$2-C5))

 

[Slpygry]

I really wish I was this smart. I feel like you answered this from your elementary book and I struggled and struggled. I've accomplished a lot with Excel, but this formula made me feel "less than". You are a rock star, and I really appreciate your help. Thank you, so, so much.