I'm having a hard time with something that I feel should be pretty basic. I'm trying to have excel return a selling price based on profit after fees and shipping costs. Simple right?

So I have a cost, selling price, shipping charge, selling fees, profit, and markup column. Profit is (selling price - fees - shipping - cost). Markup is (profit/selling price). What I need to do is be able to insert my cost, and it automatically return a selling price with all expenses included in the price.

So far, the only way I can see doing it is using the goal seek tool to tell excel to change the selling price based on leaving the mark up at a certain percent. I can tell it that I want mark up to be 0.50 (50%) and have it change the selling price based on that. I cant use this tool when I have 10,000 line items though. Is there a way to have it automatically run goal seek after telling it a cost or can you guys think of a better way to write my equations so I don't have to use goal seek?

And by the way, it won't let me upload a XLS file, only an XML file. Let me know if you guys can't open that file. Thanks

 

I'm no expert, but have you tried converting your data field to a table? This will auto calculate all the formulas that you enter for the first row for example. I'm away from my computer so I haven't really opened your attachment. Hope this helps

 

Bump it up. I don't know why running it in a table would make any difference..? Can you explain what you mean

 

Maybe I didn't understand your question. I thought you were concerned about having to add the formula to thousands of items. Converting your data field will automatically repeat your formulas for every calculation inside the field. Again I'm no expert so maybe someone else can chime in here

 

So I solved the selling price equation for profit, set it equal to the other equation for profit and then did some algebra to solve for selling price. This makes selling price independent of profit. It also assumes that Markup is constant (as you have it set up, selling price depends on itself because markup depends on selling price). Here is the equation with words

Selling Price= -(Cost + Shipping)/(Markup-0.89)

And how you would type it into excel assuming you make markup a constant:

=-(A2+C2)/(F2-0.89)

You can then solve for profit with the equation you have now in the profit cell.

Does that help?

 

This is where I couldn't get out of my own way and rethink how to have selling price independent of profit... Thank you so much!! So simple, yet I could only see it working one way

 

No problem man. Now in return, you must send me your 408.

 

Not sure you would want the 408 as it sits right now. The callies crank needs to be turned at least 10/10, needs all new bearings, blah blah blah... The list is endless as always.

Wait a second. I'm confusing my self again thinking about this. Your equation works flawlessly. But what was the original equation you set up to solve for selling price?

 

So I took
Profit=(selling price)-fees-shipping-cost

and
Markup=profit/(selling price)

which I rearranged to
Profit=markup*(selling price)

Then I set both profit equations above equal to each other like so:
(selling price)*markup=(selling price)-fees-shipping-cost

substitute the equation for fees, fees= 0.11*(selling price), and plug it in
(selling price)*markup=(selling price)-0.11*(selling price)-shipping-cost

after that it's more algebra to get the final equation. Let me know if you need explanation of that.

 

Golden. The algebra is the easy part. I couldn't figure out how to set up the equation. Thanks bud!