Finance Excel Question Problems

profilejandreg
ch02_tool_kit1.xlsx
Home>Business & Finance homework help>Finance Excel Question Problems

Chapter

4/11/10
Chapter 2. Tool Kit for Financial Statements, Cash Flows, and Taxes
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.00 $26.00
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,850.0
Operating costs except depreciation $2,616.2 $2,497.0
Earnings before interest, taxes, deprn., and amortization (EBITDA)* $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%
= $52.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 VLOOKUP 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

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 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 $8,350 $0.00 10.0% 10.0%
$8,350 $33,950 $835.00 15.0% 13.8%
$33,950 $82,250 $4,675.00 25.0% 20.4%
$82,250 $171,550 $16,750.00 28.0% 24.3% Average rate at:
$171,550 $372,950 $41,754.00 33.0% 29.0% $1,000,000 32.8%
$372,950 and up $108,216.00 35.0% 35.0% $10,000,000 34.8%
Married (Joint Return) Tax Table for the 2009 Tax Year
If a couple's 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 $16,700 $0.00 10.0% 10.0%
$16,700 $67,900 $1,670.00 15.0% 13.8%
$67,900 $137,050 $9,350.00 25.0% 19.4%
$137,050 $208,850 $26,637.50 28.0% 22.4% Average rate at:
$208,850 $372,950 $46,741.50 33.0% 27.1% $1,000,000 32.0%
$372,950 and up $100,894.50 35.0% 35.0% $10,000,000 34.7%
Other Tax Data: Exemption phase-out begins for:
Individuals Married
Exemption per person = $3,650 $159,950 $239,950
Capital gains rate (most investments) = 20%
Standard deduction (individual) = $5,700 Phase-out begins: $159,950
Standard deduction (married filing joint) = $11,400 Phase-out begins: $159,950
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.
Taxable Income: $35,000
Base taxable income: $33,950.00
Base tax: $4,675.00
Marginal tax rate: 25.0%
Tax: $4,937.50
Average tax rate: 14.1%

2.2

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?
Total assets $8,000,000
Current liabilities $3,000,000
Long-term debt $2,000,000
Preferred stock $1,000,000
Common equity $2,000,000

2.3

SECTION 2.3
SOLUTIONS TO SELF-TEST
A firm has $2,000,000 million in earnings before taxes. The firm has an interest expense of $300,000 and depreciation of $200,000; it has no amortization. What is its EBITDA?
Earnings before taxes $2,000,000
Interest $300,000
Depreciation $200,000
Amortization $0
EBITDA $2,500,000

2.4

SECTION 2.4
SOLUTIONS TO SELF-TEST
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 $3,000,000
Current net income $2,500,000
Common dividends $1,000,000
Current retained earnings balance $4,500,000

2.5

SECTION 2.5
SOLUTIONS TO SELF-TEST
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?
Net income $5,000,000
Depreciation $1,000,000
Net cash flow $6,000,000

2.6

SECTION 2.6
SOLUTIONS TO SELF-TEST
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 $2,000,000
Current year's inventories $1,500,000
Change in net cash provided by operations $500,000

2.7

SECTION 2.7
SOLUTIONS TO SELF-TEST
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 $2,000,000
Current year's total net operating capital $2,500,000
Current year's NOPAT $1,200,000
Net investment in operating capital $500,000
Free cash flow $700,000

2.8

SECTION 2.8
SOLUTIONS TO SELF-TEST
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
ROIC 14%
WACC 10%
Free cash flow $4,000,000

2.9

SECTION 2.9
SOLUTIONS TO SELF-TEST
If a corporation has $85,000 in taxable income, what is its tax liability?
Taxable income $85,000
Base amount of tax from Table 3-6 $13,750
Base of tax range $75,000
Taxable income above range $10,000
Tax rate in base 34%
Tax liability $17,150