Many users gravitate toward QuickBooks because numbers can have a tendency to blur together. This can particularly be the case with financial statements where Total Revenue blurs into Gross Profit, which then cascades into Total Expenses and down to Net Income. Up until now it’s been difficult to present financial statements in chart form that can also give the user a sense of scale for the numbers. If you’re using Excel 2016, you can now use the Waterfall Chart feature to easily illustrate numbers – as long as you remain mindful of a key nuance to this type of chart. However, this feature is exclusive to Excel 2016, so you’ll need to print your chart to a PDF or use another means of sharing with someone that is using Excel 2013 or earlier.
The first step in creating a Waterfall Chart involves the Profit & Loss report in QuickBooks. For this purpose, we need the Summary version of the report. The steps differ somewhat between QuickBooks Desktop and QuickBooks Online, but you’ll see how to quickly distil either report down to its essence.
Within your books, choose Reports, Company & Financial, and then Profit & Loss Summary. Click the Collapse button when the report appears onscreen. This will hide subaccounts from the report, but we still need to collapse the report further. As shown in Figure 1, click the arrows next to Income, Cost of Goods Sold, Expense, Other Income, and any other categories that may appear on your report. The goal is to get the report down to as few lines as possible. From there you’ll export the report to Excel. At this point your report will look like Figure 2.
Figure 1: Use the Collapse button to narrow down your report.
Figure 2: Your report will appear as shown after exporting it from QuickBooks to Excel.
We can’t chart the data yet because of the blank cells that QuickBooks introduces into the report, but there is an easy fix, as shown in Figure 3 and Figure 4:
- Click on the Income amount within your report.
- Press Ctrl-A to select the entire report.
- Press Ctrl-C or use the Copy command to copy the report to the Windows
- Right-click on any cell in the report and select the Values icon, or select Paste Special and then double-click Values (double-clicking enables you to avoid clicking the OK button).
- On Excel’s Home menu, select Find & Select.
- Select Go To Special.
- Double-click on Blanks.
- Press Ctrl-Minus to display the Delete (Alternatively, you can select the Delete submenu from Excel’s Home menu, and then select Delete Cells.)
- Double-click Shift Cells Left within the Delete
- Click Format on Excel’s Home
- Choose Autofit Column Width to widen the columns of the report.
Figure 3: Use the Go To Special feature to find the blank cells.
Figure 4: We must remove the blank cells before creating the Waterfall Chart.
At this point we’re almost ready to chart our data but, as shown in Figure 5, we have one more series of clean-up steps to do:
- Click cell A1 within your worksheet.
- Press Ctrl-Shift-L, or choose the Filter command on Excel’s Data menu or under the Sort & Filter submenu on Excel’s Home menu.
- Click the filter arrow in cell B1.
- Type a left parenthesis in the Search
- Click OK to display the rows within your data where column B is blank.
- Keep row 1, but delete any other visible rows.
- Press Ctrl-Shift-L or choose the Filter menu command again to display the remaining rows of data.
- Delete the Net Other Income
- Change the sign on Cost of Goods Sold and Expenses (as well as any other decreases) to be negative.
Figure 5: The Filter command is a helpful feature that enables you to clean up your data.
You can optionally delete any redundant rows from your report, such as Net Other Income since its amount is identical to Other Income.