# Determine opening and closing balance

• ### Question

• Hi,

I am preparing a monthly cash flow statement and for doing so, I need to determine the monthly opening and closing cash balance.  To simplify, this is what I did.

1. Dragged months to the columns labels
2. Created a slicer for selecting the Financial Year.  In this slicer, I chose 2015-16 i.e. April 1, 2015 to March 31, 2016.

Since I have data only for two months of this Financial Year i.e. April and May, only these two months show up in the column labels.

To determine the monthly opening cash balance, I added the following measure

`=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),PREVIOUSMONTH(calendar[Date]))`

To determine the monthly closing cash balance, I added the following measure

`=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),calendar[Date])`

Much to my surprise, I got the result as seen in the image below.  Cells B5, C6 and D5 are blank.  On going through my Bank Book, I realised that

1. There is no figure in cell B5 (Opening balance of April) because there was no transaction on the last day of March i.e. March 31.  The last transaction was on March 28

2. There is no figure in cell C6 (Closing balance of May) because there was no transaction on the last day of May i.e. May 31.  The last transaction was on May 30

3. There is no figure in cell D5 (Opening balance of June) - same reason as mentioned in point 2 above.

As seen in the image, I have also computed the monthly "Last date of previous month" and "last date of current month" but do not know how to make use of them in computing the opening and closing Cash balances.

Please also note that there can be multiple transactions on the last day of any month.  For e.g., let's say the last day of transaction in May 2015 was May 30 (not may 31) but there were multiple transactions on this last day (both inflow and outflow).  So I cannot simply determine the last day of transaction and take MAX/MIN/SUM that day.  I have to take the final balance on that day.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Wednesday, June 3, 2015 12:22 AM

• Hi,

Thank you once again for your solution.  I adopted a different approach

1. I inserted a Serial Number column in the Bank Book
2. Here is the formula I wrote for determining the Closing Bank balance

`=CALCULATE(SUM(bank_book[Balance]),FILTER(bank_book,bank_book[S. No.]=MAX(bank_book[S. No.])))`

3. Here is the formula I wrote for determining the Opening balance

`=[Closing Bank balance - Excel Enthusiasts]-[Total deposits]+[Total payments]`
Thank you once again for helping me.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

• Proposed as answer by Wednesday, June 10, 2015 11:17 PM
• Marked as answer by Wednesday, June 17, 2015 2:33 AM
Sunday, June 7, 2015 3:08 AM

### All replies

• Hi Ashish,

try to wrap your date filter into: ENDOFMONTH

Imke

• Proposed as answer by Wednesday, June 3, 2015 5:52 PM
• Unproposed as answer by Thursday, June 4, 2015 7:10 AM
Wednesday, June 3, 2015 4:58 PM
• Hi,

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Wednesday, June 3, 2015 11:02 PM
• Hi Ashish,

your response made me try to build it myself and now I see that it doesn't work (would have been too easy anyway :-)) - sorry.

Here we go then:

`ClosingBalance=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),LASTNONBLANK(bank_book[Date], CALCULATE(SUM(bank_book[Balance])))))`
`OpeningBalance=CALCULATE([ClosingBalance];PREVIOUSMONTH(calendar[Date]))`

The trick is to take the datefield from the FactTable in your filter argument instead of from the calendar table (just don't make it a habit :-).

Imke

• Proposed as answer by Thursday, June 4, 2015 7:11 AM
Thursday, June 4, 2015 7:08 AM
• Hi,

Thank you for trying but your formulas did not work either - I got the wrong result.  I tried something else but am facing a problem with one number.  Please see the file below.

http://1drv.ms/1BN2xyi

Thank you for all your help.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Thursday, June 4, 2015 11:11 PM
• Ah, that file helped - thanks!

Now I can see where my understanding was wrong: I thought we need to aggregate all balances of the last day of the month – which makes no sense here.

The difference between your wrong number and the desired one is actually the sum of 2 bank transactions of 28-3-2015.

Problem seems to be that there is no sign which one of the multiple transactions per day is actually the last one. As long as you don’t have that, I see no chance to work with the field balance sheet. You’d have to calculate your figures by adding up all deposits and payments movements.

The other figures in your example will probably just be correct by coincidence.

If you need any help on the new calculation – just drop a line.

Imke

Friday, June 5, 2015 4:16 AM
• Hi,

Thank you for your interest in solving my problem.  How else can I achieve what I want.  Please share your alternate formula.

Thank you.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Friday, June 5, 2015 5:13 AM
• Can deliver a start at least:

Movements:=SUM([Deposits])-SUM([Payments])

ClosingNew:=CALCULATE([Movements];FILTER(ALL(calendar[Date]); calendar[Date] <=MAX(calendar[Date])))+138938,48

Problem is the Balance Carried Forward (Starting Point) – have hardcoded it because removing the filter context is above me.

Also check if you’ll always only have on row on the starting date. Otherwise you’d have the old problem again: Which row to take?

BR, Imke

Friday, June 5, 2015 10:46 AM
• Hi,

Thank you for trying.  I my have multiple starting dates as well.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

Friday, June 5, 2015 11:06 AM
• Then you need Power Query to prepare your data before loading it to the data model.

This code identifies the correct Balance Carried Forward and moves it into the Deposit Column. With this preparation, you’re able to use my last formulas, simply delete the hardcoded entry of the number and then everything should work fine:

let

Source = Excel.CurrentWorkbook(){[Name="YourSourceTable"]}[Content],

Group = Table.Group(AddIndex, {"Date"}, {{"Count", each Table.RowCount(_), type number}}),

Sort = Table.Sort(Group,{{"Date", Order.Ascending}}),

FirstDate = Table.FirstN(Sort,1),

Expand = Table.ExpandTableColumn(Merge, "NewColumn", {"Cheque number", "Date", "Purpose", "Category", "Deposits", "Payments", "Balance", "Funds spent from", "Index"}, {"Cheque number", "Date.1", "Purpose", "Category", "Deposits", "Payments", "Balance", "Funds spent from", "Index"}),

ChgType = Table.TransformColumnTypes(Expand,{{"Balance", type number}, {"Payments", type number}, {"Deposits", type number}}),

Repl = Table.ReplaceValue(ChgType,null,0,Replacer.ReplaceValue,{"Deposits", "Payments"}),

Expand1 = Table.ExpandTableColumn(Merge1, "NewColumn", {"Balance"}, {"Balance.1"}),

Filter = Table.SelectRows(Expand1, each ([Balance.1] = null)),

Expand2 = Table.ExpandTableColumn(Merge2, "NewColumn", {"BalanceCarriedForward"}, {"BalanceCarriedForward"}),

Repl2 = Table.ReplaceValue(Expand2,null,0,Replacer.ReplaceValue,{"Deposits", "Payments"}),

AddCust2 = Table.AddColumn(Repl2, "Deposits_", each if [BalanceCarriedForward] = "BCF" then [Balance] else [Deposits]),

RemCols = Table.RemoveColumns(AddCust3,{"Deposits", "Index", "BalanceCarriedForward", "Payments"}),

Rename = Table.RenameColumns(RemCols,{{"Deposits_", "Deposits"}, {"Payments_", "Payments"}}),

Reorder = Table.ReorderColumns(Rename,{"Cheque number", "Date", "Purpose", "Category", "Deposits", "Payments", "Balance", "Funds spent from"})

in

Reorder

Imke

Friday, June 5, 2015 2:40 PM
• Hi,

Thank you once again for your solution.  I adopted a different approach

1. I inserted a Serial Number column in the Bank Book
2. Here is the formula I wrote for determining the Closing Bank balance

`=CALCULATE(SUM(bank_book[Balance]),FILTER(bank_book,bank_book[S. No.]=MAX(bank_book[S. No.])))`

3. Here is the formula I wrote for determining the Opening balance

`=[Closing Bank balance - Excel Enthusiasts]-[Total deposits]+[Total payments]`
Thank you once again for helping me.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

• Proposed as answer by Wednesday, June 10, 2015 11:17 PM
• Marked as answer by Wednesday, June 17, 2015 2:33 AM
Sunday, June 7, 2015 3:08 AM