Journal of Accountancy Large Logo
ShareThis
|
TECHNOLOGY Q&A
A good measure  
By J. Carlton Collins, CPA
July 2013

Q: How can we tell if our internet service provider is delivering the internet speed we are paying for?

A: There are many web-based internet speed tests, but use caution: Some so-called “speed tests” also install malware on your computer. For this reason, I like to use reputable speed tests such as the one provided by Xfinity at speedtest.comcast.net. Just visit the website and click on a server on the U.S. map under the “Speed Test” headline. The tool then will test the speed of your internet connection to that server. In my test, I scored a download speed of 11.99 megabits per second (Mbps) and an upload speed of 4.19 Mbps.

Note that by clicking the NEW SERVER button, you can test your internet speed to various locations throughout the United States. Generally, the farther the server is from you, the slower the internet speed is because the data must pass through more routers, and each router represents a potential bottleneck. 

The Xfinity test does not install any software on your computer, and it works almost instantaneously. The test is performed by pinging the server 10 times, then averaging those times to determine the final result. (The Xfinity speed test measures the performance for any internet service provider (ISP), not just Xfinity.)

I recommend you use the Print Screen key or the Windows Snipping Tool to capture the report and paste it into a Word document saved as “Internet Speed Tests.” Repeat this test periodically and capture the results to document your internet speed. These test results will help you determine whether you are receiving the internet speed you are paying for, and over time allow you to detect any changes in speed. Because each new ISP customer in your locality shares the same bandwidth and routers, your internet speeds eventually may decline. If this happens, you should ask your ISP to upgrade its capacities in your area to restore the contracted internet speeds (I admit that I have done this myself, multiple times). Use the following table and bar chart to evaluate your reported internet speed:

Note: The Xfinity speed test uses Flash, which is not supported by Apple’s iOS operating system. Apple iOS users should use the speed tests at att.com/speedtest or speedtest.net.


TECHNOLOGY Q&A
A bit confusing  
By J. Carlton Collins, CPA
July 2013

Q: When installing Office 2013 on my 64-bit computer running a 64-bit operating system, the Microsoft installation routine recommends installing the 32-bit version of Office 2013, but it also provides the 64-bit option of Office 2013, which seems to be the more obvious option. Which do you recommend and why?

A: It is true that the Microsoft Office 2013 installation routine recommends you install the 32-bit version, but the installation routine’s small print explains this recommendation. The 32-bit version is compatible with 32-bit add-ons and plug-ins, whereas the 64-bit is not. Additionally, the 64-bit version does not support the ActiveX controls library (which contains ActiveX controls used to build solutions commonly used in Access, Excel, and Word), or SharePoint list controls. If these items are nonissues for you, then installing the 64-bit version of Office 2013 is the better option. The advantage of running the 64-bit version is support for larger files, faster performance, and superior bragging rights at cocktail parties.


TECHNOLOGY Q&A
Three Word-based hyperlink questions  
By J. Carlton Collins, CPA
July 2013

Q: Why is it that sometimes when I type a webpage address in Word, it converts to a hyperlink automatically, and sometimes it does not?

A: Word recognizes and converts to a hyperlink webpage addresses containing “www” (such as www.irs.gov), but not those that don’t (such as irs.gov). The next time your webpage address fails to convert to a hyperlink, insert a “www.” in front of the webpage address, position your cursor after the last letter of the webpage address, and press the space bar to convert it to a hyperlink. 

 
Q: I am baffled by this. I received a Word document containing a hyperlink, but when I click it, my browser says it cannot find the webpage, even though the web address is correct (see attached file). Can you tell me why this is happening?

 
A: If you look closely, there are forensic signs that suggest what is causing your hyperlink problem. Specifically, the hyperlink and the hyperlinked text do not match.

If you create a hyperlink in Word 2007 (or earlier Word editions), then later edit the hyperlinked text, Word 2007 does not automatically update the hyperlink to match that text. Thereafter, the hyperlink remains incorrect, even for users opening the document in Word 2010 or 2013. It appears that the author of your document originally created this hyperlink in Word 2007 (or an earlier edition), then edited the linked text.

Note: It is not possible to create this type of error in Word 2010 and 2013, as those editions of Word automatically update hyperlinks as the hyperlinked text is edited.

Q: I received the attached Word document (pictured below), but I can’t get the hyperlinks to work. What am I doing wrong?

 
A: The Word document you forwarded to me does not contain hyperlinks: The author of that document used blue text and underline formatting to make those web addresses look like hyperlinks. Either the author wanted to make that text look like hyperlinks for printing purposes, he doesn’t know better, or perhaps he sent this document to you on April Fools’ Day.


TECHNOLOGY Q&A
A large problem  
By J. Carlton Collins, CPA
July 2013

Q: Each day I export a list of outstanding accounts receivable balances consisting of several thousand customers, and it is my job to produce a report containing the customers with the 25 highest outstanding balances for follow-up. As illustrated below, the problem is that the accounting system produces a list of the customers, balances, and days outstanding (columns A–C below), but I have a separate list of the A/R customer contact information (columns O–R) that must be merged into the final report. Right now I sort columns A–C by outstanding balance and copy the top 25 rows to my report area. I then hunt and peck for the corresponding 25 rows of contact information, copying and pasting each one separately to my report. I’m sure there is an easier solution. Can you please give me a clue?
 


A: Excel’s Large function, coupled with the Match, Index, and Vlookup functions, can help you get the job done quicker. You’ve already done a good job of setting up your worksheet layout; you just need to fill in a few formulas using these four steps.

1. Eliminate duplicates. To eliminate the possibility of two identical outstanding A/R balances, add a unique fractional amount to each outstanding A/R balance, as follows. Insert two new columns (columns E and F in the screenshot below) labeled Fractional and Amount+. Number the Fractional column E sequentially, starting with the value “1.” In the Amount+ column, create a formula adding the outstanding A/R balance from column B to a fractional amount using the Fractional column E as the numerator and the value “10,000” as the denominator (as shown below), then copy the formula down.


This measure eliminates any identical balances that would otherwise cause problems using the Match or Vlookup functions.

2. Extract 25 largest values. In cell J2, enter the function =LARGE($F$2:$F$101,H2) to extract and return the largest outstanding balance, then copy the function down to cells J3 through J26. The Large function searches the list of values you specify and returns the value that corresponds to the rank you specify. In the example below, the Large function in cell J2 looks in column F for the Nth largest value, based upon the function’s second attribute, which references the value “1” in cell H2.

As this function is copied downward, the second attribute changes each time to reference the second-, third-, fourth-highest values, and so on until a list of the top 25 values has been reported.

3. Extract company names. In cell I2, enter the function =MATCH(J2,F:F,0) to return the company name corresponding with the outstanding A/R amount (be sure to add the 0 (zero) attribute to the end of the function to force an exact match).

The Match function compares the value (now reported in column J) against the list of outstanding A/R values (in column F), and returns the row number where that value is positioned (row 33 corresponds with the value reported in cell J2 in this example).

Again in cell I2, edit the formula by adding the Index function, which uses the information returned by the Match function to fetch the company name from the 33rd row in column A. The completed formula containing the two nested functions appears as follows:

=INDEX(A:A,MATCH(J2,F:F,0))

Copy this formula downward to the next 24 rows.
 
Note A: Ordinarily, the Vlookup is easier to use, but because the lookup value (in column F) is not positioned to the left of the company name (in column A), we use the Match and Index functions instead to avoid the need to rearrange the source data.

Note B: Notice that without the addition of the fractional values in step 1, this formula would erroneously return the same company name for duplicate outstanding A/R balances, which is why step 1 is necessary.

4. Extract remaining data. The remaining report data can now be easily extracted from the second table array (in columns O through R) using a Vlookup function with the company name as the lookup criteria. Enter the functions =VLOOKUP($I2,$O:$R,2), =VLOOKUP($I2,$O:$R,3), and =VLOOKUP($I2,$O:$R,4) in cells K2, L2, and M2, respectively, and copy them down.

The final result is that the desired report is completed using data from the two table arrays. As the data in columns A–C are updated, so too is the report, ranked in order of the top 25 largest outstanding A/R balances. 

Note C: Ordinarily, I would position the second table array (columns O–R) on a separate worksheet. Instead, I positioned all data and calculations on the same worksheet for simplicity.

Note D: In the Match, Index, and Vlookup functions, I referenced entire columns rather than the data range (for simplicity), which assumes there is no other data in that column.

Download this Excel file (2013 or 2003 versions) at carltoncollins.com/large.xlsx or carltoncollins.com/large.xls.  


TECHNOLOGY Q&A
Five iPad tips  
By J. Carlton Collins, CPA
July 2013

1. View full URLs. In Safari, holding your finger on any hyperlink for a few seconds reveals the full URL and also allows you to copy that URL.

2. Full-screen videos. You can increase the size of a video to full-size screen by tapping twice on the video. Pinch the video to return it to its original size.

3. The Google Maps curl on the iPad. It’s not completely obvious when viewing a Google map on your iPad, but swiping the curl in the bottom-right corner reveals a menu of options for viewing Classic, Satellite, Hybrid, or Terrain maps, as well as traffic overlays.

4. Pin a location. On the Google Maps app, hold your finger on any point on the map for a few seconds to place a pin. A pop-up then displays options for accessing street view, getting directions, or sharing that location via email.

5. Convert your iPad into a Picture Frame slideshow. To enable Picture Frame mode, tap the flower icon in the bottom-right corner of the iPad’s Lock screen. To adjust your slideshow, select Settings, Picture Frame, and then select your desired images, transitions, and options. As a promotional device, you might set up an iPad in your company’s lobby to continuously display slides of your company’s products, services, or history. Note: If you use a Passcode Lock, you’ll need to go to Settings, General, Passcode Lock, and slide Picture Frame to On to enable Picture Frame mode from the Lock screen.


View CommentsView Comments   |  
Add CommentsAdd Comment   |   ShareThis

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