|Terry J. Ward, is an associate
professor of accounting at Middle Tennessee State University in
Murfreesboro, Tennessee. |
Jon Woodroof, CPA, PhD, is an assistant professor of accounting at the same university.
If your company or client is required to track the current market value of securities it owns, then you know what an irksome, time-consuming task that can be. But now, with the help of the Internet, the task can be accomplished almost automatically.
Under Financial Accounting Standards Board Statement no. 115, Accounting for Certain Investments in Debt and Equity Securities, businesses must account for the securities in their financial statements. Using two computer tools—the Internet and spreadsheet software—you can do the job with little more than a few mouse clicks.
Statement no. 115 applies to all investments in debt and equity
securities with readily determinable fair values. (For more details,
see "FASB 115: Its Back to the Future for Market Value
Accounting," JofA, Sept.93, page 49.) It requires companies to
classify all securities investments that are not accounted for under
the equity method into three categories: trading, available for sale
or held to maturity. On top of that, companies must use a market value
approach to account for investments held for trading or available for
sale. Under that approach, companies must periodically adjust their
investments to fair market value. A valuation allowance account is
then used to adjust investments in securities to market value on the
balance sheet. The primary difference between accounting for
investments held for trading and accounting for available-for-sale
investments is that any holding gain or loss to adjust the investment
to market value is shown on the income statement for trading shares,
while available-for-sale stocks accumulated holding gains or losses
are reported as a separate component of shareholders equity on the
LETS GO TO THE INTERNET
While the Internet has several portfolio programs—America Online, for example, has a popular one—we selected one developed by Ethos Corp. This site allows a company to create, at no charge, a customized portfolio list of stocks and mutual funds, and all prices are updated automatically. When needed, this information can be imported into a spreadsheet template that generates accounting information in compliance with Statement no. 115. An example spreadsheet template, together with instructions on how to access this information service and transport the data, is detailed below.
We chose to work with Microsofts Excel 5.0 for Windows. However, with some modification to the parsing technique, a template of this type can be used in any popular spreadsheet.
There are two worksheets in this template: Trading Stock (see exhibit 1 ), which contains the formulas, and the parsing worksheet in which you paste market information copied from the Internet. The only column in which you enter data directly is labeled Allowance at 12/31/95. Here you enter the valuation allowance for the previous year. The other columns contain formulas that retrieve information from the parsing page.
To get the latest market information, tap into the Internet and go to http://www.irnet.com/pages/login.htp. This Web page ( exhibit 2 ) asks for a login name and a password. If you are new to this Web page and do not have a login name and password, pull up the screen that invites you to join at no charge. After entering a unique login/password combination, press the button labeled SUBMIT, which brings up a screen (see exhibit 3 ) that asks you to customize either a stock or mutual fund portfolio. After selecting one, you are presented with a screen ( exhibit 4 ) that invites you to enter each securitys symbol, the quantity and the purchase price.
BOOKMARK THE ADDRESS
Each login/password combination entitles you to obtain market information for up to 15 stocks and 15 mutual funds. Companies can track additional securities simply by registering under different login/ password combinations. Each time you enter a line on your portfolio, press the UPDATE button. This will refresh the screen, adding the stock or mutual fund to your portfolio. You can now bookmark the address of this page in your Web browser for later retrieval.
The 10-stock portfolio in exhibit 4 shows, in addition to the data you entered, the total original purchase price, per-unit and total current market price, todays change in market value and total change in value of the investment. This information must now be converted into spreadsheet-readable data.
When you need to prepare a report, select the range of information to copy to the Windows clipboard (by pressing Ctrl C or by going through the Edit menu). Then load the spreadsheet application, retrieve the template and go to the parsing page. With the cursor positioned in cell A1, paste the entire contents of the clipboard. The result, with unparsed data, should look like exhibit 5 . Then, to parse the data, move your mouse arrow to the Excel menu and click on Data, Text to Columns and Finish. This sequence of mouse clicks parses the strings copied to column A into the appropriate columns so the information can be retrieved by the formulas on the Trading Stock page. The results of parsing are shown in exhibit 6 .
Now return to the Trading Stock page for the updated results (see exhibit 7 ). This same procedure can be followed for tracking stocks or mutual funds that are available for sale. Exhibit 7 contains the completed schedule using the Internet information. The schedule contains the original cost of the investment, the current market value of the investment, the current valuation allowance account balance needed to adjust the investment account to market, last years valuation allowance account balance, this periods gains or losses and the accumulated gains and losses on the investments.
Those using Lotus 1-2-3 for Windows would enter all the same formulas as the ones shown for Excel. However, when parsing in Lotus, highlight the data to be parsed and then, on the Lotus main menu, click on Range, Parse, Create and OK.
The valuation allowance account adjusts the investment account on the balance sheet to market. The difference between last years valuation allowance balance and this years balance is this periods holding gain or loss, which would be reported on the income statement for trading stock. For example, in this case the holding gain for 1996 is $1,350. Because the periodic gains or losses are reported on the income statement and included in retained earnings, the accumulated gain or loss balances are not reported for trading stock. The accumulated gain or loss accounts are reported in stockholders equity for available-for-sale stock. For example, if the stocks were all available for sale, the company would report a net holding gain of $2,450 ($2,8002$350) in stockholders equity at the end of 1996. Still, companies need to maintain accumulated gain or loss accounts for trading stock to account for stocks reclassified to available-for-sale stocks.
Companies can use one portfolio to account for all investments in stock (trading and available for sale) and then separate out these shares at the end of the period. Or they can develop separate portfolios for trading and available-for-sale stocks.
As you can see, the Internet is a valuable tool. Its beginning to revolutionize the way organizations do business and the way accountants do their work. CPAs should be prepared to take advantage of the Internetfor both their own work and for their clients and employers.