Finance Excel Question Problems

Please See Attachments

Ch02 P14 Build a Model

,000; operating costs (excluding depreciation) were equal to 85% of sales; net fixed assets were

,000; depreciation amounted to 10% of net fixed assets; interest expenses were

,000; the state-plus-federal corporate tax rate was 40% and Cumberland paid

of its net income out in dividends. Given this information, construct Cumberland’s income statement. Also calculate total dividends and the addition to retained earnings.

Revenue

$455,000

$67,000

$8,550

25%

2010

Sales

DA

EBIT
Interest expense

,000 of new common stock in the most recent year. Using this information and the results from part a, fill in the missing values for common stock, retained earnings, total common equity, and total liabilities and equity.

$10,000

(in thousands of dollars)
2010

67,000

$252,670

(in thousands of dollars)

Mike Ehrhardt: An increase in debt is a positive cash flow.

$91,450

Spring 1, 2013

7/22/12

Chapter 2. Ch 02 P14 Build a Model

Except for charts and answers that must be written, only Excel formulas that use cell references or functions will be accepted for credit.

Numeric answers in cells will not be accepted.

a. Cumberland Industries’ most recent sales were

$455,000

$

67,000

$8,550

25%

The input information required for the problem is outlined in the “Key Input Data” section below. Using this data and the balance sheet above, we constructed the income statement shown below.

Key Input Data for Cumberland Industries

2010

(Thousands of dollars)

Sales

Expenses (excluding depreciation) as a percent of sales

85.0%

Net fixed assest

Depr. as a % of net fixed assets

10.0%

Tax rate

40.0%

Interest expense

Dividend Payout Ratio

Cumberland Industries: Income Statement (Thousands of dollars)

Operating costs excluding depreciation

EBIT

Depreciation (Cumberland has no amortization charges)

EBT

Taxes (40%)

Net income

Common dividends

Addition to retained earnings

b. Cumberland Industries’ partial balance sheets are shown below. Cumberland issued

$10,000

Dollar value of common stock issued

(in thousands of dollars)

Cumberland Industries December 31 Balance Sheets

2009

Assets

Cash and cash equivalents

$91,450

$74,625

Short-term investments

11,400

15,100

Accounts Receivable

108,470

85,527

Inventories

38,450

34,982

Total current assets

$249,770

$210,234

Net fixed assets

42,436

Total assets

$316,770

$252,670

Liabilities and equity

Accounts payable

$30,761

$23,109

Accruals

30,405

22,656

Notes payable

12,717

14,217

Total current liabilities

$73,883

$59,982

Long-term debt

80,263

63,914

Total liabilities

$154,146

$123,896

Common stock

$90,000

Retained earnings

38,774

Total common equity

$128,774

Total liabilities and equity

Check for balancing (this should be zero):

c. Construct the statement of cash flows for the most recent year.

Statement of Cash Flows

Operating Activities

Net Income

Adjustments:

Noncash adjustment:

Depreciation

Due to changes in working capital:

Due to change in accounts receivable

Due to change in inventories

Due to change in accounts payable

Due to change in accruals

Net cash provided (used) by operating activities

Investing Activities

Cash used to acquire gross fixed assets

Due to change in short-term investments

Net cash provided (used) by investing activities

Financing Activities

Due to change in long-term debt

Mike Ehrhardt: An increase in debt is a positive cash flow.

Due to change in notes payable

Due to change in common stock

Mike Ehrhardt: An increase in common stock is a positive cash flow.

Kenneth D. Jackson: An increase in accounts receivable from the pevious year to the current year reduces the net cash provided by operating activities

Kenneth D. Jackson: An increase in Inventory from the previous year to the current year reduces the net cash provided by operation activities

Mike Ehrhardt: An increase in accounts payable increases cash flow.

Mike Ehrhardt: An increase in accruals is a positive cash flow.

Christopher Buzzard: Remember, to calculate cash used to acquire fixed assets, we must include depreciation, i.e., assets purchased are equal to the increase in net assets plus depreciation.

Mike Ehrhardt: Selling securities is a positive cash flow, buying securities is a negative cash flow.

Payment of common dividends

Net cash provided (used) by financing activities

Net increase/decrease in cash

Add: Cash balance at the beginning of the year

Cash balance at the end of the year

Check: cash balance in statement of cash flows should equal the cash on balance sheets as shown here:

Sheet2

7/22/12

Ch03 P15 Build a Model

$379,659 $327,240

(Thousands of Dollars)

2010 2009

DA

EBIT

Michael C. Ehrhardt: Due to rounding, the numbers calculated in the Chapter 2 problem may differ slightly from these.

$17,262

2010 2009

4,000

$20,000 $20,000

$0

2010 2009

coverage ratio

NA

NA

2010

2009

Assets 2010 2009
Cash and cash equivalents
Short-term investments
Accounts Receivable
Inventories
Total current assets
Net fixed assets
Total assets
Liabilities and equity 2010 2009
Accounts payable
Accruals
Notes payable
Total current liabilities
Long-term debt
Total liabilities
Common stock
Retained Earnings
Total common equity
Total liabilities and equity

2010 2009

Sales
Expenses excluding depr. and amort.
EBITDA
Depreciation and Amortization
EBIT
Interest Expense
EBT
Taxes (40%)

Net Income

Assets 2010 2009
Cash and cash equivalents
Short-term investments
Accounts Receivable
Inventories
Total current assets
Net fixed assets
Total assets

Base

Liabilities and equity 2010 2009
Accounts payable
Accruals
Notes payable
Total current liabilities
Long-term debt
Total liabilities
Common stock
Retained Earnings
Total common equity
Total liabilities and equity

Base

2010 2009

Sales
Expenses excluding depr. and amort.
EBITDA
Depreciation and Amortization
EBIT
Interest Expense
EBT
Taxes (40%)
Net Income

																																																																																																																																																																																																Spring 1, 2013
	7/22/12
Chapter 3. Ch 03 P15 Build a Model
Except for charts and answers that must be written, only Excel formulas that use cell references or functions will be accepted for credit.
Numeric answers in cells will not be accepted.
Joshua & White Technologies: December 31 Balance Sheets
	(Thousands of Dollars)
		Assets											2010
										2009
		Cash and cash equivalents	$21,000			$20,000
		Short-term investments	3,759	3,240
		Accounts Receivable	52,500	48,000
		Inventories	8	4,000	56,000
		Total current assets	$161,259	$127,240
		Net fixed assets	218,400	200,000
		Total assets		$379,659		$327,240
		Liabilities and equity
		Accounts payable	$33,600	$32,000
		Accruals	12,600	12,000
		Notes payable	19,929	6,480
		Total current liabilities	$66,129	$50,480
		Long-term debt	67,662	58,320
		Total liabilities	$133,791	$108,800
		Common stock	183,793	178,440
		Retained Earnings	62,075	40,000
		Total common equity	$245,868	$218,440
		Total liabilities and equity
Joshua & White Technologies December 31 Income Statements
		Sales	$420,000	$400,000
		Expenses excluding depr. and amort.	327,600	320,000
			EBIT	$92,400	$80,000
		Depreciation and Amortization	19,660	18,000
$72,740	$62,000
		Interest Expense	5,740	4,460
		EBT	$67,000	$57,540
		Taxes (40%)	26,800	23,016
	Net Income
$40,200	$34,524
Common dividends	$18,125		$17,262
Addition to retained earnings	$22,075
Other Data
Year-end Stock Price	$100.00	$96.00
# of shares (Thousands)	4,052
Lease payment (Thousands of Dollars)
Sinking fund payment (Thousands of Dollars)		$0
Ratio Analysis	Industry Avg
Liquidity Ratios
Current Ratio	2.58
Quick Ratio	1.53
Asset Management Ratios
Inventory Turnover	7.69
Days Sales Outstanding	47.45
Fixed Assets Turnover	2.04
Total Assets Turnover	1.23
Debt Management Ratios
Debt Ratio	32.1%
Times-interest-earned ratio	15.33
		EBITDA	4.18
Profitability Ratios
Profit Margin	8.86%
Basic Earning Power	19.48%
Return on Assets	10.93%
Return on Equity	16.10%
Market Value Ratios
Earnings per share			NA
Price-to-earnings ratio	10.65
Cash flow per share
Price-to-cash flow ratio	7.11
Book Value per share
Market-to-book ratio	1.72
a. Has Joshua & White’s liquidity position improved or worsened? Explain.
b. Has Joshua & White’s ability to manage its assets improved or worsened? Explain.
c. How has Joshua & White’s profitability changed during the last year?
d. Perform an extended Du Pont analysis for Joshua & White for 2008 and 2009. What did you find?
ROE =	PM x	TA Turnover x Equity Multiplier
e. Perform a common size analysis. What has happened to the composition
(that is, percentage in each category) of assets and liabilities?
Common Size Balance Sheets
Common Size Income Statements
f. Perform a percent change analysis. What does this tell you about the change in profitability
and asset utilization?
Percent Change Balance Sheets			Base
Percent Change Income Statements

Sheet2

7/22/12

Chapter

4/11/1

0 Chapter 2. Tool Kit f

or

Financial Statements, Cash Flows, and

Ta

x

es FINANCIAL STATEMENTS AND REPORTS (Section 2.1) The annual report contains a verbal section plus four key statements: the balance sheet, income statement, statement of retained earnings, and statement of cash flows. Spreadsheets can be used both to create and to analyze these statements, as we demonstrate in this model. In addition, note that in cells which summarize data in other cells, such as sums or differences, the spreadsheet uses formulas rather than fixed numbers. For example, the cell for

Total assets

contains the Sum formula rather than just

$2,000

. (The cell itself shows $2,000, but if you put the pointer on the cell, then the formula line will show that the cell actually contains a formula.) That way, if the data for any input (cash, for instance) changes, the spreadsheet will automatically recalculate and provide the correct net value for Total assets. As you will see as you go through our models, this automatic recalculation feature is one of the most useful and powerful aspects of Excel and other spreadsheets. Finally, note that there is a section for inputs immediately before we begin the analysis. In financial modeling, it is helpful to users when input data is grouped together, so you should follow this practice in your own models, too.

THE BALANCE SHEET (Section

2.2

)

INPUT DATA SECTION: Historical Data Used in the Analysis 2010 2009 Year

–

end common stock price $23.0

0 $26.0

0 Year-end shares outstanding (in millions) 50

50
Tax rate 40%

40%
Weighted average cost of captal (

WACC

) 11.0% 10.8% Table 2-1 MicroDrive Inc. December 31 Balance Sheets
Bart Kreps: The Balance Sheet is a snapshot of Microdrive’s financial position for a particular point in time. (in millions of dollars)

2010 2009

Assets Cash and equivalents $10 $15 Short-term investments $0 $65 Accounts receivable $375 $315 Inventories $615 $415 Total current assets $1,000 $810 Net plant and equipment $1,000
Bart Kreps: Property, Plant and Equipment minus

Depreciation $870 Total assets $2,000

$1,680 Liabilities and equity Accounts payable $60 $30 Notes payable $110

$60
Accruals $140 $130 Total current liabilities $310 $220 Long-term bonds $754 $580 Total liabilities $1,064 $800 Preferred stock

(400,000 shares) $40

$40
Common stock (50,000,000 shares)

$130 $130
Retained

earnings $766 $710 Total common equity $896 $840 Total liabilities and equity

$2,000 $1,680

THE

INCOME STATEMENT

(Section

2.3

)

Table 2-2 MicroDrive Income Statements for Years Ending December 31
Bart Kreps: The income statement represents the operating results for the accounting period

(in millions of dollars)
2010 2009

INCOME STATEMENT
Net sales $3,000.0 $2,8

50.0 Operating costs except depreciation $2,616.2 $2,497.0 Earnings

before interest, taxes, deprn., and amortization (

EBIT

DA)* $383.8 $353.0 Depreciation $100.0 $90.0 Amortization $0.0

$0.0
Depreciation and amortization

$100.0 $90.0
Earnings before interest and taxes (EBIT) $283.8 $263

.0 Less interest $88.0 $60.0 Earnings before taxes

(EBT) $195.8 $203.0 Taxes

$78.3 $81.2 Net Income before preferred dividends $117.5 $121.8 Preferred dividends $4.0

$4.0
Net Income available to common stockholders $113.5 $117.8 Common dividends $57.5 $53.0 Addition to retained earnings $56.0 $64.8 *MicroDrive has no amortization charges. We can now use the above information to calculate three specific per-share data measures: earnings per share ‘(EPS), dividends per share (DPS), and book value per share (BVPS). Simply divide the totals by the appropriate number of shares outstanding. Note that BVPS is calculated by dividing total common equity (common stock plus retained earning) by shares outstanding. Per-share Data Earnings per share (EPS) Bart Kreps: An increase in Earnings Per Share either means the company is generating more net income or they are reducing the amount of common shares outstanding.

Shares

that are repurchased by the company are called Treasury stocks. $2.27 $2.36 Dividends per share (DPS) Bart Kreps: The same rationale holds for interpreting Dividends Per Share data. If the company increases their dividend payout policies or reduces shares outstanding, DPS will increase. $1.15 $1.06 Book value per share (BVPS) $17.92 $16.80 Cash flow per share (CFPS) $4.27 $4.16 The per share data gives managers and investors a quick look at some items that affect the price of the stock.

STATEMENT OF STOCKHOLDERS’ EQUITY (Section

2.4

)

The statement of stockholders’ equity takes the previous year’s balance of common stock, retained earnings, and stockholders’ equity and then adds the current year’s net income and subtracts dividends paid to common stockholders. The end result is the new balance of common stock, retained earnings, and stockholders’ equity. Table 2-3 MicroDrive, Inc.: Statement of Stockholders’ Equity Common Stock (Millions)

Retained
Shares

Amount

Earnings

Total Equity Balances, Dec. 31,

2009 50

$130.0 $710.0 $840.0 Net income

$113.5 $113.5
Cash dividends (57.5)

(57.5)
Issuance of common stock

0 $0.0
Balances, Dec. 31, 2010 50 $130.0

$766.0 $896.0

NET CASH FLOW (Section

2.5

)
2010 2009

Net income $113.5 $117.8
Depreciation $100.0 $90.0
Net cash flow $213.5 $207.8

STATEMENT OF CASH FLOWS (Section

2.6

)

Information from the balance sheet and income statement can be used to construct the Statement of Cash Flows, which is shown below for MicroDrive, in millions of dollars. Table 2-4 MicroDrive Statement of Cash Flows for Years Ending Dec. 31
Bart Kreps: The statement of cash flows provides information about cash inflows and outflows during an accounting period.

(in millions of dollars)

Operating Activities Net Income before preferred dividends

$117.5
Noncash adjustments Depreciation and amortization

$100.0
Due to changes in working capital Increase in accounts receivable ($60.0)
Bart Kreps: Figures in parentheses are negative Bart Kreps: The income statement represents the operating results for the accounting period Bart Kreps: The statement of cash flows provides information about cash inflows and outflows during an accounting period. Bart Kreps: Property, Plant and Equipment minus Depreciation Increase in inventories (

$200

.0) Increase in accounts payable $30.0 Increase in accruals $10.0 Net cash provided (used) by operating activities ($2.5) Investing activities Cash used to acquire fixed assets ($230.0) Sale of short-term investments $65.0 Net cash provided (used) by investing activities ($165.0) Financing Activities Increase in notes payable $50.0 Increase in bonds $174.0 Payment of common and preferred dividends (

$61.5

) Net cash provided (used) by financing activities $162.5 Net change in cash and equivilents ($5.0) Cash and securities at beginning of the year $15.0 Cash and securities at end of the year

$10.0

MODIFYING ACCOUNTING DATA FOR MANAGERIAL DECISIONS (Section

2.7

)

Net Operating Working Capital Those current assets used in operations are called operating working capital, and operating working capital less operating current liabilities is called Net Operating Working Capital. 2010

NOWC = Operating current assets

–

Operating current liabilities =

$1,000 – $200
2010 NOWC = $800
2009 NOWC = Operating current assets – Operating current liabilities
= $745

–

$160 2009 NOWC =

$585 Total Net

Operating Capital

(also just called Operating Capital) The Total Net Operating Capital is Net Operating Working Capital plus any fixed assets. 2010

TOC =

NOWC

+ Fixed assets = $800 + $1,000
2010 TOC =

$1,800 2009 TOC = NOWC + Fixed assets
= $585 + $870
2009 TOC =

$1,455 Net Operating Profit After Taxes NOPAT

is the amount of profit MicroDrive would generate if it had no debt and held no financial assets. 2010

NOPAT =

EBIT x

( 1 – T ) = $284

x

60% 2010 NOPAT =

$170.3 2009 NOPAT = EBIT x ( 1 – T )
= $263 x 60%
2009 NOPAT =

$157.8 Free Cash Flow MicroDrive’s Free Cash Flow caluclation is the cash flow actually availabe for distribution to investors after the company has made all necessary investments in fixed assets and working capital to sustain ongoing operations. 2010

FCF = NOPAT + Depr.

–

Gross investment in operating capital =

$270.3

–

$445 2010 FCF =

-$174.7 or
2010 FCF = NOPAT –

Net investment in operating capital = $170.3 –

$345

2010 FCF = -$174.7

Uses of Free Cash Flow 1. After-tax interest payments 2010

After-tax interest expense = (Pre-tax interst expense) (1-T) = $88.0 x 60%

= $5

2.8

2. Net repayment of debt The amount of debt that is repaid is equal to the amount at the beginning of the year minus the amount at the end of the year. This includes notes payable and long-term debt. If the amount of ending debt is less than the beginning debt, the company paid of some of its debt. But if the ending debt is greater than the beginning debt, the company actually borrowed additional funds from creditors. In that case, it would be a negative use of FCF. 2010

Repayment to debtholders = All debt at beginning of year – all debt at end of year =

$640.0

–

$864.0 =

-$224.0 3. Total dividend payments This includes all dividends to preferred stockholders and dividends to common stockholders. 2010

Dividends = Prefered dividends + common dividends = $4.0 + $57.5
= $61.5
4. Net repurchase of stock The amount of stock that is repurchased is equal to the amount at the beginning of the year minus the amount at the end of the year. This includes preferred stock and common stock. If the amount of ending stock is less than the beginning stock, the company made net repurchases. But if the ending stock is greater than the beginning stock, the company actually made net issuances. In that case, it would be a negative use of FCF. 2010

Repurchase stock = Preferred stock and common stockat beginning of year – Preferred stock and common stock at end of year =

$170.0

– $170.0
= $0.0
5. Net purchase of short-term investments The amount of net purchases of ST investments is equal to the amount at the end of the year minus the amount at the beginning of the year. If the amount of ending investments is greater than the beginning investments, the company made net purchases. But if the ending investments are less than the beginning investments, the company actually sold investments. In that case, it would be a negative use of FCF. 2010

Purchase ST investments = ST investents at end of year – ST investments at beginning of year = $0.0 – $65.0
=

-$65.0 Summary of uses of FCF 2010
1. After-tax interest payments

$52.8 2. Net repayment of debt -$224.0
3. Total dividend payments $61.5
4. Net repurchase of stock $0.0
5. Net purchase of short-term investments -$65.0
Total uses of FCF =

-$174.7
Notice that the total uses of FCF equals the previously calculated value of FCF. MVA AND EVA (Section 2.8) Market Value Added is the difference between the market value of MicroDrive’s stock and the amount of equity capital supplied by shareholders. 2010

MVA = Stock price

x

# of shares

– Total common equity
= $23.00 x 50 – $896
=

$1,150

– $896
2010 MVA =

$254 2009 MVA = Stock price x # of shares – Total common equity
= $26.00 x 50 – $840
=

$1,300

– $840
2009 MVA =

$460 Economic Value Added Economic Value Added represents MicroDrive’s residual income that remains after the cost of all capital, including equity capital, has been deducted. 2010

EVA =

NOPAT –

Operating Capital x Weighted average cost of capital = $170.3 – $1,800 x

11% = $170.3 –

$198.0 2010 EVA =

-$27.7 2009 EVA = NOPAT – Operating Capital x Weighted average cost of capital
= $157.8 – $1,455 x 11%
= $157.8 –

$157.1 2009 EVA =

$0.7 Return on Invested Capital The Return on Invested Capital tells us the amount of NOPAT per dollar of operating capital. 2010

ROIC

=

NOPAT

÷

Operating Capital
$170.30

÷ $1,800
2010 ROIC =

9.46% 2009

ROIC =

NOPAT ÷ Operating Capital
$157.80

÷ $1,455
2009 ROIC =

10.85% Table 2-5 MVA and EVA for MicroDrive (Millions of Dollars)

2010 2009

MVA Calculation Price per share

$23.0 $26.0
Number of shares (millions)

50.0 50.0
Market value of equity = Share price (number of shares) $1,150.0 $1,300.0 Book value of equity

$896.0 $840.0
MVA = Market value – Book value $254.0 $460.0 EVA Calculation EBIT $283.8

$263.0

Tax rate 40% 40%

NOPAT = EBIT (1-T)

$170.3 $157.8
Total investor-supplied operating capitala $1,800.0 $1,455.0 Weighted average cost of capital, WACC (%)

11.0% 10.8%
Dollar cost of capital = Operating capital (WACC)

$198.0 $157.1
EVA = NOPAT – Capital cost

-$27.7 $0.7
ROIC = NOPAT/Operating capital

9.46% 10.85%
ROIC – Cost of capital = ROIC – WACC -1.54% 0.05% EVA = (Operating capital)(ROIC – WACC)

-$27.7 $0.7
aInvestor-supplied operating capital equals the sum of notes payable, long-term debt, preferred stock, and common equity, less short-term investments. It could also be calculated as total liabilities and equity minus accounts payable, accruals, and short-term investments. It is also equal to total net operating capital.

THE FEDERAL INCOME TAX SYSTEM (Section

2.9

)

This worksheet explores the calculation of corporate income taxes under the federal tax system. By using special Excel functions, we can input a corporate tax schedule into a spreadsheet and then have a cell automatically display a company’s tax liability. Either of two procedures can be used, the IF function or the V

LOOKUP

function. Both functions are explained below, using the data shown in the following tax table. LOOKUP
There are actually two lookup functions, VLOOKUP for looking up items in vertical columns, and HLOOKUP for looking up things in horizontal rows. Since our tax table is arranged in columns, we use VLOOKUP. When we use VLOOKUP, Excel first looks down the Column

(1)

of Table 2-6 below and finds the largest value that does not exceed the firm’s

taxable income

. Next, it looks for the corresponding value in Column

(3)

of Table 2-6, which is the base amount of the tax. Then, it again looks down Column (1) and finds the corresponding marginal tax rate as shown in Column

(4)

. Then it multiplies the tax rate times the difference between the firm’s taxable income and the bottom tax bracket to get the incremental tax. Then it adds the base tax to the incremental tax to get the firm’s total tax liability. It will be easier for us to use the VLOOKUP function if we first “name” the range of cells that has the data for the tax table. To do this, highlight the range which contains the tax table, A373:D380. Then click on the inverted triangle just above Column A (the formula bar) and type the word “Fedtaxtable” to name the range. We will explain how to use VLOOKUP here, and then we will use it for the calculations below Table 2-6. To get the VLOOKUP formula, click the function wizard, fx, select “Lookup & Reference,” and then select VLOOKUP. You will then get a dialog box like the one shown here. For example, suppose we have taxable income of

$65,000

. We first need to identify the bracket that this is in, then find the amount of tax on the bracket. We can do that by filling out the dialog box for the function arguments. In particular, we set the Lookup_value to $65,000, we set the Table_array to Fedtaxtable, and set the Col_index_num to 3, which is the column in the table that has the amount paid on the base. See the calculations below Table 2-6 for applications of the VLOOKUP function. Table 2-6 Corporate Tax Rates for

2009
If a corporation’s taxable income is between It pays this amount on the base of the bracket Plus this percentage on the excess over the base (1)

(2)

(3) (4)
$0

$50,000

$0

15.0% $50,000

$75,000 $7,500 25.0% $75,000

$100,000 $13,750 34.0% $100,000

$335,000 $22,250 39.0% $335,000

$10,000

,000 $113,900

34.0%
$10,000,000

$15,000,000 $3,400,000 35.0% $15,000,000

$18,333,333 $5,150,000 38.0% $18,333,333

and up $6,416,667

35.0%
Taxable Income:

$65,000
1st VLOOKUP to find the base amount of tax: $ 7,500 =VLOOKUP(C393,Fedtaxtable,3) 2nd VLOOKUP to find the marginal tax rate: 0.25 =VLOOKUP(C393,Fedtaxtable,4) 3rd VLOOKUP to find the marginal income to be taxed: $ 15,000 =C393-VLOOKUP(C393,Fedtaxtable,1) Tax on marginal income above the base: $ 3,750 Total tax liability: $11,250 Table 2-7: Apex Corporation: Calculation of $12 million Loss Carry-Back and Amount Available for Carry-Forward Past Year

Past Year

Curent Year 2008

2009 2010
Original taxable income $

2,000,000 $2,000,000 -$12,000,000 Carry-back credit

2,000,000 2,000,000
Adjusted profit

$0 $0
Taxes previously paid (40%) 800,000

800,000
Difference = Tax refund due $800,000

$800,000
Total tax refund received $1,600,000 Amount of loss carry forward available Current loss

-$12,000,000
Carry-back losses used 4,000,000 Carry-forward losses still available –

$8,000,000

Extension 2A

Plus this percentage

taxable income amount on the on the excess

base of the bracket over the base

(1) (2) (3) (4)

$0

10.0%

$8,350

15.0%

$33,950

25.0%

$82,250

$171,550

$372,950 and up

35.0% 35.0% $10,000,000

(Joint Return) Tax Table for the 2009 Tax Year

It pays this Plus this percentage Average tax

taxable income amount on the on the excess rate at
is between: base of the bracket over the base top of bracket
(1) (2) (3) (4) (5)

$0

$0.00 10.0% 10.0%

$16,700

15.0% 13.8%

$67,900

25.0%

$137,050

28.0%

Average rate at:

$208,850 $372,950

33.0%

$1,000,000

$372,950 and up

35.0% 35.0% $10,000,000

Married

$159,950

Phase-out begins: $159,950

Taxable Income:

$4,675.00

25.0%

4/11/10
Web Extension 2A: Tool Kit for Individual Taxes
Individual Tax Table for the 2009 Tax Year
If an individual’s	He/she pays this		Average tax
	rate at
	is between:		top of bracket
	(5)
	$8,350		$0.00				10.0%
	$33,950	$835.00		13.8%
	$82,250		$4,675.00	20.4%
	$171,550	$16,750.00		28.0%	24.3%		Average rate at:
			$372,950	$41,754.00		33.0%	29.0%					$1,000,000	32.8%
$108,216.00	34.8%
	Married
If a couple’s
	$16,700
	$67,900	$1,670.00
	$137,050	$9,350.00	19.4%
	$208,850	$26,637.50	22.4%
$46,741.50	27.1%	32.0%
$100,894.50	34.7%
Other Tax Data:	Exemption phase-out begins for:
Individuals
Exemption per person =	$3,650			$159,950	$239,950
Capital gains rate (most investments) =	20%
Standard deduction (individual) =	$5,700		Phase-out begins:
Standard deduction (married filing joint) =	$11,400
Base on social security (OASDI)=	$102,000
Rate on social security (OASDI, payroll)=	6.2%
Rate on social security (OASDI, self-employed)=	15.3%
Rate on medicare (payroll) =	1.45%
Rate on medicare (self-employed) =	2.90%
Example
Find the tax, the marginal tax rate, and the average tax rate for the following situation.
$35,000
Base taxable income:	$33,950.00
Base tax:
Marginal tax rate:
Tax:	$4,937.50
Average tax rate:	14.1%

2.2

Total assets $8,000,000

$2,000,000

Preferred stock $1,000,000

$2,000,000

SECTION 2.2
							SOLUTIONS TO SELF-TEST
A firm has $8 million in total assets. It has $3 million in current liabilities, $2 million in long-term debt, and $1 million in preferred stock. What is the total value of common equity?
Current liabilities		$3,000,000
Long-term debt
Common equity

2.3

SOLUTIONS TO SELF-TEST

and depreciation of

; it has no amortization. What is its

?

Earnings before taxes $2,000,000

$300,000

Depreciation $200,000
Amortization $0
EBITDA

SECTION 2.3
A firm has $2,000,000 million in earnings before taxes. The firm has an interest expense of	$300,000	$200,000	EBITDA
Interest
		$2,500,000

2.4

SOLUTIONS TO SELF-TEST

$3,000,000

$2,500,000

Common dividends $1,000,000

SECTION 2.4
A firm had a retained earnings balance of $3 million in the previous year. In the current year, its net income is $2.5 million. If it pays $1 million in common dividends in the current year, what it its resulting retained earnings balance?
Previous retained earnings balance
Current net income
Current retained earnings balance	$4,500,000

2.5

SOLUTIONS TO SELF-TEST

Net income

Depreciation $1,000,000
Net cash flow

SECTION 2.5

A firm has net income of $5 million. Assuming that depreciation of $1 million is its only noncash expense, what is the firm’s net cash flow?

$5,000,000

$6,000,000

2.6

SOLUTIONS TO SELF-TEST

$2,000,000

SECTION 2.6
A firm has inventories of $2 million for the previous year and $1.5 million for the current year. What impact does this have on net cash provided by operations?
Previous year’s inventories
Current year’s inventories	$1,500,000
Change in net cash provided by operations		$500,000

2.7

SOLUTIONS TO SELF-TEST

$2,000,000

$2,500,000

Net investment in operating capital $500,000

SECTION 2.7
A firm’s total net operating capital for the previous year was $2 million. For the current year, its total net operating capital is $2.5 million and its NOPAT is $1.2 million. What is its free cash flow for the current year?
Previous year’s total net operating capital
Current year’s total net operating capital
Current year’s NOPAT	$1,200,000
	Free cash flow	$700,000

2.8

SOLUTIONS TO SELF-TEST

ROIC

WACC

Free cash flow

SECTION 2.8
A firm has $100 million in total net operating capital. Its return on invested capital is 14 percent, and its weighted average cost of capital is 10 percent. What is its EVA?
Total net operating working capital	$100,000,000
14%
10%
$4,000,000

2.9

SOLUTIONS TO SELF-TEST

in taxable income, what is its tax liability?

$85,000

$13,750

$75,000

$10,000

SECTION 2.9
If a corporation has	$85,000
Taxable income
Base amount of tax from Table 3-6
Base of tax range
Taxable income above range
Tax rate in base	34%
Tax liability	$17,150

Chapter

4/

1

1/10 Chapter

3

. Tool Kit for Analysis of Financial Statements Financial statements are analyzed by calculating certain key ratios and then comparing them with the ratios of other firms and by examining the trends in ratios over time. We can also combine ratios to make the analysis more revealing, one below are exceptionally useful for this type of analysis. RATIO ANALYSIS (Section 3.1) Input Data: 2010 200

9 Year-end common stock price $2 3.00 $2

6

.00 Year-end shares outstanding (in millions) 50

50
Tax rate 40%

40%
After-tax cost of capital 1

1.0% 10.

8

% Lease payments $28

$28
Required sinking fund payments $20

$20
Balance Sheets (in millions of dollars) Assets

2010

2009 Cash and equivalents $10 $15 Short-term investments $0 $6

5 Accounts receivable $375 $315 Inventories $615 $415 Total current assets $1,000 $810 Net plant and equipment

$1,000

$870 Total assets $2,000 $1,680 Liabilities and equity Accounts payable $60 $30 Notes payable $110

$60
Accruals $140 $130 Total current liabilities $310 $220 Long-term bonds $754 $580 Total liabilities $1,064 $80

0 Preferred stock

(400,000 shares) $40

$40
Common stock (50,000,000 shares)

$130 $130
Retained earnings $766 $710 Total common equity $896 $840 Total liabilities and equity

$2,000 $1,680
Income Statements

(in millions of dollars)
2010 2009

Net sales $3,000.0 $2,850.0 Operating costs $2,616.2 $2,497.0 Earnings before interest, taxes, depr. & amort. (EBITDA)

$38

3.8

$353.0 Depreciation $100

.0 $90.0 Amortization $0.0

$0.0
Depreciation and amortization

$100.0 $90.0
Earnings before interest and taxes (EBIT) $283.8 $263.0 Less interest $88.0 $60.0 Earnings before taxes (EBT) $195.8 $203.0 Taxes (40%) $78.3 $81.2 Net income before preferred dividends $117.5 $12

1.8 Preferred dividends $4.0

$4.0
Net income available to common stockholders

$11

3.5

$117.8 Common dividends $57.5 $53.0 Addition to retained earnings $56.0 $64.8 Calculated Data: Operating Performance and Cash Flows

2010 2009

Net operating working capital (NOWC) $800.0 $58

5.0 Total operating capital $1,800.0 $1,455.0 Net Operating Profit After Taxes (NOPAT) $170.3 $157.8 Net Cash Flow (Net income + Depreciation) $213.5 $207.8 Operating Cash Flow (OCF) $270.3 $247.8 Free Cash Flow (FCF) ($174.7) N/A Calculated Data: Per-share Information

2010 2009

Earnings per share

(EPS) $2.27 $2.

36 Dividends per share (DPS) $1.15 $1.06 Book value per share

(BVPS) $17.92 $1

6.8

0 Cash flow

per share (CFPS) $

4.2

7 $4.16 Free cash flow per share (FCFPS)

($

3.4

9) N/A

LIQUIDITY RATIOS (Section

3.2

)

Industry 2010 2009

Average Liquidity ratios Current Ratio 3.23

3.6

8 4.2

Quick Ratio 1.24 1.80 2.1

ASSET MANAGEMENT RATIOS (Section

3.3

) Industry
2010 2009 Average

Asset Management ratios Inventory Turnover 4.88 6.87

9
Days Sales Outstanding 45.6 40.34
Christopher Buzzard: To calculate the DSO ratio, a 365-day accounting year was used.

36
Fixed Asset Turnover

3.00

3.28

3
Total Asset Turnover 1.5

0 1.7

0

1.8
DEBT MANAGEMENT RATIOS (Section 3.4)

Industry

2010 2009 Average

Debt Management ratios Debt Ratio 53.20% 47.62% 40.00% Debt-to-Equity Ratio 1.14 0.91 0.67 Market Debt Ratio 48.06% 38.10%

N/A
Times Interest Earned

3.23

4.38

6
EBITDA Coverage Ratio 3.03
Brigham: (EBITDA + Lease Payments) / (Interest + Loan Payments + Lease Payments) 3.53

8
PROFITABILITY RATIOS (Section 3.5)

Industry

2010 2009 Average

Profitability ratios Profit Margin 3.78% 4.13% 5.00% Basic Earning Power 14.19% 15.65% 17.20% Return on Assets 5.67% 7.01% 9.00% Return on Equity 12.67% 14.02% 15.00% MARKET VALUE RATIOS (Section 3.6)

Industry

2010 2009 Average

Market Value ratios Price-to Earnings Ratio 10.13 11.04 1

2.5 Price-to-Cash Flow Ratio 5.39 6.26
Christopher Buzzard: P/CF ratio is calculated by dividing the price by the net cash flow per share. Brigham: (EBITDA + Lease Payments) / (Interest + Loan Payments + Lease Payments) Christopher Buzzard: To calculate the DSO ratio, a 365-day accounting year was used.

6.8
Price-to-EBITDA

3.00

3.68 4.6 Market-to-Book Ratio 1.28 1.55

1.7
TREND ANALYSIS

,

COMMON SIZE ANALYSIS

, AND

PERCENT CHANGE ANALYSIS

(Section 3.7) TREND ANALYSIS
Trend analysis allows you to see how a firm’s results are changing over time. For instance, a firm’s

ROE

may be slightly below the benchmark, but if it has been steadily rising over the past four years, that should be seen as a good sign. A trend analysis and graph have been constructed on this data regarding

MicroDrive

‘s ROE over the past 5 years. (

MicroDrive

and indusry average data for earlier years has been provided.) ROE
MicroDrive Industry
2006 14.0% 1

3.2% 2007 16.1% 1

5.0% 2008 1

4.8% 1

6.0% 2009 14.0%

16.2% 2010

12.7%

15.0%
Figure 3-1 Rate of Return on Common Equity COMMON SIZE ANALYSIS
In common size income statements, all items for a year are divided by the sales for that year. Figure 3-2 Common Size Income Statements Industry Composite

MicroDrive
2010 2010 2009
Net sales

10

0.0% 100.0%

100.0%
Operating costs

87.6% 87.2%

87.6%
Earnings before interest, taxes, depr. & amort. (EBITDA)

1

2.4% 1

2.8%

12.4%
Depreciation and amortization 2.8%

3.3%

3.2%
Earnings before interest and taxes (EBIT)

9.6% 9.5% 9.2% Less interest

1.3% 2.9% 2.1% Earnings before taxes (EBT)

8.3% 6.5% 7.1% Taxes (40%) 3.3%

2.6%

2.8%
Net income before preferred dividends 5.0%

3.9% 4.3% Preferred dividends 0.0%

0.1%

0.1%
Net income available to common stockholders (profit margin)

5.0%

3.8% 4.1% In common sheets, all items for a year are divided by the total assets for that year. Figure 3-3 Common Size Balance Sheets

Industry Composite MicroDrive
2010 2010 2009

Assets
Cash and equivalents 1.0%

0.5% 0.9% Short-term investments

2.2%

0.0% 3.9%
Accounts receivable

17.8% 18.8%

18.8%
Inventories

1

9.8% 30.8% 24.7% Total current assets

40.8% 50.0% 48.2% Net plant and equipment

59.2%

50.0%

5

1.8% Total assets 100.0% 100.0% 100.0%

Liabilities and equity

Accounts payable 1.8%

3.0%

1.8%
Notes payable

4.4% 5.5% 3.6% Accruals 3.6%

7.0% 7.7% Total current liabilities 9.8%

15.5% 13.1% Long-term bonds

30.2% 37.7% 34.5% Total liabilities

40.0% 53.2% 47.6% Preferred stock 0.0%

2.0

%

2.4%
Total common equity

60.0% 44.8%

50.0%
Total liabilities and equity 100.0% 100.0% 100.0%
PERCENT CHANGE ANALYSIS
In percent change analysis, all items are divided by the that item’s value in the beginning, or base, year. Figure 3-4 Income Statement Percent Change Analysis Base year =

2009

Percent Change in 2010
Net sales

5.3% Operating costs 4.8%
Earnings before interest, taxes, depr. & amort. (EBITDA)

8.7% Depreciation and amortization

11.1% Earnings before interest and taxes (EBIT)

7.9% Less interest

4

6.7% Earnings before taxes (EBT)

(3.5%) Taxes (40%) (3.5%)
Net income before preferred dividends (3.5%)
Preferred dividends 0.0%
Net income available to common stockholders

(3.7%) Balance Sheet Percent Change Analysis (not in textbook)

Base year = 2009 Percent Change in
2010
Assets

Cash and equivalents

-33.3% Short-term investments

-100.0% Accounts receivable

19.0% Inventories 48.2%
Total current assets

23.5% Net plant and equipment

1

4.9

% Total assets 19.0%

Liabilities and equity

Accounts payable 100.0%
Notes payable

83.3% Accruals 7.7%
Total current liabilities

40.9% Long-term bonds

30.0% Total liabilities

33.0% Preferred stock (400,000 shares)

0.0%
Common stock (50,000,000 shares) 0.0%
Retained earnings 7.9%
Total common equity 6.7%
Total liabilities and equity 19.0%
DU PONT ANALYSIS (Section 3.8) ROE = (

Profit margin

) (TA turnover) (Equity Multiplier) MicroDrive 2010 12.67% 3.78% 1.50

2.23 MicroDrive 2009 14.02% 4.13%

1.70 2.00 Industry Average

15.00% 5.00% 1.80

1.67

MicroDrive

2006.0 2007.0 2008.0 2009.0 2010.0 0.14 0.161 0.148 0.140238095238095 0.126651785714286 Industry

2006.0 2007.0 2008.0 2009.0 2010.0 0.132 0.15 0.16 0.162 0.15

ROE
(%)

3.2

$800

2.5

$2,000

Current liabilities ($M) $800
Current ratio 2.5

2.0

SECTION 3.2
	SOLUTIONS TO SELF-TEST
A company has current liabilities of $800 million, and its current ratio is 2.5. What is its level of current assets? If this firm’s quick ratio is 2, how much inventory does it have?
	Current liabilities ($M)
	Current ratio
Current assets ($M)
Quick ratio
Curr assets – Inv ($M)	$1,600
Inventories ($M)	$400

3.3

QUESTIONS

million, $40 million of inventory, and $60 million of accounts receivable. What is its inventory turnover ratio?

$200

$40

$60

5.0

Annual Sales ($M) $200
Inventory ($M) $40
Accounts receivable ($M) $60

SECTION 3.3
			SOLUTIONS TO SELF-TEST
A firm has annual sales of					$200
	Annual Sales ($M)
	Inventory ($M)
	Accounts receivable ($M)
Inventory turnover
Days sales outstanding	109.5

3.4

SOLUTIONS TO SELF-TEST

million, interest payments of $60 million, lease payments of $40 million, and required principal payments (due this year) of $30 million. What is its

ratio?

$600

$60

Lease payments $40

$30

EBITDA coverage 4.9

SECTION 3.4
A company has EBITDA of	$600	EBITDA coverage
EBITDA ($M)
Interest payments
Principal payments

3.5

SOLUTIONS TO SELF-TEST

? What is its ROE?

$200

$10

$100

Profit margin 5.00%

Sales ($B) $200
Net income ($B) $10
Total assets ($B) $100
Debt ratio 50%

ROA

Sales ($B) $200
Net income ($B) $10
Total assets ($B) $100
Debt ratio 50%

ROE

SECTION 3.5
A company has $200 billion of sales and $10 billion of net income. Its total assets are $100 billion, financed half by debt and half by common equity. What is its profit margin? What is its	ROA
		Sales ($B)
			Net income ($B)
		Total assets ($B)
		Debt ratio			50%
10.00%
20.00%

3.6

SOLUTIONS TO SELF-TEST

per share, what is its price/earnings ratio? Its price/cash flow ratio? Its market/book ratio?

Net income ($B) $6

$2

$80

1

$96

Earnings per share $6
Cash flow

8.00

Book value per share

SECTION 3.6
A company has $6 billion of net income, $2 billion of depreciation and amortization, $80 billion of common equity, and one billion shares of stock. If its stock price is	$96
Amortization and depreciation ($B)
Common equity
Number of shares ($B)
Stock price per share
P/E ratio	16.00
$	8.00
Cash flow per share
Price/cash flow	12.00
80.00
Market/Book	1.20

3.8

SOLUTIONS TO SELF-TEST

Profit margin 6.0%

2.0

1.5

ROE

SECTION 3.8

A company has a profit margin of 6%, a total asset turnover ratio of 2, and an equity multiplier of 1.5. What is its ROE?

Total asset turnover

Equity multiplier

18.0%