Using Excel in the Classroom: Performing a Multilevel Tax Analysis of an S Corporation Conversion

Editor: Annette Nellen, CPA, CGMA, Esq.

The AICPA, incorporating feedback from practicing accountants, developed the Model Tax Curriculum (MTC) to provide recommendations to accounting academics for designing a tax course for college undergraduates. One emphasis in the MTC is the need for students to develop technology skills necessary to successfully undertake tax planning, compliance, and research strategies (visit the AICPA’s MTC resources page at aicpa.org for more information). Since accountants regularly use Microsoft Excel, developing students’ ability to employ this tool in an efficient and reliable way, such as to model tax planning strategies, is essential to their future career success.

The authors created an Excel-based case to give students an opportunity to use Excel to integrate multiple types of tax law, including the 0.9% additional Medicare tax on wage income:

• Microsoft Excel case template
• Answer key
• Answer key formulas

By integrating multiple types of taxes into one analysis and watching how those components change with various income assumptions and the type of business entity, students will gain a deeper understanding of the interplay among the taxes. They can discover which taxes dominate at low, midrange, and high levels of business income. Performing this case analysis can also improve students’ understanding of how the tax law may affect various business forms with similar operations differently. The case also allows students to develop their skills in Excel and see its value in examining various tax planning scenarios. For instance, they will see how Excel graphs and charts can create a visualization of the various planning options.

The case requires students to perform the work of a hypothetical tax professional who is helping a client determine the federal tax implications of converting her sole proprietorship to a single-owner S corporation. To complete the case successfully, students must employ a multilevel analysis that incorporates the impact of conversion on income taxes, payroll taxes, self-employment (SE) taxes, and the additional Medicare tax. Students are asked to model the relevant factors and calculations using a formula-based approach in Excel, using a predesigned template to accompany the case. After completing the case, students should gain a deeper understanding of the differences between the taxation of S corporation income and that of SE income at the individual level, including the concept of salary and the liability for payroll or SE taxes. The analysis also showcases the advantage the S corporation form possesses with respect to the 0.9% additional Medicare tax.

Implementing this case offers educators an opportunity to invite local tax practitioners into the classroom to discuss their own Excel projects and the importance of Excel skills in tax practice. Tax practitioners employ Excel-based analyses across many areas of taxation, from tracking partnership basis elections to analyzing tax aspects of financial statement reporting. Having a practitioner discuss these experiences or demonstrate how he or she uses Excel in certain analyses is highly valuable to reinforce the importance of mastering Excel-based skills, such as those required by this case. Instructors are encouraged to invite local practitioners either before or after distributing this case.

Microsoft Excel Case Templates

Using Excel to complete projects allows educators access to tools that can not only make student learning more effective but also make grading and providing feedback to students more efficient. For example, without sufficient guidance on the quality of his or her response, a student could become confused and frustrated by the intricacies of (and interactions among) the various federal tax laws. To address this issue, the Excel template accompanying the case contains a checking mechanism for the expected values for certain cells. By using an “IF” function in the adjacent cell of a required answer, the template provides feedback while the student is working on the exercise by displaying “please review” until the student inputs the correct answer. This Excel feature allows students to acknowledge and correct their errors in real time, which enhances their learning experience.

In addition to requiring numerical answers, the case requires students to answer qualitative questions. For example, after calculating a tax cost or savings upon conversion from a sole proprietorship to an S corporation under the various income scenarios, the case asks the student to identify the primary driver of the change, reinforcing the fact that federal income taxes, regular payroll taxes, the SE tax, and the additional Medicare tax are all relevant decision-making factors. Rather than requiring students to type open-ended responses, the Excel data validation tools allow the instructor to create a dropdown list of potential responses for students to choose among. The dropdown menu of options helps students comprehend the purpose of the question, and instructors can more easily determine the correctness of the response.

The Case Study

Jayne is a sole proprietor of Daily Buzz, a local coffeehouse she opened six years ago close to a large college campus. The business has become very popular and profitable, and Jayne works in the coffeehouse nearly every day. After conversations with other local business owners, Jayne comes to you, her tax adviser. She heard that incorporating her business as an S corporation might have favorable tax implications.

Federal income taxes are a critical component of any income tax analysis, but tax advisers must not overlook other tax levies that apply to the business. Payroll and SE taxes, both of which fund Social Security and Medicare, also represent a material tax element, as could the additional Medicare tax. In your analysis, please incorporate each of these taxes for the 2016 tax year. You may restrict your analysis to federal tax implications (and ignore any state or local taxes on the business).

If Jayne incorporates her sole proprietorship, she will have to begin paying herself a “reasonable” salary, which you may assume equals 25% of business income for purposes of this case. For simplicity, you may assume Jayne has other income equal to her itemized/standard deduction and personal exemptions, so that the taxable income equals the business income. Jayne is not married and has no dependents.

Task No. 1

As Jayne’s tax adviser, you should provide a dynamic tax analysis, being mindful of how each federal tax levy will change if Jayne’s business grows in the future. Therefore, use the accompanying Excel template to complete a comprehensive federal tax analysis at four levels of business income ($100,000, $200,000, $400,000, and $600,000). Each income assumption has its own worksheet in the Excel template. After you complete the calculations for each income level, create a “cluster column” chart at the bottom of each worksheet to show how each component of the total federal tax liability would change (increase or decrease) if Jayne converts her sole proprietorship to an S corporation.

Task No. 2

After completing a numerical analysis of the four levels of business income, reflect on the meaning behind the results. Specifically, complete the table in the “Task 2—Analysis” worksheet and then answer the subsequent questions. These questions will help you assimilate the computations you just performed and analyze the primary drivers of each change.

Task No. 3

Tax consulting projects generally culminate in a written work product. Write a letter to your client detailing your recommendation on whether she should convert from a sole proprietorship to an S corporation. Provide justification for your recommendation and include the financial savings of converting at the various income levels. It is important not only to relay accurate information but also to do so in a way the client can easily read and understand. Therefore, pay attention to the ordering and flow of your letter, as well as punctuation and grammar.

A Review of Federal Non-Income Taxes (Optional to Provide With Case)

A sole proprietorship is not legally separate from its underlying owner, so for tax purposes, a sole proprietor is considered to have earned all the profits of her business. Any cash she receives must be withdrawn from the operations of that business and cannot be paid in the form of a salary. In contrast, an S corporation is a separate legal entity. Any employee who works for the business, including an owner, is entitled to a reasonable wage or salary payment.

1. Social Security and regular Medicare taxes on wages or salary: Social Security and regular Medicare taxes are collected together as part of the Federal Insurance Contributions Act (FICA) tax. For 2016, employers pay 6.2% on the first $118,500 of wage or salary payments to each employee to fund Social Security and 1.45% on every dollar of wage or salary (with no upper limit) to fund Medicare. The business may deduct its portion of each of these taxes. Employees are liable for a Social Security tax of 6.2% on the first $118,500 of salary earned (in total from all employers), and for regular Medicare tax of 1.45% on all salary earned. Employers withhold this liability from each employee’s paycheck and remit it to the government on the worker’s behalf. (Having multiple employers during the year may result in excess withholding of FICA taxes for the employee, for which the employee may claim a refund when he or she files Form 1040, U.S. Individual Income Tax Return.)

2. SE tax on sole proprietorship and other SE earnings: Since sole proprietors and other self-employed individuals cannot pay themselves a salary, they compute and pay their Social Security and Medicare taxes under the SE tax instead of FICA. Though the mechanism is different, the tax assessment is largely the same. For 2016, the SE tax rates are 12.4% for Social Security and 2.9% for Medicare (representing the hypothetical employer and employee portion of 6.2% each and 1.45% each, respectively). The Social Security portion of this tax only applies to the first $118,500 of self-employment income for the 2016 tax year, similar to FICA. The 2.9% regular Medicare tax does not have an upper limit. Self-employed individuals can deduct 50% of the total SE tax paid (theoretically, the employer portion) as a deduction for determining adjusted gross income on their individual income tax return.

One important detail in the SE tax computation is the base on which the tax is levied. Specifically, SE tax is not levied on every dollar of net business income reported by the sole proprietor. Rather, it is applied to only 92.35% of net business income. So, while 100% of the business profits are reported as income on the sole proprietor’s Form 1040, only 92.35% of the profits are subject to SE tax.

3. Additional Medicare tax: Taxpayers must now pay an additional tax levy of 0.9% of wages, salary, and SE income above $200,000 for single filers ($250,000 if married filing jointly). Employers do not pay the additional Medicare tax—only employees must pay it.

Sole proprietorship income is also subject to the additional Medicare tax, though only 92.35% of net business income is subject to the tax (similar to the regular Medicare computation). Further, the tax applies only to the base amount in excess of the aforementioned $200,000 or $250,000 thresholds (in combination with any other wages and salary). For example, if a sole proprietorship generates $250,000 of profit for the year, a single sole proprietor would have to pay the 0.9% tax on $30,875 ([$250,000 × 92.35%] ‒ the $200,000 floor).

The additional Medicare tax does not apply to flowthrough business profits from an S corporation but only to the salary received by the owner in excess of the $200,000 floor (for single filers). If the owner pays herself a lower (but reasonable) salary, she will avoid paying this additional tax.

There is also a 3.8% net investment income tax on certain types of investment and passive income. Since the income in this case study is classified as earned income (before and after conversion, since the sole proprietor is still actively involved in her business), the net investment income tax will not apply.

Case Variations

Several modifications of this case are available, lending flexibility in the timing of offering the case and its learning objectives. In a more advanced, master’s-level course, the instructor could choose to offer the case to reinforce choice-of-entity concepts. The case could be offered in a beginning Business Entities tax course as a summary comparison between individual taxation (sole proprietorships) and flowthrough S corporation taxation after the instructor has covered both types of business entities. Alternatively, an instructor whose class covers individual but not flowthrough taxation could vastly simplify the case and remove the S corporation conversion aspect altogether, allowing students in an introductory course to appreciate how federal income taxes, SE taxes, and the 0.9% additional Medicare tax simultaneously apply to sole proprietorships.

In any level course, the instructor could omit the optional portion of the case that details the relevant tax law. Though it would increase the time required to complete this case (which is estimated at two to three hours in its current form), the instructor could instead require students to research the tax implications themselves, further developing and testing their ability to apply what they learned in class. To incorporate an emphasis on tax research in a simpler way, instructors could continue to provide the optional tax law analysis but require students to build an Excel spreadsheet that lists the primary tax authority that leads to each tax treatment. (For example, students must find Rev. Rul. 59-221, which holds that an owner’s share of S corporation income does not constitute SE income.)

Since the case holds constant the assumption of reasonable compensation to the S corporation shareholder, instructors could have students perform additional Excel analyses that manipulate that underlying assumption. A local S corporation tax practitioner could be invited to discuss this extension of the case with students, since tax law and professional judgment affect what pay level can be considered reasonable. This issue (and other issues related to this analysis) is also discussed in detail in an earlier article by two of the co-authors of this column that instructors may distribute and discuss in conjunction with this case (Hennig and Evans, “Is Now the Time to Incorporate Your Business?” Taxes: The Tax Magazine 33-39 (October 2013)). To further extend the study of S corporation taxation after completing the case, instructors could require students to create Excel spreadsheets on other topics of S corporation taxation, such as computing shareholder basis.

Additional modifications could place the focus of this case even more heavily on Excel skills. For example, instructors could ask students to create their own Excel template to compute the various tax assessments rather than use the one that accompanies the case. Another Excel-intensive variation of this case would be to require the student to only use formulas and/or cell references and to not enter any direct values in the cells. With this modification, the student should be able to complete the other scenarios quickly by simply changing the underlying assumptions (e.g., the expected business income). The sample solution that accompanies this case contains only formulas and cell references.

Conclusion

This case enhances students’ understanding of how technology, specifically Microsoft Excel, can be efficiently and effectively used for tax planning. It requires students to assume the role of a tax professional whose client may benefit from converting a sole proprietorship into a single-owner S corporation. To develop a recommendation for the client, students must perform a multilayer tax analysis investigating the effects of conversion, using Excel to model the decision parameters.

This case furthers students’ understanding of the interplay among income taxes, payroll taxes, SE taxes, and the additional Medicare tax. It gives them practical experience with Excel, a tool they will most likely need to use when they enter tax practice. The case also offers an opportunity to involve local practitioners in the classroom to further discuss or illustrate the use of Excel in tax practice. This involvement will help solidify students’ appreciation for Excel as an essential tax planning and compliance tool.

Downloads

• Microsoft Excel case template
• Answer key
• Answer key formulas