R
ryguy7272
I just can’t seem to get my arms around this. Here’s the scenario.
For a bunch of consultants, I’m calculating State Unemployment Tax (on a
weekly basis, which I can call a period basis). The calculation is as such
=IF(H5="NY",MIN(554.62,(L5*0.06525)),IF(H5="NJ",MIN(274.73,(L5*0.00925)),IF(H5="CT",MIN(285,(L5*0.019)),0)))
This is then filled down.
I’m also calculating Federal Unemployment Tax (on a period basis). This
calculation is as such:
=MIN(56,(0.008*AF5))
This is then filled down.
.. . . And now for the oranges
For consultants, I’m doing a running sum, as such:
=SUMIF($G$5:G5,G5,$L$5:L5)
This is then filled down. I need to do this to get the total paid to a
consultant to see if the amount exceeds $106,800 which is the cap for Social
Security Tax. If I did the per-period calculation for the SS Tax, I’d NEVER
hit the $106,800 threshold.
Medicare is calculated on a period basis, as such:
=MIN(U5*0.062,6621.6)
This is then filled down.
Now as I’m subtracting these taxes from the Gross Income, per consultant, as
such:
=(N5-S5-T5-V5-W5-L5)*D5
This is then filled down.
Each consultant may work for a few different firms per year and they may
work for several months, and then not work for a couple weeks, and then work
for several more months. This is why the consulting firm wants to see it’s
profit per consultant, per period. As I’m trying to find the consulting
firm’s profit per consultant, it’s running into a major problem because the
running sum is constantly increasing (to get to that $106,800 number) but the
other calculations are all per period (basically one row)!! I must be
looking at this the wrong way; shouldn’t be so hard. I inherited this
spreadsheet from someone who doesn’t know much about Excel and now I’m trying
to straighten out there problems.
Can someone here think of a better way to do what I need to do?
Thanks!
Ryan---
For a bunch of consultants, I’m calculating State Unemployment Tax (on a
weekly basis, which I can call a period basis). The calculation is as such
=IF(H5="NY",MIN(554.62,(L5*0.06525)),IF(H5="NJ",MIN(274.73,(L5*0.00925)),IF(H5="CT",MIN(285,(L5*0.019)),0)))
This is then filled down.
I’m also calculating Federal Unemployment Tax (on a period basis). This
calculation is as such:
=MIN(56,(0.008*AF5))
This is then filled down.
.. . . And now for the oranges
For consultants, I’m doing a running sum, as such:
=SUMIF($G$5:G5,G5,$L$5:L5)
This is then filled down. I need to do this to get the total paid to a
consultant to see if the amount exceeds $106,800 which is the cap for Social
Security Tax. If I did the per-period calculation for the SS Tax, I’d NEVER
hit the $106,800 threshold.
Medicare is calculated on a period basis, as such:
=MIN(U5*0.062,6621.6)
This is then filled down.
Now as I’m subtracting these taxes from the Gross Income, per consultant, as
such:
=(N5-S5-T5-V5-W5-L5)*D5
This is then filled down.
Each consultant may work for a few different firms per year and they may
work for several months, and then not work for a couple weeks, and then work
for several more months. This is why the consulting firm wants to see it’s
profit per consultant, per period. As I’m trying to find the consulting
firm’s profit per consultant, it’s running into a major problem because the
running sum is constantly increasing (to get to that $106,800 number) but the
other calculations are all per period (basically one row)!! I must be
looking at this the wrong way; shouldn’t be so hard. I inherited this
spreadsheet from someone who doesn’t know much about Excel and now I’m trying
to straighten out there problems.
Can someone here think of a better way to do what I need to do?
Thanks!
Ryan---