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

 

[Hammerjoe]

Thank you all for the help.

 

[Hammerjoe]

I was wondering if the same formula can be applied if there was a third or more shipments involved?

After making the two purchases and before selling the marchandise I decided to purchase more iphones (doesnt matter the price).

What would be the formula to calculate the price point to recover the costs + profit?

 

[shg]

	A​	B​	C​	D​	E​
1​	Lot​	Qty​	Unit Cost​	Tot Cost​
2​	1​	100​	1150​	$115,000​
3​	2​	220​	600​	$132,000​
4​	3​	115​	500​	$ 57,500​
5​	Avg Cost		$ 700​		C5: =SUM(D2:D4) / SUM(B2:B4)
6​	Tax on Sale		15%​
7​	Target Profit		10%​
8​	Unit Price		$ 933.33​		C8: =C5 / (1 - SUM(C6:C7))
9​	Check​
10​		Tax	$ 140.00​		C10: =C8*C6
11​		Cost	$ 700.00​		C11: =C5
12​		Profit	$ 93.33​		C12: =C8-C10-C11
13​		Margin	10%​		C13: =C12/C8

 

[Hammerjoe]

	A​	B​	C​	D​	E​
1​	Lot​	Qty​	Unit Cost​	Tot Cost​
2​	1​	100​	1150​	$115,000​
3​	2​	220​	600​	$132,000​
4​	3​	115​	500​	$ 57,500​
5​	Avg Cost		$ 700​		C5: =SUM(D2:D4) / SUM(B2:B4)
6​	Tax on Sale		15%​
7​	Target Profit		10%​
8​	Unit Price		$ 933.33​		C8: =C5 / (1 - SUM(C6:C7))
9​	Check​
10​		Tax	$ 140.00​		C10: =C8*C6
11​		Cost	$ 700.00​		C11: =C5
12​		Profit	$ 93.33​		C12: =C8-C10-C11
13​		Margin	10%​		C13: =C12/C8

<tbody>
</tbody>

Thank you for the table, very easy to follow.
Theres only one problem I think with it.

When I finally decide to sell the whole lot I have to pay tax on the total amount too.
So I want to have that 15% sale tax added to the total and then apply the 10% profit to it to get the price to sell.

 

[shg]

That's exactly how it is calculated and shown.

 

[Hammerjoe]

That's exactly how it is calculated and shown.

Not quite, in my story, I still have to pay the 15% tax on that $933.

That 933 covers the tax and profit on the items bought.
I have to pay tax as well to sell.
You table is only correct if the $1150 and $600 and $500 already include the 15% tax.

 

[shg]

Right. That's the $140 shown in C10.

You table is only correct if the $1150 and $600 and $500 already include the 15% tax.

It does. It doesn't matter how the money you paid gets distributed (to the manufacturer, to the distributor, to the retailer, to the Government), it's what you paid.

 

[Hammerjoe]

Right. That's the $140 shown in C10.


It does. It doesn't matter how the money you paid gets distributed (to the manufacturer, to the distributor, to the retailer, to the Government), it's what you paid.

I really appreciate your help but I am not getting the same result:

Lot	Qty	Unit Cost	Tax	Total cost
1	100	$1,150.00	$17,250.00	$132,250.00
2	220	$600.00	$19,800.00	$151,800.00
3	115	$500.00	$8,625.00	$66,125.00
Total cost with tax			$350,175.00	SUM(E2:E4)
Unit price			$805.00	E5/SUM(B2:B4)
Tax on the sale		15%	$120.75	E6*0.15
Sale price			$925.75	E6+E7
Target Profit		10%	$92.58	E8*0.1
Unit price			$1,018.33	E8+E9

<tbody>
</tbody>

 

[shg]

	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,073.33​	E9: =E6 / (1 - E7 - E8)
10​
11​	How much is TAX?				$ 161.00​	E11: =E7*E9
12​	How much is PROFIT?				$ 107.33​	E12: =E9-E6-E11
13​	What's my MARGIN?				10.00%​	E13: =E12/E9

 

[Hammerjoe]

	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,073.33​	E9: =E6 / (1 - E7 - E8)
10​
11​	How much is TAX?				$ 161.00​	E11: =E7*E9
12​	How much is PROFIT?				$ 107.33​	E12: =E9-E6-E11
13​	What's my MARGIN?				10.00%​	E13: =E12/E9

<tbody>
</tbody>

Shg, the numbers look good but they dont match my manual math for F9 and I dont know why.
So price per unit is $805.00.

Have to pay 15% tax to sell them, so thats 805*0.15=$120.75
So total with tax $805+$120.75=$925.75

Now that I have the cost figured out I want to get 10% profit with the sale so 10% of $925.75 is $92.575 which makes a total of $1018.32.

So why does the formula =E6/(1-E7-E8) give a total of $1073.33???