CMU Brady Corporation Balance Sheets Cost of Goods Sold Excel Worksheet DescriptionComplete the common-size and common-base year financial statements [Inco

CMU Brady Corporation Balance Sheets Cost of Goods Sold Excel Worksheet DescriptionComplete the common-size and common-base year financial statements [Income Statement (IS) and Balance Sheet (BS)] or Brady Corp. in Excel in the M2_Project_Excel Preview the documentfile I provided. Be sure to include spark lines to easily show the trends. (25 pts) Write a brief analysis in the Excel worksheet of the main trends that you notice. (25 pts)
See Assigned Readings / Videos “Related to common-size and common-base year financial statements for Project” for videos on using Excel for complete these statements.
See Assigned Readings / Videos “Related to Excel Basics for Project” for videos 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. (25 pts) Write a brief analysis (50-100 words) of what you learn from the DuPont analysis on your Excel worksheet. (25 pts)
For a good example see assigned Readings / Videos “Related to DuPont Return on Equity (ROE) for ProjectPreview the document”. Schedule One
Brady Corporation
Income Statements (000’s/$)
Revenue
Cost of Goods Sold
Gross Profit
Operating Expenses
Selling
General & Administrative
Total Operating Expenses
Operating Profit
Interest Expense
Earnings Before Taxes
Taxes
Earnings After Taxes
3 Step DuPont ROE Analysis
Common-size
2014
2015
$
$
18,675 28,675
15,932 24,393
2,743
4,282
1,251
1,090
2,341
402
210
192
67
125
1,851
1,590
3,441
841
510
331
116
215
2016
$
48,845
42,007
6,838
2,734
2,192
4,926
1,912
1,059
853
299
554
2014
2015
2016
Common-base year
Trend
Trend
2014
2015
2016
Brady Corporation
Balance Sheets (000’s/$)
Common-size
2014
$
2015
$
2016
$
Current Assets
Cash
Receivables
Inventory
Total current assets
25
1,712
1,582
3,319
20
10
3,412 6,454
2,958 6,490
6,390 12,954
Fixed Assets
Land
Net plant, property & equipment
Total fixed assets
590
4,740
5,330
590
4,882
5,472
Total Assets
8,649 11,862 19,239
Current liabilities
Account payables
Short-term notes payables
Total current liabilities
1,019
300
1,319
2,019
1,300
3,319
4,656
2,500
7,156
Long-term liabilities
2,300
3,330
6,355
3,619
6,649 13,511
Shareholders’ equity
Common stock
Retained earnings
Total equity
4,500
218
4,718
4,500
433
4,933
Total liabilities & equity
8,649 11,862 19,239
Total liabilities
590
5,695
6,285
4,500
987
5,487
2014
2015
Common-size
Common-base year
Trend
2016
Trend
2014
2015
2016
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.
Sales
Earnings After Tax
Total Assets
Total Equity
2016
2700
400
3500
1600
2017
3300
400
4800
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?).

Purchase answer to see full
attachment