+ Reply to Thread
Results 1 to 7 of 7

Need Cumulative running totals that never fall below zero despite outflows

  1. #1
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Need Cumulative running totals that never fall below zero despite outflows

    In the attached I have a series of Cash Flows and corresponding months; some are positive, some negative. I'm trying to distribute positive cash flows when they occur, assuming the negative amounts can be covered in the running total.

    For example, columns B and C would assume a distribution each month as they are obviously positive, however, that distribution in column C ($54,422) would need to cover the negative $46,891 to the right; a net effect of $7,531. From there, the running balance would reset at zero and continue in the same manner, only distributing when the cumulative cash flow is positive but never allowing the running balance to fall below zero.

    Any help is greatly appreciated.

    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Need Cumulative running totals that never fall below zero despite outflows

    I don't understand; it sounds like you don't want the cumulative row to show accumulation of cash flows. Do you want it to zero out any time you get a negative cash flow? Not sure what that would show...

    Please provide a few hand coded examples in your spreadsheet that represent what you want it to show. I'd be interested in what happens with a negative cash flow that should be able to be covered by the accumulation (e.g. C4=100000 and D3=(500)), a negative cash flow that cannot be covered by the accumulation (e.g. C4=500 and D3=(100000)). Would it be possible for the 'cumulative' values to be negative in your scenario?
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need Cumulative running totals that never fall below zero despite outflows

    No, the cash flows can't be negative. I basically need to distribute enough cash as it is available while not falling negative at any point.

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Need Cumulative running totals that never fall below zero despite outflows

    But you show negative cash flows in your data. Do you just want them to not be included in the cumulative numbers? So, just consider them to be zero?
    So in C4, =B4+MAX(0,C3)
    As requested above, some examples would help explain your needs better.

  5. #5
    Registered User
    Join Date
    04-02-2019
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    74

    Re: Need Cumulative running totals that never fall below zero despite outflows

    Yes, there are negative cash flows in the data. I need a formulaic way to cover these outflows while also distributing as much as is available in a given month.
    I'm having trouble creating the logic that anticipates potential shortfalls while also pushing out any cash that would be available despite future outflows.
    I'm attaching another mockup that hopefully helps.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Need Cumulative running totals that never fall below zero despite outflows

    So, you have an outlook for 'foreseeable negative amounts'? How far ahead does it go? Two years like your spreadsheet shows?

  7. #7
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Need Cumulative running totals that never fall below zero despite outflows

    Track your 'Debt' in cell B5:
    =MIN(0,C5+B3)
    Drag that to Z5

    Your payments in cell B6:
    =MAX(0,B3+C5)
    Drag that to Z6

+ 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. Running Cumulative Totals
    By Ed4 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-27-2019, 11:38 AM
  2. Cumulative Running Totals needed for donations
    By Lynx4ever in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-31-2015, 01:17 PM
  3. [SOLVED] How to get cumulative totals from weekly totals (IE sum of all previous cells)?
    By moeburn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-08-2013, 07:29 PM
  4. [SOLVED] running totals in Pivot Table - removing a user from the totals for the current week
    By eisenbergg in forum Excel Charting & Pivots
    Replies: 12
    Last Post: 09-08-2013, 04:39 PM
  5. Replies: 1
    Last Post: 03-08-2012, 05:36 PM
  6. Cumulative totals
    By sh3896 in forum Excel General
    Replies: 17
    Last Post: 10-27-2009, 02:10 PM
  7. Need totals of values that fall within a given year
    By Pierre in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-05-2005, 02:06 PM

Tags for this Thread

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