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

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

3. 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

4. In the Visual Reports – Create Report dialog, select the Cash Flow Report for Excel and click the View button.
5. 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

6. In the PivotTable Field List sidepane on the right side of the page, deselect the Cumulative Cost field.
7. In the PivotTable Field List sidepane, select the Monthly Calendar dimension.
8. In the PivotTable Field List sidepane, drag the Tasks dimension from the Filters section to the Rows section, if necessary.
9. 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.
10. 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.
11. Click the Design tab to display the Design ribbon with the PivotTable Tools applied.
12. 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

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

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

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

3. 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

4. In the external data warning dialog, click the Yes button.
5. 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

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