+ Reply to Thread
Results 1 to 8 of 8

Two queries; (1) Waterfall with negatives | (2) Line chart with negatives

  1. #1
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Two queries; (1) Waterfall with negatives | (2) Line chart with negatives

    Hi All,

    With reference to the attached.

    The two tabs attached here are part of a 20 tab document which will be used as a cash flow forecast document in a group of companies that I've recently been appointed as finance manager to. Most of the information contained within the attached document has been copy pasted as values (removing formula that link to the missing 18 tabs), although I have copy pasted some formulas that allow for changes for demonstration purposes (more on that later).

    The overall objective is to give group management a consolidated cash view of the group as a whole - some companies within the group generate cash deficits and are supported by the holding company (for strategic reasons), whilst others generate cash surpluses. The attached is an excerpt from the cash flow forecast that each individual company will populate. The data collected from this spreadsheet will then be aggregated for group reporting but at a company level, it is important that the spreadsheet is capable of reporting all scenarios i.e. cash deficits and cash surpluses.

    Waterfall chart
    The waterfall charts at present (see 'Analytics' worksheet) is only capable of correctly displaying cumulative cash use if cash reserves always remain positive. Note that on the 'Analytics-Worksheet' tab, we have an opening cash position of $100,000 (G3), income of $75,000 (C4) and expenditure of $25,000 (E5), leading to a closing cash position of $150,000 and it is this information that is used to generate the waterfall chart.

    In it's attached format, the waterfall chart correctly displays data, showing an opening balance (grey bar) of $100,000, income of $75,000 (green bar, which begins at the top of the $100,000 bar and rises to $175,000, indicating this is the new bank balance), shows expenditure of $25,000 (red bar, level with the top of the green bar at $175,000 but dropping by $25,000 to $150,000 on the y-axis) and finally a closing balance of $150,000 (grey bar which is level with the $150,000 on the y-axis).
    However, if you were to change the $25,000 expenditure on the 'Analytics_Worksheet' tab, (cell E5) to $250,000, this indicates a bigger expenditure. This big expenditure would be funded by way of overdraft (or whatever) which would result in negative cash reserves, so the waterfall chart needs to reflect this, but it doesn't. You will see that all the bars get screwed up and are not level with each other (as they should be).
    Does anyone know how to accommodate negative cash reserves on a waterfall chart?

    Line chart
    I also want a line chart that shows the closing cash position at the end of each month (using data on row 28 of the 'Analytics_Worksheet' tab), with an x-axis that details the month and a y-axis that crosses the x-axis which shows monetary values (the portion of the y-axis that is below the x-axis will show negative values, reflecting negative cash reserves e.g. overdrafts etc., whilst the portion of the y-axis above the x-axis shows positive values, indicating a positive cash reserve balance).
    Most importantly, any portion of the data line that rests above the x-axis should be green, whilst any portion of the data line below the x-axis should be coloured red.
    Attached Files Attached Files

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Two queries; (1) Waterfall with negatives | (2) Line chart with negatives

    Details on how to build waterfall with values that cross into negative values.
    https://peltiertech.com/excel-waterf...bridge-charts/

    Formatting line to change colour if passing between positive and negative is complex.
    http://andypope.info/charts/conditionalline2.htm

    You may be better off using a column chart where the invert if negative option can be used.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: Two queries; (1) Waterfall with negatives | (2) Line chart with negatives

    Thanks Andy, waterfall solution works perfectly.

    As for the line chart with differing colours indicating a cross over negative/positive values, I've adpoted a similar approach as suggested in the waterfall post, and created multiple data series where the positive data series shows only positive figures, else shows nil for months where there is a negative figure, and vice versa for the negative data series.

    I now have two different problems:
    1 - both data series are showing on the graph (one red, one green) but the 'positive' data series seems only to be displaying nil values, whilst the 'negative' data series plots all points on the graph (both negative and positive). What am I doing wrong here?
    2 - when one of the data series shows nil values and lies along the x-axis, I want to hide it behind the x-axis. I've made the x-axis itself thick (thanks to another post of yours I happened to stumble upon!) and black as a way to try and do this, but the data series are still clearly visible when they lie along the x-axis, as they are different colours to the x-axis. Is there a way for me to sit the data series behind the x-axis i.e. 'send to back'?
    Attached Files Attached Files

  4. #4
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: Two queries; (1) Waterfall with negatives | (2) Line chart with negatives

    Also, probably should have added that in the upload:
    'Opening' actually refers to the closing cash reserves of each month (don't know why I called it opening)
    'MP' means midpoint between the current month and the next month (CM + NM / 2), but this is only calculated when both CM & NM are both positive or both negative. When one is positive and the other is negative, then the MP value is zero
    'Positive' under a month column gives a value where the closing cash reserve for that month is positive. 'Positive' under an 'MP' column gives a value figure if the midpoint between CM & NM is positive
    'Negative' under a month column gives a value where the closing cash reserve for that month is negative. 'Negative' under an 'MP' column gives a value figure if the midpoint between CM & NM is negative

    I have to have two data series; one for values above zero ('positive' - coloured green) and another for values below zero ('negative' - coloured red) as I'm not capable of the type of formatting you suggested in your first post, so this is an easy workaround for me. But having two data series necessitates the use of a 'midpoint' function, so to give the appearance of a singular line chart.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Two queries; (1) Waterfall with negatives | (2) Line chart with negatives

    You have used a stacked line chart which is causing the problems.

    Changed chart type to Line chart and added black line series to mask zero lines.
    Attached Files Attached Files

  6. #6
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: Two queries; (1) Waterfall with negatives | (2) Line chart with negatives

    That's perfect Andy, thank you.

    Everything is working as I hoped now, but I've just thought of a final feature for the graph which might be a nice addition. Since the graph will show the cash reserves at the end of each month (historic months will be actuals, future months will be projected), I'd like a very thin yellow marker that runs parallel with the y-axis, to show the current month.

    I already have the formula to do this, (although values in the attached are pasted as values) so I just need to know if it's possible to have the values in the data series 'Top' and 'Bottom' (rows 6 and 7) plotted on the y-axis, then joined to each other by vertical line (running parallel to the y-axis). At present, each data series has it's own line and they both run along the x-axis. I want them joined to each other. Is this possible?
    Attached Files Attached Files
    Last edited by STUARTXL; 06-07-2018 at 02:50 PM.

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Two queries; (1) Waterfall with negatives | (2) Line chart with negatives

    You can use custom error bars to draw the vertical line.

    I used the black line series, renaming it Current Period.
    The negative value actually needs to be converted to a positive value.

    Add error bars to the current period series and set the custom values to use Top and Bottom data.
    Attached Files Attached Files

  8. #8
    Banned User!
    Join Date
    09-26-2015
    Location
    Manchester
    MS-Off Ver
    2013
    Posts
    218

    Re: Two queries; (1) Waterfall with negatives | (2) Line chart with negatives

    That's great Andy, thanks. Document is now complete. I appreciate your help. Rep added. Thread marked as solved.

+ 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] Display Negatives as Positives in Column Chart
    By AstToTheRegionalMGR in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 06-25-2015, 05:02 PM
  2. Getting negatives (-1) in the out put
    By kasi.maddula in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-30-2014, 06:21 AM
  3. [SOLVED] Best Chart for mix of positives and negatives
    By Eric S in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-24-2013, 11:00 AM
  4. [SOLVED] Best Chart for mix of positives and negatives
    By Eric S in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-22-2013, 10:37 PM
  5. Sum Negatives into next Positive
    By ScooterNM5 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-05-2010, 07:01 AM
  6. formula to allow only negatives
    By Jack 42 in forum Excel General
    Replies: 8
    Last Post: 05-04-2007, 01:58 PM
  7. No negatives
    By PCOR in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2005, 03:06 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