# CORPORATE FINANCE

12/09/2019
12/09/2019

## 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′!A57Absolute references

/xl/drawings/drawing1.xml#’Section%203.3′!A65Shapes and lines

/xl/drawings/drawing1.xml#’Section%203.3′!A65Conditional formatting

/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.