Could you please help with the attached file and following problem: a. Within this workbook there is a worksheet labeled "Adjusted Trial Balances".Within that worksheet you will find fully completed trial balances for 5 years.Also within this workbook you will find worksheets for comparative income statements and comparative balance sheets.Using the trial balances or comparative financial statements you need to calculate a variety of ratios.You will find the required ratios in the worksheet labeled "Ratios Historical".Complete all the required ratios AND analysis (see Ratios Historical worksheet for further instructions). You MUST use formulas and linking to compute your ratios. You cannot simply hard key the answers. You must reference the appropriate data from the trial balances or comparative financial statements.

Davenport University: ACCT202 Project - Part III *Part III is completely independent of parts I and II and cannot be used to check figures for those parts. Part III - READ ALL INSTRUCTIONS Financial Statement Analysis a. Within this workbook there is a worksheet labeled "Adjusted Trial Balances". Within that worksheet you will find fully completed trial balances for 5 years. Also within this workbook you will find worksheets for comparative income statements and comparative balance sheets. Using the trial balances or comparative financial statements you need to calculate a variety of ratios. You will find the required ratios in the worksheet labeled "Ratios Historical". Complete all the required ratios AND analysis (see Ratios Historical worksheet for further instructions). You MUST use formulas and linking to compute your ratios. You cannot simply hard key the answers. You must reference the appropriate data from the trial balances or comparative financial statements. b. After completing all of your ratio calcualtions, analyze the results in comparison to prior years and industry averages. Write a business memo explaining your findings. Your memo must be writtin in Word and follow normal business memo conventions - use word memo templates for this. There is not a length requirement, however you will be graded upon the substance of your analysis. Adjusted Trial Balance Acct. No. Account Title Dr. Cr. 100 Cash 384,504.00 102 Accounts Receivable 555,666.00 103 Allowance for Doubtful Accounts 104 Merchandise Inventory 105 Estimated Returns Inventory 106 Office Supplies 107 Prepaid Insurance 15,400.00 120 Investments - Trading 80,000.00 121 Investments - Available for Sale 35,400.00 122 Investments - Held to Maturity 80,000.00 123 Valuation Allowance 140 Land 1,500,000.00 145 Building 1,200,000.00 146 Accumulated Depreciation - Building 151 Equipment 152 Accumulated Depreciation - Equipment 153 Office Furniture 154 Accumulated Depreciation - Office Furniture 201 Accounts Payable 202 Wages Payable - 203 Interest Payable - 204 Dividends Payable 205 Unearned Rent 206 Customer Refunds Payable 250 Notes Payable 650,000.00 251 Bonds Payable 1,300,000.00 252 Premium on Bonds Payable 253 Discount on Bonds Payable 252 Mortgage (Warehouse) Payable 300,000.00 300 Common Stock, $2 Par, 100,000 Authorized; 50,000 shares Issued/Outstanding 100,000.00 301 Paid In Capital - Excess of Par 350,000.00 330 Retained Earnings 331 Cash Dividends 200,000.00 340 Treasury Stock 50,000.00 341 Unrealized (Gain) Loss Available for Sale Securities 500 Sales 600 Cost of Goods Sold 700 Wage Expense (hourly workers) 756,894.00 701 Salaries Expense (Exempt Staff) 800,000.00 702 Marketing Expense 295,000.00 703 Travel and Entertainment Expense 704 Bad Debt Expense 705 Property Tax Expense 706 Office Maintenance & Repair Expense 707 Accounting Expenses 10,000.00 708 Insurance Expense 12,000.00 709 Utilities Expense 65,450.00 22,580.00 456,897.00 32,500.00 5,689.00 52,000.00 750,000.00 85,000.00 68,000.00 14,000.00 12,000.00 680,980.00 10,000.00 18,795.00 56,000.00 16,000.00 222,158.00 - 4,056,800.00 1,285,698.00 56,789.00 42,185.00 500,000.00 2,000.00 Panther Marine Comparative Income Statement For the Years end June 30, 2017, 2016, 2015, 2014, 2013 6/30/2017 6/30/2016 6/30/2015 6/30/2014 6/30/2013 Sales Revenue Net Credit Sales 4,056,800.00 4,137,936.00 4,302,480.00 4,097,368.00 3,935,096.00 Cost of Goods Sold 1,285,698.00 1,311,411.96 1,464,267.80 1,298,554.98 1,247,127.06 Gross Profit 2,771,102.00 2,826,524.04 2,838,212.20 2,798,813.02 2,687,968.94 Wage Expense (hourly workers) 756,894.00 772,031.88 832,583.40 764,462.94 734,187.18 Salaries Expense (Exempt Staff) 800,000.00 816,000.00 880,000.00 808,000.00 776,000.00 Marketing Expense 295,000.00 300,900.00 324,500.00 297,950.00 286,150.00 56,789.00 57,924.78 62,467.90 57,356.89 55,085.33 Operating Expenses Travel and Entertainment Expense Bad Debt Expense Property Tax Expense Office Maintenance & Repair Expense 42,185.00 43,028.70 46,403.50 42,606.85 40,919.45 500,000.00 510,000.00 550,000.00 505,000.00 485,000.00 1,940.00 2,000.00 2,040.00 2,200.00 2,020.00 Accounting Expenses 10,000.00 10,200.00 65,000.00 10,100.00 9,700.00 Insurance Expense 12,000.00 12,240.00 13,200.00 12,120.00 11,640.00 Utilities Expense 65,450.00 66,759.00 71,995.00 66,104.50 63,486.50 546.00 556.92 600.60 551.46 529.62 5,425.00 5,533.50 5,967.50 5,479.25 5,262.25 Office Supplies Expense Telecommunications Expense Depreciation Expense - Building 30,000.00 30,000.00 30,000.00 30,000.00 30,000.00 Depreciation Expense - Equipment 8,500.00 8,500.00 8,500.00 8,500.00 8,500.00 Depreciation Expense - Office Furniture 2,000.00 2,000.00 2,000.00 2,000.00 2,000.00 2,586,789.00 2,637,714.78 2,895,417.90 2,612,251.89 2,510,400.33 184,313.00 188,809.26 (57,205.70) 186,561.13 177,568.61 Rent Income 8,000.00 2,000.00 10,000.00 15,000.00 5,000.00 Unrealized Gain - Trading Securities 5,500.00 1,200.00 5,600.00 16,000.00 8,000.00 Realized Gain - Investment Securities 85,460.00 56,485.00 8,690.00 28,560.00 15,000.00 Total Other Revenue and Gains 98,960.00 59,685.00 24,290.00 59,560.00 28,000.00 25,000.00 26,500.00 27,500.00 27,250.00 24,500.00 2,500.00 2,650.00 2,750.00 2,725.00 2,450.00 33,230.00 35,223.80 36,553.00 36,220.70 32,565.40 60,730.00 64,373.80 66,803.00 66,195.70 59,515.40 Net Income (Loss) 222,543.00 184,120.46 (99,718.70) 179,925.43 146,053.21 Common Dividends Total Operating Expenses Income from Operations Other Revenues and Gains Other Expenses and Losses Interest Expense Unrealized Loss - Trading Securities Realized Loss - Investment Securities Total Other Expenses and Losses 200,000 250,000 300,000 300,000 Weighted Average Common Shares 50,000 50,000 50,000 50,000 Market Price per Common Share 46.00 41.00 31.00 42.00 #DIV/0! Panther Marine Balance Sheet June 30, 2017, 2016, 2015, 2014, 2013 6/30/2017 6/30/2016 6/30/2015 6/30/2014 6/30/2013 ASSETS Current assets Cash 384,504.00 407,574.24 392,194.08 419,109.36 376,813.92 Accounts Receivable 555,666.00 589,005.96 566,779.32 605,675.94 544,552.68 Allowance for Doubtful Accounts (22,580.00) (23,934.80) (19,588.80) (24,612.20) (22,128.40) Merchandise Inventory 456,897.00 484,310.82 466,034.94 498,017.73 447,759.06 32,500.00 34,450.00 33,150.00 35,425.00 31,850.00 5,689.00 6,030.34 5,802.78 6,201.01 5,575.22 15,400.00 16,324.00 15,708.00 16,786.00 15,092.00 1,428,076.00 1,513,760.56 1,460,080.32 1,556,602.84 1,399,514.48 Land 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 1,500,000.00 Building 1,200,000.00 1,200,000.00 1,200,000.00 1,200,000.00 1,200,000.00 (750,000.00) (720,000.00) (690,000.00) (660,000.00) (630,000.00) 85,000.00 80,000.00 80,000.00 80,000.00 75,000.00 (68,000.00) (59,500.00) (51,000.00) (42,500.00) (34,000.00) 14,000.00 Estimated Returns Inventory Office Supplies Prepaid Insurance Total Current Assets Property, plant & equipment Accumulated Depreciation - Building Equipment Accumulated Depreciation - Equipment Office Furniture 14,000.00 14,000.00 14,000.00 14,000.00 (12,000.00) (10,000.00) (8,000.00) (6,000.00) (4,000.00) 1,969,000.00 2,004,500.00 2,045,000.00 2,085,500.00 2,121,000.00 Investments - Trading 80,000.00 84,800.00 81,600.00 87,200.00 78,400.00 Investments - Available for Sale 35,400.00 37,524.00 36,108.00 38,586.00 34,692.00 Investments - Held to Maturity 80,000.00 84,800.00 81,600.00 87,200.00 78,400.00 Valuation Allowance 52,000.00 55,120.00 53,040.00 56,680.00 50,960.00 247,400.00 262,244.00 252,348.00 269,666.00 242,452.00 3,644,476.00 3,780,504.56 3,757,428.32 3,911,768.84 3,762,966.48 170,000.00 Accumulated Depreciation - Office Furniture Total Property, plant & equipment Other Assets Total Other Assets Total Assets LIABILITIES & STOCKHOLDER'S EQUITY LIABILITIES Current Liabilities Accounts Payable 680,980.00 721,838.80 694,599.60 168,900.00 Unearned Rent 10,000.00 10,600.00 10,200.00 10,900.00 9,800.00 Customer Refunds Payable 18,795.00 19,922.70 19,170.90 20,486.55 18,419.10 Total Current Liabilities 709,775.00 752,361.50 723,970.50 200,286.55 198,219.10 Long-term Liabilities Notes Payable 650,000.00 689,000.00 663,000.00 708,500.00 637,000.00 Bonds Payable 1,300,000.00 1,378,000.00 1,000,000.00 1,417,000.00 1,274,000.00 Premium on Bonds Payable 56,000.00 50,000.00 35,000.00 55,000.00 45,000.00 Discount on Bonds Payable (16,000.00) (10,000.00) (10,000.00) (12,000.00) (15,000.00) 1,000,000.00 Mortgage (Warehouse) Payable 300,000.00 318,000.00 950,000.00 920,000.00 2,290,000.00 2,425,000.00 2,638,000.00 3,088,500.00 2,941,000.00 2,999,775.00 3,177,361.50 3,361,970.50 3,288,786.55 3,139,219.10 Common Stock, $2 Par, 100,000 Authorized; 50,000 shares Issued/Outstanding 100,000.00 100,000.00 100,000.00 100,000.00 100,000.00 Paid In Capital - Excess of Par 350,000.00 371,000.00 357,000.00 381,500.00 343,000.00 Treasury Stock (50,000.00) (60,000.00) (20,000.00) (20,000.00) (10,000.00) Cash Dividends (200,000.00) (250,000.00) (300,000.00) (300,000.00) (250,000.00) Total Long-term Liabilities Total Liabilities STOCKHOLDER'S EQUITY Ending Retained Earnings 444,701.00 442,143.06 258,457.82 461,482.29 440,747.38 Total Stockholder's Equity 644,701.00 603,143.06 395,457.82 622,982.29 623,747.38 Total Liabilities and Stockholder's Equity 3,644,476.00 3,780,504.56 3,757,428.32 3,911,768.84 3,762,966.48 TOTAL ASSETS TOTAL LIAB & EQUITY VARIANCE 3,644,476.00 3,644,476.00 0 3780504.56 3780504.56 0 3757428.32 3757428.32 0 3911768.84 3911768.84 0 3762966.48 3762966.48 0 6/30/2017 6/30/2016 6/30/2015 2.10 1,513,761 752,362 2.10 1,460,080 723,971 2.20 Quick Ratio 1.41 1.41 1.62 Accounts Receivable Turnover 7.44 7.63 10.00 Inventory Turnover 2.76 3.04 2.50 4.45% -2.32% 3.5% 1.10 1.12 1.02 4.89% -2.60% 10.50% 36.88% -19.58% 18.30% Liquidity Current Ratio Current Assets Current Liabilities 2.01 1,428,076 709,775 1 INDUSTRY Profitability Profit Margin Asset Turnover Return on Total Assets Return on Common Shareholders' Equity Earnings per Share (EPS) $ Price-Earnings (P/E ) Ratio Dividends per Share 3.68 $ 11.13 $ 5.00 (1.99) $ (15.54) $ 6.00 3.05 12.54 $ 2.00 Solvency Ratios Ratio of Fixed Assets to Long-Term Liabilities Fixed Assets (net) Long-Term Liabilities 0.83 0.78 0.80 Prior Year/Industry: Favorable, Unfavorable, Same Prior Year - Unfavorable Industry - Unfavorble