CORPORATE FINANCE

shareholders’ equity $ 600

To calculate the shareholders’ equity, realize that total liabilities and shareholders’ equity equals both total assets and current liabilities plus long-term debt plus shareholders’ equity. Using these relationships, we can solve for shareholders’ equity.

Net working capital = Current assets – Current liabilities

Net working capital = $ 30

Many of the examples in this chapter deal with the fictitious U.S. Corporation. The balance sheet for the company is below.

RWJ Excel Tip

Note that we will enter the data for the balance sheet here, but reference the numbers across different sheets, a very handy tool in Excel. Also, even though we know the balance sheet balances, we will have Excel do the balance sheet calculations for us anyway. For example, total current assets will be calculated in Excel even though the number is given in the textbook.

U.S. Corporation 2015 and 2016 Balance Sheets ($ in millions)

Assets Liabilities and Owner’s Equity

2015 2016 2015 2016

Current assets Current liabilities

Cash $ 104 $ 160 Accounts payable $ 232 $ 266

Accounts receivable 455 688 Notes payable 196 123

Inventory 553 555 Total $ 428 $ 389

Total $ 1,112 $ 1,403

Long-term debt $ 408 $ 454

Fixed assets

Net plant and Owner’s equity

equipment $ 1,644 $ 1,709 Common stock and

paid-in surplus $ 600 $ 640

Retained earnings 1,320 1,629

Total $ 1,920 $ 2,269

Total liabilities and

Total assets $ 2,756 $ 3,112 owner’s equity $ 2,756 $ 3,112

RWJ Excel Tip

In the balance sheet, select the 2015 cash balance, go to Formulas, and select Trace Dependents. This will draw a line from the cash balance cell to the 2015 total current liabilities. This function draws an arrow to any cell that uses the selected cell in a calculation. If you go to the total assets cell, and choose Trace Precedents from the same menu, Excel will draw a line to total current assets and net plant and equipment to total assets. Tracing precedents allows us to see which cells the cell we selected is using for the calculation in that cell. Both functions are useful for tracing where cells are used in future calculations or which cell or cells are used in the current calculation.

We will be using this balance sheet in future calculations.

Example 2.2: Market Value versus Book Value

We are given the following information on Klingon Corporation:

Book value of fixed assets: $ 700

Market value of fixed assets: $ 1,000

Book value of net working capital: $ 400

Market value of net working capital: $ 600

Book value of long-term debt: $ 500

Market value of long-term debt: $ 500

Below we have both the book value and market value balance sheets for Klingon Corporation.

Klingon Corporation Balance Sheets Book Value and Market Value

Assets Liabilities and Owner’s Equity

Book Market Book Market

Net working capital $ 400 $ 600 Long-term debt $ 500 $ 500

Net fixed assets 700 1,000 Shareholders’ equity 600 1,100

$ 1,100 $ 1,600 $ 1,100 $ 1,600

RWJ Excel Tip

Notice we used separate input cells for the market value and book value of debt even though the two numbers are the same. While they are the same in this case, they do not have to be the same. Using different input cells makes the spreadsheet more versatile.

Section 2.2

Chapter 2 – Section 2

The Income Statement

The income statement measures performance over time. The income statement for U.S. Corporation is:

Tax rate: 34%

U.S. Corporation 2016 Income Statement ($ in millions)

Net sales $ 1,509

Cost of goods sold 750

Depreciation 65

Earnings before interest and taxes $ 694

Interest paid 70

Taxable income $ 624

Taxes (34%) 212

Net income $ 412

Dividends $ 103

Addition to retained earnings 309

We have the tax rate as an input cell. In this way, Excel will calculate the taxes automatically. The dividends are an input cell since the dividends are at management discretion.

Example 2.3: Calculating Earnings and Dividends per share

Suppose that the shares outstanding for U.S. Corporation in millions are: 200

What are the earnings per share and dividends per share?

Earnings per share = Net income / Total shares outstanding = $ 2.06

Dividends per share = Total dividends / Total shares outstanding = $ 0.515

Section 2.3

Chapter 2 – Section 3

Taxes

The corporate income tax table currently in effect is:

Taxable income is greater than or equal to…. But less than or equal to… Tax rate

$ – $ 50,000 15%

50,001 75,000 25%

75,001 100,000 34%

100,001 335,000 39%

335,001 10,000,000 34%

10,000,001 15,000,000 35%

15,000,001 18,333,333 38%

18,333,334 35%

Notice that we entered the minimum and maximum amount for each marginal tax rate in separate columns. Why we did this we will explain shortly. The marginal tax rate is the tax on the next dollar of income. Suppose we have gathered the following information from a company’s income statement:

Taxable income: $ 200,000

Taxes: $ 68,000

The marginal tax rate will be:

Marginal tax rate: 39%

RWJ Excel Tip

To have Excel find the marginal tax rate at the requested level of income, we used VLOOKUP. This function is found under Lookup & Reference. The format for this function is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup). The lookup_value is the number we want to find, in this case the net income. The table_array is where we want to look for the number, index_number is the column in the selected data we want the answer returned from, and range_lookup allows you to request an answer only for an exact match (if FALSE is entered) or an approximate match (the default, or if TRUE is entered.) In this case, we highlighted the entire table, used the cell with the net income amount as the lookup_value, and entered “3” as the index_num since we wanted the number from the 3rd column we highlighted returned as our answer. Since we did not enter a value for range_lookup, the default option was to look for the closest number and return the value from the column. Click on the marginal income tax rate cell calculation cell to see the syntax we used.

The average tax rate is the total taxes divided by the taxable income, or:

Average tax rate: 34.00%

Of course, you could create a spreadsheet to calculate the total tax bill and average tax rate for a given level of taxable income. In fact, the Master it! problem at the end of this workbook asks you to create such a spreadsheet.

Section 2.4

Chapter 2 – Section 4

Cash Flow

Now we are ready to calculate the financial cash flows for the fictitious U.S. Corporation. When we calculate the cash flows, we are going to reference inputs and calculations from the previous worksheets so we do not have to enter the balance sheet and income statements again.

RWJ Excel Tip

To reference data in another worksheet, first enter an equal sign in the cell you want the data in, then move the cursor to the tab at the bottom of the spreadsheet that corresponds to the worksheet you want. Go to the cell that contains the desired data, left click your mouse, and press enter. To get back to the current worksheet, simply click on the appropriate worksheet tab at the bottom of the spreadsheet.

U.S. Corporation 2016 Operating Cash Flow

Earnings before interest and taxes $ 694

- Depreciation 65
- Taxes 212

Operating cash flow $ 547

RWJ Excel Tip

Excel will not let you enter a mathematical operator ( = , + , – , etc.) at the beginning of a text cell. However, if you enter an apostrophe as the first character in a cell, Excel will consider anything in the cell as text, not a mathematical operation. This is how we entered the “+” before Depreciation.

Capital spending is the amount a company spends on fixed assets. For U.S. Corporation, the capital spending was:

U.S. Corporation 2016 Capital Spending

Ending fixed assets $ 1,709 - Beginning fixed assets 1,644
- Depreciation 65

Net capital spending $ 130

The change in net working capital is the amount spent on current assets. Net working capital is current assets minus current liabilities. So, for U.S. Corporation, the change in net working capital was:

U.S. Corporation 2016 Change in Net Working Capital

Ending NWC $ 1,014 - Beginning NWC 684

Change in NWC $ 330

Now we are ready to calculate the cash flow from assets for U.S. Corporation. The cash flow from assets is the operating cash flow less the capital spending less the change in NWC. The cash flow from assets is:

U.S. Corporation 2016 Cash Flow from Assets

Operating cash flow $ 547 - Net capital spending 130
- Change in NWC 330

Cash flow from assets $ 87

The cash flow to creditors is the interest paid minus net new borrowing. Net new borrowing is simply the difference between the ending long-term debt and the beginning long-term debt.

U.S. Corporation 2016 Cash Flow to Creditors

Interest paid $ 70 - Net new borrowing 46

Cash flow to creditors $ 24

The cash flow to stockholders is dividends paid minus net new equity. To find net new equity, we can subtract the beginning common stock and paid-in surplus from the ending common stock and paid-in surplus. So, the cash flow to stockholders was:

U.S. Corporation 2016 Cash Flow to Stockholders

Dividends paid $ 103 - Net new equity 40

Cash flow to stockholders $ 63

Master it!

Chapter 2 – Master it!

Using Excel to find the marginal tax rate can be accomplished using the VLOOKUP function. However, calculating the total tax bill is a little more difficult. Below we have shown a copy of the IRS tax table for an individual. Often, tax tables are presented in this format.

If taxable income is over — But not over — The tax is:

$0 $9,225 10% of the amount over $0

9,225 37,450 $922.50 plus 15% of the amount over $9,225

37,450 90,750 $5,156.25 plus 25% of the amount over $37,450

90,750 189,300 $18,481.25 plus 28% of the amount over $90,750

189,300 411,500 $46,075.25 plus 33% of the amount over $189,300

411,500 413,200 $119,401.25 plus 35% of the amount over $411,500

413,200 $119,996.25 plus 39.6% of the amount over $413,200

In reading this table, the marginal tax rate for taxable income less than $9,225 is 10%. If the taxable income is between $9,225 and $37,450, the tax bill is $922.50 plus the marginal taxes. The marginal taxes are calculated as the taxable income minus $9,225 times the marginal tax rate of 15%.

Below, we have the tax table as shown in the textbook, as well as a taxable income.

Taxable income: $ 1,350,000

The corporate income tax table currently in effect is:

Taxable income is greater than or equal to…. But less than… Tax rate

$ – $ 50,000 15%

50,001 75,000 25%

75,001 100,000 34%

100,001 335,000 39%

335,001 10,000,000 34%

10,000,001 15,000,000 35%

15,000,001 18,333,333 38%

18,333,334 35%

a. Create a tax table for corporate taxes similar to the individual tax table shown above.

b. For a given taxable income, what is the marginal tax rate?

c. For a given taxable income, what is the total tax bill?

d. For a given taxable income, what is the average tax rate?

Solution

Master it! Solution

a. If taxable income is over — But not over — The tax is:

of the amount over

plus of the amount over

plus of the amount over

plus of the amount over

plus of the amount over

plus of the amount over

plus of the amount over

plus of the amount over

b. The marginal tax rate is:

c. The total tax bill is:

Tax on the bottom of marginal tax bracket:

Amount of income in marginal tax bracket:

Total tax:

d. The average tax rate is:

Professional college paper writers: #1 Best Essay Writer: Are you looking for a custom essay service to accommodate your specific needs? At study-tutors.com, we have earned the reputation for being a leading provider of original, high-quality essays at affordable prices. Our professional writers will do their best to create a paper you need and help you succeed in studying. Our custom essay writing service is aimed at giving you the professional writing help you need to succeed. If you do not have the time for all your college writings, our experienced essay writer will have your back.

**Order Original and Plagiarism-free Answers Written from Scratch**

GET ESSAY WRITING HELP!

- Any citation style
- 24/7/365 Live support

- 100% Original
- No hidden charges