Chapters 2 & 3 Worksheet Assignment

Fill in the yellow cells with the appropriate functions/formulas.

Do not type in the numerical answer. Let Excel calculate the number using your formula in the cell.

Balance Sheet ($million)

2012

2011

2012

2011

Start each formula with and equal sign: =

Cash

24

20

Accounts Payble

216

180

You can use these symbols in your formulas:

Accounts receivable

360

300

Notes Paybable

134

103

Inventory

360

400

Accrued wages&taxes

144

120

Add

+

Total Current Assets

744

720

Total Current Liab.

494

403

Subtract

-

Gross Plant & Equip.

650

L-t debt/bonds

300

300

Multiply

*

-Accum. deprciation

150

Common stock

100

100

Divide

/

Net Plant & Equip.

600

500

Retained earnings

450

417

Make use of Parentheses: ( )

Total Assets

Total Stockhldr Equity

Total Liab. and Equity

Do not type any values in the cell or in the formula.

Income Statement ($million)

Example of a nonsensical ratio:

Net sales (all credit)

2400.0

2000.0

Additional information:

I want to calculate the 2012 value of: (Total Assets - Accruals)/((Retained Earnings*shares outstanding)+dividends)

Cost of goods sold

2040.0

1700.0

Average Tax Rate =

40%

Depreciation

60.0

50.0

Apply to all Net Income

EBIT

shares outstanding =

50

million

I would type the following in the cell:

interest

43.4

40.4

Common stock price =

$10.00

=(C15-G9)/((G13*G20)+C26)

EBT

Equals market value

The Answer is:

-0.006

taxes

The firm has no preferred stock

You can click on cell L23 to see the formula in the

Net Income

bar above.

Common dividends

121.0

8.8

Calculate the following:

2012

2011

OCF - Operating Cash Flow

Net Operating Working Capital

Change in NOWC

Change in Gross Plant & Equipment

Investment in Operating Capital

FCF - Free Cash Flow

Liquidity Ratios

Current Ratio

1.51

1.79

Quick Ratio

0.78

0.79

Asset Mgmt. Ratios

Inventory Turnover (use Sales)

6.67

5.00

Average Collection Period (days)

54.8

73.0

Fixed Asset Turnover

4.00

4.00

Total Asset Turnover

Capital Intensity

Debt Mgmt. Ratios

Debt Ratio

Debt to Equity Ratio

Times Interest Earned

Profitability Ratios

Profit Margin

Basic Earnings Power

ROA

ROE

Dividend Payout ratio

EPS

Market Value Ratios

P/E

Market to Book