please expline your answer
D B 4 Module 1 Problem Set Financial Analysis 5 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 6 functions of Excel for calculating your data. 7 8 Dunder Mifflin, Inc. 9 December 31 Balance Sheets 10 (Thousands of Dollars) 11 12 Assets 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 $387 665 $382.744 20 21 Liabilities and equity 22 Accounts payable 23 Accruals $24,765 $25.000 24 Notes payable 14,230 14,000 22.890 Mod 1 - Financial Analysis Sheet3 25.400 Ready Type here to search F 25 Total current liabilities 26 Long-term debt 27 Total liabilities 28 Common stock 29 Retained Earnings 30 Total common equity 31 Total liabilities and equity $61,885 63,780 $125,665 208,000 54,000 $262.000 $387 665 E $64,400 62,335 $126,735 203,000 53,009 $256,009 $382.744 32 33 Dunder Mifflin Inc. 34 December 31 Income Statements 35 (Thousands of Dollars) 36 37 Sales 38 COGS except excluding depr. and amort. 39 Depreciation and Amortization 40 Other operating expenses 41 EBIT 42 Interest Expense 43 EBT 44 Taxes (40%) 45 Net Income 96 47 Common dividends 28 Addition to retained earnings 19 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 H D 2016 $84.00 6,203 $30,000 $3.000 E 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 50 Other Data 51 Year-end Stock Price 52 # of shares (Thousands) 53 Lease payment (Thousands of Dollars) 4 Sinking fund payment (Thousands of Dollar 5 6 Ratio Analysis 7 Profitability Ratios 3 Profit Margin 3 Basic Earning Power Return on Assets Return on Equity Asset Management Ratios Inventory Turnover (Total COGS/Inventories) Days Sales Outstanding Fixed Assets Turnover Total Assets Turnover Liquidity Ratios Current Ratio Quick Ratio Debt Management Ratios Debt Ratio (Total debt-to-assets) Liabilities-to-assets ratio Times-interest-earned ratio EBITDA coverage ratio Market Valie Rating Mod 1 - Financial Analysis Sheet3 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 abilities 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 lase paym 0 D E F 5 Market Value Ratios 5 Earnings per share 7 Price-to-earnings ratio 3 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 sha 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 TA Turnover x Equity Multiplier 2016 2015 Perform a Common Size Analysis of the balance sheets and income statements for Dunder Mifflin, Inc. 2016 2015 Common Size Balance Sheets Assets Cash and cash equivalents Short-term investments Accounts Receivable Inventories Total current assets Mod 1. Financial Anni 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 C E F H total current assets/total assets net fixed assets/total assets total assets/total assets, or sum of percentages 2016 2015 100 Total current assets 101 Net foxed assets 102 Total assets 103 104 Liabilities and equity 105 Accounts payable 106 Accruals 107 Notes payable 108 Total current liabilities 109 Long-term debt 110 Total liabilities 111 Common stock 112 Retained Earnings 113 Total common equity 114 Total liabilities and equity 115 116 Common Size Income Statements 117 Sales 118 COGS except excluding depr. and amort. 119 Depreciation and Amortization 120 Other operating expenses 121 EBIT 122 Interest Expense 123 EBT 124 Taxes (40%) 125 Net Income 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 abilities and equity Total liabilities/total liabilities and equity common stock/ total abilities and equity retained earnings / total liabilities and equity total common equity / total abilities and equity total liabilities and equity / total liabilities and equity, or the sum of a percentages 2016 2015 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 8 Analysis: 9 What does the calculated data tell us about Dunder Mifflin, Inc.? Provide an analysis that includes O comparison to industry averages as well as year to year comparison. Is Dunder Mifflin, Inc. performing 1 better in 2016 compared to 2015? Support your response. 2 -3 54 35 36 37 38 39
