Corporate Finance.Essentials 9e Chapter 03 Excel Master student

Chapter 3

Ross, Westerfield, and Jordan's Excel Master

Essentials of Corporate Finance, 9th edition

by Brad Jordan and Joe Smolira

Version 9.0

Chapter 3

In these spreadsheets, you will learn how to use the following Excel functions:

The following conventions are used in these spreadsheets:

1) Given data in blue

2) Calculations in red

NOTE: Some functions used in these spreadsheets may require that

the "Analysis ToolPak" or "Solver Add-In" be installed in Excel.

To install these, click on the File button

then "Options," "Add-Ins" and select

"Go." Check "Analysis ToolPak" and

"Solver Add-In," then click "OK."

SUMIF

/xl/drawings/drawing1.xml#'Section%203.1'!A57

Absolute references

/xl/drawings/drawing1.xml#'Section%203.1'!A26

Text boxes

/xl/drawings/drawing1.xml#'Section%203.3'!A65

Shapes and lines

/xl/drawings/drawing1.xml#'Section%203.3'!A65

Conditional formatting

/xl/drawings/drawing1.xml#'Section%203.5'!A10

IF

/xl/drawings/drawing1.xml#'Section%203.2'!A51

Section 3.1

Chapter 3 - Section 1
Standardized Financial Statements
The balance sheets for Prufrock Corporation are:
Prufrock Corporation
2015 and 2016 Balance Sheets ($ millions)
2015	2016	2015	2016
Current assets	Current liabilities
Cash	$ 84	$ 98	Accounts payable	$ 312	$ 344
Accounts receivable	165	188	Notes payable	231	196
Inventory	393	422	Total	$ 543	$ 540
Total	$ 642	$ 708	Long-term debt	$ 531	$ 457
Owners' equity
Fixed assets	Common stock and
Net plant and equipment	$ 2,731	$ 2,880	paid-in surplus	$ 500	$ 550
Retained earnings	1,799	$ 2,041
Total	$ 2,299	$ 2,591
Total assets	$ 3,373	$ 3,588	Total liabilities and equity	$ 3,373	$ 3,588
To construct the common-size balance sheet, we need to divide each asset account by the total assets and each liabilities and equity account by the total liabilities and equity. Once we enter the formula, we can copy and paste the formula instead of inserting in an equation in each cell. One problem with copying and pasting is that Excel keeps the formula in relation to the previous cells. For example, if you copy and paste a formula in cell B5 that references cell B2, wherever you paste the equation, it will always reference 3 cells above where you pasted it. For example, if you paste a formula from cell B5 that references cell B2 into cell D12, in the new position it will reference cell D9. A solution is to use absolute references in your formula.
RWJ Excel Tip
To use an absolute reference, use the $ before the column letter and row number. For example, to reference cell E21 when you copy and paste a formula, you would enter it as $E$21. A quick way to do this is just enter E21, then hit the F4 key. Wherever copied and pasted, this equation will always reference cell E21. If you want to reference the column and not the row, you would enter $E21, or if you want to reference the row and not the column, you would enter it as E$21.
Using absolute references and the balance sheet from the previous worksheet, the common-sized balance sheet is:
Prufrock Corporation
Common-Size Balance Sheets 2015 and 2016
2015	2016	Change	2015	2016	Change
Current assets	Current liabilities
Cash	2.49%	2.73%	0.24%	Accounts payable	9.25%	9.59%	0.34%
Accounts receivable	4.89%	5.24%	0.35%	Notes payable	6.85%	5.46%	-1.39%
Inventory	11.65%	11.76%	0.11%	Total	16.10%	15.05%	-1.05%
Total	19.03%	19.73%	0.70%	Long-term debt	15.74%	12.74%	-3.01%
Owners' equity
Fixed assets	Common stock and
Net plant and equipment	80.97%	80.27%	-0.70%	paid-in surplus	14.82%	15.33%	0.51%
Retained earnings	53.34%	56.88%	3.55%
Total	68.16%	72.21%	4.05%
Total assets	100.00%	100.00%	0.00%	Total liabilities and equity	100.00%	100.00%	0.00%
Using absolute references and the income statement above, the common-sized income statement is:
Prufrock Corporation
2016 Common-Size Income Statement
Sales	$ 2,311
Cost of goods sold	$ 1,344
Depreciation	$ 276
Earnings before interest and taxes	$ 691
Interest paid	141
Taxable income	$ 550
Taxes	187
Net income	$ 363
Dividends	$ 121
Additions to retained earnings	242
The common-base year balance sheet is constructed by dividing the current year by the base year, in this case, dividing 2016 numbers by 2015 numbers. The common-size and base year balance sheet is found by dividing the 2016 account values by the total assets or total liabilities and equity, respectively. The common base year and common-size base year balance sheet for Prufrock are:
Prufrock Corporation
Common Balance Sheets 2015 and 2016
Common- base year	Common- size and base year	Common- base year	Common- size and base year
2016	2016	2016	2016
Current assets	Current liabilities
Cash	1.17	1.10	Accounts payable	1.10	1.04
Accounts receivable	1.14	1.07	Notes payable	0.85	0.80
Inventory	1.07	1.01	Total	0.99	0.93
Total	1.10	1.04	Long-term debt	0.86	0.81
Owners' equity
Fixed assets	Common stock and
Net plant and equipment	1.05	0.99	paid-in surplus	1.10	1.03
Retained earnings	1.13	1.07
Total	1.13	1.06
Total assets	1.06	1.00	Total liabilities and equity	1.06	1.00

Section 3.2

Chapter 3 - Section 2
Ratio Analysis
To compare the performance of companies, rather than compare the financial statements, financial ratios are often used. Ratios are divided into categories based on what each ratio measures. Below, we calculate some basic financial ratios for Prufrock for 2016:
Short-Term Solvency, or Liquidity, Measures
Current ratio:	1.31	times
Quick ratio:	0.53	times
Cash ratio:	0.18	times
Long-Term Solvency Measures
Total debt ratio:	0.28	times
Debt-equity ratio:	0.38	times
Equity multiplier:	1.38	times
Times interest earned:	4.90	times
Cash coverage:	6.86	times
Asset Management, or Turnover, Measures
Inventory turnover:	3.18	times
Days' sales in inventory:	114.61	days
Receivables turnover:	12.29	times
Days' sales in receivables:	29.69	days
Total asset turnover:	0.64	times
Profitability Measures
Profit margin:	15.71%
Return on assets (ROA):	10.12%
Return on equity (ROE):	14.01%
Since ROA and ROE are intended to measure performance over a prior period, often the average assets and average equity, respectively, are used in the calculation. The ROA and ROE using the averages in the denominator are:
ROA with average assets:	10.43%
ROE with average equity:	14.85%
Market Value Measures
Market value ratios use other numbers such as shares outstanding and share price that are not found on the balance sheet. The other numbers we need are:
Shares outstanding:	33.00	(in millions)
Price per share:	$ 88.00
Earnings per share (EPS):	$ 11.00
Price-earnings ratio (PE):	8.00	times
Price-sales ratio:	1.26	times
Market-to-book ratio:	1.12	times
RWJ Excel Tip
We used a "trick" in the calculation of ROA, ROE, and the price-earnings ratio. You can always mathematically calculate a ratio, however what happens when the denominator is negative? While this is very unlikely to happen with ratios such as the current ratio, it does happen in these three ratios when the net income is negative, or if the equity is negative. In this case, these ratios are often reported as "NMF" for "No meaningful figure." Excel will try to calculate the ratio, as since the denominator is negative, Excel will report "#DIV/0" to let you know you have tried to divide by zero. We prefer to see "NMF," so we used a simple IF statement to display "NMF" if these ratios are negative. Do you see any of these other ratios which could be negative?
RWJ Excel Tip
IF statements are used for a logical test. The IF function is located under Logical. The basic format of an IF statement is =IF(logical_test,[value_if_true],[value_if_false]). In an IF statement, we determine the possible conditions first, then determine what value we want if the test is true, or else the value in the cell will be false. For example, in the case of the PE ratio, our possible conditions are that earnings are positive or negative (we will ignore the possibility earnings are exactly zero). If earnings are positive, we want to calculate the PE as the stock price divided by earnings per share. However, if earnings are negative, we want NMF to display. To have an IF statement return text, put the text in parentheses. The format for the PE ratio is:

Section 3.3

Chapter 3 - Section 3
The Du Pont Identity
The three-factor Du Pont equation separates the ROE into 3 components. For Prufrock, the three-factor Du Pont equation is:
ROE =	Profit margin ´ Total asset turnover ´ Equity multiplier
ROE =	15.71%	´	0.64	´	1.38
ROE =	14.01%
This is exactly the same as the ROE we previously calculated, but it breaks ROE into its constant parts. Of course, ROE can be further subdivided, which we will do next. The abbreviated financial statements for Du Pont for the most recent year were:
Du Pont Income Statement	Du Pont Balance Sheet
12 months ending Dec 31, 2015	12 months ending Dec 31, 2015
($ in millions)	($ in millions)
Sales	$ 35,553	Assets	Liabilities and Owner's Equity
CoGS	21,703	Current assets	Current liabilities
Gross profit	$ 13,850	Cash	$ 7,034	Accounts payable	$ 11,217
SG&A expenses	6,411	Accounts receivable	6,594	Notes payable	1,423
Depreciation	2,067	Inventory	8,120
EBIT	$ 5,372	Total	$ 21,748	Total	$ 12,640
Interest	377
EBT	$ 4,995	Long-term debt	$ 23,916
Taxes	1,370
Net income	$ 3,625	Fixed assets	$ 28,128	Total equity	$ 13,320
Total liabilities and
Total assets	$ 49,876	owner's equity	$ 49,876
Now we can show an expanded Du Pont analysis, which is:
Return on equity
27.21%
ROA	Equity multiplier
7.27%	3.74
Profit margin	Total asset turnover
10.20%	0.71
Net income	Sales	Sales	Total assets
$ 3,625	$ 35,553	$ 35,553	$ 49,876
Total costs	Sales	Fixed assets	Current assets
$31,928	$ 35,553	$ 28,128	$21,748
Cost of goods sold	Dep.	Cash
$21,703	$2,067	$ 7,034
Selling, general, & admin. expense	Interest	Accounts receivable	Inventory
$6,411	$377	$6,594	$8,120
Taxes
$1,370
RWJ Excel Tip
To insert the mathematical instructions, we used text boxes. To insert a text box, simple go to "Insert" and select "Text Box." Text boxes allow you to enter text that is "linked" to the position you put the box. Although we could have entered the text in a cell, text boxes in this case allow the text to go across multiple rows and/or columns, which can often look more professional. Text boxes are also very useful in graphing, which we will show later. To draw the lines between the boxes, we went to "Insert" and selected "Shapes." We chose the straight line, although many other choices are available.

Multiplied by

Divided by

Divided by

Subtract-ted from

Plus

Multiplied by

Section 3.5

Chapter 3 - Section 5
Using Financial Statement Information
Financial ratio analysis is very much a management by exception tool. When you are analyzing ratios, a number by itself tells you very little. However, whenever a ratio is above or below some level, it tells us that something is different. It is up to us to determine if that difference is good or bad. We can always compare each ratio to the industry average, or some ratio target, but looking through columns of numbers can be difficult. Excel has a conditional formatting function which will allow you to change the display for numbers within a specified range or outside a specified range. In this case, we have a range in mind, so we want to change the font color of the calculated number, as well as the cell color if the company's ratio is outside a specified range. Additionally, we used a basic IF statement to tell us specifically if the ratio appears to be too high, too low, or within the acceptable range.
While we used factors of 0.75 and 1.25, these factors could be higher or lower depending on the industry. If you change these factors in the spreadsheet, Excel will automatically change the graphics to match the new calculations.
RWJ Excel Tip
In our conditional formatting, on the Home tab, we selected Conditional Formatting, and New Rule. This allows us to create a rule for the conditional formatting. We selected "Format only cells that contain" and then used the pulldown menu to choose "not between." The next two cells allow use to enter the calculations, so we entered a formula to multiply the industry ratio by the lower bound and the industry ratio by the upper bound. Next, we selected "Format.." on this window. The "Format.." option took us to another window that allows you to specify the font color, cell color fill, and more. We made the font color white and the cell fill red if the company's ratio is outside the range specified in the 3rd and 4th inputs.
Lower range:	0.75
Upper range:	1.25
Short-Term Solvency, or Liquidity, Measures	Average
Current ratio:	1.31	1.39	Looks OK.
Quick ratio:	0.53	0.85	Too low?
Cash ratio:	0.18	0.35	Too low?
Long-Term Solvency Measures
Total debt ratio:	0.28	0.19	Too high?
Debt-equity ratio:	0.38	0.29	Too high?
Equity multiplier:	1.38	1.29	Looks OK.
Times interest earned:	4.90	3.80	Too high?
Cash coverage:	6.86	4.15	Too high?
Asset Management, or Turnover, Measures
Inventory turnover:	3.18	2.95	Looks OK.
Days' sales in inventory:	114.61	123.73	Looks OK.
Receivables turnover:	12.29	14.15	Looks OK.
Days' sales in receivables:	29.69	25.80	Looks OK.
Total asset turnover:	0.64	0.71	Looks OK.
Profitability Measures
Profit margin:	15.71%	17.83%	Looks OK.
Return on assets (ROA):	10.12%	12.18%	Looks OK.
Return on equity (ROE):	14.01%	15.83%	Looks OK.
A quick glance indicates that Prufrock's liquidity ratios and long-term solvency ratios are generally outside the ranges we set, while the asset management and profitability ratios appear to be similar to the industry average for all of the ratios in these two categories. This means we should investigate the causes of the differences in the liquidity ratios and long-term solvency ratios.

Master it!

Chapter 3 - Master it!

The exTensible Business Reporting Language (XBRL) is likely the future of financial reporting. XBRL is a computer language that "tags" each item and specifies what that item is. XBRL reporting has already been adopted for use in Australia, Japan, and the United Kingdom. As of early 2009, a Securities and Exchange Commission advisory committee has recommended that U.S. companies be required to submit financial reports to the SEC in XBRL format. It has been suggested that requiring XBRL be gradually implemented, so it could be several years before we see all companies file XBRL financial reports. All listed U.S. companies file electronic reports with the SEC, but the reports are only available in html or text format. XBRL reporting will allow investors to quickly download financial statements for analysis.

Currently, several companies voluntarily submit financial statements in XBRL format. For this assignment, go to the SEC website at www.sec.gov. Once there, search for a company and click the link that says "Interactive Data" in the report list and follow the link. Now follow the "Interactive Data Viewers" link, then "Interactive Financial Reports." Download the income statement and balance sheet from the annual report for the company you chose. Using these reports, calculate the financial ratios for the company from the data available, typically two or three years. Do you notice any changes in these ratios that might indicate further investigation?

Solution

Master it! Solution
Short-Term Solvency, or Liquidity, Measures	2015	2014
Current ratio:
Quick ratio:
Cash ratio:
Long-Term Solvency Measures
Total debt ratio:
Debt-equity ratio:
Equity multiplier:
Times interest earned:
Cash coverage:
Asset Management, or Turnover, Measures
Inventory turnover:
Days' sales in inventory:
Receivables turnover:
Days' sales in receivables:
Total asset turnover:
Profitability Measures
Profit margin:
Return on assets (ROA):
Return on equity (ROE):