Journal of Accountancy Large Logo
ShareThis
|
TECHNOLOGY Q&A
Reader tip  
By J. Carlton Collins
July 2012

Responding to the March 2012 Technology Q&A topic “Monitoring the Field” (JofA, page 69), reader Scott Bollenbacher says his firm (Bollenbacher & Associates LLC, of Portland, Ind.) uses the $9.99 iPad app MaxiVista (tinyurl.com/6nnvpkz) to wirelessly convert its staff’s iPads into external monitors for use with their laptop computers in the field. This solution provides each user with dual monitors without anyone having to carry bulky desk monitors. Bollenbacher said this solution “has worked out great for us.” Other apps that allow iPads to be used as a second monitor include the $9.99 AirDisplay (avatron.com) and the free DisplayLink (tinyurl.com/6b48g6n).

 


TECHNOLOGY Q&A
Footers take a step backward  
By J. Carlton Collins
July 2012

Q: To avoid losing documents in the digital universe, we like to insert the document file name and path into the document’s footer. I can still do that with Excel, but I cannot figure out how to do it with Word now that I have upgraded to the 2010 version, and Word’s help section/index doesn’t seem to provide a workable solution. Any suggestions?

A: Inserting the document’s path and file name was a relatively easy task in Word 2003; unfortunately, duplicating this function in Word 2010 is a little more difficult. Word 2010 does offer a solution for inserting the file name and path into the footer (or header) as follows:

1. Double-click the footer (or header) area to make the footer (or header) area active;

2. On the Header & Footer Tools tab, select Quick Parts (from the Insert group);

3. Select Field to display the Field dialog box (shown below);

4. Next select FileName from the Field names list box;

5. Check the check box labeled Add path to filename under Field options (on the right side of the dialog box);

6. Click OK.

If the file name or path changes, the file name and path displayed in the footer (or header) are updated automatically when you Print (or preview) the document; or if you prefer, you can update the file name and path in the footer (or header) manually by right-clicking on the file name and path and selecting Update Field from the popup menu.


TECHNOLOGY Q&A
A taxing question  
By J. Carlton Collins
July 2012

Q: Our company operates in many states and ships products throughout the United States. Dealing with sales taxes is a significant problem for our organization, as our current accounting system does not meet our sales tax needs. Can you recommend an accounting system that provides a strong sales tax solution?

A: Sales taxes are problematic for any company that ships goods, and your question is common. There are thousands of sales tax jurisdictions in the U.S., and last year alone, there were more than 1,000 rate, spatial boundary, and tax rule changes within those jurisdictions. That high rate of change makes accounting for sales taxes more complicated than accounting for payroll taxes. As examples, the state of Washington taxes the streaming of digital movies and music; Texas holds an annual sales tax holiday when belts with buckles attached can be purchased tax-free, but belt buckles cannot; and Mississippi charges a 3% sales tax on the sale of aircraft equipment but only 1.5% on farm implements, so how much sales tax is owed on the sale of a crop-duster?

The good news is that you do not need to replace your accounting system, because there are many third party add-on sales tax solutions in the marketplace from companies such as ADP (tinyurl.com/6o6mf5e), Avalara (avalara.com), Sales Tax Solutions (salestaxsolutionsinc.com), Tax Data Systems (taxdatasystems.com), and Vertex (tinyurl.com/7ry9rj2).

Avalara, which offers some free products and charges $45 per month and higher for others, integrates with many accounting systems, including the one mentioned in your question. Three Avalara features I find useful are:

1. Pinpoint jurisdictions. Because a single ZIP code can encompass numerous sales tax rates, Avalara's AvaTaxRates.com uses mapping coordinates that allow users to identify the exact sales tax jurisdiction and applicable sales tax rates for each shipment. The image below depicts the AvaTaxRates.com drillable map, which is color-coded by sales tax rates.

2. Single payment sales tax filing. Avalara's AvaTax product enables a company to send a single Automated Clearing House payment to a secured account each sales tax pay period. AvaTax divides and forwards the sales tax to the appropriate jurisdictions, along with the necessary e-file or hard-copy sales tax returns.

3. Integration. Avalara integrates with popular accounting systems such as QuickBooks, Peachtree, Epicor, Microsoft Dynamics GP/NAV/AX/SL, Sage MAS 90/200/500, Sage ERP Accpac, Sage Pro ERP, and more than 100 others. Avalara also integrates with many e-commerce software vendors. These integrations help calculate the correct sales tax during the sales order process so the customer is charged accurately at inception. Additionally, because Avalara is cloud-based, changes in sales tax laws automatically are incorporated into the system throughout the year, lessening the likelihood of sales tax amounts being calculated incorrectly based on antiquated sales tax law information.

Sales taxes are one of more than 20 types of taxes U.S. residents and businesses may have to pay. To learn more, see "Total Tax Insights," page 54.


TECHNOLOGY Q&A
A relatively easy macro  
By J. Carlton Collins
July 2012

Q: I have written an Excel macro that copies a column of data on Sheet1 and pastes that data as a row on Sheet2. However, I want the paste location to drop down to the next blank row each time I paste, but the macro keeps pasting the data to the same row each time, overwriting my previous data. How do I get my macro to move the cursor to the next blank row before pasting?

A: Before discussing the solution, it is important to understand the difference between Excel's absolute and relative macros. By default, Excel records an absolute macro in which the macro stores your exact cell locations as you record each macro. As an option, you can also record a relative macro in which Excel stores your cursor movements (instead of exact cell locations) as you record each macro. In your case, the macro needs to be half absolute and half relative, as described in the following example.

In this example, column B contains a range of data (highlighted in yellow) related to a sales order, and row 2 (beginning in column D) contains headings for a data collection area (highlighted in blue).

The objective is to record a macro that will copy the vertical data from the yellow cells and paste the results horizontally to the first available row under the blue cells. To accomplish this task:

  1. From the View tab, select Macros, Record Macro.
  2. In the Macro name box, enter a name such as PostSalesOrder (macro names cannot contain spaces), and click OK. (You are now recording a macro as indicated by the blue, square stop recording icon located in the bottom-left corner, as pictured.)

  3. Highlight the yellow cells and press Ctrl+C to copy the data.
  4. From the View tab, select Macros, Use Relative References. (You are now recording a relative macro from this point forward. The only indication that you are recording a relative macro (as pictured below) is that the Use Relative References icon has a small orange border highlight, and you must select Macros from the View tab to see this indication.)

  5. Press the F5 key to launch the Go To dialog box, enter D1 in the Reference box, and click OK. (This action will position your cursor at the top of the data collection area.)
  6. Press the End key, then the down arrow key. (This action will move your cursor to the last completed row in the data collection range—cell D2 in this example). Next, press the down arrow key again to position the cursor at the first available blank row—cell D3 in the example.
  7. From the Home tab, select Paste, Paste Special, and place a check in the box next to Transpose, then click OK.
  8. Next, to prepare the sales order form to receive the next sales order, press the F5 key to launch the Go To dialog box, enter B3:B19 in the Reference box, click OK, then press the delete key. (This erases the existing data from the sales order form and positions the cursor in cell B3, making it easier for the sales order clerk to enter the next sales order.)
  9. From the View tab, select Macros, Stop Recording. You have now created a macro that will copy a given column of data and paste it to the next available blank row in the data collection area.

To make your macro easier to access and execute, create a macro button. A macro button is really just a text box with a macro assigned to it, so that clicking the text box executes (or runs) the macro. Steps for creating a macro button are as follows:

  1. Insert a text box in the upper-left corner of the worksheet. From the Insert tab, select Shapes and click the Text Box icon (the first icon under the Basic Shapes section), then click and drag a range using your mouse to highlight the desired position and size of the text box.
  2. Click the middle of the text box and enter your desired text, such as Post Sales Order, and format the text box as desired. (In this example, I increased the font size, centered the text, bolded the text, and applied the Intense Effect – Blue, Accent 1 shape style from the Drawing Tools, Format tab, Shape Styles group. The Drawing Tools become visible at the top of the screen when you click the text box.)
  3. Right-click the edge of the text box and select Assign Macro from the popup menu, select your newly recorded macro, and click OK. These three steps are depicted below.

Your resulting workbook should appear as follows:

Thereafter, clicking the Post Sales Order macro button runs the macro that copies your columnar data and pastes it horizontally to the next available blank row in your data collection area, example results of which are pictured below.

You can download the example Excel workbook described above at carltoncollins.com/macro.xlsm.

Notes:

  1. Remember to save the workbook as an Excel Macro-Enabled Workbook; otherwise the macro will not be saved with the file.
  2. The example was created on a single worksheet for simplicity, but I normally would locate the data collection area and sales order form on different worksheets.

TECHNOLOGY Q&A
A PivotTable column worth repeating  
By J. Carlton Collins
July 2012

Q: How do I add a percentage-of-total column in a PivotTable in Excel 2010?

A: The October 2011 JofA Technology Q&A column (page 76) included an item titled “Make a Difference With PivotTables,” which explains how to add a calculated column to a PivotTable. That solution could work, but in this particular situation, I’d like to offer an easier option, using Excel’s Show Values As function. To use this feature, in the PivotTable’s Field List, drag the value field name that you want to summarize by amounts and percentages into the Field List’s Values box twice. This action repeats the value columns in the PivotTable, as shown below.


Next, right-click anywhere on the second value column (Sum of Revenue2 in this example) and select Value Field Settings from the popup menu. In the resulting Value Field Settings dialog box, click the Show Value As tab and select % of Column Total from the Show Values As dropdown box, and then click OK.
 
Your PivotTable will now display the same two columns of data both numerically and as a percentage of total, as pictured below. (In this example, I double-clicked and edited each column’s title to better describe the data.)

Note: This option also can be accessed from the PivotTable Tools tab by selecting the Options tab, Calculations, Show Values As.


View CommentsView Comments   |  
Add CommentsAdd Comment   |   ShareThis

AICPA Logo Copyright © 2013 American Institute of Certified Public Accountants. All rights reserved.
Reliable. Resourceful. Respected. (Tagline)