NOTE FROM PROFESSOR:

Complete the Module 1 Assignment using the included Excel Spreadsheet template. This assignment focuses on financial statement analysis and financial ratios

Excel is a powerful tool for financial calculations. Use the functions of Excel to solve these problems instead of hard wiring in your figures to Excel. This will help you practice your skills and minimize math errors. To see all of the formulas on a spreadsheet, use the command of CTRL+`

Global Financial Managaement Rivier University 1 2. 3 4 5 Module 1 Problem Set Financial Analysis 12 Assets Directions: Dunder Mifflin, Inc. is a regional paper distributor located on the east coast of the United States. The CFO has requested that data for the previous years of 2015 and 2016 be compared to industry averages as well as previous activity. Using the financial data provided below, calculated the requested ratios and analysis. Use the functions of 6 Excel for calculating your data. 7 8 Dunder Mifflin, Inc. 9 December 31 Balance Sheets 10 (Thousands of Dollars) 11 2016 2015 13 Cash and cash equivalents $36,000 $42,575 14 Short-term investments 2,765 1,500 15 Accounts Receivable 65,900 64,889 16 Inventories 105,000 98,780 17 Total current assets $209,665 $207,744 18 Net fixed assets 178,000 175,000 19 Total assets $387665 $382.744 20 21 Liabilities and equity 22 Accounts payable $24,765 $25,000 23 Accruals 14,230 14,000 24 Notes payable 22,890 25,400 25 Total current liabilities $61,885 $64,400 26 Long-term debt 63.780 62,335 27 Total liabilities $125,665 $126,735 28 Common stock 208,000 203,000 29 Retained Earnings 54,000 53,009 30 Total common equity $262.000 $256,009 31 Total liabilities and equity $387 665 $382.744 32 33 Dunder Mifflin Inc. 34 December 31 Income Statements 35 (Thousands of Dollars) 36 2016 2015 $389,000 $376,000 38 COGS except excluding depr. and amort. 285,700 259,000 37 Sales nnnn 2016 $389,000 285,700 23,600 34,000 $45,700 6,300 $39,400 15,760 $23.640 2015 $376,000 259,000 22,450 32,500 $62,050 6,000 $56,050 22,420 $33.630 $17,950 $16,730 $18,900 $23,680 Dunder Mimin Inc. 4 December 31 Income Statements 5 (Thousands of Dollars) 6 7 Sales 8 COGS except excluding depr. and amort. 9 Depreciation and Amortization 0 Other operating expenses 1 EBIT 2 Interest Expense 3 EBT 4 Taxes (40%) 5 Net Income 6 7 Common dividends 8 Addition to retained earnings 9 0 Other Data -1 Year-end Stock Price 2 # of shares (Thousands) 3 Lease payment (Thousands of Dollars) 4 Sinking fund payment (Thousands of Dollars) 5 6 Ratio Analysis -7 Profitability Ratios 8 Profit Margin 9 Basic Earning Power 0 Return on Assets 1 Return on Equity 2 Asset Management Ratios 3 Inventory Turnover (Total COGS/Inventories) 4 Days Sales Outstanding 5 Fixed Assets Turnover 6 Total Assets Turnover 7 Liquidity Ratios 8 Current Ratio 9 Quick Ratio O Debt Management Ratios 1 Debt Ratio (Total debt-to-assets) 2 Liabilities-to-assets ratio 3 Times-interest-earned ratio 4 EBITDA coverage ratio 2016 $84.00 6,203 $30,000 $3,000 2015 $92.00 6,100 $31,000 $3,000 2016 2015 Industry Avg_Formula Used 5.78% net income/sales 13.65% EBIT/Total Assets 5.90% Net income/total assets 10.04% net income/total common equity 1.95 (COGS minus depreciation and amortization + depreciation and amortization) / inventories 59.85 Accounts Receivable / (sales/365) 2.17 saleset fixed assets 1.05 sales/total assets 3.25 total current assets / total current liabilities 1.75 (total current assets - inventories) / total current assets 23.0% (notes payable + long term debt) / total assets 32.2% total liabilities / total assets 8.75 EBIT / interest expense 2.74 (EBIT + depreciation and amortization + lease payments) / (Interest expense + lease payments + sinking fund payments) Market Value Ratios Earnings per share Price-to-earnings ratio Cash flow per share Price-to-cash flow ratio Book Value per share Market-to-book ratio NA net income / number of shares 18.17 year end stock price / earnings per share NA (net income + depreciation and amortization) / number of shares 9.54 year end stock price / cash flow per share NA total common equity / number of shares 2.00 year end stock price / book value per share . . Perform a Dupont Analysis for Dunder Mifflin, Inc. for 2015 and 2016 Hint: Use the Product(...) function of Excel for ROE - ROE = PM X TA Tumover x Equity Multiplier 2016 2015 Perform a Common Size Analysis of the balance sheets and income statements for Dunder Mifflin, Inc. 2016 2015 Formula Used cash and cash equivalents / total assets Short-term investments / total assets Accounts receivable / total assets inventories / total assets total current assets / total assets net fixed assets/total assets total assets / total assets, or sum of percentages Common Size Balance Sheets Assets - Cash and cash equivalents Short-term investments Accounts Receivable Inventories 0 Total current assets 1 Net fixed assets 2 Total assets 3 4 Liabilities and equity 5 Accounts payable 6 Accruals 7 Notes payable 8 Total current liabilities 9 Long-term debt 0 Total liabilities 1 Common stock 2 Retained Earnings 3 Total common equity 4 Total liabilities and equity 2016 2015 accounts payable / total liabilities and equity accruals / total liabilities and equity Notes payable / total liabilities and equity total current liabilities / total liabilities and equity long-term debt / total liabilities and equity Total liabilities / total liabilities and equity common stock / total liabilities and equity retained earnings / total liabilities and equity total common equity / total liabilities and equity total liabilities and equity / total liabilities and equity, or the sum of all percentages 2016 2015 Common Size Income Statements Sales COGS except excluding depr. and amort. Depreciation and Amortization Other operating expenses EBIT Interest Expense EBT Taxes (40%) Net Income sales/ sales COGS except depreciation and amortization / sales Depreciation and amortization / sales other operating expenses / sales EBIT/ sales Interest expense / sales EBT/ sales Taxes / sales net income / sales Analysis: What does the calculated data tell us about Dunder Mifflin, Inc.? Provide an analysis that includes comparison to industry averages as well as year to year comparison. Is Dunder Mifflin, Inc. performing better in 2016 compared to 2015? Support your response. Global Financial Managaement Rivier University 1 2. 3 4 5 Module 1 Problem Set Financial Analysis 12 Assets Directions: Dunder Mifflin, Inc. is a regional paper distributor located on the east coast of the United States. The CFO has requested that data for the previous years of 2015 and 2016 be compared to industry averages as well as previous activity. Using the financial data provided below, calculated the requested ratios and analysis. Use the functions of 6 Excel for calculating your data. 7 8 Dunder Mifflin, Inc. 9 December 31 Balance Sheets 10 (Thousands of Dollars) 11 2016 2015 13 Cash and cash equivalents $36,000 $42,575 14 Short-term investments 2,765 1,500 15 Accounts Receivable 65,900 64,889 16 Inventories 105,000 98,780 17 Total current assets $209,665 $207,744 18 Net fixed assets 178,000 175,000 19 Total assets $387665 $382.744 20 21 Liabilities and equity 22 Accounts payable $24,765 $25,000 23 Accruals 14,230 14,000 24 Notes payable 22,890 25,400 25 Total current liabilities $61,885 $64,400 26 Long-term debt 63.780 62,335 27 Total liabilities $125,665 $126,735 28 Common stock 208,000 203,000 29 Retained Earnings 54,000 53,009 30 Total common equity $262.000 $256,009 31 Total liabilities and equity $387 665 $382.744 32 33 Dunder Mifflin Inc. 34 December 31 Income Statements 35 (Thousands of Dollars) 36 2016 2015 $389,000 $376,000 38 COGS except excluding depr. and amort. 285,700 259,000 37 Sales nnnn 2016 $389,000 285,700 23,600 34,000 $45,700 6,300 $39,400 15,760 $23.640 2015 $376,000 259,000 22,450 32,500 $62,050 6,000 $56,050 22,420 $33.630 $17,950 $16,730 $18,900 $23,680 Dunder Mimin Inc. 4 December 31 Income Statements 5 (Thousands of Dollars) 6 7 Sales 8 COGS except excluding depr. and amort. 9 Depreciation and Amortization 0 Other operating expenses 1 EBIT 2 Interest Expense 3 EBT 4 Taxes (40%) 5 Net Income 6 7 Common dividends 8 Addition to retained earnings 9 0 Other Data -1 Year-end Stock Price 2 # of shares (Thousands) 3 Lease payment (Thousands of Dollars) 4 Sinking fund payment (Thousands of Dollars) 5 6 Ratio Analysis -7 Profitability Ratios 8 Profit Margin 9 Basic Earning Power 0 Return on Assets 1 Return on Equity 2 Asset Management Ratios 3 Inventory Turnover (Total COGS/Inventories) 4 Days Sales Outstanding 5 Fixed Assets Turnover 6 Total Assets Turnover 7 Liquidity Ratios 8 Current Ratio 9 Quick Ratio O Debt Management Ratios 1 Debt Ratio (Total debt-to-assets) 2 Liabilities-to-assets ratio 3 Times-interest-earned ratio 4 EBITDA coverage ratio 2016 $84.00 6,203 $30,000 $3,000 2015 $92.00 6,100 $31,000 $3,000 2016 2015 Industry Avg_Formula Used 5.78% net income/sales 13.65% EBIT/Total Assets 5.90% Net income/total assets 10.04% net income/total common equity 1.95 (COGS minus depreciation and amortization + depreciation and amortization) / inventories 59.85 Accounts Receivable / (sales/365) 2.17 saleset fixed assets 1.05 sales/total assets 3.25 total current assets / total current liabilities 1.75 (total current assets - inventories) / total current assets 23.0% (notes payable + long term debt) / total assets 32.2% total liabilities / total assets 8.75 EBIT / interest expense 2.74 (EBIT + depreciation and amortization + lease payments) / (Interest expense + lease payments + sinking fund payments) Market Value Ratios Earnings per share Price-to-earnings ratio Cash flow per share Price-to-cash flow ratio Book Value per share Market-to-book ratio NA net income / number of shares 18.17 year end stock price / earnings per share NA (net income + depreciation and amortization) / number of shares 9.54 year end stock price / cash flow per share NA total common equity / number of shares 2.00 year end stock price / book value per share . . Perform a Dupont Analysis for Dunder Mifflin, Inc. for 2015 and 2016 Hint: Use the Product(...) function of Excel for ROE - ROE = PM X TA Tumover x Equity Multiplier 2016 2015 Perform a Common Size Analysis of the balance sheets and income statements for Dunder Mifflin, Inc. 2016 2015 Formula Used cash and cash equivalents / total assets Short-term investments / total assets Accounts receivable / total assets inventories / total assets total current assets / total assets net fixed assets/total assets total assets / total assets, or sum of percentages Common Size Balance Sheets Assets - Cash and cash equivalents Short-term investments Accounts Receivable Inventories 0 Total current assets 1 Net fixed assets 2 Total assets 3 4 Liabilities and equity 5 Accounts payable 6 Accruals 7 Notes payable 8 Total current liabilities 9 Long-term debt 0 Total liabilities 1 Common stock 2 Retained Earnings 3 Total common equity 4 Total liabilities and equity 2016 2015 accounts payable / total liabilities and equity accruals / total liabilities and equity Notes payable / total liabilities and equity total current liabilities / total liabilities and equity long-term debt / total liabilities and equity Total liabilities / total liabilities and equity common stock / total liabilities and equity retained earnings / total liabilities and equity total common equity / total liabilities and equity total liabilities and equity / total liabilities and equity, or the sum of all percentages 2016 2015 Common Size Income Statements Sales COGS except excluding depr. and amort. Depreciation and Amortization Other operating expenses EBIT Interest Expense EBT Taxes (40%) Net Income sales/ sales COGS except depreciation and amortization / sales Depreciation and amortization / sales other operating expenses / sales EBIT/ sales Interest expense / sales EBT/ sales Taxes / sales net income / sales Analysis: What does the calculated data tell us about Dunder Mifflin, Inc.? Provide an analysis that includes comparison to industry averages as well as year to year comparison. Is Dunder Mifflin, Inc. performing better in 2016 compared to 2015? Support your response