Get the Word Out
| January 28, 2014 | Comments Off on Get the Word Out
Get the Word Out
How do you distribute financial reports to clients, shareholders and key people in your organization? If you’re low-tech, you probably mail them. If you’ve been following my suggestions of several years ago, you create hyperlinks in Excel files and e-mail the files so clients can drill down into them for details (see “Financial Reports in a Snap,” JofA, Apr.00, page 31). But today’s technology lets you do it a better way: uploading all clients’ financial information into a Web site so they can access current and past reports 24/7, no matter how the data were originally formatted. If you want to learn how to do that, read on.
| EXECUTIVE SUMMARY | |
 CPAs can use Web sites to store many years of data, permitting clients quick and easy access 24/7. The sites, also called reporting portals, can handle data that are formatted in many different ways. Data that are otherwise hard to display can be formatted without any special technology so they can be read by a Web browser. A special function in Excel can transform a static file being uploaded to a Web site to a dynamic, or interactive, file. Typical dynamic worksheets are loan-payment calculators and pivot tables, whose data adjust when their underlying information change. Easy access to sensitive client data comes with a price. You don’t want such information to be easily accessible to everyone, so you must implement very effective security measures. If your organization’s Internet site does not have effective security, you have two choices: Hire a Web consultant with security experience or rent a professionally managed site and let its specialists set up a security system for you.Jeff Lenning, CPA/CITP, is the founder of Click Consulting
|
| Key to Instructions
To help readers follow the instructions in this article, we use two different typefaces: Boldface type is used to identify the names of icons, agendas and URLs. Sans serif type indicates commands and instructions that users should type into the computer and the names of files. |
As you know, client data come in different formats. For example, tax software programs calculate and display returns with proprietary technology, and usually are able to convert themselves into the free Web-friendly Adobe Acrobat (files with a .pdf extension). Tax professionals usually prepare support data for schedule D basis computations in Excel, which isn’t native to a Web browser, but it, too, can convert itself to hypertext markup language (HTML) so browsers can read it. P&L statements, which CPAs usually calculate and display with proprietary accounting software programs, also can translate themselves into Adobe Acrobat. Thus CPAs can easily store this virtual Tower of Babel of financial information, and clients can easily access it any time of the day or night via a Web site without any special technology.
|
|
A Web site also lets you extend Excel’s presentation in a very powerful way. If you display a static worksheet, such as a balance sheet, and change only a single number on it, you have to replace the entire worksheet. But a special function in Excel transforms a static file being uploaded to a Web site into a dynamic, or interactive, file. Typical dynamic worksheets are loan-payment calculators and pivot tables, whose data adjust when their underlying information changes; there’s no need to upload a whole new worksheet.
A BANK OF DATA
NOW that we’ve described the flexibility and diversity of information that can be stored on the Web, let’s see what such a site, also called a reporting portal, looks like. Exhibit 1 shows a screenshot of a typical opening page of a portal created by a CPA firm. As you can see, it contains three years of data—2003 to 2005—for client John Smith.
NOW that we’ve described the flexibility and diversity of information that can be stored on the Web, let’s see what such a site, also called a reporting portal, looks like. Exhibit 1 shows a screenshot of a typical opening page of a portal created by a CPA firm. As you can see, it contains three years of data—2003 to 2005—for client John Smith.
Exhibit 1
A click on any of the years’ icons drills down to three further choices for that period: a .pdf copy of the client’s 2004 final tax return, an HTML version of an Excel file on schedule D basis support and a .pdf version of final QuickBooks financial statements (Exhibit 2).
Exhibit 2
If a client is applying for a mortgage, say, and needs a copy of last year’s tax return and a current P&L statement, all he or she has to do is open the portal and click on a few icons for immediate access.
CONVERT AN EXCEL FILE
To transform an Excel worksheet so it can be viewed by a browser, begin by opening the target file, which has an .xls extension (see Exhibit 3).
To transform an Excel worksheet so it can be viewed by a browser, begin by opening the target file, which has an .xls extension (see Exhibit 3).
Exhibit 3
Then click on File, Save as Web Page (Exhibit 4)
Exhibit 4
That launches the first screen of a Save As wizard (Exhibit 5) that performs the actual conversion. In the File name box add a name; I used bsheet.htm. Be aware that the original Excel file (.xls) is left intact. You can select how much of the spreadsheet to include in the new bsheet.htm. You’ll get the entire workbook if you place a check next to Entire Workbook or just the current worksheet if you check Selection: Sheet. Click on Save.
Exhibit 5
When the .htm page appears, it will be an exact visual duplicate of the original file right down to the formatting of the double underlines (Exhibit 6).
Exhibit 6
Finally, to make the report available for viewing, upload bsheet.htm to your Web server.
INTERACTIVE DISPLAY
To prepare a dynamic display for the site, create an interactive file, such as a loan-payment worksheet (see Exhibit 7), which uses Excel’s =pmt() function in C6 to perform the dynamic calculations.
To prepare a dynamic display for the site, create an interactive file, such as a loan-payment worksheet (see Exhibit 7), which uses Excel’s =pmt() function in C6 to perform the dynamic calculations.
Exhibit 7
Then follow the same steps for a static file except place a check in the Add interactivity checkbox (see Exhibit 5).
To specify additional options, click on the Publish button (see Exhibit 5) to produce the dialog shown in Exhibit 8, below.
Exhibit 8
Make sure you select Spreadsheet functionality from the Add interactivity with option. The other choices in that menu are Pivot Table functionality, which allows you to display pivot tables, and AutoRepublish option, which automatically uploads to the site any revisions you make in that workbook.
Click on Publish to close the wizard and the loan-payment workbook is ready for use.
Alert: In order to use the dynamic worksheet you must access it with Microsoft’s Internet Explorer browser; it will not work with Firefox or any other browser.
THE SECURITY ISSUE
Easy access to sensitive client data comes with a price. After all, you don’t want such information to be readily available to everyone. So you must implement very effective security measures to be sure only authorized users get at it.
Easy access to sensitive client data comes with a price. After all, you don’t want such information to be readily available to everyone. So you must implement very effective security measures to be sure only authorized users get at it.
If your organization’s Internet site is of a do-it-yourself variety, it’s not likely to have an effective security feature. That leaves you two choices: Engage a Web site consultant with security experience, or instead of using your own site, rent a professionally managed site and let its specialists set up a sophisticated security system for you. For a list of vendors that provide such services, see “Space for Rent” below.
As you can see, using a reporting portal provides you with a convenient place to store many years of data and allows your clients to view information, even if it’s formatted in proprietary ways. 
| Space for Rent
Vendors that rent Web sites:
 Accountants Office (www.accountantsoffice.com) provides Web sites with CyberCabinet online file sharing. Prices start at $695 per year. CCH SiteBuilder’s (www.cch.com) software, ProSystemfx File Share, allows secure file sharing. The first 50 megabytes (Mb) of storage space are included with the basic subscription. Additional space is available in 100 Mb increments for about $100 a year. Free trial is available. CPA Site Solutions (www.cpasitesolutions.com) offers Web site templates with a secure online file-exchange portal. Prices start at $50 per month. Creative Solutions Thomson (www.creativesolutions.thomson.com) offers two products, Web Builder CS and NetClient CS, that provide clients with access to their financial reports through the Internet. Demos are available on its Web site. Prices start at $120 a month, with a one-time setup fee of $500. IKE (www.ike.com) offers secure file sharing. A 15-day trial is available. Prices start at about $100 a year. Webex Weboffice (www.weboffice.com) provides powerful collaboration services, including file sharing. A 30-day trial is available. Prices start at about $50 a month. XDrive (www.xdrive.com) lets clients share files securely. Free trials are available. Prices start at about $100 a year. |
Posted in
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
