+ Reply to Thread
Results 1 to 7 of 7

Month-end cash balance from the running cash balances

  1. #1
    Registered User
    Join Date
    05-05-2014
    Location
    Voorheesville, NY
    MS-Off Ver
    Excel 2007
    Posts
    72

    Month-end cash balance from the running cash balances

    Hello,
    Attached is a sample of an income and expenses report (1st tab), and month-end cash balance report (2nd tab), that I maintain.
    Right now, I am using “=Report!E12” to record the cash balance for the month of January 2012. This manual method is tedious and potentially can create an error if not careful.

    I have been researching the internet for an excel formula of a cash balance for a specific month and year.

    Can anyone help me, please

    Sincerely
    Dache416
    Last edited by dache416; 05-05-2014 at 03:20 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Month-end cash balance from the running cash balances

    Here, try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit: You can also add dates instead of number in A column of 2nd sheet and format as MMMM to show months, but I had to change to numbers for the test.
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Month-end cash balance from the running cash balances

    A word of caution with zbor's solution above: it relies on "Interest paid" being the last transaction each month. If that's true then it will give the closing balance of each month of the year before the year shown in column 1 of the Month-end Report tab. I suspect you need to add a new column for 2012 in which case you would need to remove the "-1" from the "'Month-end Report'!B$1-1" part of the formula.

  4. #4
    Registered User
    Join Date
    05-05-2014
    Location
    Voorheesville, NY
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Month-end cash balance from the running cash balances

    Hello Zbor,

    Thank you very much for your reply to my Thread.

    The formula worked as it shown on the edited attachment. However, the formula would not work, if I combine the two reports together (meaning both tables is in one worksheet) using your formula or the formula below:

    =SUMPRODUCT((MONTH($A$3:$A$25019)=1)*(YEAR($A$3:$A$25019)=2012)*($B$3:$B$25019="Interest Paid"),$G$3:$G$25019)

    Please advice
    Dache416
    Last edited by dache416; 05-05-2014 at 08:01 PM.

  5. #5
    Registered User
    Join Date
    05-05-2014
    Location
    Voorheesville, NY
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Month-end cash balance from the running cash balances

    Attached is the modify report spreadsheet

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Month-end cash balance from the running cash balances

    You looking for 2013 while all data is in 2012.
    You need to take -1 from 2013 to get correct result.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Also, you looking for month 2 in January and month 1 in February.

    Maybe you should change into
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    05-05-2014
    Location
    Voorheesville, NY
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: Month-end cash balance from the running cash balances

    Thank you for your help Zbor

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Ending day cash balance
    By brandedadnan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-02-2014, 05:41 AM
  2. Replies: 1
    Last Post: 07-17-2013, 11:25 AM
  3. Income Statement/balance sheet/cash flow summary generator
    By xleo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-04-2012, 04:10 PM
  4. Waterfall Cash Model, dividing up cash flows based on IRR
    By tomservo2009 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-21-2008, 05:57 PM
  5. Cash flow and VBA
    By luke1438 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2005, 06:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1