CORPORATE FINANCE

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: