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.