Analyze Sales and Purchase Data in a Snap

By David Ringstrom, CPA

Do you remember that game show where contestants would say “I can name that tune in five notes”? The accountant’s equivalent is “I can analyze your sales or purchase data in five clicks.” With Microsoft Office Small Business Accounting you are literally a few clicks away from impressing your clients with tabular and graphic analysis of their sales and purchases. Read on as I show you how to create a simple analysis of accounting data — which I’ll then transform into a more extensive reporting tool.

   

Meet the Analysis Tools

The analytical power of Small Business Accounting is contained within a separate Analysis Tools application. Create your first sales analysis by following these steps:

   

1. Click Start.
2. Choose All Programs.
3. Choose Microsoft Small Business Accounting.
4. Choose Analysis Tools.
5. If necessary, click the Change button, as shown in Figure 1, to select a company. In this article I use the sampleproductcompany.sbc file that ships with Small Business Accounting.
6. Choose Sales Analysis.
7. Click Display.

   

Timesavers: You can double-click on Sales Analysis instead of clicking once on Sales Analysis and clicking Display. You can also typically skip step 5 unless you need to switch to another Small Business Accounting company. By utilizing these time savers you can indeed analyze sales data in five clicks.

  

Figure 1: The Analysis Tools can streamline analyzing sales and purchases.

  

When you initiate the Sales Analysis tool, Small Business Accounting establishes an electronic conversation with Microsoft Office Excel – which creates a new workbook with two worksheets. When the process is complete, you’ll see a pivot table that summarizes total sales by year, as shown in Figure 2. You’ll also be presented with a sales chart, as shown in Figure 3. The pivot table resides on a worksheet named Sales Cube, while the chart appears on the Sales Chart tab.

   

Parallel track: All of the tips and techniques that I demonstrate using the Sales Analysis tool work in the exact same fashion with the Purchase Analysis tool.

   

Smart: Streamline your access to the Analysis Tools by following these steps:

1. Click Start.
2. Choose All Programs
3. Choose Microsoft Small Business Accounting
4. Right-click on Analysis Tools.
5. Choose Pin to Start Menu.

   

Figure 2: The analysis tool automatically creates a Sales Cube pivot table.

  

Figure 3: In addition, the tool automatically generates a sales chart.

   

Pivot Tables Unmasked

Pivot tables are one Excel’s most powerful — yet least understood — features. In a nutshell, pivot tables summarize data, typically from lists. In this instance the Sales Analysis tool establishes a connection between your sales transactions in Small Business Accounting and Excel. As shown in Figure 2, the resulting pivot table automatically creates one row for each customer and displays their phone number and e-mail address. The table also includes columns for 2005 and 2006 that tabulate each customer’s total sales.

   

Keep in mind that a pivot table is a static snapshot of the underlying data. If you choose to save this Excel file and open it later, you may want to refresh the data. To do so, click the floating Refresh from SBA icon that appears onscreen. The pivot table will dynamically expand to show any new customers that have purchased from you and update the sales totals for all customers listed in the table.

   

Toolbar Tip: If the SBA Report toolbar, which contains the Refresh Report button, isn’t visible, you can easily restore it. Right-click on any Excel toolbar, and then choose SBA Reports.

   

Expert Tip: You can ensure that the Refresh Report icon is always at the ready:

1. Right-click on the SBA Report toolbar.
2. Choose Customize.
3. Hold down the Ctrl key as you drag the Refresh Report button from the SBA Reports toolbar up to the main menu bar, to the right of Help.
4. Close the Customize dialog box.

   

Return to the pivot table, and look at the two fields above the table on row 6 that display Item and All. Presently the pivot table summarizes all items and customers for the last two years. From this point you easily drill-down to a specific item type, or go deeper and choose one or more individual items. To understand how this drill-down feature works, follow these steps:

   

1. Click the button in cell C6.
2. Double-click All.
3. Double-click Inventory Item.
4. Click once on Baseball Bat.
5. Click OK.

   

The pivot table changes to show you only those customers that have purchased baseball bats and provides the total sales for that item. If we had clicked OK after step 3, the pivot table would have reflected total sales of all inventory items, instead of just baseball bats.

   

Helpful: If you’d like more background on pivot tables, see Joseph Anthony’s and the Crabby Office Lady’s online tutorials.

   

   

Pivoting the Table

Now that you have some experience filtering the pivot table, let’s rearrange the table to view sales by customer. You’ll transform the report by simply dragging and dropping fields on to — or off — the pivot table.

   

Caution: Changes to pivot tables cannot be undone. Always work with a copy of your original workbook until you gain confidence with this feature.

   

You’ll be using the Pivot Table toolbar to manage the changes. If you don’t see the toolbar, right-click on any toolbar, and then choose Pivot Table.  Next, you’ll display the Pivot Table Field list. To do so, click the Show Field List button on the Pivot Table toolbar — the last icon at the right-hand side of the toolbar.

   

Next, follow these steps to expand the pivot table report:

   

1. Remove the Item field from the table by clicking and dragging the field in cell C6 to column H. Release the mouse when the red X appears on your cursor.
2. Click on cell B9, at which point you’ll see the words Drop Page Fields Here appear in cell C6.
3. Drag the Customer Name button from cell B9 to cell C6.
4. Drag Item from the Pivot Table Field List to cell B10, where the word Total appears.
5. Temporarily remove Sales Dollars from the table by dragging the Data button from cell C8 to column H.
6. Drag Sales Units from the Pivot Table Field list to cell C10 (or anywhere in the section that displays Drop Data Fields Here).
7. Drag Sales in Dollars from the Pivot Table Field list to cell C10. At this point the pivot table displays Sales Units by year, and Sales in Dollars by year.
8. Group the sales units and sales in dollars together by dragging the Year button to row 9.
9. Expand the report to display all inventory items clicking the arrow in cell B10, and then click the checkbox adjacent to Inventory Item in the list. The box will display two small checkboxes. Repeat this process for each item type, and then click OK.

   

Tricky: If you click on the Item Name in the list, you’ll expand that category. This is useful if you want to select individual inventory items. Conversely, click on the checkbox itself to display all items within the category. Within the checkboxes, a single checkmark for an Item Type means the data will be summarized at that level, while a double checkmark indicates that all items within that Item Type will be displayed. A blank checkbox indicates that an item type or inventory item will not appear on the report.

   

At this point, the pivot table report shows all items purchased by all customers. Follow these steps to view sales for a specific customer:

   

1. Click on the arrow in cell C6.
2. Double-click on All.
3. Click once on Coho Sports.
4. Click OK. The pivot table adjusts to display sales for Coho Sports only, as shown in Figure 4.

Figure 4: You can drill down to a single customer in the Sales Analysis pivot table.

   

Toolbar Tip: A Change Company icon appears on the SBA Reports toolbar when you open a saved workbook that contains a pivot table generated by the Analysis Tools. This is particularly helpful if you have a customized pivot table that you’d like to use with another Small Business Accounting company.

   

Get the Graph

As you make changes to the Sales Cube worksheet, the graph on the Sales Chart changes automatically. Therefore, if you followed the steps to modify the pivot table to show only sales for Coho Sports, the chart changed simultaneously. Conversely, if you make a selection from the Customer dropdown list on the chart itself, the pivot table will adjust at the same time.

   

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.