Preparing the income tax footnote: A comprehensive study in Excel

By Allison Evans, CPA, Ph.D., and Victoria Hansen, CPA, Ph.D.

Editor: Annette Nellen, CPA, CGMA, Esq.

Income tax expense represents a large dollar amount on the income statement of most companies, and it can have a significant impact on company net income and earnings per share. It is also a primary driver of a company's effective tax rate (ETR), which has steadily risen in importance to investors, analysts, and regulators. Despite the heightened scrutiny on income tax reporting and disclosures, a majority of new entrants into the accounting profession have only basic-level training in this complex area. The lack of detailed knowledge manifests itself year after year as a significant (and increasing) area of weakness in financial reporting, where tax accounting errors continue to be a primary cause of both restatements and accounting-related ineffectiveness in internal controls (Audit Analytics report, 2016 Financial Restatements: A Sixteen Year Comparison (May 2017)).

The accompanying Microsoft Excel-based case study is designed to give accounting students and entry-level accounting staff experience in working with the requirements of FASB Accounting Standard Codification (ASC) Topic 740, Income Taxes. The comprehensive nature of the case study allows the user to move beyond the basic study of book-tax differences and deferred tax assets and liabilities by incorporating higher-level situations. Users must identify multiple temporary and permanent differences, prepare a book-tax reconciliation, calculate deferred tax assets/liabilities, enter the tax provision journal entries, and produce the effective tax rate reconciliation. Students must also evaluate and record a valuation allowance and perform and properly report an intraperiod tax allocation. Though this case offers a comprehensive application of Topic 740, instructors who cover portions of this material in undergraduate courses may use it, as well, by omitting the more advanced portions of the exercise.

The advantages of using Excel to complete the case study are twofold. First, the AICPA emphasizes as part of its Model Tax Curriculum (available at the need for students to leverage technology to enhance their functional competencies. Beyond using Excel to create and reinforce fundamental skills, discussions with CPA firms reveal that a majority of companies (especially smaller ones) still use Excel to perform their tax provision work (see EY report, Tax Provision Process and Technology Trends, available at

Tax-related financial statement deficiencies

In its May 2017 report, Audit Analytics revealed that tax expense and other tax-related issues increased to the second-largest cause of financial restatements in 2016 (2016 Financial Restatements: A Sixteen Year Comparison, May 2017). Tax issues caused the third-largest number of restatements in 2014 and 2015, and varied between third and fourth from 2011 to 2013. In another report, Audit Analytics found that the tax expense/benefit/deferral and related FASB Statement No. 109, Accounting for Income Taxes (now incorporated into the ASC as Topic 740), issues were the second-highest accounting reason for adverse auditor attestations of ineffective internal controls over financial reporting in 2015 (SOX 404 Disclosures: A Twelve-Year Review (August 2016)). Both reports note that the tax-related issues include errors in approach, understanding, or calculation. Public accounting firms also cite lack of understanding of the tax provision as a reason for these deficiencies (see the EY report, Tax Provision Process and Technology Trends, and the Deloitte report, Accounting Roundup—Special Edition: Changes in the Tax Landscape, available at Developing a strong understanding of how to apply the rules under Topic 740 will benefit companies and their auditors, and having a firm grasp of Topic 740 can be a strong advantage for a graduating accounting student.

Emphasis on effective tax rates

Analysts and investors frequently use a company's ETR to analyze its performance, leading companies to stress the calculation of the income tax provision under Topic 740 almost as much as they emphasize cash savings from income tax planning. In fact, a recent survey of tax executives found that 84% of the publicly traded firms that responded noted that top management at their company cares at least as much about the ETR as they do about cash taxes paid (Graham, Hanlon, Shevlin, and Shroff, "Incentives for Tax Planning and Avoidance: Evidence From the Field," 89:3 The Accounting Review 991 (May 2014)). To prepare users in this critical area, this case study requires users to generate the components of ETR by performing the rate reconciliation, thus introducing them to an important exercise that is rarely covered (in detail) in accounting textbooks.

Regulatory focus

The size and importance of income taxes have recently led to increased interest and scrutiny by regulators. A review of SEC comments and trends for 2015 and 2016 by Deloitte (SEC Comment Letters—Statistics According to "Edgar," available at indicates that accounting for income taxes is an area where SEC staff frequently ask companies for additional detail. In addition, the PCAOB July 2016 Staff Inspection Brief announced that accounting for income taxes under Topic 740 will continue to be an area of focus for PCAOB inspections. Such regulatory focus indicates accounting for income taxes under Topic 740 is an area in which practicing accountants and accounting students should become more skilled.

Case overview

The case requires students to prepare an income tax provision under Topic 740 for a hypothetical company. While textbook examples and problems typically require students only to prepare pieces of an income tax provision, this case study provides students with a comprehensive Topic 740 problem. Students use one fact pattern to prepare the tax provision from start to finish, from the initial identification of book-tax differences to the ultimate tax rate reconciliation.

The exercise is designed for a master's-level Topic 740 course or a master's-level advanced financial accounting course. Students in these courses should have the prerequisite tax and financial accounting knowledge necessary to successfully complete the case. It is also well-suited for public accounting firms to use during staff training, since many of those individuals will ultimately be preparing or auditing the income tax provision. Entry-level individuals in corporate tax departments would also benefit from the case. In addition, the case study can be modified (as described subsequently) for use in undergraduate intermediate accounting courses.

To complete the case successfully, users must begin by correctly identifying temporary and permanent book-tax differences from the company's income statement and supplemental information and then determining the effect each will have on deferred tax assets or liabilities. Based on this analysis, users must prepare the entry to record current and deferred tax expense. Students must also identify the need for and correctly calculate a valuation allowance. They must then use their knowledge of the intraperiod tax allocation to properly complete the income statement to reflect the effect of discontinued operations. Finally, the users will prepare an ETR reconciliation (and discern whether the effects of discontinued operations are incorporated into it).

Users are asked to prepare the Topic 740 calculations using an Excel template. The template includes space for users to list book-tax differences and a drop-down menu for students to select whether the book-tax difference ultimately increases a deferred tax asset, decreases a deferred tax asset, increases a deferred tax liability, decreases a deferred tax liability, or has no deferred effect (n/a). The template also provides formatting and structure for students to input their tax provision journal entries, the completed income statement, and the rate reconciliation.

While the provided Excel template offers some structure and guidance to students, many intermediary calculations are not provided or suggested. For example, students must know to multiply the book-tax differences by the applicable tax rate to calculate deferred tax assets/liabilities, which they must link to the journal entry on the subsequent worksheet tab. After identifying the book-tax differences, students must prepare a book-tax reconciliation on their own. They must then know how to properly incorporate the credit and calculate current income tax expense, which they should also link to the journal entry on the second worksheet tab. Students must understand how the pieces of the provision puzzle relate to one another to take the necessary steps involved in calculating the total income tax provision.

The case study

The case study packet includes the assignment and relevant information in Microsoft Word and the Excel template. The solution file includes the supplemental calculations that students will need to perform, with certain check figures highlighted and/or linked. (Download the Word case study file and the Excel template and solution files.) To minimize the possibility that students can search for key terms and find the solution, it is recommended that instructors alter the corporation name and description each time the case is used. To change the name throughout the case, instructors should perform a global search and replace.

The case study focuses on an accrual-method, calendar-year C corporation. It operates in Florida, which has no state corporate income tax. Students are provided a partial income statement, prepared according to GAAP, and additional information necessary to identify book-tax differences and to complete the tax provision calculations. The additional information includes the applicable federal rate; modified accelerated cost recovery system (MACRS) depreciation; actual bad debt write-offs; the date of incorporation and the startup, organizational, and syndication costs incurred that year; a nonrefundable tax credit for which the company qualifies; and a loss from discontinued operations incurred by the company that has not been included in the provided partial income statement.

Students are also provided additional details for select income statement items: The life insurance premium expense represents payments made for policies on the lives of the owners, of which the corporation is the beneficiary; interest expense is incurred for general business purposes; dividend income is received from companies of which the corporation owns less than 20%; and income (loss) from financial investments is categorized as capital gain (loss) for tax purposes. The requisite tasks are:

Task 1Use the partial income statement and additional information provided to identify the company's book-tax differences for the year. Enter the amount of each difference in the first worksheet of the accompanying Excel template, and then use the drop-down menu in the "deferred effect" column to note whether the difference increases/decreases a deferred tax asset, increases/decreases a deferred tax liability, or neither (n/a). At the bottom of the worksheet, calculate the annual change to the cumulative taxable differences and cumulative deductible differences and enter the effects in the associated drop-down boxes.

Task 2: Based on the information provided and your calculations in Task 1, calculate the year's total income tax provision on the second worksheet and prepare the journal entry (or entries) required to record the total income tax provision.

Task 3: Use your total income tax provision journal entry (or entries) from Task 2 and the additional information provided to complete the income statement in proper form on the third worksheet. The company will disclose the required current/deferred income tax breakout on the face of the income statement in lieu of disclosing it in the footnotes.

Task 4: Prepare the year-end rate reconciliation for the company's income tax footnote. Assume all items are significant.

Case variations

Several modifications can be made to the case study, allowing the case to be used in different courses/settings and at varying levels of difficulty. If the case is assigned before intraperiod tax allocations are discussed, for example, the loss from discontinued operations can be removed from the assignment. The valuation allowance included in the case study can be removed or expanded upon as desired. Also, the case study includes only federal income taxes. State income taxes and/or foreign taxes (and associated repatriation assumptions) can be added to increase complexity.

The current case study includes book-tax differences that are commonly seen. However, additional differences can be added and/or current differences can be subtracted from the case study depending on the level of desired difficulty. For example, as the case is written, students are provided with MACRS depreciation. However, instructors could provide students with asset information and require them to calculate tax depreciation. Instructors also have the flexibility to require students to determine whether to elect Sec. 179 and/or bonus depreciation. Another option (which would require amounts to be increased by a factor of 10 and additional information on executive pay to be provided) is for instructors to incorporate the limitation on the deductibility of CEO compensation for public companies to create another permanent difference for the excess amount. Instructors could also delete the assumption of significance in Task 4 to test students on whether they know that only items that rise to the level of 5% significance per the SEC (Regulation S-X, §210.4-08(h)(2)) are to be itemized (and that items below that threshold are included in an "other" line item).

Instructors using the case in a tax-focused course can require students to complete portions of Form 1120, U.S. Corporation Income Tax Return, notably the book-tax reconciliation on Schedule M-1 or M-3. For more advanced tax courses, more complex book-tax differences can be incorporated. The case study can be used to explain and emphasize differences such as those related to revenue recognition, intangibles, and/or acquisition-related costs. If the course has covered the tax treatment of stock options and the new financial accounting treatment of options under Topic 740 and Accounting Standards Update (ASU) No. 2017-09, Compensation—Stock Compensation (Topic 718): Scope of Modification Accounting, the instructor could add incentive and/or nonqualified stock options to the fact pattern. Another option is to add a tax research component to the case study by requiring students to research the tax treatment of income/expense items in these areas.

For intermediate-level accounting courses and/or Topic 740—specific courses, instructors can add research elements to the case study. As with the tax research option, students can be required to research the correct accounting treatment of more complex issues. In addition, instructors can require students to research components of the Topic 740 computations, such as determining the applicable tax rate, and what amounts should be disclosed as significant in the rate reconciliation.

In the university setting, this case offers educators an opportunity to invite tax practitioners into the classroom to discuss their own Topic 740 experiences and the use of Excel in preparing Topic 740 calculations. Tax practitioners can introduce the case study, discuss various book-tax differences in more depth, and relate their experiences with tax provision calculations and rate reconciliations. Practitioners could also discuss using Excel in preparing Topic 740 calculations, versus using purchased provision software. Another option would be for a tax practitioner to conduct a debriefing session, explaining similarities of the current case study to their own Topic 740 calculations.

Filling a gap in skill sets

"Income tax accounting continues to be a 'leader in the clubhouse' on lists that no one wants to lead, such as the number of restatements and Sarbanes-Oxley material weaknesses. The topic continues to be a troublesome area for companies of all sizes." This quote, found on PwC's webpage describing its advisory services for income tax accounting (, illustrates why increased education in the area of Topic 740 would create a marketable skill set for any student and would better prepare new entrants in the accounting profession. This case study provides practice in an area that is traditionally difficult for accounting students because it requires them to have a firm grasp of both financial accounting and income tax concepts, both of which are emphasized in the case study to help fill the existing educational gap.

The case study also allows students to see how Excel can be (and is) effectively used in practice. It does not do the thinking for the student by pre-fixing which numbers should be linked and what computations should be performed. Requiring users to create these relations between numbers and worksheets will help them better understand them, so when they are called upon to audit a client's Excel-based provision (or to create one themselves), they are prepared for the task.   



Allison Evans is EY Faculty Fellow and an associate professor of accountancy, and Victoria Hansen is an associate professor of accountancy, both at the University of North Carolina—Wilmington. Annette Nellen is a professor in the Department of Accounting and Finance at San José State University in San José, Calif., and is the chair of the AICPA Tax Executive Committee. For more information about this column, please contact ­­


Newsletter Articles


States look to unclaimed property for revenue

State audits of abandoned and unclaimed property (AUP) have exploded in recent years. This report outlines the escheat process, common types of AUP, how different states are handling it and how companies can plan for potential audits and liabilities.


Understanding the new Sec. 199A business income deduction

The new deduction allows certain business owners to keep pace with the significant corporate tax cut provided by the Tax Cuts and Jobs Act.