Electronic spreadsheets: Powerful and flexible educational tools

ELECTRONIC SPREADSHEETS: POWERFUL FLEXIBLE EDUCATIONAL TOOLS Lawrence P. Kalbers WI I I ENHERC; l~NlVFRSl’l .Ah.,/rur 1; Electronic

spreadsheet5

become vehicles for solving numerou, while

accounting

requiring

accounting

topics.

them

offer man) opportunitio

claa exercks 7he)

and problems

pro\ ide accounting

to uw the traditional

thought

AND

Y

for the accountinacurriculum. and valuable student5

procevx

They

aid\ for student learning

of

with the power of the computer in understanding

and wiving

problems.

INTRODUCTION Many different, powerful electronic spreadsheets are now available for virtually all microcomputers, minicomputers, and mainframes. With little or no computer background a student or faculty member can become acquainted with a spreadsheet in a relatively short time. The rows and columns of the spreadsheet become tools for representing simple or complex relationships. Once the basic functions and instruction set of a spreadsheet are learned, the user is ready to deal with specific accounting problems. This is unlike asking a student to write a computer program to calculate depreciation, or to produce a bond amortization table. In programming, there would be two major hurdles. The first would be to understand and be competent in a computer language. Second would be to understand the accounting problems involved well enough to map out the solution in the context of a computer program. The spreadsheet eliminates much ofthe initial hurdle in that it is significantly easier to learn a spreadsheet instruction set than to become a computer programmer. The spreadsheet also provides a better educational experience than some “canned” software that gives a student an opportunity to find their way around the keyboard and little else.

CONCEPTS

AND PROBLEM-SOLVING

A major goal of any accounting course is for the student to understand underlying accounting concepts and then to use these concepts in solving problems. As a practical matter, the problem solving usually translates into solving exercises and problems at the end of textbook chapters and then on tests. Many times the problems will require students tocalculatea number or a series of numbers as part of the solution. In some cases this necessitates the

164

preparation of supporting schedules. More often than not these numbers or schedules represent part of a whole. For a depreciation problem with an asset life of ten years students may only be asked to calculate three years of depreciation. On a ten year bond amortization table students may be asked to do onfy three of four years. The expectation is that if a student can do the calculations for three years then the remaining years could also be done correctly. To force the student to do a long tedious schedule would be unproductive and repetitive. The final result is to have the student do several short schedules with different sets of information. What is missing, however, is the completion of the problem. The student is left with the assumption that continuation of the same process would result in the desired conclusion. They must have faith that the depreciation method will successfully amortize the cost of an asset down to salvage value and that the bond will be successfully amortized to the maturity value. The spreadsheet allows the best of both worlds. If the students are to set up a bond amortization table on the spreadsheet the only way they will be successful will be to understand why the certain columns exist and what the mathematical relationships among them are. This is also true of the traditional manual exercise of preparing a bond amortization schedule. The spreadsheet table, however, opens up many additional learning possibilities. First, if a student prepares a schedule incorrectly there may be no way to know this immediately. If a partial schedule is prepared, the fact that the schedule would not work out to the desired conclusion is not available. If a complete schedule is prepared and is incorrect the student has the option of starting over. This is very time consuming and frustrating. With the spreadsheet, if the schedule doesn’t work out it may require only one orjust a few changes either in numbers, columns, or formulas. Most of the spreadsheets available have many simple built-in functions to add columns and rows and to replicate numbers and formulas by columns and rows. Some even calculate means, standard deviations and net present values without any user-provided mathematical formulas. Any mathematical formula may be entered in any cell of a column or row. If a mistake is made the student needn’t start from scratch. Since the hard part of preparing any schedule on the spreadsheet is setting up columns and mathematical relationships, once this is properly determined the formulas may easily be replicated for as many periods as necessary. This means that all schedules prepared will be complete. Feedback as to the success or failure of preparing the schedule will be immediate. Second, once students have successfully completed the schedule they may study it in its entirety. This will reinforce their success and alfow them to look at the way in which the entire schedule works to completion. In this way, each homework problem is as good as a complete illustration in the textbook. Third, once the schedule for one kind of problem is successful, such as a bond amortization table, it can be used again to help solve other problems in the chapter. This will encourage not only the completion of all assigned problems, but makes doing extra problems less of a chore.

165

Fourth, and perhaps most important. is the real strength of the spreadsheet. This is the “what if’ aspect. Because the spreadsheet allows immediate recalculation of all numbers after any change of a number or formula, a series of “what if’ questions may be asked with quick answers. What if the salvage value changed? What if the interest rate changed? What if the number of years changed? What if sales increased by ten percent each year instead of eight? Along with discussions of these kind of relationships in the textbook and the classroom, students can experiment and find answers for themselves with little additional work. While students may put in the same amount of work as having done several problems, in a sense they will have done many problems through the “what if’ process.

APPLICATION

AREAS

The areas that the spreadsheet can be applied in the curriculum are numerous. Schedules, problems, and concepts that may easily lend themselves to use of the spreadsheet in introductory and upper level accounting courses are listed in Exhibit I. The list is in no way meant to be exhaustive. Exhibit Areas FINANCIAL * * * * *

Lending

Themselves

ACCOUNTING

TOPICS.

Income statements Worksheets Special journals Loan amortization schedules Depreciation schedules

* Payroll schedules * Mortgage amortization * * * *

1

to Use of the Spreadsheet

schedules

Bond amortization schedules Lease payment schedules Pension amortization schedules Earnings per share

MANAGERIAL ACCOUNTING TOPICS. * Breakdown analysis * Incremental cash flow analysis * Capital budgeting * Pro forma budgeting * Cost allocation

One example of a financial accounting topic that may be done with the spreadsheet is a bond amortization table. This table is made to amortize a premium or discount on a bond issued at proceeds other than par. Students may either be given the amount of the proceeds or may be required to

calculate the proceeds using present value techniques. Exhibit 2 shows the spreadsheet as outlined for a bond amortization table for a bond issued at a premium. The items at the top of the spreadsheet ask for input such as the yield (market) rate of interest, the stated rate of interest, the face value (Face), and the number of periods. The table was built assuming a five-year, 12%) bond with a face value of $500,000. The bond pays interest semiannually and was issued when the market (yield) rate of interest was 100/o.The rest of the spreadsheet is set up to calculate the proceeds of the bond issue and to set up a bond amortization schedule. Cells E4 and E5 calculate the present value of the lump sum (maturity value) and the interest payments. The two added together give the net proceeds of the bond issue. or original carrying value. The rest of the table sets up the normal relationships of a bond table that would be done manually. The Credit Cash column is the stated interest rate (cell B6) times the face value (cell E2). Once this formula (B6*E2) is entered once it can be repeated using the”replicate” function of the spreadsheet rather than reentering it in the other cells individually. Likewise, in the Interest Expense column. once the formula B3*El6 is entered, the remainder of the column can be replicated with 93 as an “absolute” value and El6 as a “relative” value rather than individually entering the other cells. Exhibit 3 shows the completed schedule with the numbers rather than the formulas. This schedule may now be changed easily to handle any bond premium amortization by merely changing the face value, periods, stated rate, and yield rate. If the bond has a longer or shorter maturity, rows can be easily added or deleted. An adjustment for a bond discount amortization can be made by changing the title and formulas in Column D under Bond Premium.

CONCLUSIONS The electronic spreadsheet offers a tremendous tool for the accounting educator and student. The problem of whether to have students do something manually or on the computer is to some extent solved. This is because the spreadsheet provides the power of a computer yet requires the thought process of doing it manually.

REFERENCES Thomas. Grading

Arthur

L. (19X3). “Use of Microcomputer

Complex

Accounting

Problems.”

Spreadsheet

Accounting

Software

Rewew (October

in Preparing 1983).

and

pp. 777-786.

0.06

Stated rate per period

2 Table

set for this spreadsheet.

SUM(C17:C26)

SUM(B17:B26)

2 3 4 5 6 7 8 9 10

in the instruction

B3”E16 B3*E17 B3*E18 B3*E19 B3*E20 B3*E21 B3*E22 B3*E24 B3-E25 B3*E26

B6”E2 B6*E2 B6*E2 B6*E2 B6*E2 B6*E2 B6*E2 B6*E2 B6-E2 B6*E2

Interest Expense

Cash

C

Period

Credit

0.05

B

Yield rate per period

(A) E2*(l/(l+B3)rE3) (B) l-(I/(l+B3)~E3-I)/B3*B17 Note: t indicates exponent

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

A

Exhibit Bond Amortization

SUM(D17:D26)

E25-D26

E4+E5 E16-D17 E17-D18 E18-D19 El g-D20 E20-D21 E21 -D22 E22-D23 E23-024 E24-D25

Value

B17-Cl7 B18-Cl8 B19-Cl9 B20-C20 821 -C21 B22-C22 B23-C23 B24-C24 B25-C25 B26-C26

Carrying

Bond

500000.00 10.00 (A) (B)

E

Premium

Face Periods PV-lump sum PV-interest

D

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 26930.43 26776.96 26615.80 26446.59 26268.92 26082.37 25886.49 25680.81 25464.85 25238.10 261391.33

30000 .oo 30000.00 30000 .oo 30000.00 30000 .oo 30000.00 30000 .oo 30000.00 30000 .oo 30000 .oo

300000.00

1 2 3 4 5 6 7 8 9 10

Debit Interest Expense

Period

0.06

Stated rate per period

3

Credit Cash

0.05

B

Yield rate per period

A

Exhibit Bond Amortization C

Table

38608.67

3223.04 3384.20 3553.41 3731.08 3917.63 4113.51 4319.19 4535.15 4761.90

3069.57

Debit Bond Premium

Face Periods PV-lump sum PV-interest

D

538608.67 535539.10 532316.06 528931.86 525378.46 521647.38 517729.75 513616.24 509297.05 50476 1.90 500000.00

Bond Carrying Value

500000.00 10.00 306956.62 231652.05

E