Modeling investment tax planning with Excel.

• Author: Nellen, Annette

Ensuring that professionals can effectively use ever-changing technology is one of the biggest challenges facing the accounting industry (see Drew, "Staffing Concerns Again Top List of CPA Firm Issues, "Journal of Accountancy (June 14, 2017), available at tinyurl.com/y9j5gzfp). Technology skills are nearly always listed as a requirement for entry-level accounting jobs (see Robert Half, "Accounting Skills You Need to Succeed on the Job" (May 8,2018), available at tinyurl.com/y9zsbxbu). These skills are so important, in fact, that accounting firms such as PwC consider a foundation in Microsoft Excel a core competency of accounting undergraduate education and recommend that accounting programs include a computer course that stresses spreadsheet, database, and programming skills (see the PwC report Data Driven: What Students Need to Succeed in a Rapidly Changing Business World (February 2015), available at tinyurl.com/ydfy2354).

While new technologies evolve frequently, Excel remains one of the key technologies that accounting firms and corporations expect their accountants to be able to use. The AICPA also expects those who are pursuing a CPA license to have strong Excel skills. Effective April 1,2018, the AICPA replaced the generic spreadsheet tool that was embedded within the CPA Exam with Excel. Because Excel is useful in analyzing many types of transactions, accounting firms are investing significant amounts to create and offer courses to help their employees improve Excel skills. Accounting faculty can also assist students in building strong Excel skills by incorporating assignments that require students to combine technical tax knowledge with Excel's analytic capabilities.

This column presents a series of cases designed to give students an opportunity to use Excel to perform various analyses related to stock and mutual fund investments. In addition to helping students improve their Excel skills, the cases allow students to see the value of Excel in examining various real-world tax scenarios. These cases require students to use the analyses performed in Excel to make decisions and/or provide recommendations to a client. In practice, practitioners may use tax-planning software specifically designed to analyze these complex scenarios with minimal inputs. However, by having students use Excel to create formulas, the cases encourage students to think analytically about the decision process underlying the tax planning software. The following articles from The Tax Adviser provide examples of how Excel can be used in a tax course: Evans, et al., "Using Excel in the Classroom: Performing a Multilevel Tax Analysis of an S Corporation Conversion," 47 The Tax Adviser 350 (May 2016), available at tinyurl.com/yb47qhaw; Evans and Hansen, "Preparing the Income Tax Footnote: A Comprehensive Study in Excel," 48 The Tax Adviser 826 (November 2017), available at tinyurl.com/y8qox8av; and Brink and Hansen, "Using Big Data to Identify Tax Risk," 49 The Tax Adviser 318 (May 2018), available at tinyurl.com/y7261njt.

Each case is independent of the other, allowing instructors the flexibility to use the case(s) appropriate for their course. The cases differ in terms of their levels of Excel use and technical tax knowledge required and may be used at the undergraduate and/or graduate level. Case 1 and Case 2 provide Excel templates for students to complete. These templates include embedded correctness checks to assist students in performing their analyses. Case 3 requires students to develop their own Excel worksheet. Instructors have the flexibility with Case 3 to tailor the requirements for student worksheets to include those Excel skills they want students to demonstrate, such as requiring the use of IF functions and/or PV functions. The templates for all three cases, along with other online-only content, are available at thetaxadviser.com/investment-tax.

It is recommended that instructors who use these Excel case studies modify the facts of each case annually to update them for tax law changes, but also to maintain the academic integrity of each case. Additionally, while the cases have been designed to emphasize investment tax planning from an individual taxation standpoint, instructors can use these cases in other tax areas with minor modifications, as the rules applicable to investments are similar for corporations and investment funds, and other passthrough entities.

Case 1 requires students to use Excel to perform calculations applying the wash-sale rules to stock sale and purchase transactions. To complete the case successfully, students must employ a multilevel analysis that incorporates the loss disallowance and subsequent basis adjustment rules. Students will use a formula-based approach for their analysis, using a predesigned Excel template that accompanies the case. After completing the case...