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

 

[BadgerRoad]

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

If I understand this correctly, you bought 100 phones at a cost of $1000 plus $150 tax for a cost of $1150 per phone. Total investment is 1150 x 100 = $115,000.
Then you bought another 100 phones at a cost of $500 plus $75 tax for a cost of $575 per phone. Total investment is 575 x 100 = $57,500.
The grand total investment for 200 phones is 115,000 + 57,500 = $172,500 or $862.50 per phone.
You want to sell the phones at cost + 15% tax plus 10% profit or $862.50 x 1.10 x 1.15 = $1091.06 per phone.
Is that correct?

 

[AndrewHoffenberg]

Hi Hammerjoe,

I made a couple of assumptions:
1.The total amount you spent is the cost of the 200 iPhone plus Tax = $172,500 =A1*B1*(1+C1)+A2*B2*(1+C2)
2. The 10% profit you want to make is 10% of this entire initial purchase (all 200 iphones and tax) = $17,250 = above *.10
3. Working backwards you need to make $189,750 after taxes: Initial purchase plus the profit.
4. Factor in the 15% tax you had to pay to sell to get to Pre-Tax Profit = $223,235.29 = Above/.85
4. Divide that by 200 and you get price per iPhone
-----------------------------
Therefore you need to sell each iPhone for: $1,116.18 =(((A1*B1*(1+C1)+A2*B2*(1+C2))*0.1)+(A1*B1*(1+C1)+A2*B2*(1+C2)))/0.85/200

I may have made some assumptions you did not in terms of the 10% profit. Let me know.

-Andrew

 

[AndrewHoffenberg]

If I understand this correctly, you bought 100 phones at a cost of $1000 plus $150 tax for a cost of $1150 per phone. Total investment is 1150 x 100 = $115,000.
Then you bought another 100 phones at a cost of $500 plus $75 tax for a cost of $575 per phone. Total investment is 575 x 100 = $57,500.
The grand total investment for 200 phones is 115,000 + 57,500 = $172,500 or $862.50 per phone.
You want to sell the phones at cost + 15% tax plus 10% profit or $862.50 x 1.10 x 1.15 = $1091.06 per phone.
Is that correct?

I think you need to divide by .85 instead of multiplying by 1.15 so

$862.50 x 1.10 / .85 = 1116.18 Per phone instead of
$862.50 x 1.10 x 1.15 = $1091.06 per phone.

 

[Hammerjoe]

.

 

[Hammerjoe]

Hi Hammerjoe,

I made a couple of assumptions:
1.The total amount you spent is the cost of the 200 iPhone plus Tax = $172,500 =A1*B1*(1+C1)+A2*B2*(1+C2)
2. The 10% profit you want to make is 10% of this entire initial purchase (all 200 iphones and tax) = $17,250 = above *.10
3. Working backwards you need to make $189,750 after taxes: Initial purchase plus the profit.
4. Factor in the 15% tax you had to pay to sell to get to Pre-Tax Profit = $223,235.29 = Above/.85
4. Divide that by 200 and you get price per iPhone
-----------------------------
Therefore you need to sell each iPhone for: $1,116.18 =(((A1*B1*(1+C1)+A2*B2*(1+C2))*0.1)+(A1*B1*(1+C1)+A2*B2*(1+C2)))/0.85/200

I may have made some assumptions you did not in terms of the 10% profit. Let me know.

-Andrew

Thank you for the explanation, I am greedy so I want the 10% profit to be applied to the amount after tax and not pretax.

The formula looks good. Can the formula be changed to allow for the profit % and tax % being variable (example tax is on cell D1 and profit on E1 for example?

Again thank you so much for the help.
I thought the formula would be simpler, I guess I was wrong.

 

[AndrewHoffenberg]

Thank you for the explanation, I am greedy so I want the 10% profit to be applied to the amount after tax and not pretax.

The formula looks good. Can the formula be changed to allow for the profit % and tax % being variable (example tax is on cell D1 and profit on E1 for example?

Again thank you so much for the help.
I thought the formula would be simpler, I guess I was wrong.

Yes. I believe if you change the values in the equation from 0.1 and 0.85 to cells D1 and E1 for profit and tax respectively then it would make the equation dynamic. You can also change the 200 to (A1+A2):
(((A1*B1*(1+C1)+A2*B2*(1+C2))*D1)+(A1*B1*(1+C1)+A2*B2*(1+C2)))/E1/(A1+A2)

-Andrew

 

[BadgerRoad]

Yes. I believe if you change the values in the equation from 0.1 and 0.85 to cells D1 and E1 for profit and tax respectively then it would make the equation dynamic. You can also change the 200 to (A1+A2):
(((A1*B1*(1+C1)+A2*B2*(1+C2))*D1)+(A1*B1*(1+C1)+A2*B2*(1+C2)))/E1/(A1+A2)

-Andrew

If I understand you correctly, you want to charge tax on the sales price and profit on the sales price. If that is correct then sales price = cost/.75. Correct?

 

[Hammerjoe]

Yes. I believe if you change the values in the equation from 0.1 and 0.85 to cells D1 and E1 for profit and tax respectively then it would make the equation dynamic. You can also change the 200 to (A1+A2):
(((A1*B1*(1+C1)+A2*B2*(1+C2))*D1)+(A1*B1*(1+C1)+A2*B2*(1+C2)))/E1/(A1+A2)

-Andrew

There is a problem with this formula Andrew, it looks good and seems to be working, but if I feel generous and decide not to take profit d1=0% then the formula throws an error.
I like the formula, its a greedy one too.

 

[AndrewHoffenberg]

There is a problem with this formula Andrew, it looks good and seems to be working, but if I feel generous and decide not to take profit d1=0% then the formula throws an error.
I like the formula, its a greedy one too.

Hi,

I just tried making D1 = 0 and the equation still worked fine. Using all the other same inputs I got a value of $1014.71 per iPhone.

I do get an error when I make E1 = 0 but this is an illogical situation. The value in E1 is (1 - tax rate) or .85 in your case. A value of 0 would mean you are taxed 100% of anything you make.

-Andrew