Enhancing Tax Analysis Skills Through Excel-Based Scenarios

Editor: Annette Nellen, J.D., CPA, CGMA

A joint task force of the AICPA and the American Taxation Association (ATA) issued a revised Model Tax Curriculum (MTC) in May 2014. This represents the fourth iteration of this valuable resource for accounting educators.

The document highlights both the importance of, and challenges for, the study of taxation. The MTC has provided guidance to educators since 1996, complementing other resources such as the Accounting Education Change Commission report, the AICPA Core Competency Framework, the Association to Advance Collegiate Schools of Business Assurance of Learning standards, and the ongoing work of the Pathways Commission. Despite this encouragement and support, however, the MTC notes that “significant gaps in fundamental principles and skills remain.”

Researchers have explained this shortfall by noting the dynamic state of the business environment and growth in technology and the resulting inability of accounting education to keep pace (Wessels, “A Critical Learning Outcome Approach in Designing, Delivering and Assessing the IT Knowledge Syllabus,” 19 Accounting Education 439 (October 2010)). The issue must be considered from the viewpoint of both accounting and tax educators. From a tax perspective, the MTC emphasizes that all accounting students should develop basic tax knowledge “because taxation is pervasive, complex and critical to decision-making” (MTC, 2014).

The MTC recommends a variety of active learning approaches to achieve the document’s learning outcomes and as a basis for future learning. Similarly, researchers have explored pedagogies from a number of perspectives. For example, the MTC and the Core Competency Framework recommend a broader approach to tax education and active student involvement, an approach that supports a skills-based curriculum and enhances lifelong learning (Hite and Hasseldine, “A Primer on Tax Education in the United States of America,” 10 Accounting Education 3 (2001)). Educators can enhance student-centered learning through an interactive environment such as computer-based instruction (Larres and Radcliffe, “Computer-Based Instruction in a Professionally-Accredited Undergraduate Tax Course,” 9 Accounting Education 243 (2000)). Educators can also develop abilities with information communication technologies such as spreadsheet software through active learning methods, thereby improving skill sets desired by employers (Ling and Nawawi, “Integrating ICT Skills and Tax Software in Tax Education,” 27 Campus-Wide Information Systems 303 (2010)). Panelists at an accounting conference on professional judgment emphasized the benefits of developing accounting intuition, building problem-solving skills, using experiential hands-on learning, focusing on process rather than outcomes, and learning to choose between alternatives (Correll, Jamal, and Robinson, “Teaching Professional Judgement in Accounting,” 6 Accounting Perspectives 123 (2007)).

Educators frequently seek to accomplish learning outcomes through the traditional use of textbooks and, specifically, end-of-chapter (EOC) material. An emphasis on EOC material, however, can result in a narrow focus on students’ technical knowledge and a lack of development of higher-order cognitive skills (Gupta and Marshall, “Congruence Between Entry-Level Accountants’ Required Competencies and Accounting Textbooks,” 14 Academy of Educational Leadership Journal 1 (2010)). Higher-­order skills, according to the classification developed by Benjamin Bloom in 1956, include analysis of data, application of knowledge, choosing among alternatives, and the process of evaluation (Davidson and Baldwin, “Cognitive Skills Objectives in Intermediate Accounting Textbooks: Evidence From End-of-Chapter Material,” 23 Journal of Accounting Education 79 (2005)). As the MTC Task Force reorganized and reworded the MTC learning outcomes, it did so based on Bloom’s taxonomy of learning.

The assignment that follows represents a modest attempt to address within a first course in federal income taxation of individuals the MTC’s concern for delivering basic tax knowledge and developing student skill sets. The assignment requires students to use tax-compliance software to generate multiple Form 1040 tax returns and Excel to summarize and graph output from the returns. From an analysis and application standpoint, the assignment challenges students to carefully consider a series of questions designed to enhance their understanding of some of the intricacies of the underlying tax law. Given the length of the assignment, the authors recommend dividing students into two- or three-person teams.

Assignment Description

After the instructor provides students a basic understanding of the individual tax formula and a primer on entering information into selected tax-­compliance software, students receive an Excel spreadsheet detailing income and expense items for a married couple filing a 2013 joint return (see Exhibit 1). The input spreadsheet allocates total income within a $100,000 to $1 million range among these categories:

• Salary (50%);
• Schedule C income (25%);
• Taxable interest (5%);
• Tax-exempt income (5%);
• Qualified dividends (5%);
• Net long-term capital gain (NLTCG) (5%); and
• Schedule E income (5%).

The following highlighted items on the input spreadsheet are fixed:

• Mortgage interest;
• Property taxes;
• Medical expenses;
• Charitable contributions;
• Safe deposit box rental fees;
• Exemptions;
• Dependent ages and higher-education expenses; and
• Estimated state tax payments on Jan. 15, 2013.

The remaining input spreadsheet items (federal and state withholding, federal and state estimated tax payments, and itemized deductions for state income tax, investment advisory fees, and investment interest expense) are automatically calculated. Students should review the assumptions built into the underlying cell formulas, particularly the assumptions used to derive estimated federal tax payments.

The instructor can next require students to perform the following tasks:

Part I

Enter data from the input spreadsheet into the tax-compliance software to generate 10 separate returns corresponding to each of the 10 income levels. Students can expect to generate the following forms and schedules, depending on the scenario:

• Form 1040, U.S. Individual Income Tax Return;
• Schedule A, Itemized Deductions;
• Schedule B, Interest and Ordinary Dividends;
• Schedule C-EZ, Net Profit From Business;
• Schedule D, Capital Gains and Losses;
• Schedule E, Supplemental Income and Loss;
• Schedule SE, Self-Employment Tax;
• Schedule 8812, Child Tax Credit;
• Form 2106-EZ, Unreimbursed Employee Business Expenses;
• Form 4952, Investment Interest Expense Deduction;
• Form 8863, Education Credits;
• Form 8959, Additional Medicare Tax; and
• Form 8960, Net Investment Income Tax—Individuals, Estates, and Trusts.

For simplicity, students should enter the Schedule C income as a gross receipts amount and the Schedule E income as the taxpayer’s share of ordinary income from a partnership. Note that the scenario assumptions, while somewhat artificial, do allow for meaningful comparisons across income levels.

After completing and saving the tax returns, construct a summary output spreadsheet reflecting the following for each total income level:

• Income and deduction items:
  • Tax-exempt interest;
  • Gross income;
  • Deductions for determining ­adjusted gross income (AGI) (“above the line”);
  • AGI;
  • Itemized deductions;
  • Exemptions; and
  • Taxable income.
• Tax items:
  • Regular tax;
  • Alternative minimum tax (AMT);
  • Refundable tax credits;
  • Self-employment tax;
  • Additional Medicare tax;
  • Net investment income tax; and
  • Total tax.

For analysis purposes, students should also express each of the above items as a percentage of total income within the output spreadsheet.

Generate three bar graphs depicting the following: (1) the Form 1040 items that together make up taxpayer gross income—taxable income, exemptions, itemized deductions, for-AGI deductions, and tax-exempt interest; (2) regular tax liability; and (3) other taxes (i.e., net investment income tax, additional Medicare tax, self-employment tax, and AMT) across all total income levels. Exhibit 2 reflects a sample output spreadsheet for this Base scenario.

Part II

To illustrate various client planning strategies, the instructor can now require students to modify tax returns and the output spreadsheet employing the following assumptions:

1. The taxpayer becomes a material participant in the activities of her partnership interest (an MP scenario). (The K-1 worksheet within the tax-compliance software defaults to a passive investor assumption.) Exhibit 3 represents a sample revision of the output spreadsheet.
2. The taxpayer elects to include qualified dividends and NLTCG as investment income to maximize the deduction for net investment interest expense (a Max scenario). Exhibit 4 represents a sample revision of the output spreadsheet.
3. The taxpayer becomes a material participant in the activities of her partnership interest and elects to include qualified dividends and NLTCG as investment income (an MP+Max ­scenario). Exhibit 5 represents a sample revision of the output spreadsheet.

Note: The creation of additional scenarios (MP, Max, and MP+Max) generates several compelling taxable income and tax liability differences that instructors can use to develop some challenging analysis questions for students (see Part III sample questions below).

Part III

After students complete output spreadsheets for each of the above scenarios, the instructor can then require students to respond to various questions designed to enhance their understanding of the tax-compliance software and under­lying tax law as well as their appreciation for the interplay of the various scenarios and associated planning opportunities. The number and sophistication of the questions will vary from instructor to instructor based on time constraints, course emphasis, and student ability.

Sample Questions

1. Explain why the itemized deduction total for each income level on the input spreadsheet does not match the corresponding amount on the output spreadsheet for the Base scenario. As income progresses from $100,000 to $1 million, describe how and why the difference changes.
2. The taxpayer does not appear to benefit from any nonrefundable credits, including an education credit, at any of the Base scenario income levels. What accounts for the absence of a nonrefundable education credit at $200,000? What accounts for the absence at $100,000?
3. If one defines the taxpayer’s marginal tax rate as the change in total tax liability divided by the change in total income, compute a rate for each $100,000 change in income within the Base scenario. Explain why the rate appears to spike as income increases from $200,000 to $300,000. As income increases from $300,000 to $1 million, explain any other significant changes to the marginal rate (e.g., changes equal to or greater than 2%).
4. If one alternatively defines the taxpayer’s marginal tax rate as the change in total tax liability divided by the change in taxable income, recompute the rate for each $100,000 change in income within the Base scenario. How do the rates, as recomputed, compare with the above rates? As income increases from $300,000 to $1 million, explain any other significant changes to the marginal rate (e.g., changes equal to or greater than 2%).
5. If one defines the taxpayer’s effective tax rate as the total tax liability divided by total income, describe how the rate changes over the Base scenario income levels. How would the effective rates change if the effective tax rate equals total tax liability divided by taxable income? Graph both sets of effective tax rates across the Base scenario income levels and summarize the results.
6. Using the graphs generated within the output spreadsheet for the base scenario, describe the progression of AMT as income increases from $100,000 to $1 million. Explain why AMT declines in the upper income ranges.
7. For income levels within the Base and Max scenarios that generate a net investment income tax:
   1. Summarize how investment-related expenses (including investment interest expense) on Schedule A compare with those reflected on Form 8960, net of any cutbacks. Explain any differences.
   2. Summarize how investment income on Form 8960 compares with total income on Form 1040 exclusive of salary and Schedule C income. Explain any differences. Repeat the above steps comparing the Base and MP scenarios.
8. If the taxpayer becomes a material participant in the activities of her partnership interest (i.e., the taxpayer moves from the Base scenario to the MP scenario), explain why net investment income tax declines for income levels equal to or more than $300,000. At $300,000 in total income, would you encourage the taxpayer to pursue material participation status, all other things being equal?
9. If the taxpayer elects to include qualified dividends and NLTCG as investment income (i.e., the taxpayer moves from the Base scenario to the Max scenario), explain what causes net investment income tax to (1) decline for income levels equal to or greater than $400,000 and (2) remain the same for income totaling $300,000. For income levels equal to or greater than $200,000, why does regular tax liability decrease as more income loses preferential treatment?
10. If the taxpayer becomes a material participant in the activities of her partnership interest and elects to include qualified dividends and NLTCG as investment income (i.e., the taxpayer moves from the Base scenario to the MP+Max scenario), total tax liability appears to decrease for income levels equal to or greater than $200,000. Why does the strategy appear to be ineffective in years when the taxpayer earns $100,000? Would your answer change if the taxpayer earns $200,000 in total income allocated as follows?:

Hint: You will need to change your input spreadsheet and tax returns to derive a solution. Sample Student Results

Listed below are condensed responses from one three-student team in the authors’ master of science in accounting program. The team completed the assignment during the summer 2014 term. Given the condensed summer term, the authors elected to supply the team with Excel files associated with Exhibits 1–5, listed above, and access to all the associated tax returns prior to assigning the Part III questions. Ideally, the assignment should be administered during a regular term, allowing students sufficient time to generate their own tax returns, output spreadsheets, and graphs. Grading the project in phases would also permit teams to correct their tax returns prior to addressing any analysis questions.

Question 1

The team observed that limits on various itemized deductions (e.g., medical expenses), certain miscellaneous itemized deductions (e.g., unreimbursed employee and investment expenses), and investment interest expense can create a mismatch between amounts on the input and output spreadsheets. The team explained that as income progresses from $100,000 to $1 million, the spread between the input and output spreadsheet amounts increased. The team attributed most of this widening discrepancy to the phaseout of total itemized deductions associated with high-income taxpayers.

Question 2

With income at $200,000, the team attributed the absence of a nonrefundable education credit to the income limit associated with taxpayers’ filing jointly. With income at $100,000, the team explained that the taxpayer reported insufficient tax liability to trigger a nonrefundable credit.

Question 3

The team computed the following marginal rates for the taxpayer:

The team attributed the rate spike between $200,000 and $300,000 in income to significant increases in regular tax liability (from $13,839 to $34,178), AMT (from $302 to $7,625), and the net investment income tax (from $0 to $1,210). The team also noted a substantial increase in the marginal rate once the taxpayer earned $800,000 or more, which it attributed to increases in regular tax liability more than offsetting the absence of AMT.

Question 4

The team recomputed the following marginal rates for the taxpayer:

The team attributed most of the rate changes to the same factors as in Question 3. The team did note, however, that the lower base (taxable income vs. total income) resulted in consistently higher percentages across the entire income range.

Question 5

The team computed the following effective tax rates for the taxpayer:

The team noted that the output sheet for the Base scenario already showed these percentages. The team explained how the effective rate grew at a decreasing rate over the income range and graphed the results (see Exhibit 6).

The team recomputed effective tax rates using taxable income as a denominator and graphed the results (see Exhibit 7). The percentages follow:

The team described how total tax at $100,000 in income (i.e., $3,532) measured against $6,447 in taxable income generated an artificially high rate of 54.79%.

Question 6

The team attributed the decline or absence of AMT at upper income levels to a simple comparison on Form 6251, Alternative Minimum Tax—Individuals, of tentative minimum tax (i.e., alternative minimum taxable income less an AMT exemption amount, multiplied by either a 26% or 28% statutory rate) to the regular tax liability, reflected on Form 1040, line 44. The team noted that at income levels equal to or greater than $800,000, the regular tax liability exceeded tentative minimum tax and allowed the taxpayer to avoid AMT.

Question 7

For investment-related expenses, the team determined, for both the Base and Max scenarios, that Form 8960 expenses exceeded Schedule A expenses by a deduction for state income taxes. More specifically, the team concluded that Form 8960 allows a deduction for the portion of state income taxes allocated to investment income. The team also observed that the Max scenario expenses exceeded the Base scenario expenses on both Schedule A and Form 8960 by the amount of investment income elected to be included on Form 4952 to maximize investment interest expense.

For investment income, the team observed that income on Form 8960 equaled the total income on Form 1040, line 22 less any salary and Schedule C income.

The team’s conclusions for investment-related expenses for both the Base and MP scenarios did not change. From an income perspective, however, the team noted that the MP scenario’s income on Form 8960 was less than total income on Form 1040, line 22 (less any salary and Schedule C income) by the amount of recharacterized Schedule E income. In other words, the team concluded that once the Schedule E income is treated as active, it is no longer subject to the net investment income tax on Form 8960.

Question 8

The team concluded that the taxpayer should pursue material participation status, all other things being equal, because doing so would prevent the Schedule E income from being included on Form 8960 and would lower both the net investment income tax and regular tax liability. The team ignored, however, that pursuing material participation at the $300,000 income level would cause total tax liability to increase.

Question 9

The team determined that net investment income tax is less in the Max scenario than in the Base scenario at income levels equal to or greater than $400,000 because investment interest expense is maximized. At $300,000, however, the team noted that the tax is the same in both scenarios, as it is based on the lower of (1) net investment income or (2) modified AGI above a $250,000 threshold for joint filers. Despite a difference in net investment income, the lesser figure in both scenarios is the same: modified AGI above the threshold.

Question 10

The team concluded that moving from the Base scenario to the MP+Max scenario was ineffective at $100,000 in income because self-employment tax represented the only tax faced by the taxpayer. By characterizing the Schedule E income as nonpassive, the taxpayer triggered additional self-employment tax.

After changing the income allocation percentages, the team generated two additional spreadsheets (see Exhibit 8 and Exhibit 9) and concluded it would not pursue the strategy. The decrease from the Base scenario’s regular tax liability was more than offset by an increase in self-employment tax.

Assignment Modifications/Extensions

The assignment described above could be modified or extended in several ways. Listed below are a few possibilities that instructors might consider:

1. As the input spreadsheet allows students to modify income allocation percentages for the 10 income categories, instructors could assign unique percentages to various student teams. Students could then compare and contrast resulting taxable income, taxation, graphs, and planning strategies in class.
2. Schedule E income could relate to an interest in an S corporation, highlighting, for example, significant differences in self-employment tax relative to a partnership interest.
3. Schedule E income could also relate to various rental real estate activities, shifting the assignment’s focus to an enhanced student understanding of material participation, active participation, passive loss rules, and the related impact on the net investment income tax.
4. Expenses related to Schedule C and Schedule E activities could be introduced to emphasize various limits and cutbacks.
5. Instructors could incorporate various loss amounts into the input spreadsheet categories. Net operating losses, capital losses, and ordinary partnership losses, for example, would help illustrate relative tax effects.
6. Rather than analyzing various income levels within a particular tax year, the assignment could focus on income levels earned over a period of years. Income progression could illustrate how a taxpayer uses various carryover deductions and credits.
7. Students could use the following documents to assess how various elements of the 2014 tax reform proposal by former Rep. Dave Camp, R-Mich., the former chairman of the House Ways and Means Committee, could impact the income and taxation effect on one or more of the above scenarios:
   1. Technical Explanation of the Tax Reform Act of 2014: A Discussion Draft of the Chairman of the House Committee on Ways and Means to Reform the Internal Revenue Code: Title I—Tax Reform for Individuals, available at waysandmeans.house.gov; and
   2. Technical Explanation of the Tax Reform Act of 2014: A Discussion Draft of the Chairman of the House Committee on Ways And Means to Reform the Internal Revenue Code: Title II—Alternative Minimum Tax Repeal, available at waysandmeans.house.gov.
8. Instructors could incorporate assumptions regarding taxpayer contributions to various qualified retirement plans (e.g., Sec. 401(k), Sec. 403(b), and traditional/Roth IRA contributions) into the input spreadsheet categories.
9. Depending on time constraints, instructors might consider reducing the number of income scenarios. Employing four or five income levels rather than 10, for example, would preserve most of the assignment’s tax policy and sensitivity analyses and still ensure that students understand how to use tax-compliance software. Alternatively, instructors could assign the $100,000 to $300,000 range to one-third of the student teams, the $400,000 to $700,000 range to another third, and the $800,000 to $1 million range to the remaining third. Selected teams within each income grouping could then report findings in class.

Conclusion

This assignment assists in the development of students’ tax knowledge through the examination of numerous tax iterations. Students are able to explore the effect of several tax items on differing levels of income, deductions, and exemptions. The use of tax compliance software and Excel spreadsheets and charts creates an active learning environment, and the analysis of various scenarios places an emphasis on higher-order ­cognitive skills.