Custom Formula Question

[andrnick7]

Hey peeps,
I just have a quick question and I know this is going to sound ridiculously simple and I apologize if it is - I'm a bit new to Excel formulas. My question is this: I'm trying to create a formula that takes into account a certain price, adds fees (percentages + fixed fees + taxes) to that price, and then marks up that sum by 50%. Then, in another cell, I'd like to compare that price to the selling price and calculate the profit margin.

This is what I've done so far: Screenshot by Lightshot. Here's a breakdown:

• 1st cell: Retail Price
• 2nd cell: Wholesale Price
• 3rd cell: Adding Fees (=J2*0.129-0.3 (Second cell, multiplied by 12.9% and I probably should've added the 0.30 cents as it's a fixed fee)
• 4th cell: Total Cost (=K2+J2) (The fees + the wholesale price)
• 5th cell: Difference from retail to wholesale + costs (=I2-L2)
• 6th cell: Do a 50% markup on the wholesale + fees price =((1.5*J2)+K2)
• 7th cell: Determine Profit Margins: (=(M2/I2)*100) (Profit potential/selling price * 100)

I know the format is a bit wonky. It should be: Retail Price, Wholesale Price, Fees, Total Cost, Profit Margins, 50% markup, and difference from retail. But I'm specifically putting the profit margins at the end of the evaluation.

Is there anyway I can condense my steps into fewer cells with a more complex formula? Is this efficient?

Thank you for your time and I hope this serves as a lesson for me and others on here. I'd appreciate if anyone could help me with my problem.

 

[andrnick7]

everything made up of a2 and b2
https://www.dropbox.com/s/c1w8w23xaj00w7z/Expected Result_etaf.xlsx?dl=0

Thank you so much, Etaf! You're what makes this forum so great

 

[andrnick7]

One last question, sorry to be a pest. On the "Total Cost" Row, you have the formula at: =(((1.5*B2)+(B2*0.129+0.3)))-(((((1.5*B2)+(B2*0.129+0.3)))*(0.0825))). Should it be instead: =(((1.5*B2)+(B2*0.129+0.3)))+(((((1.5*B2)+(B2*0.129+0.3)))*(0.0825)))

Rather than subtracting, you add. I wondered why the total cost with a 8.25% tax added would make it cheaper than the sale price that is marked up 50%. Could you help clarify this?

 

[etaf]

i just used your formula
in E
you have
=A2-D2
NOTE the -
Also in I2 you have a - see below

in D2 you have =C2+B2
so replace D2 with the above

=A2-D2 becomes =A2-(C2+B2)

In C2 you have
=B2*0.129+0.3

so replace C2 with =A2-(C2+B2)
becomes
=A2-((B2*0.129+0.3)+B2)

I have not looked at the maths - just substituted all your formulas , and checked they give the same answer on each column

Is your row 2 - correct
you have A2 as 12.95
you have I2 as 12.23
Cheaper
if so then the substitution is giving the exact same answers in the rows i showed

as i say I did not check the logic or maths , just reproduced your rows

in I2 you have
=F2-H2
and IF that should be =F2+H2
then
=(((1.5*B2)+(B2*0.129+0.3)))+(((((1.5*B2)+(B2*0.129+0.3)))*(0.0825)))
which is 14.43

 

[andrnick7]

Thank you so much, Etaf! You're right, it was the logic that was off. Thank you for expounding on it and confirming it for me