Formula, Pivot Table, ??? Beats me

[Scott A]

Hello,

I am trying to set up this table below so that it does some
autocalculations. What I am looking to do is in column N I would like it to
auto calculate whether an employee owes the company each month and when they
do owe the company when it is repayed back to the company.

How this scenario works is the sales employees base salary is devided up
into monthly draws (column L), they need to sell enough product to pay for
their salary though commisions, their commisions for all of their sales goes
into column M for each month. This is where it gets a little tricky and
needs to be calculated monthly. If the sales person has a commision that is
over the amount of their draw (L) then they do not owe the company any money
(because they brought in enough business to pay for their salary) and they
get the total amount of their commision. Now it gets really tricky, if they
do not sell enough to cover their salary in actual commisions, they owe the
company the difference detween the two. Example: during month 3 the sales
persons salary is $2500.00 (L8) and he only sold $2427.58 (M8) he then owes
the company $72.43 (N8) so he still gets his full salary (P8) but no
additioanl money or commision. The next month the sales person again did not
sell enough to cover his salary and owes the company and additional $1867.80
but now he owes a total of $1940.23 (which it should show as a total in the
TOTALS at the bottom (N18). The next month the sales person has a great
month and sells $18,317.19 thereby covering his salary cost but I need the
formula to see if there is any balance owed from previous bad months and if
there is ($1940.23 balanced owed) then deduct that amount from the commision
and show it as a negative number to balance out what was owed. If the sales
person ends up with commision of only $500.00 over his salary then the amount
paid back to the company would only be the $500.00 and he would still get his
base salary but would have repaid back $500.00 of what was owed.

K L M N O
P
5 Time Draw Initial Owed to Comm Total
Period Amount Commission Company Paid Earnings
6 Month 1 $2,500.00 $2,534.63 $0.00 $34.63 $2,534.63
7 Month 2 $2,500.00 $7,349.60 $0.00 $4,849.60 $2,500.00
8 Month 3 $2,500.00 $2,427.58 $72.43 $0.00 $2,500.00
9 Month 4 $2,500.00 $632.20 $1867.80 $0.00 $2,500.00
10 Month 5 $2,500.00 $18,317.90 $0.00 $15,817.90 $18,317.90
11Month 6 $2,500.00 $6,849.85 $0.00 $4,349.85 $6,849.85
12 Month 7 $2,500.00 $1,934.93 $565.08 $0.00 $2,500.00
13 Month 8 $2,500.00 $4,928.88 $0.00 $2,428.88 $4,928.88
14 Month 9 $2,500.00 $20,207.93 $0.00 $17,707.93 $20,207.93
15 Month 10 $2,500.00 $3,253.30 $0.00 $753.30 $3,253.30
16 Month 11 $2,500.00 $1,436.50 $1063.50 $0.00 $2,500.00
17 Month 12 $2,500.00 $3,132.98 $0.00 $632.98 $3,132.98
18 Totals $30,000.00 $73,006.25 $0.00 $46,575.05 $71,725.45

I have spent over 6 hours on this and am still no closer to figuring this
out. I have the actual spreadsheet that I can email to someone if they need
it. Just let me know if you do at (e-mail address removed).

Thank you so much for any help you can provide,

Scott A
This is the most amazing group of people! Always willing to help laymen like
myself. LOL

 

[Paul C]

if you add a column for repaid this may be a little easier

K L M N O P Q

5 Period Draw Ini Com Owed Repaid Comm P TE
6 Month 1 $2,500 $2,535 $0 $0 $35 $2,535
7 Month 2 $2,500 $7,350 $0 $0 $4,850 $7,350
8 Month 3 $2,500 $2,428 $72 $0 $0 $2,500
9 Month 4 $2,500 $632 $1,868 $0 $0 $2,500
10 Month 5 $2,500 $18,318 $0 $1,940 $13,878 $16,378
11 Month 6 $2,500 $6,850 $0 $0 $4,350 $6,850
12 Month 7 $2,500 $1,935 $565 $0 $0 $2,500
13 Month 8 $2,500 $4,929 $0 $565 $1,864 $4,364
14 Month 9 $2,500 $20,208 $0 $0 $17,708 $20,208
15 Month 10 $2,500 $3,253 $0 $0 $753 $3,253
16 Month 11 $2,500 $1,437 $1,064 $0 $0 $2,500
17 Month 12 $2,500 $3,133 $0 $633 $0 $2,500
18 Totals $30,000 $73,006 $3,569 $3,138 $43,437 $73,437

O6 Formula =MAX(M6-L6-P6,0) and copy down
P6 Formula =IF(N6=0,M6-L6,0)
p7 Formula =IF(N7=0,MAX(M7-L7-(SUM(N$6:N6)-SUM(O$6:O6)),0),0) and copy down

You could then subtract O18 from N18 to see if there is an end of year
balance owed (as there is in your example)

 

[Scott A]

Once again Paul, you are the man!
Thanks,
Scott A