Calculating Gross Margin from numbers that include sales tax

[jangell]

In the attached spreadsheet I need to get to a gross margin percentage in column H that takes into account that the contract value includes 10% sales tax (would like to do this without adding a column that is the contract value without the sales tax.)

I got as far as a formula that produces the value without the tax but now need to subtract the COGS number and divide it by the contract value without sales tax in it. For this sheet tax rate is 10%

Here is my sales tax free contract value formula...Just cant get tot eh gross margin number.

Any help would be appreciated.

=sum(F15)-sum(F15-(F15/(1+10%)))

Here is the sheet
Dropbox - Workbook3.xlsx

 

[jangell]

anyone have any thoughts on this?

 

[Dave Patton]

You may have received more responses if you provided a concise example.
Many people do not open external files which may endanger their systems.

Excel 2010
	A	B	C
1
2	Contract	60000	66000
3	COGS	50000
4	Margin	10000
5	Percentage	17%	17%
6
2a

Cell Formulas
Range	Formula
C2	=B2*1.1
C5	=(C2/(1+0.1)-B3)/(C2/(1+0.1))
B4	=B2-B3
B5	=B4/B2

 

[jangell]

SORRY - Duplicate post

 

[jangell]

Dave Patton, thanks for the advice.

Here is an example of what I am trying to do with G2 being the cell I need the help with.

Again, In the below spreadsheet I need to get to a gross margin percentage in column H that takes into account that the contract amount (column F) includes 10% sales tax (would like to do this without adding a column that is the contract value without the sales tax.)

So - Its F - the sales tax amount that is included in F and then subtract G and then divide that by F.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name[/TD]
[TD]Address[/TD]
[TD]Zip[/TD]
[TD]Date[/TD]
[TD]Active[/TD]
[TD]Contract amount[/TD]
[TD]COGS[/TD]
[TD]GM[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]12345[/TD]
[TD]1/1/01[/TD]
[TD]Y[/TD]
[TD]$60,391[/TD]
[TD]$47,119[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any help!

 

[Dave Patton]

Did you try the suggestion?

 

[jangell]

I reviewed it Dave but was hoping for a suggestion that did not add additional rows or columns - Just a formula that handled the tax calc and the gross margin calc.

 

[jangell]

Did you try the suggestion?

My mistake - It works great!

Thanks!

 

[Dave Patton]

Thanks for the feedback.