1. In your text, go through Chapter 3 ST1 through ST5 (pages 104-105). Use the Excel file M2_Problem_Set_Excel Download M2_Problem_Set_Excel that I provide and the formula sheet in the book to make the calculations. Be sure to use cell references. (50 pts) See Assigned Readings Related to Excel Basics for Problem Set for tutorials on entering formulas in Excel. Note: You can skip ST3. part D if you want with no penalty. Write one sentence (in the Excel File) next to each ratio you calculated in Question 1 describing what that number means in words. (50 pts) For example, if you calculate the average collection period as 40, you should write something like on average, it takes 40 days for Freemont Corporation to collect its accounts receivable.

2) Instructions: Complete 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 Download M2_Project_Excel file I provided. Be sure to include sparkles to easily show the trends. (25 pts) Write a brief analysis in the Excel worksheet of the main trends that you notice. (25 pts)

Schedule One Brady Corporation Income Statements (000's/$) Common-size Common-base year Trend Trend 2014 2015 2016 2014 2015 2016 2014 2015 2016 $ $ $ 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 Trend Trend 2014 2015 2016 2014 2015 2016 2014 2015 2016 $ $ $ 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

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 Project Download Related to DuPont Return on Equity (ROE) for Project.

Submit your modified Excel file when you have completed the project by 11:59 PM ET Sunday.

Dupont Analysis 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 firms 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?).