Running Balance Sheet

Okay, there are many different ways and formats to achieve this, so I'll show two very simple methods that you can expand to your own needs.

The first way will have four columns, Date, Deposits, Withdrawals and Comments. The second will use three columns Date, Amount and Comments.  The second method will use negative values for withdrawals and positive values for deposits.  Both will show the current running total at the top..

Example 1: Two separate columns for Deposits and Withdrawals

• On the Balance Sheet, Click the cell you want to show the running total in.
• Now add the Formula: =SUM(B4:B34)-SUM(C4:C34)  Where B4:B34 are the cells to hold the Deposits, and C4:C34 are the cells to hold the Withdrawals.
• Note: You should make a lot more cells available for the formula, you may have say 200 transactions per month, so instead of B34 & C34 use say, B300 & C300 

• The above is not the cleanest way to calculate the running total, and gives no indication of how much was deposited or withdrawn..  So by simply calculating each column independently and displaying their running total and then calculating their totals will show much more information.  
• The deposits total is calculated in cell B4 using:  =SUM(B6:B34)   
• The withdrawals total is calculated in C4 using:  =-SUM(C6:C34)   Note: the minus sign before SUM, this will give the result a negative value as it is withdrawals
• Now just carry out a normal SUM on these two cells and display the result in D2.  Now we have the current amount deposited, withdrawn and total all displayed.

To try any of the above, just copy and paste the formulas to the cells, ie, copy =SUM(B6:B34) and paste it into cell B4.  I have also formatted the cells to display £ and format the date, to do this Right Click on the cell and select Format Cells from the popup menu...


Example 2: Having Deposits and Withdrawals in the same column, using negative values for Withdrawals

• Again, on the Balance Sheet, Click the cell you want to show the running total in, C2 in the screenshot below.
• Now add the Formula: =SUM(B4:B40)  Where B4:B40 are the cells to hold the Deposits, and Withdrawals.

I will build on this post later adding another column to allow the withdrawals to be grouped giving a running total for each category..

www.briancunningham.co.uk
keep learning