ShareThis
|
TECHNOLOGY Q&A
By J. Carlton Collins, CPA
January 2013

Q: We produce a monthly report in Excel summarizing the previous 12 months’ accounts-receivable balances for each of our 2,600 customers, and it is my job to analyze this data to identify growing A/R balances that indicate customers who might require special attention. Ideally, I’d like to chart each of the 2,600 rows of data so I can visually identify customers whose outstanding balances are trending upward, but creating and updating this many charts is too time-consuming to be practical. Can you suggest a different approach?

A: Excel 2010 provides a new tool called Sparklines, which enables you to create a small chart within a single cell and quickly copy that chart to other cells. Using this approach, you could create 2,600 charts in just a few seconds. As an example, I created a workbook containing 12 months of A/R balances for 2,600 customers (pictured in columns A through M below). To create the Sparkline, I placed my cursor in cell N2, and, from the Insert tab, I selected Line from the Sparklines tools group, highlighted the data range (B2:M2 in this example), and clicked OK.

This action produced a small line chart in cell N2. Next, I copied the Sparkline in cell N2 and pasted it to the remaining 2,599 cells below to display 2,600 line charts depicting trends for each customer’s A/R balance.

Note: Sparklines are sometimes easier to read if you increase the column width and row height of the cells containing Sparklines. Additional Sparkline settings allow you to change a Sparkline’s color and width, emphasize high points, display the Sparkline using a column format, or adjust other settings. Two examples of these additional settings are pictured below.

While Sparklines may add readability to your analysis, I also would recommend adding a formula to help you identify specific customers with growing A/R balances. For example, in column O below, I used the IF function to add the calculation =IF(M2/AVERAGE(J2:L2)>1.1,"Attention","") comparing each customer’s most recent A/R balance to the average balance for the previous three months. If the current A/R balance exceeds the average balance for the preceding three months by more than 10%, then the word “Attention” is displayed. (Of course, you would need to decide how many months to use in computing your average, and the percentage threshold that warrants attention.)

TECHNOLOGY Q&A
Green computing tips
By J. Carlton Collins, CPA
January 2013

Q: Should we be concerned about the amount of energy our computers consume, and if so, what measures can we take to conserve energy and reduce the cost of operating our computers?

A: Of course, we should all strive to use energy efficiently. Accordingly, listed below are possible computer-related tips and measures that will help you conserve energy and save a few dollars:

1. Upgrade CRTs to LCDs. Compared to older CRT monitors, today’s flat panel LCD screens are not only easier on the eyes, they consume about 70% to 75% less energy (assuming similar size CRT and LCD screens), which, depending on amount of use and the cost of electricity, could translate to an annual energy savings of approximately \$15 per year per monitor (assuming a 20-inch LCD monitor is used four hours a day at the average electrical rate of 15 cents per kilowatt hour).

2. Adjust your monitor power settings. Even when using LCD screens, your monitor typically consumes two-thirds of your computer’s energy needs. If you can do so without affecting your productivity, decrease your monitor’s brightness setting. Some monitors (usually laptops) allow you to adjust your settings so the monitor dims after a couple of minutes of inactivity. Also, make sure that your monitors are set to automatically turn off after 10 to 30 minutes of inactivity. Keep in mind that on laptops, Windows provides additional power-setting options depending on whether you are plugged in or running on battery (pictured below).

Note: Be aware that while screensavers may black out your screen, the monitor remains on and continues to consume electricity; hence, the option to automatically revert to standby mode saves more energy than does the screensaver option.

3. Turn monitors off. Some monitors use a significant amount of power even in standby mode. If you are like me and use large monitors (I use two 40-inch LCD monitors on my desktop computer system), then turning off your monitors instead of allowing them to go into standby can conserve a meaningful amount of energy over the course of a year.

4. Computer power management. Some computers offer stronger power management options than Windows does. For example, my Lenovo desktop’s power settings (pictured below) enable me to also manage the power usage of my wireless adapter, video card, USB drive, and other settings. You should explore your computer system for additional energy-saving options.

5. Draft mode printer setup. Set up your printer twice in Control Panel; the second time, set the default printer settings to draft mode and name that printer setting "Print in Draft Mode". That way you can reduce your toner usage by as much as 75% by printing draft copies using this printer selection. This tip may not reduce energy consumption, but it will save money via reduced toner use and may reduce the toner and cartridges that eventually wind up in landfills.

6. Turn printers off. If you are like me and use your color printer only occasionally, keeping it turned off until you are ready to use it can save a meaningful amount of energy. In a RISO Inc. report titled The Hidden Costs of Copiers and Printers, it was reported that \$107.88 worth of energy is required for a typical laser printer to print 100,000 pages, and \$382.50 worth of energy is required for that same printer to stand by in idle or sleep mode the remainder of the year. (Note: RISO sells energy- efficient printers and is therefore not independent with respect to this study, but the reported calculation methods it used appear reasonable.)

7. Hard drive settings. In Control Panel, you might adjust your hard drive so that it stops spinning after approximately 10 minutes of inactivity to conserve energy (the default setting is 20 minutes).

8. Upgrade your hard drive to SSD. One of your best energy-saving measures might be to upgrade to a computer with a solid-state drive (SSD). This step will not only help your computer perform faster, but SSDs also use almost no energy when idle.

9. Keep external hard drives turned off. If you have an external USB hard drive connected to your computer for backup or archive purposes, turning it off until you are ready to use it will conserve energy.

10. Microsoft Windows 8. Windows 8 is reportedly up to 12% more energy efficient than Windows 7 when installed on today’s energy-efficient computers, and far more efficient on older computers. Note: Windows 8 requires a CPU running at 1GHz or faster and at least 1 gigabyte (32-bit) or 2 GB (64-bit) of RAM.

TECHNOLOGY Q&A
Touchy underlines
By J. Carlton Collins, CPA
January 2013

Q: Because I think the solid underlines on my Excel-based financial reports look less professional, I insert thin, blank columns between my data so the underlines don’t touch one another. This process of inserting and reformatting columns is tiresome. Is there another way to produce the underline breaks I want without having to insert blank columns between my data? The screenshots in the next column provide an example of my data before (with solid underlines) and after inserting blank columns (with underlines that don’t touch).

A: There is a slightly better option. Excel’s Accounting Format was designed for accountants, and it allows you to insert nontouching single and double underlines in adjacent columns. Below are the five steps needed to apply this format using your example data:

1. Add double underlines. Highlight the total row, right-click on the total row, select Format Cells from the pop-up menu, and then on the Font tab, select Double Accounting from the Underline dropdown box, then click OK.

2. Add single underlines. Highlight both the header row and the row above the total row (hold the Ctrl key down to select multiple ranges), right-click anywhere on the highlighted range, and then select Format Cells from the pop-up menu. Next, on the Font tab, select Single Accounting from the Underline dropdown box, then click OK.

3. Format the numerical data. Highlight the numerical data, right-click anywhere on the highlighted range, and select Format Cells from the pop-up menu. Next, on the Number tab, select Accounting from the Category list box, set the Decimal places spinner to 0, select the dollar symbol (\$) in the Symbol dropdown box, and click OK. (Note: After applying the accounting format, not only do commas and dollar signs appear, but the single and double accounting underlines will also resize to match.)

4. Remove unwanted dollar signs (optional). Highlight the numerical data where you want to suppress the dollar signs, right-click anywhere on the highlighted range, select Format Cells from the pop-up menu, and then on the Number tab, select None from the Symbol box and click OK.

5. Control text underlines. Notice in the image at the bottom of the previous column that the header underlines are larger than the numeric underlines. To correct this problem, highlight the header row, right-click anywhere on the highlighted range, select Format Cells, and on the Number tab, select the Accounting format from the Category list box. Note: This step may sound strange, but you must format the header text using the accounting format in order for the size of the underlines below the headers to match the underlines in the numeric data. These steps will produce the desired format in adjacent columns, as pictured below.

While these steps may also be tiresome, this approach eliminates blank columns, making it easier to navigate using the End+Arrow Keys combinations, and to perform other operations such as sorting, filtering, and subtotaling.

TECHNOLOGY Q&A
By J. Carlton Collins, CPA
January 2013

Q: How can we determine if any of our employees are visiting inappropriate websites at the office using company-supplied computers and internet access?

A: The process of browsing the internet leaves telltale breadcrumbs on your wired or wireless routers, enabling you to monitor web activity by user. Almost all routers possess the ability to capture each user’s unique IP address along with the date, time, and URL for each webpage the user visits. As an example, following are steps for logging in and configuring the NetGear Wireless-N router to capture and send you log files. Note: These steps will vary slightly depending on the brand of router you use.

Note: Some CPAs find the process of using a browser to log in to their router confusing because it appears that your router’s settings are maintained on the internet. This is not the case; despite the use of a browser to access your router’s menu, that menu is maintained on the router itself, not the internet.

2. Enable monitoring. To capture user activity logs and have them emailed to you at regular intervals, select E-mail from the router menu, and check the box next to Turn E-mail Notification On. Enter the necessary outgoing mail server, email address, and authentication information in the Send Alerts and Logs Via E-mail section, and then indicate the desired reporting interval in the Send Logs According to this Schedule section. (I selected Hourly in this example.)

3. Set up a blocked site. My NetGear Wireless-N router requires me to block at least one keyword or website before it will start capturing any user activity (your wireless router may or may not require this step). To block a keyword or website, select Block Sites from the menu and enter a phrase or website name into the Type Keyword or Domain Name Here box. (In this example, I have blocked the fictional website violentgames.com.) Once a blocked phrase or website is set up, my router starts capturing (or logging) all users’ internet activity.

4. Review log files. Later, after your router has had time to log user activity, you can review that activity by selecting Logs from the menu, an example of which is pictured atop the next column.

The log reports each user’s IP address along with the webpages visited by date and time. (In this example, the report shows that user 192.168.1.12 visited the webpage media.journalofaccountancy.com on Sept. 12, 2012, at 9:07 a.m.)

5. Identify a user’s IP address. To match an IP address to the respective computer, select Attached Devices from the router menu to display your network’s IP Address and Device Name list, as pictured below. In this example, we find that user 192.168.1.12 is associated with the computer named CARLTON_IBM, which identifies the computer using that specific IP address, and thus the user.

Alternatives:

1. Review your employee’s computer. Usually, you can also track a user’s web activity by examining his or her computer, depending on the browser, email, and computer archive settings. To do this, review the computer’s search history, cookie history, temporary internet files, emails sent and received, junk email folder, and recycle bin for inappropriate activity.

2. Install monitoring software. You might consider installing web monitoring software such as Spector Pro (\$99.95) (spectorsoft.com), WebWatcher (\$97) (webwatchernow.com), or PC Pandora Pro (\$69.95) (pcpandora.com) for more professional control and web monitoring.