Macro/Program to calculate adjusted sale price

Slpygry

New Member
Joined
Feb 23, 2022
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,996
Office Version
  1. 365
Platform
  1. Windows
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.

ABCDEF
1
2Cost priceRequired margin on costSell priceProfitProfit as % Cost
3
4$1020%$12$220%
5$2512%$28$312%
6$3006%$318$186%
7
8
9Cost priceRequired margin on sell priceSell priceProfitProfit as % Sell price
10
11$1020%$12.50$2.5020%
12$2512%$28.41$3.4112%
13$3006%$319.15$19.156%
Sheet1
Cell Formulas
RangeFormula
D4:D6D4=B4*(1+C4)
E4:E6,E11:E13E4=D4-B4
F4:F6F4=E4/B4
D11:D13D11=B11/(1-C11)
F11:F13F11=E11/D11
 

Slpygry

New Member
Joined
Feb 23, 2022
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,996
Office Version
  1. 365
Platform
  1. Windows
Thanks for clarifying. And sorry, my first post oversimplified. Is this closer to what you're looking for?

ABCDEFGH
1Fees:$2506.0%
2then3.5%CHECK:
3Cost priceRequired margin on sell priceSell priceFeesMarginMargin as % Sell price
4
5$5020%$67.57$4.05$13.5120%
6$10012%$121.95$7.32$14.6312%
7$2506%$283.15$16.16$16.996%
8$30010%$354.05$18.64$35.4010%
9$50015%$621.17$27.99$93.1715%
10
Sheet1
Cell Formulas
RangeFormula
F5:F9F5=D$2*D5+(D$1-D$2)*MIN(D5,C$1)
G5:G9G5=D5-F5-B5
H5:H9H5=G5/D5
D5:D9D5=IF(B5<=C$1*(1-D$1-C5),B5/(1-D$1-C5),(B5+C$1*(D$1-D$2))/(1-D$2-C5))
 
Solution

Slpygry

New Member
Joined
Feb 23, 2022
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,176,079
Messages
5,901,274
Members
434,884
Latest member
ares0027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top