Help, pls, with reverse formula

[Uncle Vinnie]

Hi... I need a little help in reversing a formula, as follows:

Column A is Gross, B is Fica, C is Medicare, D is Fed, E is State, F is NET.

Columns B thru F are individual percentages of A (the Gross) - F is the Net,
which takes the total sum of the percent columns, adding B thru E, and
deducting them from A (Gross), leaving the net balance.

At times, I may know the Net number and want to simply key in that number,
and have Excel fill in all the others.

What I have been doing in the meantime is continually putting in Gross
numbers until I hit the net I want...

Is there an easier way with better formulas?

Thank you!

 

[Bernard Liengme]

You say "sometimes": so "sometimes" you know Gross and need Net and other
times known Net and need Gross.
Suppose A2 has gross as number, B2:E2 have formulas such as =A2*10%, and F2
has =A2-SUM(B2:E2)
In place of just experimenting with A2 to get a know Net, use Goal Seek to
get the know F2 by varying A2
Does this address the question?
happy new year

 

[KC Rippstein]

Hi, Vinnie. I am not an accountant and don't know how withholding works,
but my understanding is that withholding changes based on how much you
actually make for the calendar year. If that's true, I believe you need a
column or worksheet that keeps a running total of that person's pay so that
when they cross an appropriate threshold, they are bumped to another tax
bracket.

I would personally set up a tax table on Sheet2 that has all the breakpoints
for different taxation levels (if I recall, FICA, Medicare, Federal, and
State all have separate thresholds independent of each other that dictate
how much to withhold and when to start and stop withholding for that
particular tax). Once you set up your table with 2006 data, you'll easily
be able to update it with 2007 and future years' changes.

Then I'd go back to Sheet1 and set up 3 data entry columns: column A for
name or ID, column B for the Wage Entry, column C for the word "Net" or
"Gross". Set up the worksheet data and formulas as a list (give the list a
name) and start putting in your formulas. Finally, I'd set up yet another
worksheet to track each person's YTD gross pay (you could also use it to
track your totals for the other columns to make it easier to complete your
W2s in January).

Does that help?

 

[Uncle Vinnie]

Hi.. I have no need to keep track of withholding, I use QuickBooks for
that...and of course, my accountant. I don't want to mess up here..


All I am actually trying to do is: I know the Net I want to take and I want
to enter that Net and have the spreadsheet fill in the other numbers,
working it's way up to the Gross.

In other words, the reverse of what I have been doing....
Thanks!

 

[Uncle Vinnie]

Goal Seek! Never touched this command in all they years I've used Excel!

And, that is my answer.. it worked... I plugged in the Net I wanted, chose
the Gross cell as the target, and it came out, filling in the 4 in the
middle!

Thanks! Much faster than working a whole slew of formulas, thanks!

 

[Dana DeLouis]

...I may know the Net number

Hi. If b,c,d & e represent different percentages of Gross, then I believe
the equation is:

Gross = Net/(1 - b - c - d - e)

 

[Harlan Grove]

Dana DeLouis wrote...
....

Hi. If b,c,d & e represent different percentages of Gross, then I believe
the equation is:

Gross = Net/(1 - b - c - d - e)

....

Fine if b..e are *constant* percentages of the gross, but trickier when
they vary. Simple example: b is something like

=IF(a<25000,100+0.02*a,IF(a<50000,600+0.04*(a-25000),1600+0.06*(a-50000)))/a

which simplifies to

=IF(a<25000,100+0.02*a,IF(a<50000,-400+0.04*a,-1400+0.06*a))/a

Then with a = Gross and f = Net,

f = a * (1 - b - c - d - e)

f = a * (1 -
IF(a<25000,100+0.02*a,IF(a<50000,-400+0.04*a,-1400+0.06*a))/a - b - c -
d - e)

f = IF(a<25000,a * (1 - 0.02 - c - d - e) - 100, IF(a<50000, a * (1 -
0.04 - c - d - e) + 400,
a * (1 - 0.06 - c - d - e) + 1400))

a = IF(f<25000*(1-0.02-c-d-e)-100,(f+100)/(1-0.02-c-d-e),
IF(f<50000*(1-0.04-c-d-e)+400,(f-400)/(1-0.04-c-d-e),
(f-1400)/(1-0.06-c-d-e)))

It gets much messier when all the percentages vary with Gross (a).

 

[Uncle Vinnie]

Now my head is spinning.. Goal Seek worked, by the way- but:

The percents in each column are always the same- they never change.
Example, FICA is always 6.2% of the gross...Medicare is always 1.45%, and so
forth....

My business is very seasonal, a real roller coaster ride, so I cannot take a
regular weekly salary... I might need $200 for the week, so I have to work
backwards, plugging in all sorts of numbers in the gross box until I 'hit'
$200.

The formula Dana suggests make sense, yet I must be entering it wrong, Excel
doesn't seem to like it.. is it exact, except that it should be further
defined (F21?)

A3 = F3/(1 - b3 - c3 - d3 - e3)

???

 

[Dana DeLouis]

FICA is always 6.2% of the gross...etc

Hi. If the percentages are just fixed amounts, then I believe the equation
just works out to be something like:

=200/(1-6.2%-1.45%-3%-4%)

Gross = $234.33

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003

Now my head is spinning.. Goal Seek worked, by the way- but:

The percents in each column are always the same- they never change.
Example, FICA is always 6.2% of the gross...Medicare is always 1.45%, and
so forth....

My business is very seasonal, a real roller coaster ride, so I cannot take
a regular weekly salary... I might need $200 for the week, so I have to
work backwards, plugging in all sorts of numbers in the gross box until I
'hit' $200.

The formula Dana suggests make sense, yet I must be entering it wrong,
Excel doesn't seem to like it.. is it exact, except that it should be
further defined (F21?)

A3 = F3/(1 - b3 - c3 - d3 - e3)

???

<sni>

 

[Uncle Vinnie]

Got it... thanks!

works great!