This is an excel assignment but it is 70% done...you must use questions from textbook.
Spreadsheet exercise gives students an opportunity to use Excel software to create one or more spreadsheets to analyze a financial problem.
Please go to your textbook pageP140-141 (Dayton Products, Inc)
and provide solutions to spreadsheet exercise problems a, b, and c.
Please refer to attached spreadsheet template.2019spreadsheet01Detailedtemplate.xls
This is from teacherI have received some question on how to do excel spreadsheetexercise assignments. First of all, go to Canvasto download spreadsheet exercise #1 template. There are three taps (Balance sheet, Income Statement, Ratios). At least 60-70% homework has been already completed by me. All you need to fill in the blacks on the tap using original questions given in the textbook.
incomestatement (2) Dayton, Inc. Annual Income Statement (Values in Millions) Common Size 2019201820192018 Sales$ 178,909$ 187,510100.0%100.0% Cost of Sales109,701111,63161.3%59.5% Gross Operating Profit$ 69,208$ 75,87938.7%40.5% Selling, General & Admin. Expense12,35612,9006.9%6.9% Other Taxes33,57233,37718.8%17.8% EBITDA$ 23,280$ 29,60213.0%15.8% Depreciation & Amortization12,1037,9446.8%4.2% EBIT$ 11,177$ 21,6586.2%11.6% Other Income, Net3,1473,3231.8%1.8% Earnings Before Interest and Taxes$ 14,324$ 24,9818.0%13.3% Interest Expense3982930.2%0.2% Earnings Before Taxes$ 13,926$ 24,6887.8%13.2% Income Taxes0.21tax rate Net Income Available to Common$ 13,926$ 24,6887.8%13.2% Dividends per share$ 1.50$ 0.91 EPS$ 1.71$ 1.15$ 0.91 $ 1.64$ 2.87 Computation of 2012 Cost of Sales: Beginning Inventory7,904 Purchases109,865 Goods Available117,769 Cost of Sales109,701 Ending Inventory8,068 incomestatement Dayton, Inc. Annual Income Statement (Values in Millions) Common Size 2019201820192018 Sales$ 178,909$ 187,510100.0%100.0% Cost of Sales109,701111,63159.5% Gross Operating Profit$ 69,208$ 75,87940.5% Selling, General & Admin. Expense12,9006.9% Other Taxes33,37717.8% EBITDA$ 29,60215.8% Depreciation & Amortization7,9444.2% EBIT$ 21,65811.6% Other Income, Net3,3231.8% Earnings Before Interest and Taxes$ 24,98113.3% Interest Expense2930.2% Earnings Before Taxes$ 24,68813.2% Income Taxes5,1840.21tax rate Net Income Available to Common$ 19,50410.4% Dividends per share$ 1.15$ 0.91 EPS$ 1.64$ 2.87 Computation of 2019 Cost of Sales: Beginning Inventory$ 7,904 Purchases109,865 Goods Available$ 117,769 Cost of Sales109,701 Ending Inventory$ 8,068 balancesheet (2) Dayton, Inc. Annual Balance Sheet (Values in Millions) Common Size 2019201820192018 Assets Current Assets Cash and Equivalents$ 7,229$ 6,5474.74%4.57% Receivables21,16319,54913.86%13.65% Inventories8,0687,9045.29%5.52% Other Current Assets1,8311,6811.20%1.17% Total Current Assets$ 38,291$ 35,68125.09%24.92% Non-Current Assets Property, Plant & Equipment, Gross$ 204,960$ 187,519134.3%131.0% Accum. Depreciation & Depletion110,02097,917 Property, Plant & Equipment, Net$ 94,940$ 89,60262.2%62.6% Other Non-Current Assets19,41317,89112.7%12.5% Total Non-Current Assets$ 114,353$ 107,49374.9%75.1% Total Assets$ 152,644$ 143,174100.0%100.0% Liabilities & Shareholder's Equity Current Liabilities Accounts Payable$ 13,792$ 22,8629.0%16.0% Short Term Debt4,0933,7032.7%2.6% Other Current Liabilities15,2903,54910.0%2.5% Total Current Liabilities$ 33,175$ 30,11421.7%21.0% Non-Current liabilites Long Term Debt$ 6,655$ 7,0994.4%5.0% Deferred Income Taxes16,48416,35910.8%11.4% Other Non-Current Liabilities21,73316,44114.2%11.5% Total Non-Current Liabilities$ 44,872$ 39,89929.4%27.9% Total Liabilities$ 78,047$ 70,01351.1%48.9% Retained Earnings$ 74,597$ 73,16148.9%51.1% Total Equity$ 74,597$ 73,16148.9%51.1% Total Liabilities & Stock Equity$ 152,644$ 143,174100.0%100.0% Total Common Shares Outstanding6.7 Bil6.8 Bil Treasury Shares1.3 Bil1.2 Bil balancesheet Dayton, Inc. Annual Balance Sheet (Values in Millions) Common Size 2019201820192018 Assets Current Assets Cash and Equivalents$ 9,090$ 6,5474.57% Receivables19,54913.65% Inventories8,0687,9045.52% Other Current Assets1,6811.17% Total Current Assets$ 35,68124.92% Non-Current Assets Property, Plant & Equipment, Gross$ 187,519131.0% Accum. Depreciation & Depletion97,917 Property, Plant & Equipment, Net$ 89,60262.6% Other Non-Current Assets17,89112.5% Total Non-Current Assets$ 107,49375.1% Total Assets$ 143,174100.0% Liabilities & Shareholder's Equity Current Liabilities Accounts Payable$ 22,86216.0% Short Term Debt3,7032.6% Other Current Liabilities3,5492.5% Total Current Liabilities$ 30,11421.0% Non-Current liabilites Long Term Debt$ 7,0995.0% Deferred Income Taxes16,35911.4% Other Non-Current Liabilities16,44111.5% Total Non-Current Liabilities$ 39,89927.9% Total Liabilities$ 70,01348.9% Retained Earnings$ 73,16151.1% Total Equity$ 73,16151.1% Total Liabilities & Stock Equity$ 143,174100.0% Total Common Shares Outstanding6.7 Bil6.8 Bil Treasury Shares1.3 Bil1.2 Bil ratios (2) Dayton, Inc. Operating Ratios 20192018Analysis:Compare Firm 2003 to Firm 2002 Liquidity Current Ratio0.001.180 Quick Ratio0.000.920 Activity Inventory Turnover13.6014.12Worse than last year Average Days of Inventory26.8425.49Worse than last year A/R Turnover0.009.590 Average Collection Period0.0037.530 Fixed Asset Turnover0.001.740 Total Asset Turnover0.001.310 Debt Debt-to-Asset Ratio0.0%48.9% Times Interest Earned0.0085.260 Profitability Gross Profit Margin38.7%40.5%Worse than last year Operating Profit Margin0.0%13.3%Worse than last year Net Profit Margin0.0%10.4%Worse than last year Return on Assets0.0%13.6%0 Return on Equity0.0%26.7%0 Market Ratios outstanding stock6,700,000,0006,800,000,000 Earnings Per Share$ 1.71$ 2.25Worse than last year Cash Flow per Share$ - 0NA Price$ 90NA P/E ratio52.62NA P/CF ratio0.00NA ratios Dayton, Inc. Operating Ratios 20192018Analysis:Compare Firm 2018 to Firm 2019 Liquidity Current Ratio1.18Worse than last year Quick Ratio0.92Worse than last year Activity Inventory Turnover14.12 Average Days of Inventory25.49 A/R Turnover9.59 Average Collection Period37.53 Fixed Asset Turnover1.74 Total Asset Turnover1.31 Debt Debt-to-Asset Ratio48.9% Times Interest Earned85.26 Profitability Gross Profit Margin40.5% Operating Profit Margin13.3% Net Profit Margin10.4% Return on Assets13.6% Return on Equity26.7% Market Ratios outstanding stock6,700,000,0006,800,000,000 Earnings Per Share$ 1.71$ 2.25