Can use the goal seek for this problem.
Data --> Forecast --> What-If Analysis --> Goal Seek
With your example values, it gives £3.77 as the selling price.
Hi folks,
I'm not sure if this has been posted before or if there is a guide on how to do this on this forum, so apologies if there is.
I want a formula which I can use to work out my selling price on eBay, assuming I want to make a 15% profit of the selling price, and the following are my costs:
Item cost £1.72
Shipping £0.87
eBay fees 10% of final selling price
PayPal fees 2.9% + £0.20 of final selling price
Many thanks
Can use the goal seek for this problem.
Data --> Forecast --> What-If Analysis --> Goal Seek
With your example values, it gives £3.77 as the selling price.
Last edited by ParamRay; Jul 21st, 2019 at 12:15 PM.
Windows 10, Excel 365
That’s great, but is it possible to use this as a formula, as I need to apply it to 55,000 products.
I can give you the logic but you will have to fill the formula to match your sheet layout.
The first thing you have to do is get the selling price of your item that generates a 15% Gross Margin.
In you example your total cost, is the item cost plus the shipping: 1.72 + .87 = 2.59. To get a Gross Margin of 15% for item that costs 2.59, you must divide the cost by the reciprocal of the GM.
So that is 2.59/(1-15%) = 3.05. Now we take care of the fees. First you add in the .20 Paypal Fee = 3.25. Now you have to get another GM of 12.9% which is the total of the Ebay 10% and the Paypal 2.9%.
Same as before you divide by the reciprocal. Now it is 3.25/(1-12.9%) = 3.73 which is your FINAL SELL Price. If you go backwards now you will see that
3.73 * 10% Ebay fee = .37
3.73 * 2.9% PPal fee = .11
also subtract the .20
These 3 add up to .68 subtract that from your sell of 3.73 and you have 3.05 which was your original sell price at a 15% margin.
If you layout all the base figures on a row you can generate a easy formula...
I hope this helps.
igold
I'm a drinker with a coding problem...
All code is written with Excel 2010 - Please test all code on a backup copy of your data.
See if this helps you out:
Excel 2012
A B C D E 1 Profit Shipping eBay fees PayPal % PayPay fix 2 15% 0.87 10% 2.90% 0.2 3 4 Cost Break even price Profit price 5 1.72 3.203215 3.683697 6 7 Cost 1.72 8 Shipping 0.87 9 eBay fees 0.320321 10 Paypal % 0.092893 11 paypal Fixed 0.2 12 13 Total 3.203215 Sheet2
Worksheet Formulas
Cell Formula C5 =(A5+$B$2+$E$2)/(1-$C$2-$D$2) E5 =C5*(1+$A$2) C7 =A5 C8 =B2 C9 =C5*C2 C10 =C5*$D$2 C11 =$E$2 C13 =SUM(C7:C11)
Everything from row 7 down is just for a check. You can just use the C5 and E5 formulas (or combine them) to get your answer.
Last edited by Eric W; Jul 21st, 2019 at 01:34 PM.
Cheers,
Eric
When you eliminate the impossible, whatever remains, however improbable, must be the truth.
-Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
-Post a screen shot with the HTML Maker
@Eric
If you use a 3.68 sell price, after you deduct the fixed fee of .20, the 10% Ebay = .37 and the PPal of 2.9% = .11 you are left with 3.01. If the item costs 2.59 (cost + shipping) that leaves a Gross Margin % = 11.41%.
igold
I'm a drinker with a coding problem...
All code is written with Excel 2010 - Please test all code on a backup copy of your data.
That was a typo, a 3.68 Selling Price leaves a 13.95% Gross Margin. Sorry about the confusion...
igold
I'm a drinker with a coding problem...
All code is written with Excel 2010 - Please test all code on a backup copy of your data.
@eaxlns.... Hmm, 3 responses with 3 different answers. How can that be? Which should you use?
In part, that hinges on terminology and what you want profit to be based on: just the item cost, simply passing along the "operating costs" (shipping, ebay fees and paypal fees); or all of your costs.
Personally, I don't believe you should make a profit on shipping and fees. But that's entirely up to you.
The following demonstrates the formulas for the two different methods.
A B C D E F 1 item cost 1.72 2 ship 0.87 3 ebay% 10.00% 4 paypal% 2.90% 5 pp fixed 0.20 6 prof% 15.00% 7 8 profit on item cost profit on all costs 9 Exact Rounded Exact Rounded 10 price 3.5517 3.56 3.8696 3.87 11 ship -0.8700 -0.87 -0.8700 -0.87 12 ebay -0.3552 -0.36 -0.3870 -0.39 13 paypal -0.3030 -0.30 -0.3122 -0.31 14 net rev 2.0235 2.03 2.3004 2.30 15 prof% 15.00% 15.27% 15.00% 14.99%
Code:Formulas: B10: =(B1/(1-B6)+B2+B5)/(1-B3-B4) B11: =-B2 B12: =-B10*B3 B13: =-(B10*B4+B5) B14: =SUM(B10:B13) B15: =(B14-B1)/B14 C10: =ROUNDUP((B1/(1-B6)+B2+B5)/(1-B3-B4),2) C11: =-B2 C12: =-ROUND(C10*B3,2) C13: =-ROUND(C10*B4+B5,2) C14: =SUM(C10:C13) C15: =(C14-B1)/C14 E10: =(B1+B2+B5)/(1-B6-B3-B4) E11: =-B2 E12: =-E10*B3 E13: =-(E10*B4+B5) E14: =SUM(E10:E13) E15: =(E10-B1+SUM(E11:E13))/E10 F10: =ROUNDUP((B1+B2+B5)/(1-B6-B3-B4),2) F11: =-B2 F12: =-ROUND(F10*B3,2) F13: =-ROUND(F10*B4+B5,2) F14: =SUM(F10:F13) F15: =(F10-B1+SUM(F11:F13))/F10
I highlighted the primary formulas.
The calculations in columns B and E are provided as a proof of concept. They demonstrate that exact profit margin is indeed calculated.
The calculations in columns C and F are the correct calculations, given the real-world requirement to round to unit of currency. This necessarily causes a difference in the profit margin. But it should be "close".
Note that if the profit is applied only to the item cost, excluding operating costs, the profit margin is really a percentage of net revenue (price minus operating costs), not the full price.
Since you specified a profit margin as a percentage of price per se, you would want the profit applied to all costs.
Note that my numbers different somewhat from the other 3 responses. This is explained in the comments below.
Comparable to my 3.87, I suspect. But since ParamRay did not show his formulas, we do not what the cause of the discrepancy is.
My comparable numbers are 2.03 and 3.56. The difference is: I do not include shipping costs in the amount that the profit margin is based on.
If we exclude ebay and paypal operating costs, I see no reason not to also exclude the shipping cost. It is just another operating cost; that is, it is not a cost of goods sold.
After thought.... That depends on an interpretation of "shipping" in the OP. I interpret it to mean: the cost of shipping to the customer. Arguably, igold might interpret it to mean: the cost of shipping to the seller. Both interpretations are equally valid, IMHO. If igold's interpretation is correct, I would agree with his inclusion of shipping with cost of goods sold. In that case, change the B1/(1-B6)+B2 to (B1+B2)/(1-B6) in B10 and C10. Corresponding changes are also needed in the "proof of concept" formulas in 11:15. Let know if details are required.
In any case, note that igold's profit margin is based on the net revenue (price minus operating costs), not on the price, as eaxlns specified.
Your mistake is in simply multiplying the break-even price by (1+15%).
That modified price would change the ebay and paypay components in the derivation of the price, which is not reflected correctly.
Last edited by joeu2004; Jul 21st, 2019 at 05:35 PM.
I did read the shipping cost to be the cost of shipping to the seller. Therefore, I say that the true cost of the item is the "Landed Cost" which would include the shipping. Additionally, I took the OP to mean the Selling Price of the item before fees. Gross Margin is defined as the amount of money remaining after deducting the Cost of Good Sold. You would pay your operating costs out of your Gross Margin. The fees would be treated no different than the expense of keeping the lights on.
Last edited by igold; Jul 21st, 2019 at 06:58 PM.
igold
I'm a drinker with a coding problem...
All code is written with Excel 2010 - Please test all code on a backup copy of your data.
I chose my final formula of
=(Break-even price)*(1+percent profit)
because the OP said "I want to make a 15% profit of the selling price". However, I really didn't do a complete analysis. Multiplying the break-even price by the desired profit ended up with higher fees, so it ended up as a 13% profit on the sale price, or a 24% profit on the cost. Neither one is a desired answer.
It appears joeu2004 has the most complete and accurate analysis, so I'd stick with that.
On a related note, I spent a lot of time once working out the same type of analysis for another eBay seller. His setup was much more complicated, with different types of products getting different fees and tax rates. I finally concluded in that case that the only solution was to create a UDF that would start at a base sale price, probably the cost times a percentage, then would increment by 1, checking all the fees and taxes, and quit when all constraints were met. There just were no fixed formulas that covered all situations for him.
Cheers,
Eric
When you eliminate the impossible, whatever remains, however improbable, must be the truth.
-Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
-Post a screen shot with the HTML Maker
Like this thread? Share it with others