Creating a construction cash flow chart

Hi everyone,

I am trying to prepare a chart to show cash flow on a construction project. Usually, for smaller scale construction, say under $2 million US for a residential development, payments to the builder can be made every month. I would like to analyse payments authorised.

One of the issues I am running into by using a PivotChart is that I can't seem to get excel to understand that my data is based on time duration. At the moment. I.e. my months are to the left of the chart when I went them to be the 'X' series. I would also like to be able to add a cumulative line to show total payment made.

If anyone could please assist I'd be grateful.

Thank you

Tom

Thank you JieJenn, I'm now part of the way there. The next item I'm looking to do is add a cumulative line.

I've tried following the instructions on Contextures Blog (https://contexturesblog.com/archives...-pivot-tables/) in order to get a cumulative line but can't seem to do this. I know it's a bit dated, but I think this advice still applies.

The only issue is I cannot select my data by month, but rather I have to select each month. Then it returns N/A.

I.e. here is the menu I get when I try following Contextures advice (see yellow highlight):

1.PNG

I am after something like the below (from Contextures):

2.PNG

I need to have something like OrderDate, but I'm not sure how to add that to my PivotTable. If anyone could advise I'd be grateful. I've attached my updated cash flow in line with JieJenn's comment.

Thank you

Hi JieJenn, thank you very much for the chart.

That has the cumulative line that I was after and also the totals. I am keen however to keep the flexibility of having all the data so I can drill down.

Would the method you used be able to also include the breakdowns of Trade Works and Prelims like I have shown in the sheet "tomca PivotChart"? I used Power Query to unpivot the month columns, but still can't get a cumulative total on this sheet.

Would formula or Power Query be best for what I am trying to achieve?

Thank you JieJenn, this is really helpful.