locked
Determine opening and closing balance RRS feed

  • 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.

    Please help me solve this problem i.e. in determining the opening and closing cash balances.


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

    Wednesday, June 3, 2015 12:22 AM

Answers

  • 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 Michael Amadi Wednesday, June 10, 2015 11:17 PM
    • Marked as answer by Charlie Liao 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 Michael Amadi Wednesday, June 3, 2015 5:52 PM
    • Unproposed as answer by Michael Amadi Thursday, June 4, 2015 7:10 AM
    Wednesday, June 3, 2015 4:58 PM
    Answerer
  • Hi,

    Please share the full formula.


    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 Michael Amadi Thursday, June 4, 2015 7:11 AM
    Thursday, June 4, 2015 7:08 AM
    Answerer
  • 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
    Answerer
  • 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

    OpeningNew:=CALCULATE([ClosingNew];DATEADD(calendar[Date];-1;MONTH))

    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
    Answerer
  • 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],

        AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),

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

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

        FirstDate = Table.FirstN(Sort,1),

        Merge = Table.NestedJoin(FirstDate,{"Date"},AddIndex,{"Date"},"NewColumn"),

        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"}),

        AddCust = Table.AddColumn(Repl, "OB", each [Balance]+[Payments]-[Deposits]),

        Merge1 = Table.NestedJoin(AddCust,{"OB"},AddCust,{"Balance"},"NewColumn"),

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

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

        AddCust1 = Table.AddColumn(Filter, "BalanceCarriedForward", each "BCF"),

        Merge2 = Table.NestedJoin(AddIndex,{"Index"},AddCust1,{"Index"},"NewColumn"),

        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]),

        AddCust3 = Table.AddColumn(AddCust2, "Payments_", each if [BalanceCarriedForward] ="BCF" then 0 else [Payments]),

        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
    Answerer
  • 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 Michael Amadi Wednesday, June 10, 2015 11:17 PM
    • Marked as answer by Charlie Liao Wednesday, June 17, 2015 2:33 AM
    Sunday, June 7, 2015 3:08 AM