Results 1 to 8 of 8
Thread: Various

20090524, 22:16 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Scenario: I have a worksheet that derives, in cell C12, "Revenue Before Return and Taxes". Based on and from this number, I need three (3) formulae to compute (1) in cell C14, a "Return" which, for these purposes, is equal to "x", (2) in cell C16, a Federal Income Tax provision which is, for these purposes, equal to "x*(.35/(1.35))", and (3) in cell C18, a State Income Tax provision which is, for these purposes, equal to the sum of (a) the Return, plus ( the Federal Income Tax provision, multiplied by 6%, or "(x+(x*(.35/(1.35))))*6%". All three of the results in cells C14, C16, and C18 must equal the total in cell C12, "Revenue Before Return and Taxes". What I need are formulas to come up with the different pieces, and they must be based on the "Revenue Before Return and Taxes" figure in cell C12. Any ideas?
Thanks in advance.
Jeff

20090525, 01:11 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
The numbers in C14, C16 and C18 will be different from each other, so how could all three of them equal C12?
If you mean that the SUM of C14, C16 and C18 must equal C12, use the following formula in C14:
=C12/(1+0.35/(10.35))/(1+6%)
You can then use the formulas you mentioned for C16 and C18, with C14 instead of x (and beginning with = of course)

20090525, 01:20 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='776791' date='25May2009 01:11']The numbers in C14, C16 and C18 will be different from each other, so how could all three of them equal C12?
If you mean that the SUM of C14, C16 and C18 must equal C12, use the following formula in C14:
=C12/(1+0.35/(10.35))/(1+6%)
You can then use the formulas you mentioned for C16 and C18, with C14 instead of x (and beginning with = of course)[/quote]
Sorry, Hans. I meant to say the SUM of the three cells, C14, C16, and C18 equal C12. How would I phrase C15 and C18?

20090525, 01:37 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Take the formulas that you mentioned in the first post in this topic, replace x with C14 and stick an = in front of it.

20090525, 09:39 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='776796' date='25May2009 01:37']Take the formulas that you mentioned in the first post in this topic, replace x with C14 and stick an = in front of it.[/quote]
Thanks Hans.

20090526, 07:27 #6
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='776796' date='25May2009 01:37']Take the formulas that you mentioned in the first post in this topic, replace x with C14 and stick an = in front of it.[/quote]
Hans (or anyone),
I have been dealt a new twist: For federal and state income tax purposes, one is allowed to deduct interest paid. If I know the amount of interest paid, and it is a figure found at, say, B1 (and it is not part of the computation of "Revenue Before Income and Taxes"), how would this be handled in the equations? My original equations would be restated as follows:
1. Return = x
2. Federal Income Tax Provision = (x  B1) * (.35 / (1  .35))
and
3. State Income Tax Provision = (x + ((x  B1) * (.35 / (1  .35)))) * .06
Any ideas?
Thanks in advance.
Jeff

20090526, 07:47 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
In C14:
=(C12/(1+6%)+B1*0.35/(10.35))/(1+0.35/(10.35))
See attached workbook.

20090526, 07:56 #8
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='776990' date='26May2009 07:47']In C14:
=(C12/(1+6%)+B1*0.35/(10.35))/(1+0.35/(10.35))
See attached workbook.[/quote]
Thanks again.