THE APPLICATION OF SPREADSHEET SOFTWARE TECHNOLOGY TO COMPLEX TAXPAYER ELECTIONS Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 219 THE APPLICATION OF SPREADSHEET SOFTWARE TECHNOLOGY TO COMPLEX TAXPAYER ELECTIONS Larry E. Watkins, Northern Arizona University ABSTRACT The expansion of personal computer availability combined with a desire to provide learning experiences with situational validity has stimulated the use of spreadsheet software in the accounting curriculum. This paper provides an example of spreadsheet technology applied to an environmentally realistic decision model, discusses the integration of spreadsheet software applications into a taxation course, arKi finally mentions some of the inherent drawbacks of their use. Specifically, this paper examines taxpayer elections concerning distributions from qualified pension plans. A Lotus 1-2-3 application is utilized to choose the election path arid decision model that provides the largest annual annuity to the plan participant. INTRODUCTION Personal computers (PCs) have become an influential arKi common tool in accounting. Accounting faculty and business students are using PCs in increasing numbers. The desire to use PC technology in the classroom, combined with the increased availability of powerful spreadsheet software, has stimulated the introduction of spreadsheet applications in the accounting curriculum [1]. It may well be that such applications will become as popular in the accounting curriculum as they have in practice. The purpose of this paper is to discuss the development of a software application that can be utilized in selected undergraduate arid graduate accounting courses. Specifically, a complex decision model relating to taxpayer elections will be discussed. In addition, some of the problems related to the use of such applications as a pedagogical tool will be presented. Students appear to benefit from an introductory statement that provides situational realism for the coming task. This desire for realism is even more pronounced in upper division and professional graduate courses. Accordingly, the software application presented below is prefaced with such an introduction. DISTRIBUTIONS FROM QUALIFIED RETIREMENT PLANS In a time of increased corporate takeover attempts, both successful and unsuccessful, individuals are being faced with the prospect of early retirement, transferring from one pension plan to another, or any one of several other options previously not considered. Accountants functioning as tax advisors or personal financial planners may be called upon to address the various alternatives available to their clients arid the related tax consequences of those alternatives [2]. The client’s objective in this election choice is to maximize the periodic payments available from the proceeds of the retirement plan for a specified number of years. Participants (clients) usually have two options available under terms of qualified plans. The first option is for the participant to leave the accumulates balance with the plan and receive monthly benefits (payments) from the plan for the designated number of years as an annuity. The amount of this annuity is known with certainty and would be specified by the plan. The second option provides for withdrawal of the accumulated balance as a lump sum. It is the lump sum option that generates the tax elections and therefore is the central point of the software application. Receipt of a Lump Sum and the Tax Computation If the participant chooses the lump sum distribution, he/she may pay tax on the distribution in the year the distribution is received or may defer tax by rolling the distribution over into an individual retirement account (IRA). It should be noted that the amount involuntarily contributed with after- tax dollars is excluded from taxation in all cases. If the participant chooses to be taxed currently several possibilities exist for the computation of the amount of tax due. The taxable portion attributable to pre-1974 participation in the plan is given the lower long-term capital gain treatment. Post-1973 participation amounts are taxed at the rates used for other items of ordinary income [3]. Code section 402(e) provides for computation of the tax using a special 10-year averaging given the participant meets certain criteria. To utilize the special averaging, the participant must have (1) attained the age of 59 1/2, (2) separated from service, (3) died or become disabled, and must have participated in the plan a minimum of five years prior to the taxable year of the distribution [4]. An individual may use the 10-year averaging only once subsequent to reaching the age of 59 1/2 but there is no limit to the number of times it may be used prior to reaching 59 1/2 [5]. Other restrictions do exist relevant to the 10-year averaging. If the participant elected the averaging method for a lump sum distribution received within six years of the current taxable year, the current year’s distribution must be combined with the previous distribution(s) when computing the tax. In effect this forces the participant into a higher bracket than if the previous distributions were not considered [6]. If the recipient receives two or more lump sum distributions during one taxable year (as with two or more pension plans), the 10-year averaging method must be used for all distributions or it is disallowed [7]. Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 220 The taxpayer may elect to forego the long-term cap- ital gain treatment afforded the pre-1974 portion of the distribution and use the 10-year averaging approach for the entire taxable portion of the lump sum amount. This could be consistent with a tax minimizing approach since in many cases the long-term capital gain deduction receives treatment as a tax preference item subject to alternative minimum tax [8]. Although this all sounds very complex, much detail has been admitted, such as the actual computation of the tax under the 10-year averaging election. Form 4972 is utilized for the tax computation subject to this election. Roll Over of a Lump Sum into an IRA If the participant makes the election to roll the distribution over into an IRA no tax is due on the lump sum distribution in the year received. Also, earnings on the amount in the IRA are not taxed. Instead, tax obligations are incurred when atiounts are withdrawn from the IRA [9]. An important point to remember is that generally, no amounts withdrawn from an IRA can receive long-term capital gain treatment or the 10-year averaging treatment even though they qualified for such treatment in the year they were initially distributed [10]. The lump sum distribution must be transferred to an IRA within 60 days of the date on which the recipient received the distribution. The amount that is eligible for roll over is limited to the cash and fair market value of property received from the qualified plan less nondeductible employee contributions [11]. Partial distributions may be rolled over into an IRA if (1) the distribution is at least 50% of the balance to the credit of the participant, (2) the distribution is not a series of payments, and (3) the election for special treatment is made. If this election is made, no portion of the distribution may be taxed using the long-term capital gain deduction or the special 10- year averaging method. All portions of the lump sum distribution not rolled over into an IRA are treated as ordinary income t12]. Leaving the Balance to the Credit of the Participant in the Qualified Plan The participant has the option of leaving the balance to their credit with the plan and receiving benefits in the form of an annuity. The balance to their credit must be received over either (1) the joint lives of the participant and their spouse or the life of the participant, or (2) a period not longer than the expected life of the participant or their spouse. If the participant contributed to the plan, an equal amount of each annuity payment received will be excluded from taxable income unless the participant’s contributions are fully recoverable within three years. In that case, the payments are considered a return of investment (tax-free) until all of the participant’s contributions have been returned. Subsequent payments received are taxed as ordinary income. If the participant made no contributions to the plan all payments received are considered ordinary income [13]. The foregoing is a fairly complete discussion of the tax considerations involving payments from qualified plans. Our attention will now turn to actual spreadsheet application and to integrating the application of this decision model into a taxation course. Lotus 1-2-3 Application The foregoing taxpayer election decision model was applied to Lotus 1-2-3 although other spreadsheet software such as SuperCalc or Multiplan are as appropriate [14]. An understanding of the software specific development of this application may be gleaned from the printout of cell formulas and a contrived solution that are provided in Appendix A. The user must input the following data to utilize the decision support system: (1) year entered into the retirement plan, (2) pretax lump sum amount at retirement, (3) pre-tax rate of return for investments, (4) tax rate during retirement, (5) life expectancy of participant or spouse, and (6) tax rate at date of retirement. The output from the decision support system consists of the annual after tax annuity which would be received under each of the three taxpayer election options. Discounting all qualitative considerations, it is anticipated that the participant would choose the alternative that yields the largest annuity payment. INTEGRATION INTO COURSES As mentioned earlier, an introductory statement that provides a realistic setting for the decision model makes the spreadsheet application more relevant from a student perspective. Students seem to be less resistant to PC usage if it can be shown that the application allows an analysis that would otherwise be time (cost) prohibitive. In this particular situation one could put the student in the role of an individual practitioner with a client seeking advice on the best approach regarding a distribution from a qualified plan. Give each student a different fact set and require a written analysis of the situation along with a letter to the client indicating the practitioner’s (student’s) recommendation. The initial evaluation of the student’s work would likely be an inspection of the analysis for accuracy. This could be completed with a small time commitment by using the fact set as input into the spreadsheet application previously developed. Once the instructor has ascertained that the students understand the technical issues and can successfully complete an analysis on their own, the students should be relatively comfortable with the next phase of the assignment. The next phase is a return to the practitioner setting. Inform the students that the client was so pleased with the perceived quality of the analysis that she told several of her co- workers. This in turn has facilitated a dramatic surge in potential new tax clients, all of which desire the same service as previously rerxlered. The magnitude of new business will require the accountant to turn away potential clients unless a way can be found to service then in a fraction of the time required for the first client. This is the opportune time to introduce the decision model spreadsheet application. In most cases a thorough explanation of the development of the decision support system would be provided rather than attempting to have the students develop the spreadsheet application themselves. Such development would typically consite too much class time for a traditional tax course. However, sane graduate tax programs currently have courses dedicated to Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 221 developing these skills. Actual development of the decision support application might be appropriate in an independent studies course that provides greater opportunity for one-on-- one interaction with the student. As with any computer application there can be undesirable consequences. A brief discussion of some of these problems is in order. PEDAGOGICAL PROBLEMS There are several problems associated with utilizing spreadsheet applications in accounting courses. Many of the problems are not unique to the accounting area but extend to all business courses utilizing PC technology. Many institutions simply do not provide adequate resources (PCs) to facilitate the widespread use of the applications under discussion. Considerable PC time must be made available to both faculty and students to ensure a worthwhile experience. The optimum alternative would be for each student and faculty member to have their own PC but at present few institutions find themselves in such a desirable position. Izard and Reeve identified several limitations in the utilization of spreadsheet software as a pedagogical tool [15]. The most serious of these is the time constraint, especially for faculty. Not only must the instructor learn the software package, but a great deal of time may be required to develop the decision support system. Sane very complex applications may take upward of 40 hours for development and testing. Many faculty do not have ‘this much time available considering the other institutional demands that exist. The application presented above demonstrates another potential problem identified by Izard and Reeve, that being reduced problem practice. Once a spreadsheet application has been developed and tested, students may generate solutions to any number of similar problems without thoroughly understanding the concepts underlying the software application. Avoidance of the repetitious practice that has traditionally been a part of the educational process may be a disservice to the student. SUMMARY This paper summarized the various taxpayer elections arid alternate decisions available to participants receiving distributions from qualified pension plans. The assumed objective of the participant was maximization of the annuity received subsequent to retirement. A decision support system using Lotus 1-2-3 was presented that provided for this maximization through optimal election path choice. An environmentally realistic approach for integrating this decision support system into an accounting course was provided. Unfortunately, problems are associated with spreadsheet software utilization in the curriculum. Some of the more common problems were discussed. Developments in Business Simulation & Experiential Exercises, Volume 14, 1987 222 REFERENCES [1] Izard, C. Douglass and James M. Reeve. “Electronic Spreadsheet Technology in the Teaching of Accounting and Taxation - Uses, Limitations, and Examples,” Journal of Accounting Education, Spring 1986, pp. 161. [2] See Lassila, Dennis R., and Karl B. Putnam. “Choosing the Appropriate Form of Retirement Income from a Qualified Plan,” Taxes, July, 1984, pp. 435- 443, for a more complete study of this issue. [3] Internal Revenue Code sec. 402(a) (2). [4] IRC sec. 402 (e) (4) (h). [5] IRC sec. 402 (e) (4) (B). [6] IRC sec. 402 (e) (2). [7] IRC sec. 402 (e) (4). [8] Lassila and Putnam, p. 436. [9] IRC sec. 408 (d). [10] IRC sec. 402 (a) (2) and (e). [11] IRC sec. 402 (a) (5) (B). [12] IRC sec. 402 (a) (6) (C). [13] IRC sec. 72. [14] Lotus 1-2-3 is a registered trademark of Lotus Development, SuperCalc is a registered trademark of SORCIM, and Multiplan is a registered trademark of Microsoft. [15] Izard and Reeve, pp. 69-71. Table of Contents Volume 14, 1987 Simulation Gaming as An Experimental Context for the Study of Multicriteria Decision Making How to Multiply Your Management Game Grading the Personnel In-Basket Exercise Simulation: The Players™ Perspective The Acute Susceptibility of Nominal Grouping to Negativity The Relative Value of the National ABSEL Meetings: An Analysis of Perceptions by Faculty and Deans A Survey of Behavioral Labs Used by American Business Schools A Structured Framework for Applying Conceptual Models to Problem Analysis: Hardening Up An Expert System Approach for Teaching Marketing Case Analysis From Theory to Practice: A Model for Teaching Beginning Advertising The Use of a Simple Forecasting Technique During an Interactive Computerized Business Game An Investigation of the Relationship Between Formal Planning and Simulation Team Performance and Satisfaction The Exponential Logarithm as an Algorithm for Business Simulations Learning macroeconomic Theory and Policy Analysis Via Microcomputer Simulation Laptop: A Principles of Marketing Simulation Teaching About Sampling in a Marketing Research Class Integrating Decision Support Systems and Business Games Demand Generation in a Service Industry Simulation: An Algorithmic Paradox ABSEL - At a Crossroads? Research on Predicting Performance in the Simulation An Expert System for Financial Planners "Developing Various Student Learning Abilities Via Writing, the Stock market Game, and Modified Marketplace Game in Beginning Macroeconomics" Professional Activity Reports: Getting back to Basics Œ A Preliminary Investigation "A Comparison of Performance, Attitudes, and behaviors of MBA and BBA Students in a Simulation Environment: A Preliminary Investigation" Decision Styles and Student Simulation Performance: A Replication Personal Power Strategies: An Experiential Exercise LTL I: A Trucking Simulation Game Total Enterprise Business Games: An Evaluation Can the Skill of Management be Taught? Developing Critical Thinking and Effective Communication Meaningful Notebooks and Enhanced Learning Using a Totally Menu-Driven Data base and Decision Support (DSS) Program Using a Computerized Menu-Driven Data Entry and fiWhat-Iffl Program as a Pedagogical Enhancement for Management Simulation Using a Joint Project Involving MBA Marketing Management and Undergraduate Marketing Research Students to Teach Marketing Research Testing the PAGE Technique: Results and Further Developments A Comparison of Student Perceptions with Accepted Expectations for Business Simulations "Conflict Management in Action: Verbal Strategies, Nonverbal behaviors and Conflict Styles" Dramatic Monologues as Surrogates for Experiential Learning Influencing One™s Superior: An Assessment and Exercise Personality Traits and Organizational Cultures: Two One-Hour Experiential Learning Exercises Computype: A Strategic Marketing Game Open System Simulations and Simulation Based Research "Teaching Forecasting to the Masses: Little Decision Support Training, But a lot of Answers" Goal Setting and Performance Evaluation with Different Starting Positions Œ The Modeling Dilemma The Gamesmanship of Pricing: Building Pricing Strategy Skills Through Spreadsheet Modeling Teaching MPR Experientially Through the Use of Lotus 1-2-3 Business Simulation Through Activities of a Manufacturing Company Simulated Stock Broker Teaching Employee Counseling Skills to Management Students: A Simulation Airline: A Strategic Management Simulation Developing Simulations and Experiential Exercises on the Personal Computer: Some Critical Issues A Powerful Tool Œ For What? An Integrative Systems Approach to Simulating Free Enterprise in a Small Business Course in a Business School Profits: The False Prophet The Use of Expert Systems to Develop Strategic Scenarios: An Experiment Using a Simulated Market Environment An Interdisciplinary Workshop: Spreadsheet Modeling and Case Analysis Techniques for Beginning Executive MBA Students An Innovative Approach to an O. D. Class Œ The Creative Interaction The Application of Spreadsheet Software Technology to Complete Taxpayer Elections Experiential Learning as the Cornerstone of an Industry Management Course "Identifying, Exercising and Enhancing Right Brain Skills in the Business Classroom" Utilizing Envisionary Technology in the Business Policy Course: An Experiential Pedagogy Business Simulation in the Policy Course: A Survey of American Assembly of Collegiate Schools of Business The Moving Van: Vehicle to Success of Disaster for the Two-Gender Work Force Oil and Gas Well Investment Analysis Using the Lotus 1-2-3 Decision Support System Team Cohesion Effects on Business Game Performance