A. Instructions: Complete the common-size and common-base year financial statements [Income Statement (IS) and Balance Sheet (BS)] or Brady Corp. in Excel. Be sure to include spark lines to easily show the trends. Write a brief analysis in the Excel worksheet of the main trends that you notice on creating sparklines in Excel and how to reference other excel sheets when computing ratios. Perform the 3 step DuPont Return on Equity (ROE) analysis for Brady Corp. using cell references. Write a brief analysis (50-100 words) of what you learn from the DuPont analysis on your Excel worksheet.

Schedule One
Brady Corporation
Income Statements (000's/$)
Common-size Common-base Year

2014 2015. 2016 2014 2015 2016 Trend 2014 2015 2016 Trend
$ $ $
Revenue 18,675 28,675 48,845
Cost of Goods Sold 15,932 24,393 42,007
Gross Profit 2,743 4,282 6,838
Operating Expenses
Selling 1,251 1,851 2,734
General & Administrative 1,090 1,590 2,192
Total Operating Expenses 2,341 3,441 4,926
Operating Profit 402 841 1,912
Interest Expense 210 510 1,059
Earnings Before Taxes 192 331 853
Taxes. 67 116 299
Earnings After Taxes 125 215 554

3 Step DuPont ROE Analysis

Brady Corporation
Balance Sheets (000's/$) Common-size Common-base year

2014 2015 2016 2014 2015 2016 Trend 2014 2015. 2016 Trend
$ $ $
Current Assets
Cash 25 20 10
Receivables 1,712 3,412 6,454
Inventory 1,582 2,958 6,490
Total current assets 3,319 6,390 12,954

Fixed Assets
Land. 590 590 590
Net plant, property & equipment.4,740 4,882 5,695
Total fixed assets. 5,330 5,472 6,285
Total Assets 8,649 11,862. 19,239
Current liabilities
Account payables 1,019 2,019 4,656
Short-term notes payables 300 1,300 2,500
Total current liabilities 1,319. 3,319. 7,156
Long-term liabilities 2,300 3,330 6,355
Total liabilities. 3,619 6,649 13,511
Shareholders' equi
Common stock 4,500 4,500 4,500
Retained earnings 218 433. 987
Total equity 4,718. 4,933 5,487
Total liabilities & equity 8,649 1,862 19,239

B. Dupont Analysis of Return on Equity

1. Using the following table, calculate the Calculate the ROE in each year using the simple formula.

2. As an analyst using just this information, what do you conclude?

Select Financial Data for ABC Corp.
2016 2017
Sales 2700 3300
Earnings After Tax 400 400
Total Assets 3500 4800
Total Equity 1600 1600

1. ROE 2016 = (Earnings After Tax) / (Total Equity) = 400/1600 = 25%
ROE 2017 = 400/1600 = 25%

2. ROE did not change year-over-year.
Can we go any deeper?
Breaking down ROE, a single ratio that measures profitability, into 3 ratios, one which measures profitability, one which
measures how efficiently firms use their assets to generate sales, and one that measures the use of leverage in a firm’s
capital structure, can help the financial manager identify what caused ROE to change, or in this case, stay the same.
DuPont ROE = EAT/Equity = (EAT / Sales) * (Sales / Assets) * (Assets / Equity)
2016: 400/2700 * 2700/3500 *3500 / 1600 = .1481 * .7714 * 2.1875 = 25%
2017: 400/3300 * 3300/4800 * 4800/1600 = .1212 * .6875 * 3.0000 = 25%
While ROE stayed the same, the net profit margin (a measure of profitability) declined and total asset turnover (a
measure of how well the firm is using its assets to generate sales) declined. ROE stayed the same because the firm used more financial leverage, which could be detrimental in the future if performance in other areas continues to decline(how will the firm be able to make the interest and principal payments on its increased debt if performance continues to deteriorate?).

Answer rating: 100% (QA)

A Instructions Complete the commonsize and commonbase year financial statements Income Statement IS and Balance Sheet BS or Brady Corp in Excel Be sure to include spark lines to easily show the trends ... View the full answer