Formula to calculate profit

[Hammerjoe]

I would like a quick and easy formula to help calculate profit.
Scenario.

Suppose I purchased 100 Iphone8 at $1000 each plus 15% tax.

In excel:
A1=100
B1=$1000.00
C1=15%

I tried to sell them and nobody would pay me over $1000.00 for them.
So I looked around and found another provider that would sell me the iphones for $500.00/each plus the 15% tax.

A2=100
B2=$500.00
C2=15%


Now I have a guy that would like to buy them all from me at the best price.
I need a formula that would calculate the price to sell all the iphones so that I would recoup all the money spent buying them (that includes the taxes) and on top of that I also have to pay 15% tax to sell the stock to the guy.
Of course I would also like to make a profit so to calculate the price I would like to get a 10% profit on the whole lot.

Any help would be appreciated.
Thanks

 

[shg]

Ah, I get the disconnect, sorry: The 15% tax rate is a markup, not the percent of sales collected.

	A​	B​	C​	D​	E​	F​
1​	Lot​	Qty​	Unit Cost​	Tax​	Total cost​	​
2​	1​	100​	$ 1,150.00​	$ 17,250.00​	$ 132,250.00​
3​	2​	220​	$ 600.00​	$ 19,800.00​	$ 151,800.00​
4​	3​	115​	$ 500.00​	$ 8,625.00​	$ 66,125.00​
5​	Total cost with tax				$ 350,175.00​	E5: =SUM(E1:E4)
6​	Unit COST (not PRICE)				$ 805.00​	E6: =E5/SUM(B2:B4)
7​	Sales Tax Rate				15%​	E7: Input
8​	Target MARGIN (not PROFIT)				10%​	E8: Input
9​	Sell Price				$ 1,046.05​	E9: =E6 / (1 - E7 / (1+E7) - E8)
10​
11​	How much is TAX?				$ 136.44​	E11: =E9 * E7 / (1 + E7)
12​	How much is PROFIT?				$ 104.60​	E12: =E9-E6-E11
13​	What's my MARGIN?				10.00%​	E13: =E12/E9

 

[Hammerjoe]

Ah, I get the disconnect, sorry: The 15% tax rate is a markup, not the percent of sales collected.

	A​	B​	C​	D​	E​	F​
1​	Lot​	Qty​	Unit Cost​	Tax​	Total cost​
2​	1​	100​	$ 1,150.00​	$ 17,250.00​	$ 132,250.00​
3​	2​	220​	$ 600.00​	$ 19,800.00​	$ 151,800.00​
4​	3​	115​	$ 500.00​	$ 8,625.00​	$ 66,125.00​
5​	Total cost with tax				$ 350,175.00​	E5: =SUM(E1:E4)
6​	Unit COST (not PRICE)				$ 805.00​	E6: =E5/SUM(B2:B4)
7​	Sales Tax Rate				15%​	E7: Input
8​	Target MARGIN (not PROFIT)				10%​	E8: Input
9​	Sell Price				$ 1,046.05​	E9: =E6 / (1 - E7 / (1+E7) - E8)
10​
11​	How much is TAX?				$ 136.44​	E11: =E9 * E7 / (1 + E7)
12​	How much is PROFIT?				$ 104.60​	E12: =E9-E6-E11
13​	What's my MARGIN?				10.00%​	E13: =E12/E9

<tbody>
</tbody>

Thank you so much for the help.
I really appreciate.

I am still not getting the same result.
With my manual math I get a total of $1018.32.
I still dont get how your formula gives a different result??

 

[shg]

How are you calculating the price?

 

[shg]

You are calculating Price = Cost * (1 + taxRate) * (1 + Margin).

When you do that, how much do you have to pay the Government?

What's your profit?

What's your margin?

	A​	B​	C​	D​	E​	F​
6​	Unit COST (not PRICE)				$ 805.00​	E6: =E5/SUM(B2:B4)
7​	Sales Tax Rate				15%​	E7: Input
8​	Target MARGIN (not PROFIT)				10%​	E8: Input
9​	Sell Price (WRONG)				$ 1,018.33​	E9: =E6 * (1+E7) * (1+E8)
10​	How much is TAX?				$ 132.83​	E10: =E9 * E7 / (1 + E7)
11​	How much is PROFIT?				$ 80.50​	E11: =E9-E6-E10
12​	What's my MARGIN?				7.91%​	E12: =E11/E9
13​					^^ oops​

 

[Hammerjoe]

Thank you so much for the help.
I am sorry about the confusion, its my fault.

I should have said instead of tax that it was a transaction fee.

Theres a transaction fee when buying and a transaction fee when selling.
The profit margin comes in when the price per unit and transaction fee are factored in.


In this example the margin is 10%.
So calculate the $805 plus 15% transaction fee and after that apply the profit margin of 10%

 

[Hammerjoe]

Is there something wrong with my Excel? (Version 2007).

When I do =700/(1-0.15) I get the result 823.5294118 and not 805.

(700*0.15=105, 700+105=805)

 

[shg]

Joe, the formulas in post#21 are correct. If you think they are not, explain why. Otherwise, why not use them?

 

[Hammerjoe]

Joe, the formulas in post#21 are correct. If you think they are not, explain why. Otherwise, why not use them?

Because I am not geting the same results SHG.

Read my last post.
In my excel sheet if I do the math $700.00 plus 15% should give a total of $805.00 but using the formula =700/(1-0.15) it gives as result $823.5294118.
Why arent the amounts the same?

 

[shg]

Because 1+x is not the same as 1/(1-x)

If you want 10% margin on PRICE (which is where margin is measured), you need to divide cost by (1/10%), not multiply by (1+10%).

 

[shg]

you need to divide cost by (1/10%)

That should say divide cost by (1-10%)