Spreadsheet Exercise: Chapter 2

Hemingway Corporation is considering expanding its operations to boost its income, but before making a final decision, it has asked you to calculate the corporate tax consequences of its decision. Currently, Hemingway generates before-tax yearly income of $200,000 and has no debt outstanding. Expanding operations would allow Hemingway to increase before-tax yearly income to $350,000. Hemingway can use either cash reserves or debt to finance its expansion. If Hemingway uses debt, it will have yearly interest expense of $70,000.

To Do Create a spreadsheet to conduct a tax analysis for Hemingway Corporation and determine the following:

a. What is Hemingways current annual corporate tax liability?

b. What is Hemingways current average tax rate?

c. If Hemingway finances its expansion using cash reserves, what will be its new corporate tax liability and average tax rate?

d. If Hemingway finances its expansion using debt, what will be its new corporate tax liability and average tax rate?

e. What would you recommend that the firm do? Why?

Solution Tax calculation Range of taxable income Base tax + Marginal tax rate amount over base $- to $50,000 $- + 15% amount over $- 50,000 to 75,000 7,500 + 25% amount over 50,000 75,000 to 100,000 13,750 + 34% amount over 75,000 100,000 to 335,000 22,250 + 39% amount over 100,000 335,000 to 10,000,000 113,900 + 34% amount over 335,000 10,000,000 to 15,000,000 3,400,000 + 35% amount over 10,000,000 15,000,000 to 18,333,333 5,150,000 + 38% amount over 15,000,000 over 18,333,333 18,333,333 + 35% amount over 18,333,333

a. What is Hemingways current annual corporate tax liability? Current tax liability is found using the corporate tax rate schedule: Before-tax income: $200,000 Before-tax income of first: $100,000 Before-tax income of balance: $100,000 Tax liability first $100,000: $22,250 Tax liability balance: Total tax liability:

b. What is Hemingways current average tax rate? Current average tax rate:

c. If Hemingway finances its expansion using cash reserves, what will be its new corporate tax liability and average tax rate?

Using the cash reserves, the new tax liability and average tax rate is found below using the corporate tax rate schedule: Before-tax income: $350,000 Before-tax income of first: $335,000 Before-tax income of balance: $15,000 Tax liability first $335,000: $113,900 Tax liability balance: Total tax liability: Average tax rate:

d. If Hemingway finances its expansion using debt, what will be its new corporate tax liability and average tax rate? With debt, the new tax liability and average tax rate is found below using the corporate tax rate schedule: Income before interest and taxes: $350,000 Less: Interest expense: $70,000 Taxable income: Before-tax income of first: $100,000 Before-tax income of balance: $-100,000 Tax liability first $100,000: $22,250 Tax liability balance: Total tax liability: Average tax rate:

e. What would you recommend that the firm do? Why? Y

ou should consider the after-tax income from each possibility shown below, and you should recommend choice with the highest after-tax income.

1. Current after-tax income:

2. Expansion with cash reserve after-tax income:

3. Expansion with debt after-tax income: Y

ou should recommend option number because it has the after-tax income.

Requirements Points 1 In cell F31, by using cell references, calculate the tax liability for the balance taxable income. 1 2 In cell F32, by using cell references, calculate the total tax liability. 1 3 In cell F37, by using cell references, calculate the current average tax rate. 1 4 In cell F46, by using cell references, calculate the tax liability for the balance taxable income. 1 5 In cell F47, by using cell references, calculate the total tax liability. 1 6 In cell F48, by using cell references, calculate the average tax rate. 1 7 In cell F55, by using cell references, calculate the taxable income. 1 8 In cell F59, by using cell references, calculate the tax liability for the balance taxable income. 1 9 In cell F60, by using cell references, calculate the total tax liability. 1 10 In cell F61, by using cell references, calculate the average tax rate. 1 11 In cell H67, by using cell references, calculate the current after-tax income. 1 12 In cell H68, by using cell references, calculate the after-tax income for the expansion with cash reserve. 1 13 In cell H69, by using cell references, calculate the after-tax income for the expansion with debt. 1 14 In cell G71, type either 1, 2 or 3 depending on the option that you would recommend. 1 15 In cell J71, type either lowest or highest depending on the reason for recommending the option of the previous question. 1 16 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.