- Have any Questions
- +1 760 970-5451
- support@trustedpaperwriters.com

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.1′!A26Text boxes

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

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

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 |

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: |

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

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

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? |

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): |