excel running balance sheet formulas?
I know the numbers I need in each cell.. but cant figure out the formulas that need to go in the second column.
First column, we put in the numbers manually. But second column is where the calculations come in.
Row ------ Column A ----- Column B
1 ------- 2444.44 ----- =a1
2 ------- 4888.88 ----- 2444.44 (this is 2444.44 because we already paid the person 2444.44 in B1.. they ended up earning more 4888.88, so B2 needs to be the difference of what is owed
3 ------ 1000.00 ------ (3888.88) in B3 shows since 4888.88 has been paid out already.. but the new total they earned is only $1000.. so B3 indicates how much they need to pay back
4 ----- 2666.68 ----- (1222.20) in B4 since they had a negative balance the previous month B3.. but now they earned more (a4)... b4 indicates they owe less back.
5 ----- 2000.00 ------ 777.80
6 ----- 500.00 ----- (277.80)
This is a complicated mess in my opinion. But it is part of a complicated commission calculation that works on a percentage made each month.. but also that percentage is then used to calculated how much should be given out over the course of the year.
Column A shows the commission amount earned that month and needs to be compaired with what was already paid out. I think the system if flawed.. but non the less, this is how they want it recorded. I need a formula for column B that makes the numbers I put in work out each time, regardless if column A is more or less than the previous month.
we might need a 3rd column.
Right now.. month one = 2444.44 was paid out.
month two = earned a total of 4888.88 so far for the year... already 2444.44 paid out during month one... now now an additional 2444.44 needs to be paid out.
month three = earned a total of 1000.00 so far for the year.. already received 4888.88.. so now they need to pay back 3888.88
month four = earned 2666.68 for the year so far total.. owes back 3888.88 becuae of previous month... but now becaue they earned more.. they only owe back 1222.20
month five = earned 2000 for the year... we owe them 777.80 becuase the previous month they owed us 1222.20