How to Create Excel 2016 Waterfall Chart for Financial Statements
Business Professionals precipitate toward QuickBooks because numbers can have a tendency to blur together. This happens mostly with financial statements where Total Revenue blurs into Gross Profit, which then falls into Total Expenses and down to Net Income.
Excel 2016 introduces the use of Waterfall Chart feature to easily illustrate numbers. Since this feature is exclusively for Excel 2016, so you’ll be required to print your chart to a PDF or use some other means of sharing it. You can use other earlier versions of Excel.
To create a Waterfall Chart you need to design a Profit & Loss report in QuickBooks. For more information, see 7 steps to create profit & Loss report in QuickBooks.
To create a waterfall Chart in QuickBooks Desktop
Process 1: Minimize Report
Step 1: Click on the QuickBooks icon.
Step 2: Select Reports, Company & Financial, and then Profit & Loss Summary.
Step 3: Once the report appears on screen, click the Collapse button.
Step 4: Click the arrows next to Income, Cost of Goods Sold, Expense, and Other Income as shown in the figure. Click on other categories that appear on your report.
Step 5: Once you have got the report down to as few lines as possible. You can export the report to Excel. Your report will appear as follows:
You will notice that QuickBooks introduces some blank cells in the report. Thus, you can’t chart the data before fixing this issue.
Process 2: Remove Blank Cells in QuickBooks Report
Step 1: Click on the Income amount tab in your report.
Step 2: Select the entire report by pressing Ctrl-A.
Step 3: Copy the report to the Windows by pressing Ctrl-C.
You may also use the Copy command to serve the purpose.
Step 4: Right-click on any cell in the report and select the Values icon
Select Paste Special and then double-click Values.
Step 5: Go to Excel’s Home menu, select Find & Select.
Step 6: Select Go To Special.
Step 7: Double-click on Blanks.
Step 8: Press Ctrl-Minus to display the Delete
You can select the Delete submenu from Excel’s Home menu. Select Delete Cells.
Step 9: Double-click on the Shift Cells Left within the Delete.
Step 10: Click Format on Excel’s Home.
Step 11: Choose Autofit Column Width to widen the columns of the report.
Process 3: Clean up of Report
Step 1: Go to your worksheet and click cell A1.
Step 2: Press Ctrl-Shift-L
Select the Filter command on Excel’s Data menu
Step 3: Click the filter arrow in cell B1.
Step 4: Go to the Search tab and type a left parenthesis.
Step 5: Click OK to display the rows within your data where column B is blank.
Step 6: Delete all visible rows except row 1.
Step 7: Press Ctrl-Shift-L
Choose the Filter menu command again to display the remaining rows of data.
Step 8: Delete the Net Other Income
Step 9: Change the sign on Cost of Goods Sold and Expenses (as well as any other decreases) to be negative.
You can optionally delete any redundant rows from your report, such as Net Other Income since its amount is identical to Other Income.
Hope you liked this post, for more tips on QuickBooks and other business software, subscribe to our blog posts.
- QuickBooks2016.07.01QuickBooks 2016 : How to Select the Industry Type and Accounts
- Microsoft Sharepoint2016.06.30Microsoft’s SharePoint 2016: Awesome New Features Added For It’s Users
- QuickBooks2016.06.27How To Search and Edit Customers Records In QuickBooks 2016
- Microsoft Sharepoint2016.06.22Microsoft Announces Release Of iOS App For SharePoint