A simple, do-it-yourself electronic tool.

Financial Reports
In a Snap

BY JEFF LENNING

ould you like to distribute financial reports throughout your organization electronically rather than on paper? Even better, would you like to give your reports drill-down capability that permits a user to get down to the sources and details of any number in the report? When you transmit the reports electronically, would you prefer not to use the Internet or an intranet—or any kind of e-mail technology at all? And, finally, would you like to set up this sophisticated reporting and distribution system yourself, using only your existing hardware and software?

You’ll be happy to know that—thanks to the hyperlink function of Microsoft Excel—all of the above is not only possible but is actually relatively inexpensive and easy to do. I know, because I’ve done it.

I’m the accounting manager of a small public company. One of my responsibilities is to provide management with monthly financial reports. Before I converted to the electronic system, I produced the reports on paper. The documents were voluminous, and there was no way they could provide the depth, flexibility and convenience of an electronic version. You might say that the high cost, inflexibility and inconvenience of the paper reports were the mother of the process described here.

Although the electronic process functions similarly to an Internet, its technology is totally embodied in Excel and an office network. Why use a network and Excel hyperlinks rather than an existing Web-based system? There are several reasons:

• Copying an Excel financial report to a network drive is simpler than converting the data to an HTML file and uploading it to a Web server.
• Since you can link Excel files to other Excel files, users retain the full flexibility and power of workbooks, including dynamic reports—in which any change in underlying data gets updated in the displayed data. In contrast, converting Excel reports to HTML files results in static reports with no automatic updating capabilities.
• Users retain full control of the data presentation, formatting and the printing setup.
• No additional hardware or software is needed.
• Excel hyperlinks can open any Microsoft Office file—Word, Access and PowerPoint.
• Users can drill down into reports for details.
• You can restrict access to files with network security.
• All the data are available with a click of a mouse and can be stored as archives in one place.

In addition, hyperlinks used with an Excel file can do more than just distribute reports. They provide links to, among other things, templates, forms and documents. During budget season, staff people can use them easily to call up budget templates, for example. Workers also can bring up expense report templates, phone lists or a repository of various company documents. Such a system is versatile and can be tailored to meet many needs, and users do not have to have any special expertise to activate it.

HOW IT’S DONE

Begin by creating an ordinary Excel file on your network. If you wish to follow the steps in this tutorial, call the file “index.xls.” Then place a shortcut to index.xls on the desktops of all personnel who should have access to your company’s financial reports.

Once the shortcut is created, a staff member simply double-clicks on the icon and the index opens to an Excel workbook that has hyperlinks, hidden gridlines and the look and feel of a Web site (see exhibit 1). As you’ll see, the index can contain links to various documents in the network. Besides using an index to provide financial data, I use an index of this sort to distribute information such as headcount reports, forms and operating information.

Exhibit 1

This Excel file—index.xls—contains hyperlinks to many financial documents, allowing a user to drill down into the source of the data.

Notice that the index.xls file created to illustrate this article contains two hyperlinks, Current Reports and Past Reports. As you’ll see later, a click on Current Reports evokes the current worksheet that, in turn, can access any of several financial documents, including an income statement, a balance sheet and a headcount report. Clicking on Past Reports brings up the archive worksheet, which contains prior months’ reports.

When setting up your own index.xls, remember that it will be the first file users see when they want a financial report. Therefore, keep it simple by hiding gridlines and the headers for rows and columns. You may want to include a company logo, as I have, and even add a related color scheme.

Exhibit 2

To keep the index screen clean and simple, remove gridlines and the row and column headers by changing the settings in Options.

Now place all the financial reports you want to make available through index.xls on your organization’s network. Be sure to prepare the appearance of each data file carefully—editing the format for easy viewing, setting up printer preferences, freezing panes as needed, locking down cells that should not be changed and deleting unnecessary sheets.

Continuing with our example, name the income statement file “is.xls,” the balance sheet file “bsheet.xls” and the headcount file “hc.doc.”

As you know, hyperlinks appear on a computer screen as blue underlined text. A cursor passing over a hyperlink changes to a hand, and when the user clicks on the hyperlink, the underlying file opens.

To create a hyperlink for the income statement, open index.xls and type Income Statement in the cell where you want the link to appear. Then go up to the toolbar and click on Insert, Hyperlink (shortcut: Ctrl K), evoking the Insert Hyperlink dialog box (see exhibit 3).

Exhibit 3

To create a hyperlink, open the Insert Hyperlink dialog box.

In the blank space under Link to file or URL, enter the location of the is.xls file. And then, by typing in the requested information under Named location in file (optional), you can even specify a location within the workbook. The location can be a particular sheet, a named range or a cell reference. In our example, we specify the Summary worksheet within the workbook. Create similar hyperlinks for each of the other data files.

Now when you double-click on the index.xls shortcut, you’ll bring up the screen shown in exhibit 1, and when you then click on the Current Reports hyperlink, you’ll see exhibit 4.

Exhibit 4

Clicking on Current Reports in exhibit 1 brings up this screen, which can take the user to either Financial Reports or Operating Reports.

Then, if you want to see the current Income Statement, simply click on the Income Statement hyperlink to evoke that report, as shown in exhibit 5.

Exhibit 5

Another click on the Income Statement hyperlink will bring up this file.

FILE NAME TIPS

Here are some efficient ways I’ve found to set up folders (subdirectories) and name files that work well with this procedure. I find it easy to update the index.xls each month by using the same file names for each monthly report and placing each month’s files into its own year-and-month folder. The reports all have the same name month after month: is.xls, bsheet.xls and hc.doc—only they are in different folders. Thus the December 1999 reports are placed in the 199912 folder, the November 1999 reports go into the 199911 folder and so on. This makes updating the index.xls with the current month reports easy, since the hyperlinks are edited to point to the current directory. The Exploring view of the folders looks like exhibit 6.

Exhibit 6

To make updating the index.xls easy, give each month’s file the same name, but store them in different folders.

NETWORK CONSIDERATIONS

You probably will want to restrict users from making changes to the index.xls or to any of the reports. To achieve that security, use a two-tiered approach. First, instruct your network administrator to assign read rights only (no write-rights) to users for the folders that contain index.xls and the reports. As an added precaution, in Exploring set the file Properties for the files to Read-only, as shown in exhibit 7.

Exhibit 7

To ensure that users don’t change the data in the financial files, set their file properties to Read-only in Exploring.

If you also want to limit who can see the reports, your network administrator can do this by assigning appropriate access rights only for the intended users.

In certain conditions, Excel will open the report and close the index.xls when the user clicks on a hyperlink. This occurs when there have been no changes to index.xls since the user opened it. You may prefer that Excel keep index.xls open in case the user wants another report. To achieve this, simply insert a cell in the index.xls as the current date. (Insert a formula =now() and format it as a date.) This will ensure that when a link is followed, your file will remain open.

However, the disadvantage of using the =now() method is that each time users close index.xls they will receive a Save changes? dialog box. Since the file is read-only they will not be able to make any changes, but they still will receive the irritating dialog box, which, of course, will require a No response. I use the =now() approach since the benefit of keeping the index open outweighs the cost of the Save Changes dialog box.

If your company has an intranet, you may want to incorporate your financial reporting index into it. You can do this by having your intranet administrator create a link to your index.xls. When a user selects this link from the intranet, Excel will open your index.xls.

If multiple users will be viewing the index.xls simultaneously, you may want to consider sharing index.xls. If you set up the index.xls workbook as a shared workbook, users will not receive the message The file is currently in use by another user. Open as read only? if it’s already open. Receiving this dialog box is not a problem because it’s a read-only file anyway. The disadvantage is that shared workbooks cannot modify hyperlinks. Therefore, each time you change index.xls, you will need to remove the workbook from shared status, make your changes and then set up the shared status again—a monthly inconvenience. In my case, I do not share the index.

I have implemented this technique at several companies, and it’s worked well, saving time and improving the flow of information through the organizations. The initial time investment has paid substantial dividends.