Money Balance sheet

[MarkDShark]

I need to create a working table that will deduct payments from a cash balance as i enter the payment amount in any cell even if it's out of order. If i made a payment for "car", i want the balance to show in the last cell and if i make a payment for "house", then i want the balance to show there as well after deducting the payment and so on and so on. I want it to be able to calculate passed the cash balance to a negative value which would tell me which field caused the neg entry which would mean the funds weren't there to cover the payment amount and would should in red the amount still owed. Below is the example box,

<TABLE style="WIDTH: 245pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=326 border=0 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; WIDTH: 74pt; BORDER-BOTTOM: #40ff40; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=99 height=17></TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; WIDTH: 48pt; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; WIDTH: 58pt; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent" width=77 x:str="Cash Bal: ">Cash Bal: </TD><TD class=xl23 style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; WIDTH: 65pt; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent" width=86 x:num="2000">$2,000.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent"></TD><TD class=xl23 style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Bill</TD><TD class=xl25 style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent">Sequence</TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #40ff40; BORDER-TOP: #40ff40; BORDER-LEFT: #40ff40; BORDER-BOTTOM: #40ff40; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>House</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

1​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="500">$1,500.00</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1500">$500.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Car</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

2​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="200">$200.00</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1300">$300.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Credit Card 1</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

4​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$350.00</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$150.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Credit Card 2</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

3​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="300">$100.00</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1000">$200.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Credit Card 3</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>

5​

</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

The payments are out of order based on which would have to be paid first based on a due date. In red is the negative balance for money short on a bill and where it lays on payment 4. The squence is for visual purposes to show you what i was talking about being able to calculate the balance no matter which entry i enter 1st or 4th. It would be based on any order i enter.

 

[MarkDShark]

I keep getting a circular error. The formula in wsjackmans will change correct? What should it be? I've tried different equasions but i keep getting the circular error. Thanks for your help by the way.

 

[pedro-egoli]

This is a copy of my spreadsheet to track various expenses.
It has a budget and as each entry is made the funds available from budget reduce.

Negative figures are for outgos and positive are for inflows.

The various formulae was obtained with assistance from this forum

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	BILLS ACCOUNT TO 30-06-10	*	*	*	NB ENTER EXPENSES AS A NEGATIVE	*	*	*	A POSITIVE EQUALS CREDIT BALANCE	*	*	*	*	*	*	*	*	*	*	*	BETTY'S	*	*	*	*	*
2	*	*	*	*	*	MBF	ENERGY	RATES	CAR REGO	CAR INSCE	BOAT REGO	BOATINSC	van	HOUSE IN	PHONE	FEES	M/V REP	PLAN GIV	SUNDRY	*	MONEY	*	*	*	*	*
3	*	*	*	BUDGET	*	2628	1800	2800	1100	950	60	300	650	1200	5000	1000	6000	840	*	*	*	*	24328	*	*	BALANCE
4	RUNNING BALANCE	*	$15,067.12	AVAILABLE	*	2628	947	1255	586	1064	60	300	450	46	2815	504	1325	490	-109	0	3030	0	48328	DEBIT	CREDIT	*
5	1/07/2009	ACCOUNT BALANCE BANK 30/6//09	*	*	*	0	-27	0	-514	114	0	*	-42	0	146	-165	-697	*	294	0	868	0	*	-$1,446.52	$1,424.21	-$22.31
6	1/07/2009	TRANSFER FROM ALLOC PENS DRAW17/6/09	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	24000	*	$24,000.00	$23,977.69
7	7/07/2009	Telstra (approx bill	*	*	*	*	*	*	*	*	*	*	*	*	-401.87	*	*	*	*	*	*	*	*	-$401.87	*	$23,575.82
8	1/07/2009	centrelink 2 at	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	34.7	*	*	*	*	*	$34.70	$23,610.52
9	6/07/2009	visa carpet betty	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	-219	*	*	-$219.00	*	$23,391.52
10	22/07/2009	betty shoes broome	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	-150	*	*	-$150.00	*	$23,241.52
11	22/07/2009	cvan fridge broome	*	*	*	*	*	*	*	*	*	*	*	*	*	*	-491.5	*	*	*	*	*	*	-$491.50	*	$22,750.02
12	24/07/2009	broome toyota	*	*	*	*	*	*	*	*	*	*	*	*	*	*	-212.99	*	*	*	*	*	*	-$212.99	*	$22,537.03
13	7/08/2009	*	ato refund	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	1870.28	*	*	*	$1,870.28	$24,407.31
BILLS ACCOUNT TO 30-06-10

Balance in C4 is current balance rather than that on above sheet.
Reference is also made to previous worksheet to transfer balance from previous year.

Sorry it is so big but it might help.

Pedro

 

[scottylad2]

Excel Workbook
	C	D	E
24			Remaining Ballance
25			2,872.00
26
27	Outgoings	cash ballance	Starting Amount
28			5000
29	House	1,500.00	3,500.00
30	Car
31		100.00	3,400.00
32	Card 1	140.00	3,260.00
33		22.00	3,238.00
34	Card 2
35	more bills#	366.00	2,872.00
Sheet1

Excel Workbook
	E
25	2,872.00
Sheet1

To display a running total, you can do this or have a single cell display the min of the subtraction if that column only ever has money coming out of it, it will always show you the smallest number thats remaining after your bills start coming out

 

[scottylad2]

formula in E29 and dragged down

 

[MarkDShark]

Did you get your chart to calucalte as shown in your response? I tried to recreate your sheet, but it does not work. What do you mean by drag down the formula? For each celll, i do have to refence the next cell right? Example,

=IF(D29="","",E$28-SUM(D$29:D29))
=IF(D30="","",E$28-SUM(D$30:D30)) and so on..

Doing this takes the same balance of $5000 and just subtracts out the payment and leaves that total but doesn't take into account the other payments. I must be looking at your formula wrong.

Let me know what i'm doing wrong.

 

[MarkDShark]

I do not see the formulas you used. I read at the bottom of your post to click on hyperlinks to see the formulas, but there are no hyperlinks. What is the copy formula bottom for?

 

[MarkDShark]

What i need it to do is calculcate no matter which filed i enter first. Say i have to pay "Car" first based on its due date, then i have to pay "Card2" based on its due date. Now i want to pay "House". I need the sheet to be able to jump out of order, but yet keep track of the starting balance and account for what has already been accounted for and subtracted out of the balance. I noticed that the totals are calculating down from large to small which means its calculating in an order. I want it to be able to calculate in any order where i could have car would show 4500, card2 would show something like 4450 and back up to house would show something like 3250. There would be no set order and the payments would still be accounted for however i enter them in no particular order but which would need to be paid first by its due date.

 

[crimson_b1ade]

see my post# 8 and post# 9 (they go together). Your spreadsheet must use the same columns and formulas as outlined in post# 8 in order for the vba code provided in post# 9 to work.

 

[MarkDShark]

the link in post 8, is a dead link. I get a 404 error page no longer exists.

 

[crimson_b1ade]

There's no link to go anywhere in post# 8. So I'm not sure what you are referring to. Post# 8 is a screenshot of how your data needs to be setup. In addition, there is a formula in column D2 that you need to use in your spreadsheet.