Loading...
Quick Links

Create a Monthly Cash Flow Report in Microsoft Project 2016

One of the useful new features introduced in Microsoft Project 2013, and continued in the 2016 version of the software, is the Dashboard Reports feature. This feature replaces the old paper-based reports feature found in Microsoft Project 2010 and all previous versions of the software.  Although I personally love these Dashboard Reports, they have one main limitation:  it is not possible to create a cash flow report as a Dashboard Report in Microsoft Project 2013 and 2016 that looks like the old Cash Flow report. For example, Figure 1 shows a print preview of the Cash Flow report in Microsoft Project 2010.

Figure 1: Cash Flow report

 Figure 1: Cash Flow report

One limitation of the Dashboard Reports feature in Microsoft Project 2013 and 2016 is that it simply will not allow you to create table that looks like the Cash Flow report shown previously in Figure 1. The way to work around this limitation is to create a new Visual Report for Microsoft Excel instead.  The easiest way to accomplish this is to modify the existing Cash Flow Report by completing the following steps:

  1. Open a project and then click the Report tab to display the Report ribbon.
  2. In the Export section of the Report ribbon, click the Visual Reports button. Microsoft Project displays the Visual Report – Create Report dialog shown in Figure 2.

Figure 2: Visual Reports – Create Report dialog

Figure 2: Visual Reports – Create Report dialog

  1. In the Visual Reports – Create Report dialog, click the Select Level of Usage Data pick list at the bottom of the dialog and choose a reporting option.  Notice in Figure 3 that I selected the Months level of usage data.

Figure 3: Select the Months usage data

Figure 3: Select the Months usage data

  1. In the Visual Reports – Create Report dialog, select the Cash Flow Report for Excel and click the View button.
  2. In the resulting Excel workbook, click the Task Usage tab to display the Task Usage worksheet, as shown in Figure 4.

Figure 4: Task Usage worksheet

Figure 4: Task Usage worksheet

  1. In the PivotTable Field List sidepane on the right side of the page, deselect the Cumulative Cost field.
  2. In the PivotTable Field List sidepane, select the Monthly Calendar dimension.
  3. In the PivotTable Field List sidepane, drag the Tasks dimension from the Filters section to the Rows section, if necessary.
  4. In the Task section of the PivotTable, repeatedly click the Expand button (+ button) to completely expand all of the summary tasks so that you can see all of the detail tasks in the project.
  5. In the Year section of the PivotTable, click the Expand button (+ button) to expand the Year dimension until you see months across the top of the PivotTable.
  6. Click the Design tab to display the Design ribbon with the PivotTable Tools applied.
  7. In the Layout section of the Design ribbon, click the Report Layout pick list button and select the Show in Compact Form item, as shown in Figure 5.

Figure 5: Select the Show in Compact Form option

Figure 5: Select the Show in Compact Form option

Note: If you want to remove the subtotals for years, click the Subtotals pick list button in the Layout section of the Design ribbon, and select the Do Not Show Subtotals item.

13. Select all of the numbers in the PivotTable and apply currency formatting as desired, such as US dollars, for example.

Figure 6 shows the completed Cash Flow report in Microsoft Excel.

Figure 6: Completed Cash Flow Visual Report

Figure 6: Completed Cash Flow Visual Report

Saving the Completed Cash Flow Report as a Template

To avoid needing to repeat the preceding set of steps for every project in which you want to use the same custom Cash Flow report, you can save the finished Excel workbook as a new Visual Report template. To do this, complete the following additional set of steps:

  1. Click the File tab in Microsoft Excel and then click the Export tab in the Backstage.
  2. On the Export page of the Backstage, click the Change File Type icon and then select the Template (*.xltx) icon in the Change File Type section of the page, as shown in Figure 7.

Figure 7: Save the Cash Flow report as an Excel template

Figure 7: Save the Cash Flow report as an Excel template

In the Save As dialog, Microsoft Excel navigates to your default Templates folder for Microsoft Office templates.

  1. In the Save As dialog, enter a unique name for the new Visual Report, such as Cash Flow, and then click the Save button.

Microsoft Excel displays a warning dialog about external data, such as shown in Figure 8.

Figure 8: External data warning dialog

Figure 8: External data warning dialog

  1. In the external data warning dialog, click the Yes button.
  2. Close the workbook in Microsoft Excel and then exit the software.

The next time you open a project in Microsoft Project and want to use your new custom Cash Flow report as a replacement for the old Cash Flow report found in previous versions the software, you can find your new Visual Report template in the Visual Reports – Create Report dialog, such as shown in Figure 9. Before you create the new Cash Flow report, remember to click the Select Level of Usage Data pick list at the bottom of the dialog and choose a selection such as Months.

Figure 9: New Visual Report template

Figure 9: New Visual Report template

A version of this article originally appeared on the Sensei Project Solutions blog.

Updated 1/22/18 with permission from the author.


Related Content

Webinars (watch for free now!):
Get rid of your last separate Excel sheets: Use your MS project schedule for project financials
Report Basics: Build an Agile Kanban Board in Microsoft Project

Articles:
How to Do Your Cost Management Right in Project
Create a Monthly Cash Flow Report in Microsoft Project 2013
Creating Milestone Reports in Microsoft Project


Share This Post
45 Comments
  1. Excellent and very useful article, thanks for sharing.

    Reply
  2. Cool how-to. thanks.

    Reply
  3. Extremely helpful– Thank you.
    Do you know of an enterprise version of a cash flow report?
    One that would sum our cash needs for all projects within our project server 2013?

    ~Jerry

    Reply
  4. Great Tutorial there. However I need help on Saving the Completed Cash Flow Report as a Template using microsoft excel 2010. please help.

    Reply
  5. Awesome!!

    Extremely useful

    Reply
  6. Hi Dale,
    I cannot see anything in visual report. I want to make monthly cost report but if when I go to visual report then I cannot see anything over there and edit button and I am not able to click the select level usage data pick list also.
    Really appreciate your help.

    Reply
  7. Hi Dale,
    I wanted to thank very much for this tutorial. It was really, extremely helpful! Is used it in MS Project 2010 and it works the same way.
    I know, I can’t have all, but I ask me how to bring in the “Unique id” in this report, because we use this to identify our deliverables. Any idea?
    Again, great work!
    Roland

    Reply
  8. Thanks Dale,
    That got me out of a hole!
    Ian

    Reply
  9. Simply Superb..!! Thanks a lot for sharing this article

    Reply
  10. Hi Dale

    I would like to seek your advise regarding estimating a near accurate monthly cash flow on lets say as an example a fit out project valued at 5 million $ and project duration is say 6 months .

    As a Project Manager to prepare a near accurate cash flow on such a project wouldn’t I need to have data such as quantities and rates readily available with me and most importantly the schedule of work to be done for that particular month in order for me to arrive at a near accurate figure ?

    And then we need to account for variances as well

    I would appreciate if you could steer me in the right direction as my understanding is that this is the scope of a QS ?

    Thanks

    Reply
  11. Briliiant set of instructions thanks so much! Very clear, and worked a treat.

    Only issue is when I store as template, and try to subsequently load template, I get the error “Cannot open a copy while the template “Project Cashlfow.xltx” is open. Any advice would be really appreciated, as being able to use the template would save running through all the set up steps on each of my projects weekly. Thanks

    Reply
  12. Great tutorial! keep up the good work! Thanks!

    Reply
  13. Many thanks for this Dale – I have successfully produced an annual Cash Flow Report for my project. However, I need the report to show expenditure by financial year, not calendar year.

    Any advice on how I can get my visual Cash Flow Report to report on a financial year basis rather than calendar year basis? The “Select level of useage data to include in the report” drop-down in the only offer weeks, months, years, (not financial years) in the” Visual Reports – Create Report” dialog box.

    I image this must be a common need, so I expect there is a solution, but I can’t find it.

    Any help gratefully received.

    Many thanks

    Reply
  14. Hi Dale – any thoughts n my question of February 2, regarding cash flow reports for a financial year, rather than a calendar year?

    Many thanks,

    Reply
  15. Andrew — There is no way to create a Cash Flow report that shows fiscal years. Sorry.

    Reply
  16. Curses! I’m really surprised, but I’m very grateful for the confirmation Dale. many thanks.

    PS – does MPUG provide feedback to MS about Project? If so, could we suggest that the facility to generate these Cash Flow reports on a fiscal year basis would be very helpful?

    Just a thought……….

    Thx

    Reply
  17. Excellent! Thank you!

    Reply
  18. Thanks a lot! Very useful tutorial

    Reply
  19. no words to thank you…..

    Reply
  20. Dale;

    It is not true that you cannot do a cashflow for the fiscal year. In the MS Project options, set the first month of the fiscal year to the appropriate month. It will all work. Please update your comments.

    Reply
  21. Dennis — Thank you for your comment; however, your comment has little to do with my article. You cannot display fiscal years or fiscal months in an Excel Visual Report. Hope this helps

    Reply
  22. Thank you so much for posting this – absolutely what I needed!

    Reply
  23. Many thanks, it’s perfectly explained, and very easy to follow, not like other topics!!!
    I am very grateful for your help!!!

    Reply
  24. Hi Dale, awesome advice.

    I see a problem though. After each task group header, e.g. “Design” on line 9, the group header “Design” is repeated again on line 10.

    Ditto with “Office pack up”etc.

    Is there anyway to avoid this? Then it’d be perfect 😀

    Reply
  25. This article is very good but how can a running total or cumulative total be shown at the across the bottom row, or as a sub total, that does not revert to zero at each change in year?
    Thanks,

    Reply
  26. Dears,

    Is it possible to make the cash flow only for selected items in project, this report is exporting full time schedule in excel, i want only some items. Is there a solution?

    Kindly,

    Fred

    Reply
  27. This was extremely useful, sir! I have no words… You saved hours of my time!

    Reply
  28. Thank you for this contribution! It helped me prepare a cash flow that we need to adjust the 2018 figures for inflation in 2018 with high level tasks split over months, which would have taken hours to do. Thanks again.

    Reply
  29. Exactly what I was looking for! Thank you, Dale.

    Reply
  30. Very very helpful.. thank you so much..

    Reply
  31. THANK YOU DALE, VERY USEFUL, IT TAKES OUT A LOT OF PAPERWORK TRAILING.
    CHEERS
    🙂

    Reply
  32. Hi Dale
    I’m trying to create a monthly cash flow forecast as the standard report is by quarter. I don’t get very far. I go to Visual Reports, click Cash Flow, Months and View. It tries to load then I get a message – This operation requires that you have Excel 2003 or later on your computer. I have Excel 2016 as part of Office 365. It works fine.

    Any ideas?

    Cheers

    John

    Reply
  33. Thank you for posting this Article. It has been extremely helpful. I have a question, is there an easier way to adjust the cost values in the task usage page (in Excel) instead of going back to MSP and using the Visual Report?
    Thank you

    Reply
  34. Thank you for a to-the-point, great and clean tutorial! Is there a way to include WBS or task ID in the left column?

    Reply
  35. I’m trying to create a monthly cash flow forecast as the standard report is by quarter. I don’t get very far. I go to Visual Reports, click Cash Flow, Months and View. It tries to load then I get a message – This operation requires that you have Excel 2003 or later on your computer. I have Excel 2016 as part of Office 365.

    Thank you

    Regards

    Betty

    Reply
  36. Quite Handy. Many Thanks!!!

    Reply
  37. You are a lifesaver! This is exactly what I needed. Thank you!!!

    Reply
  38. Thanks, Dale, This is so valuable. Will look at your books. Thanks for sharing such valuable knowledge. George

    Reply
  39. Thank you! This worked perfectly and is just what I was looking for.

    Reply
  40. Very helpful and useful. Many thanks

    Reply
  41. I wonder if someone can help me please.

    I am trying to produce a cost and revenue report based on a build programme for a construction project using Project 2010. I have been trying for ages but I can’t seem to produce what I need, it may be that it’s not something MS Project can produce.

    Basically, at the moment I can assign fixed costs to particular tasks and produce a cash flow report showing the costs in monthly increments (prorated). But, what I need to do is, as well as assign a cost to a task (i.e. what I will pay a subcontractor for undertaking the work) assign revenue to the same task (i.e. what the client will pay me for the work), I then need to produce a cash flow report that shows cost in the month and revenue in the month (both prorated).

    I would be very grateful for any help, even if it is just confirmation that it isn’t possible.

    The reason for it is accurate forecasting against the programme. In my case revenue is directly related to the schedule. I am a quantity surveyor for a main contractor, a fundamental part of the job is monthly reporting on cost and value. Cost being what we pay the subcontractor for a particular item of work, revenue being what the client pays us for that same item of work. We need to report actual financial performance against forecast. The forecast needs to reflect the construction programme.

    There are other ways to do it and we currently use excel. I thought using project might be an accurate method when you have a complex programme involving multiple tasks running simultaneously in any given month. An advantage of linking the cost and value to the programme is if we run behind programme I can slide the programme to reject actual progress and the costs will move too.

    In short, I need to create revenue and cost report in Microsoft Project 2010 – Can anyone help me in this ?

    Reply
  42. This was PERFECT and exactly what I was looking for. Thank you so much!

    Reply
  43. Good afternoon Dale

    I am trying to create a cash flow report that follows a fiscal year that starts in July and runs through to June. I have set it so that the fiscal year starts in July in the Project Options and the programme now shows it correctly but as when I try to set up the cash flow table in XL as per your instructions (Which have been hugely helpful, thank you) I still get the year starting in January and ending in December. Is there any way that i can get the table to apply the months according to the fiscal year as per the programme.

    Reply
  44. Thank you

    Reply
  45. amazing. just what i need. thanks!

    Reply

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Please complete this equation so we know you’re not a robot. *

5 + 3 =

Thanks for submitting your comment!
You must be logged in to comment.