This is another of our periodic posts on QuickBooks Statement Writer. We have previously gotten an overview of how the product works. We also looked at customizing rows in order to change the design and appearance of reports.
These changes in formatting are permanent. That is, as new data from new periods in QuickBooks is refreshed into the QSW report formats, the formatting work already done remains.
QuickBooks Statement Writer is only available in QuickBooks Accountant and all industry versions of QuickBooks Enterprise.
In this post, we will look at customizing columns.
When selecting QuickBooks Statement Writer (QSW) from the Reports or Accountant dropdown menu, the above window opens. If you have already formatted QSW reports before, click on Open Report and select your report format file.
If you have not created QSW files before, you will want to choose Design New and complete that process now. A review of earlier posts about using QSW will probably help.
Once the QSW Excel add-in is open, you will have a report in Excel, similar to the one shown below.
For this article, we have chosen a profit loss report or income statement. The Excel worksheet only has one column, the current period of November 2018. This is a QuickBooks sample file with today’s date being set in the future.
Let’s add some additional columns. First, let’s add a column that will compare this month’s data with that of the same month, one year ago.
First, from the QSW Column Properties panel, we will use the insert column command. From the selection of column types, we will choose QuickBooks Data. This is what we now have:
Note that the new column contains no data. Also, QSW is giving us a message in red that the report does not contain new data until we update from QuickBooks.
The arrow points to the fields in QSW that determine what QuickBooks data will populate the new column. The top setting reads “Current Period, Current/Prior Year. The field underneath it currently reads zero.
As currently set, if we refreshed from QuickBooks, the new column would contain the same data as our existing one. Data from the current period.
By changing the zero in the second field to a 1, we would be telling QSW that we wanted data for the same month, but one year prior. We could also choose two years back or more by making the appropriate entry.
We will enter a one into the field and then refresh from QuickBooks.
The arrow points to the new column, now populated with data from November, one year prior to the current period.
We could add a Variance column to show the difference in the monthly amounts between the two years. We could add a percent column to show the percent of income for each row or the percent of variance from a variance column.
We could also add year-to-date and year-to-date from previous years columns.
The list is pretty extensive. Budget reports with columns showing budget data and variances are also options.
In addition, Excel formulas could be used to create a custom column for which there is no QSW format. Care should be used in such a scenario as QSW cannot maintain the formatting in such a column. But, in some cases, such a custom column may be just the answer needed.
QuickBooks Statement Writer is only available in QuickBooks Accountant and all industry versions of QuickBooks Enterprise.
Hector Garcia, CPA
Certified Advanced QuickBooks ProAdvisor
12401 Orange Drive #136
Davie, FL 33330
954-414-1524
hector@garciacpa.com
