How to calculate running balance, monthly cashflow/expenses in Excel.

Image by Pexels from Pixabay

Most of the financial transaction records like daily sales, daily expenses/purchases and banking records are kept in Microsoft Excel. In some cases, especially project-based expenses, the expenses are drawn down from a budgeted  fixed amount.  Recording transactions is the easy part in Excel but getting the much needed information like the running total, monthly expense or sales report can be bit tedious for those who have basic Excel skills. But with a bit of planning, and simple few formulas, these tasks can be automated by using Excel Table and Pivot Table features.

Calculating running balance in Excel

Calculating running balance in Excel can be handy in several situations as follows;

Drawing down from a budgeted amount. For example, you are in given a certain amount to work with and you need to keep an eye on the running balance.

Record of payments so that the record can be used to reconcile bank statements

Keep a tally of inventory use. For example, dispersing a certain item from the inventory.

Steps:

1: Recreate a Excel worksheet as followings

Sample Sales record

2: The trick is to make the Amount column in the first transaction a cell range (eg, C2:C2) and make column Absolute in the Sum function. =SUM($C$2:C2)

3: Populate the formula by either dragging or double-click on the Auto Fill (+) sign on the bottom right of the active/formula cell.

Calculating monthly expense/revenue

Calculating monthly Expense/Revenue requires a simple three(3) column  Excel worksheet. Date, transaction details and amount.

Steps

1: Create the worksheet with the three columns as shown in the image below.

Sample of a daily sales record worksheet

2: Click on any cell inside the data set and Click Insert Table Click Table. This will display the Create Table dialog box. Check the My Table has Headers check box and click OK. This will activate the Design Table contextual tab on the Ribbon.

3: Under Properties, give the table a decent name, eg SalesRevenue. Uncheck the Filter Option under the Table Style Options group.

Note: Converting the worksheet data set into a table is important to pivot the Data.

4: Click on any cell within the data set and click Insert==>Click Pivot Table. This will display the Create Pivot Table dialog box. Leave all fields as default for now and click OK. Excel will add new Worksheet to display the pivot table. The Pivot Table fields will be displayed on the Field List pane on the right of the window.

5: It has four (4) boxes, FILTERS, ROWS, COLUMNS and Values. Drag the Date into the Rows box. Then drag Transaction details into Rows again. Then drag Amount into the Values column. You will notice that transactions will be group under the Month, followed by the Date.

Note: When a Date Filed is dragged into the Rows box, Excel automatically put the Months, Date, Year. If the transactions are only for a single year, Year will not be displayed. Same goes with months or days.

PivotTable Field list

6: Remove the Year/Date from the Row box by click it and dragging it outside of the box. The pivot table will show the transactions grouped under each month. You can collapse or expand by clicking the plus (+) sign on the pivot table.

7: Click on any cell in the amount column in the Pivot table and right-mouse click. From the shortcut menu, click Number Format. This will show the Format Cells dialog box. Select Accounting or Currency and click OK.

8: Rename the column headings to Month, and SalesRevenue accordingly.

Monthly sales report pivot table.

Updating Data

Once you have created the Pivot Table, don’t have to worry about creating it again. All you have to do is continuing recording your transactions. Anytime you want to print the updated Monthly Sales pivot table, click Data Tab==>Click Refresh ALL

Alternative keyboard short cut is to press Ctrl +Alt+F5

Note. Pivot table does not automatically update when new data is added.. So, pressing the Refresh All is required to update it.

Filter the Pivot by using Filters.

Sometimes the transaction records can be long. For example, you have a record of all 12 months (January to December)  and you need just one months transactions. To filter the pivot table, you need  to use one of the fields as Slicer (filter).

Steps

1: Go the PivotTable worksheet. On PivotTable Fields pane, Right-Mouse click Month under Date Field and click Add As Slicer.  This will add a Slicer/Filter Table of months. Click month you want and Pivot table will be filtered to that month.

The Slicer  table has the option to Multi-Select  (select more than one month), and Clear Filter button to remove the filter at the top.  Default is single select.

Note: If you cannot see the PivotTable Fields Pane for some reason. Click on any cell in the PivotTable. Click the PivotTable Analyze Tab and click Field List.

Hide the Slicer when Printing

When printing, it’s not good to have the Slicer table included in the print. So, Click View tab==>Click Page Break. Anything in the white area will be printed. Anything in the grey area will not be printed.

So, drag the Blue line inside and allow the Slicer to stay outside of the print area.

Month Slicer Table is outside the wide area so it will not be printed.

That’s allow you have a monthly transactions report.