Not sure how to upload files on here I don't think it's possible so here are screenshots of the pictures to have a clearer visual of what I need help with THANK YOU 3 Part III READ ALL INSTRUCTIONS COMPLETELY SEPARATE FROM PARTS I II 4 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) You MUST use formulas and or linking to compute your ratios You cannot simply 5 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 written 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 6 substance of your analysis 7 Help 56 0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF A SU Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert AF A Arial 14 29 Wrap Text Accounting 9X Delete V Paste BIU A Ideas V Merge Center $ 94 90 Sensitivity Conditional Format Cell Formatting as Table Styles It Format Sort Filter Find Select F62 4 x fe 3 A 1 L M N o P R R C D July 31, 2020 Adjusted Trial Balance Dr Cr 334,504 00 533 086 00 449,397 00 5,689 00 15 400 00 120 000 00 35,400 00 80,000 00 52 000 00 1,550,000 00 1,200,000 00 750,000 00 85,000 00 E F July 31, 2019 Adjusted Trial Balance Dr Cr 361,264 32 575,732 88 485,348 76 6,144,12 16 632 00 129 600 00 38,232 00 86,400 00 56 160 00 1,674,000 00 1,296,000 00 July 31, 2018 Adjusted Trial Balance Dr Cr 404,616 04 644,820 83 543,590 61 6,881 41 16,627 84 145,152 00 42,819 84 96 768 00 62,899 20 1,874,880,00 1 451,520 00 907,200 00 102,816 00 82,252 80 16,934,40 14,515 20 823 713 41 4 5 6 Aect No Account Title 7 100 Cash 8 102 Accounts Receivable, net 9 104 Merchandise Inventory net 10 106 Office Supplies 11 107 Prepaid Insurance 12 120 Investments Equity Trading (Temporary) 13 121 Investments HTM Long Term 14 122 Investments Equity Long Term 123 Valuation Allowance 140 Land 17 145 Building 18 146 Accumulated Depreciation Building 19 151 Equipment 20 152 Accumulated Depreciation Equipment 21 153 Office Furniture 22 154 Accumulated Depreciation Office Furniture 23 201 Accounts Payable 24 202 Wages Payable 25 203 Interest Payable 26 204 Dividends Payable 27 205 Unearned Rent 28 206 Customer Refunds Payable 29 250 Notes Payable Long Term 30 251 Bonds Payable 31 3 252 Premium on Bonds Payable 32 253 Discount on Bonds Payable July 31, 2017 Adjusted Trial Balance Dr Cr 441,031 48 702,854 70 592,513 77 7,500 74 20,304 35 158 215 68 46,673 63 105,477 12 68,560 13 2,043,619 20 1,582 156 80 988,848 00 112 069 44 89,655 55 18 458 50 15,821 57 897,847 61 July 31, 2016 Adjusted Trial Balance Dr Cr 432,210 85 688,797 61 580,663 49 7,350 73 19,898 26 155,051 37 45,740 15 103,367 58 67,188 93 2,002,746 82 1,550,513 66 969,071 04 109 828 05 87,862 44 18,089 33 15,505 14 879,890 66 15 16 68,000 00 91,800 00 73,440 00 15 120 00 12,960 00 14,000 00 12,000 00 680 980 00 IN 10,800 00 20,298 60 10,000 00 18 795 00 650,000 00 1 300 000 00 56,000 00 12 096 00 22,734,43 786,240 00 1,572 480 00 67 737 60 13,184 64 24,780 53 857,001 60 1 714,003 20 73,833 98 12,920 95 24,284 92 839,861 57 1,679,723 14 72,357 30 60,480 00 16,000 00 17,280 00 19,353 60 21,095 42 20,673 52 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical Enter 80 FEB 5 ME W 24 Help 56 0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF su Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert AF A Arial 14 29 Wrap Text Accounting V Paste BIU A 3 Merge Center $ $ 9 9X Delete It Format Ideas Conditional Format Cell Formatting as Table Styles Sensitivity X Sort Filter Find Select A M N P Q R July 31, 2017 Adjusted Trial Balance Dr Cr L July 31, 2016 Adjusted Trial Balance Dr Cr 387,628 42 50,000 00 395,539 20 50,000 00 490,813 03 308,639 67 484 375 09 294,694 17 200,000 00 20,000 00 200,000 00 10,000 00 4,218,914 48 4,092,347 05 F62 Xv fx 3 D F G H July 31, 2020 July 31, 2019 July 31, 2018 Adjusted Adjusted Adjusted 5 Trial Balance Trial Balance Trial Balance 6 Acct No Account Title Dr Cr Dr Cr Dr Cr 252 Mortgage (Warehouse) Payable 300,000 00 362,880 00 300 Common Stock, S1 Par, 100,000 Authorized 50,000 shares Issued Outstanding 50,000 00 50 000,00 50,000 00 35 301 Paid In Capital Excess of Par Common Stock 347 575 25 361 143 16 36 330 Retained Earnings 324,582 75 329,202 03 37 331 Cash Dividends 250,000 00 300,000 00 200,000 00 38 340 Treasury Stock 50,000 00 60,000 00 20,000 00 39 500 Sales 4,256,800 00 4,963,428 80 40 600 Cost of Goods Sold 1,485,698 00 1,574,839 88 1,732,323 87 700 Wage Expense (hourly workers) 756,894 00 802 307 64 882,538 40 42 701 Salaries Expense (Exempt Staff) 800,000 00 848,000 00 932,800 00 702 Marketing Expense 295,000 00 312 700 00 343 970,00 703 Travel and Entertainment Expense 56 789 00 60 196 34 66 215 97 704 Bad Debt Expense 42,185 00 44 716 10 49 187 71 46 705 Property Tax Expense 500,000 00 530,000 00 583,000 00 47 706 Office Maintenance Repair Expense 2,000 00 2,120 00 2,332 00 48 707 Accounting Expenses 10,000 00 10,600 00 11,660 00 49 708 Insurance Expense 12,000 00 12 720 00 13,992 00 50 709 Utilities Expense 65,450 00 69,377 00 76,314 70 51 710 Office Supplies Expense 546 00 578 76 636 64 52 711 Telecommunications Expense 5,425 00 5,750 50 6 325 55 53 713 Depreciation Expense Building 30,000 00 31,800 00 34,980,00 54 713 Depreciation Expense Equipment 8,500 00 9 010 00 9 911 00 55 714 Depreciation Expense Office Furniture 2,000 00 2,120 00 2,332 00 56 800 Rent Income 8,000 00 8,480 00 9,328 00 57 801 Unrealized Gain Trading Securities 5,500 00 5,830 00 6,413 00 58 802 Reaized Gain Investment Securities 85,460 00 90,587 60 99 646 36 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical 44 45 1,472,475 29 750,157 64 792,880 00 292,374 50 56 283 58 41,809 55 495,550 00 1,982 20 9,911 00 11,893 20 64,867 50 541 14 5,376 72 29 733 00 8,424 35 1,428,301 03 727,652 91 769,093 60 283,603 27 54,595 07 40,555 27 480,683 50 1,922 73 9,613 67 11,536 40 62,921 47 524 91 5,215 42 28,841 01 8,171 62 1,922 73 1,982 20 7,928 80 5,451 05 84,699 41 7,690 94 5,287,52 82,158 42 Enter 80 FEB 5 AME W 24 Help 56 0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF su u Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert Arial AA 14 29 Wrap Text Accounting 9X Delete V Paste BIU A 9 Merge Center V Ideas $ 98 99 Sensitivity Conditional Format Cell Formatting as Table Styles It Format Sort Filter Find Select F62 4 x fe 3 A M N o P R R 4 5 5 6 58 59 C D July 31, 2020 Adjusted Trial Balance Dr Cr 85,460 00 25,000 00 2,500 00 33 230 00 8 923 693 00 8,923,693 00 4 133 217 00 4,355,760 00 222,543 00 Acct No Account Title 802 Realized Gain Investment Securities 900 Interest Expense 90 Unrealized Loss Trading Securities 902 Realized Loss Investment Securities Total Total Expenses and Revenue Net Income (Loss) E F July 31, 2019 Adjusted Trial Balance Dr Cr 90,587 60 26,500 00 2,650 00 35,223,80 9,590,924 10 3 4 381 210 02 235,895 58 H July 31, 2018 Adjusted Trial Balance Dr Cr 99,646 36 29,150 00 2 915 00 38,746 18 10,471,010 79 10,471,010,79 4 819,331 02 5,078,816 16 259,485 14 July 31, 2017 July 31, 2016 Adjusted Adjusted Trial Balance Trial Balance Dr Cr Dr Cr 84,699 41 82,158 42 24,777 50 24,034 18 2,477 75 2 403 42 32 934 25 31,946 23 10,236,962 32 10 236,962 32 9 985,658 76 9,965,658 76 4,096 431 37 4,316,993 74 3,973,538 43 4,187 483 92 220 562 37 213,945 50 60 61 62 63 (0 00) (0 00) (0 00) (0 00) 0 00 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 84 85 86 87 88 90 91 92 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical Enter 80 FEB 5 ME W 24 7 31 17 7 31 16 4,218,914 48 1,472,475 29 2,746,439 19 4,092,347 05 1,428,301 03 2,664,046 02 For the Years ended July 31, 2020, 2019, 2018, 2017, 2016 7 31 20 7 31 19 7 31 18 Sales Revenue Sales 4,256,800 00 4,512,208 00 4,963,428 80 Cost of Goods Sold 1,485,698 00 1,574,839 88 1,732,323 87 Gross Profit 2,771,102 00 2,937,368 12 3,231,104 93 Operating Expenses Wage Expense (hourly workers) 756,894 00 802,307 64 882,538 40 Salaries Expense (Exempt Staff) 800,000 00 848,000 00 932,800 00 Marketing Expense 295,000 00 312,700 00 343,970 00 Travel and Entertainment Expense 56,789 00 60,196 34 66,215 97 Bad Debt Expense 42,185 00 44,716 10 49,187 71 Property Tax Expense 500,000 00 530,000 00 583,000 00 Office Maintenance Repair Expense 2,000 00 2,120 00 2,332 00 Accounting Expenses 10,000 00 10,600 00 11,660 00 Insurance Expense 12,000 00 12,720 00 13,992 00 Utilities Expense 65,450 00 69,377 00 76,314 70 Office Supplies Expense 546 00 578 76 636 64 Telecommunications Expense 5,425 00 5,750 50 6,325 55 Depreciation Expense Building 30,000 00 31,800 00 34,980 00 Depreciation Expense Equipment 8,500 00 9,010 00 9,911 00 Depreciation Expense Office Furnitur 2,000 00 2,120 00 2,332 00 Total Operating Expenses 2,586,789 00 2,741,996 34 3,016,195 97 Income from Operations (EBIT) 184,313 00 195,371 78 214,908 96 Other Revenues and Gains Rent Income 8,000 00 8,480 00 9,328 00 Unrealized Gain Trading Securities 5,500 00 5,830 00 6,413 00 Realized Gain Investment Securities 85,460 00 90,587 60 99,646 36 Total Other Revenue and Gains 98,960 00 104,897 60 115,387 36 Other Expenses and Losses Interest Expense 25,000 00 26,500 00 29,150 00 Unrealized Loss Trading Securities 2,500 00 2,650 00 2,915 00 Realized Loss Investment Securities 33,230 00 35,223 80 38,746 18 Total Other Expenses and Losses 60,730 00 64,373 80 70,811 18 750,157 64 792,880 00 292,374 50 56,283 58 41,809 55 495,550 00 1,982 20 9,911 00 11,893 20 64,867 50 541 14 5,376 72 29,733 00 8,424 35 1,982 20 2,563,766 58 182,672 61 727,652 91 769,093 60 283,603 27 54,595 07 40,555 27 480,683 50 1,922 73 9,613 67 11,536 40 62,921 47 524 91 5,215 42 28,841 01 8,171 62 1,922 73 2,486,853 58 177,192 44 7,928 80 5,451 05 84,699 41 98,079 26 7,690 94 5,287 52 82,158 42 95,136 88 24,777 50 2,477 75 32,934 25 60 189 50 24,034 18 2,403 42 31,946 23 58,383 82 Net Income (Loss) 222,543 00 235,895 58 259,485 14 220,562 37 213,945 50 Common Dividends Weighted Average Common Shares Market Price per Common Share 250,000 50,000 45 00 300,000 50,000 55 00 200,000 50,000 72 00 200,000 50,000 68 00 Balance Sheet July 31, 2020, 2019, 2018, 2017, 2016 7 31 19 7 31 20 7 31 18 7 31 17 7 31 16 334,504 00 533,086 00 449,397 00 5,689 00 15,400 00 120,000 00 1,458,076 00 361,264 32 575,732 88 485,348 76 6,144 12 16,632 00 129,600 00 1,574,722 08 404,616 04 644,820 83 543,590 61 6,881 41 18,627 84 145,152 00 1,763,688 73 441,031 48 702,854 70 592,513 77 7,500 74 20,304 35 158,215 68 1,922,420 72 432,210 85 688,797 61 580,663 49 7,350 73 19,898 26 155,051 37 1,883,972 30 ASSETS Current assets Cash Accounts Receivable, net Merchandise Inventory, net Office Supplies Prepaid Insurance Investments Equity Trading (Temporary) Total Current Assets Property, plant equipment Land Building Accumulated Depreciation Building Equipment Accumulated Depreciation Equipment Office Furniture Accumulated Depreciation Office Furniture Total Property, plant equipment Other Assets Investments HTM Long Term Investments Equity Long Term Valuation Allowance Total Other Assets Total Assets 1,550,000 00 1,200,000 00 (750,000 00) 85,000 00 (68,000 00) 14,000 00 (12,000 00) 2,019,000 00 1,674,000 00 1,296,000 00 (810,000 00) 91,800 00 (73,440 00) 15,120 00 (12,960 00) 2,180,520 00 1,874,880 00 1,451,520 00 (907,200 00) 102,816 00 (82,252 80) 16,934 40 (14,515 20) 2,442,182 40 2,043,619 20 1,582,156 80 (988,848 00) 112,069 44 (89,655 55) 18,458 50 (15,821 57) 2,661,978 82 2,002,746 82 1,550,513 66 (969,071 04) 109,828 05 (87,862 44) 18,089 33 (15,505 14) 2,608,739 24 35,400 00 80,000 00 52,000 00 167,400 00 3,644,476 00 38,232 00 86,400 00 56,160 00 180,792 00 3,936,034 08 42,819 84 96,768 00 62,899 20 202,487 04 4,408,358 17 46,673 63 105,477 12 68,560 13 220,710 87 4,805,110 40 45,740 15 103,367 58 67,188 93 216,296 66 4,709,008 20 2 56 0 Wed 7 01 PM Kaleb Punga W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 11 AF A ab Wrap Text Share Comments Insert General $ Ov 5 Paste BIU A Ideas 3 V 33X Delete It Format Merge Center Conditional Format Cell Formatting as Table Styles Sensitivity Sort Filter X Find Select 028 D E F G H 1 K L M N O P Q R S S T U 31 680,980 00 10,000 00 18,795 00 709,775 00 735,458,40 10,800 00 20,298 60 766,557 00 823,713 41 12,096 00 22,734 43 858,543 84 897,847 61 13,184 64 24,780 53 935,812 79 879,890 66 12,920 95 24,284 92 917,096 53 30 LIABILITIES STOCKHOLDER'S EQUITY LIABILITIES 32 Current Liabilities 33 Accounts Payable 34 Unearned Rent 35 Customer Refunds Payable 36 Total Current Liabilities 37 Long term Liabilities 38 Notes Payable Long Term 39 Bonds Payable 40 Premium on Bonds Payable 41 Discount on Bonds Payable 42 Mortgage (Warehouse) Payable 43 Total Long term Liabilities 44 Total Liabilities 650,000 00 1,300,000 00 56,000 00 (16,000 00) 300,000 00 2,290,000 00 2,999,775 00 702,000 00 1,404,000 00 60,480 00 (17,280 00) 324,000 00 2,473,200 00 3,239,757 00 786,240 00 1,572,480 00 67,737 60 (19,353 60) 362,880 00 2,769,984 00 3,628,527 84 857,001 60 1,714,003 20 73,833 98 (21,095 42) 395,539 20 3,019,282 56 3,955,095 35 839,861 57 1,679,723 14 72,357 30 (20,673 52) 387,628 42 2,958,896 91 3,875,993 44 45 46 STOCKHOLDER'S EQUITY Common Stock, $1 Par, 100,000 Authorized 47 50,000 shares Issued Outstanding 48 Paid In Capital Excess of Par, Common Stock 49 Treasury Stock 50 Cash Dividends 51 Ending Retained Earnings 52 Total Stockholder's Equity 53 54 Total Liabilities and Stockholder's Equity 50,000 00 347,575 25 (50,000 00) (250,000 00) 547,125 75 644,701 00 50,000 00 381,694 33 (60,000,00) (300,000 00) 624,582 75 696,277 08 50,000 00 361,143 16 (20 000,00) (200,000 00) 588,687 17 779,830 33 50,000 00 490,813 03 (20,000 00 (200,000 00 529,202 03 850,015 06 50,000 00 484,375 09 (10,000 00) (200 000 001 508,639 67 833,014 76 3,644,476 00 3,936,034 08 4,408,358 17 4,805, 110 41 4,709,008 19 56 57 TOTAL ASSETS 58 TOTAL LIAB EQUITY 59 VARIANCE 3,644,476 00 3,644,476 00 3,936,034 08 3,936,034 08 0 4,408,358 17 4,408,358 17 0 Ratios Historical 4,805,110 40 4,805,110 41 0 4,709,008 20 4,709,008 19 0 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ready 90 FEB 5 W 24 55 0 Wed 7 02 PM Kaleb Pung QE W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF $55 Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AA ab Wrap Text Share Comments General Insert 338 Delete It Format Ov 5 Paste B IV Ideas V A V Merge Center Sensitivity $ ) Conditional Format Cell Formatting as Table Styles X Sort Filter Find Select K K L M N O P R S T U V w Y J14 A XV fox B C D E G H 1 2 Ratio Analysis 3 July 31, 2020, 2019, 2018 Instructions Calculate each of the below ratios for the JUST THE current year FOLLOW the below steps to complete the ratio analysis 1 Round ALL answers 2 decimal places 2 Must USE ratio descriptions including how averages are calculated For example if average A R is required, students would input beginng A R, AN 3 MUST use excel formulas and links to IS comparative worksheets, BS comparative worksheets, or adjusted trial balance within this workbook (see 4 MUST indicate if current year answer is favorable, unfavorable, or same in realtion to prior year AND industry (see current ratio for example) 9 5 USE information at bottom of this worksheet to calculate certain ratios 10 6 If a portion of a ratio is not available, still use in ratio description and use 0 as part of formula 11 8 USE ratios and comparative statements to write a memo to Davenport Docking management explaining your findings 12 7 31 20 7 31 19 7 31 18 INDUSTRY Prior Year Industry Favorable, Unfavorable, Same 14 Liquidity 15 Current Ratio 2 05 1 2 10 2 10 2 25 Prior Year Unfavorable 16 Current Assets 1,458,076 1,574,722 1,763,689 Industry Unfavorble 17 Current Liabilities 709,775 766,557 858,544 18 19 Quick Ratio 2 1 39 1 39 1 20 20 361,264 32 404,616 04 21 575,732 88 644,820 83 22 129,600 00 145,152 00 23 766,557 00 858,543 84 24 25 Accounts Receivable Turnover 3 7 39 7 37 8 50 26 4,512,208 00 4,963,428 80 27 644,820 83 702,854 70 28 575,732 88 644,820 83 29 610,276 85 673 837 76 30 31 Inventory Turnover 4 3 06 3 05 2 50 32 1,574,839 88 1,732,323 87 33 543,590 61 592,513 77 34 485,348 76 543,590 61 35 514,469 69 568,052 19 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical Ready 80 FEB 5 W 24 55 0 Wed 7 02 PM Kaleb Punga W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AF A ab Wrap Text Share Comments General Insert Ov 5 Paste B IV a Av V 3 Merge Center v Ideas 33X Delete It Format $ Conditional Format Cell Formatting as Table Styles Sort Filter Sensitivity X Find Select J14 X fx 8 C D E F G H 1 K L M M N O P R s T U V w Y 37 Profitability 38 39 Times Interest Earned 40 5 15 88 9 90 235,895 58 26,500 00 26,500 00 9 90 259,485 14 29,150 00 29,150 00 42 4) 44 Asset Turnover 45 6 1 02 46 47 1 08 4,512,208 00 4,408,358 17 3,936,034 08 4,172,196 12 1 08 4,963,428 80 4,805, 110 40 4,408,358 17 4,606,734 29 7 10 50 6 29 235,895 58 26,500 00 4,408,358 17 3,936,034 08 4,172,196 12 6 27 259,485 14 29,150 00 4,805, 110 40 4,408 358 17 4,606,734 29 8 35 50 48 49 50 Return on Assets 51 52 53 54 55 56 57 Return on Common Shareholders' Equity 58 59 60 61 62 63 64 Earnings per Share (EPS) 65 66 67 31 96 235,895 58 31 84 259,485 14 779,830 33 696,277 08 738,053 71 850,015 06 779,830 33 814,922 70 9 $ $ $ 3 05 4 72 235,895 58 5 19 259 485 14 50,000 50,000 10 9 95 69 Price Earnings (P E) Ratio 70 71 11 66 13 87 55 00 72 00 4 72 5 19 IS Comparative Instructions Adjusted Trial Balances BS Comparative Ratios Historical Ready 80 FEB 5 W 24 55 0 Wed 7 02 PM Kaleb Punga W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AA ab Wrap Text Share Comments General Insert 33X Delete Ft Format Ov 5 Paste I U Ideas A 3 Merge Center v $ ) Sensitivity Conditional Format Cell Formatting as Table Styles X Sort Filter Find Select C D E G H 1 1 K L M N P R s T T U V w Y J14 4 x 77 73 Solvency Ratios 79 80 Ratio of Liabilities to Stockholder's Equity 12 0 80 4 65 3,239,757 696,277 4 65 3,628,528 779,830 7 31 20 250,000 50,000 45 7 31 19 300,000 50,000 55 7 31 18 200,000 50,000 72 82 83 84 85 Common Dividends 86 Weighted Average Common Sha 87 Market Price per Share 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 102 108 109 110 111 112 113 114 115 116 117 118 119 120 121 1122 123 124 125 126 127 128 129 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical Ready 80 FEB 5 W 24 3 Part III READ ALL INSTRUCTIONS COMPLETELY SEPARATE FROM PARTS I II 4 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) You MUST use formulas and or linking to compute your ratios You cannot simply 5 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 written 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 6 substance of your analysis 7 Help 56 0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF A SU Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert AF A Arial 14 29 Wrap Text Accounting 9X Delete V Paste BIU A Ideas V Merge Center $ 94 90 Sensitivity Conditional Format Cell Formatting as Table Styles It Format Sort Filter Find Select F62 4 x fe 3 A 1 L M N o P R R C D July 31, 2020 Adjusted Trial Balance Dr Cr 334,504 00 533 086 00 449,397 00 5,689 00 15 400 00 120 000 00 35,400 00 80,000 00 52 000 00 1,550,000 00 1,200,000 00 750,000 00 85,000 00 E F July 31, 2019 Adjusted Trial Balance Dr Cr 361,264 32 575,732 88 485,348 76 6,144,12 16 632 00 129 600 00 38,232 00 86,400 00 56 160 00 1,674,000 00 1,296,000 00 July 31, 2018 Adjusted Trial Balance Dr Cr 404,616 04 644,820 83 543,590 61 6,881 41 16,627 84 145,152 00 42,819 84 96 768 00 62,899 20 1,874,880,00 1 451,520 00 907,200 00 102,816 00 82,252 80 16,934,40 14,515 20 823 713 41 4 5 6 Aect No Account Title 7 100 Cash 8 102 Accounts Receivable, net 9 104 Merchandise Inventory net 10 106 Office Supplies 11 107 Prepaid Insurance 12 120 Investments Equity Trading (Temporary) 13 121 Investments HTM Long Term 14 122 Investments Equity Long Term 123 Valuation Allowance 140 Land 17 145 Building 18 146 Accumulated Depreciation Building 19 151 Equipment 20 152 Accumulated Depreciation Equipment 21 153 Office Furniture 22 154 Accumulated Depreciation Office Furniture 23 201 Accounts Payable 24 202 Wages Payable 25 203 Interest Payable 26 204 Dividends Payable 27 205 Unearned Rent 28 206 Customer Refunds Payable 29 250 Notes Payable Long Term 30 251 Bonds Payable 31 3 252 Premium on Bonds Payable 32 253 Discount on Bonds Payable July 31, 2017 Adjusted Trial Balance Dr Cr 441,031 48 702,854 70 592,513 77 7,500 74 20,304 35 158 215 68 46,673 63 105,477 12 68,560 13 2,043,619 20 1,582 156 80 988,848 00 112 069 44 89,655 55 18 458 50 15,821 57 897,847 61 July 31, 2016 Adjusted Trial Balance Dr Cr 432,210 85 688,797 61 580,663 49 7,350 73 19,898 26 155,051 37 45,740 15 103,367 58 67,188 93 2,002,746 82 1,550,513 66 969,071 04 109 828 05 87,862 44 18,089 33 15,505 14 879,890 66 15 16 68,000 00 91,800 00 73,440 00 15 120 00 12,960 00 14,000 00 12,000 00 680 980 00 IN 10,800 00 20,298 60 10,000 00 18 795 00 650,000 00 1 300 000 00 56,000 00 12 096 00 22,734,43 786,240 00 1,572 480 00 67 737 60 13,184 64 24,780 53 857,001 60 1 714,003 20 73,833 98 12,920 95 24,284 92 839,861 57 1,679,723 14 72,357 30 60,480 00 16,000 00 17,280 00 19,353 60 21,095 42 20,673 52 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical Enter 80 FEB 5 ME W 24 Help 56 0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF su Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert AF A Arial 14 29 Wrap Text Accounting V Paste BIU A 3 Merge Center $ $ 9 9X Delete It Format Ideas Conditional Format Cell Formatting as Table Styles Sensitivity X Sort Filter Find Select A M N P Q R July 31, 2017 Adjusted Trial Balance Dr Cr L July 31, 2016 Adjusted Trial Balance Dr Cr 387,628 42 50,000 00 395,539 20 50,000 00 490,813 03 308,639 67 484 375 09 294,694 17 200,000 00 20,000 00 200,000 00 10,000 00 4,218,914 48 4,092,347 05 F62 Xv fx 3 D F G H July 31, 2020 July 31, 2019 July 31, 2018 Adjusted Adjusted Adjusted 5 Trial Balance Trial Balance Trial Balance 6 Acct No Account Title Dr Cr Dr Cr Dr Cr 252 Mortgage (Warehouse) Payable 300,000 00 362,880 00 300 Common Stock, S1 Par, 100,000 Authorized 50,000 shares Issued Outstanding 50,000 00 50 000,00 50,000 00 35 301 Paid In Capital Excess of Par Common Stock 347 575 25 361 143 16 36 330 Retained Earnings 324,582 75 329,202 03 37 331 Cash Dividends 250,000 00 300,000 00 200,000 00 38 340 Treasury Stock 50,000 00 60,000 00 20,000 00 39 500 Sales 4,256,800 00 4,963,428 80 40 600 Cost of Goods Sold 1,485,698 00 1,574,839 88 1,732,323 87 700 Wage Expense (hourly workers) 756,894 00 802 307 64 882,538 40 42 701 Salaries Expense (Exempt Staff) 800,000 00 848,000 00 932,800 00 702 Marketing Expense 295,000 00 312 700 00 343 970,00 703 Travel and Entertainment Expense 56 789 00 60 196 34 66 215 97 704 Bad Debt Expense 42,185 00 44 716 10 49 187 71 46 705 Property Tax Expense 500,000 00 530,000 00 583,000 00 47 706 Office Maintenance Repair Expense 2,000 00 2,120 00 2,332 00 48 707 Accounting Expenses 10,000 00 10,600 00 11,660 00 49 708 Insurance Expense 12,000 00 12 720 00 13,992 00 50 709 Utilities Expense 65,450 00 69,377 00 76,314 70 51 710 Office Supplies Expense 546 00 578 76 636 64 52 711 Telecommunications Expense 5,425 00 5,750 50 6 325 55 53 713 Depreciation Expense Building 30,000 00 31,800 00 34,980,00 54 713 Depreciation Expense Equipment 8,500 00 9 010 00 9 911 00 55 714 Depreciation Expense Office Furniture 2,000 00 2,120 00 2,332 00 56 800 Rent Income 8,000 00 8,480 00 9,328 00 57 801 Unrealized Gain Trading Securities 5,500 00 5,830 00 6,413 00 58 802 Reaized Gain Investment Securities 85,460 00 90,587 60 99 646 36 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical 44 45 1,472,475 29 750,157 64 792,880 00 292,374 50 56 283 58 41,809 55 495,550 00 1,982 20 9,911 00 11,893 20 64,867 50 541 14 5,376 72 29 733 00 8,424 35 1,428,301 03 727,652 91 769,093 60 283,603 27 54,595 07 40,555 27 480,683 50 1,922 73 9,613 67 11,536 40 62,921 47 524 91 5,215 42 28,841 01 8,171 62 1,922 73 1,982 20 7,928 80 5,451 05 84,699 41 7,690 94 5,287,52 82,158 42 Enter 80 FEB 5 AME W 24 Help 56 0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF su u Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert Arial AA 14 29 Wrap Text Accounting 9X Delete V Paste BIU A 9 Merge Center V Ideas $ 98 99 Sensitivity Conditional Format Cell Formatting as Table Styles It Format Sort Filter Find Select F62 4 x fe 3 A M N o P R R 4 5 5 6 58 59 C D July 31, 2020 Adjusted Trial Balance Dr Cr 85,460 00 25,000 00 2,500 00 33 230 00 8 923 693 00 8,923,693 00 4 133 217 00 4,355,760 00 222,543 00 Acct No Account Title 802 Realized Gain Investment Securities 900 Interest Expense 90 Unrealized Loss Trading Securities 902 Realized Loss Investment Securities Total Total Expenses and Revenue Net Income (Loss) E F July 31, 2019 Adjusted Trial Balance Dr Cr 90,587 60 26,500 00 2,650 00 35,223,80 9,590,924 10 3 4 381 210 02 235,895 58 H July 31, 2018 Adjusted Trial Balance Dr Cr 99,646 36 29,150 00 2 915 00 38,746 18 10,471,010 79 10,471,010,79 4 819,331 02 5,078,816 16 259,485 14 July 31, 2017 July 31, 2016 Adjusted Adjusted Trial Balance Trial Balance Dr Cr Dr Cr 84,699 41 82,158 42 24,777 50 24,034 18 2,477 75 2 403 42 32 934 25 31,946 23 10,236,962 32 10 236,962 32 9 985,658 76 9,965,658 76 4,096 431 37 4,316,993 74 3,973,538 43 4,187 483 92 220 562 37 213,945 50 60 61 62 63 (0 00) (0 00) (0 00) (0 00) 0 00 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 84 85 86 87 88 90 91 92 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical Enter 80 FEB 5 ME W 24 7 31 17 7 31 16 4,218,914 48 1,472,475 29 2,746,439 19 4,092,347 05 1,428,301 03 2,664,046 02 For the Years ended July 31, 2020, 2019, 2018, 2017, 2016 7 31 20 7 31 19 7 31 18 Sales Revenue Sales 4,256,800 00 4,512,208 00 4,963,428 80 Cost of Goods Sold 1,485,698 00 1,574,839 88 1,732,323 87 Gross Profit 2,771,102 00 2,937,368 12 3,231,104 93 Operating Expenses Wage Expense (hourly workers) 756,894 00 802,307 64 882,538 40 Salaries Expense (Exempt Staff) 800,000 00 848,000 00 932,800 00 Marketing Expense 295,000 00 312,700 00 343,970 00 Travel and Entertainment Expense 56,789 00 60,196 34 66,215 97 Bad Debt Expense 42,185 00 44,716 10 49,187 71 Property Tax Expense 500,000 00 530,000 00 583,000 00 Office Maintenance Repair Expense 2,000 00 2,120 00 2,332 00 Accounting Expenses 10,000 00 10,600 00 11,660 00 Insurance Expense 12,000 00 12,720 00 13,992 00 Utilities Expense 65,450 00 69,377 00 76,314 70 Office Supplies Expense 546 00 578 76 636 64 Telecommunications Expense 5,425 00 5,750 50 6,325 55 Depreciation Expense Building 30,000 00 31,800 00 34,980 00 Depreciation Expense Equipment 8,500 00 9,010 00 9,911 00 Depreciation Expense Office Furnitur 2,000 00 2,120 00 2,332 00 Total Operating Expenses 2,586,789 00 2,741,996 34 3,016,195 97 Income from Operations (EBIT) 184,313 00 195,371 78 214,908 96 Other Revenues and Gains Rent Income 8,000 00 8,480 00 9,328 00 Unrealized Gain Trading Securities 5,500 00 5,830 00 6,413 00 Realized Gain Investment Securities 85,460 00 90,587 60 99,646 36 Total Other Revenue and Gains 98,960 00 104,897 60 115,387 36 Other Expenses and Losses Interest Expense 25,000 00 26,500 00 29,150 00 Unrealized Loss Trading Securities 2,500 00 2,650 00 2,915 00 Realized Loss Investment Securities 33,230 00 35,223 80 38,746 18 Total Other Expenses and Losses 60,730 00 64,373 80 70,811 18 750,157 64 792,880 00 292,374 50 56,283 58 41,809 55 495,550 00 1,982 20 9,911 00 11,893 20 64,867 50 541 14 5,376 72 29,733 00 8,424 35 1,982 20 2,563,766 58 182,672 61 727,652 91 769,093 60 283,603 27 54,595 07 40,555 27 480,683 50 1,922 73 9,613 67 11,536 40 62,921 47 524 91 5,215 42 28,841 01 8,171 62 1,922 73 2,486,853 58 177,192 44 7,928 80 5,451 05 84,699 41 98,079 26 7,690 94 5,287 52 82,158 42 95,136 88 24,777 50 2,477 75 32,934 25 60 189 50 24,034 18 2,403 42 31,946 23 58,383 82 Net Income (Loss) 222,543 00 235,895 58 259,485 14 220,562 37 213,945 50 Common Dividends Weighted Average Common Shares Market Price per Common Share 250,000 50,000 45 00 300,000 50,000 55 00 200,000 50,000 72 00 200,000 50,000 68 00 Balance Sheet July 31, 2020, 2019, 2018, 2017, 2016 7 31 19 7 31 20 7 31 18 7 31 17 7 31 16 334,504 00 533,086 00 449,397 00 5,689 00 15,400 00 120,000 00 1,458,076 00 361,264 32 575,732 88 485,348 76 6,144 12 16,632 00 129,600 00 1,574,722 08 404,616 04 644,820 83 543,590 61 6,881 41 18,627 84 145,152 00 1,763,688 73 441,031 48 702,854 70 592,513 77 7,500 74 20,304 35 158,215 68 1,922,420 72 432,210 85 688,797 61 580,663 49 7,350 73 19,898 26 155,051 37 1,883,972 30 ASSETS Current assets Cash Accounts Receivable, net Merchandise Inventory, net Office Supplies Prepaid Insurance Investments Equity Trading (Temporary) Total Current Assets Property, plant equipment Land Building Accumulated Depreciation Building Equipment Accumulated Depreciation Equipment Office Furniture Accumulated Depreciation Office Furniture Total Property, plant equipment Other Assets Investments HTM Long Term Investments Equity Long Term Valuation Allowance Total Other Assets Total Assets 1,550,000 00 1,200,000 00 (750,000 00) 85,000 00 (68,000 00) 14,000 00 (12,000 00) 2,019,000 00 1,674,000 00 1,296,000 00 (810,000 00) 91,800 00 (73,440 00) 15,120 00 (12,960 00) 2,180,520 00 1,874,880 00 1,451,520 00 (907,200 00) 102,816 00 (82,252 80) 16,934 40 (14,515 20) 2,442,182 40 2,043,619 20 1,582,156 80 (988,848 00) 112,069 44 (89,655 55) 18,458 50 (15,821 57) 2,661,978 82 2,002,746 82 1,550,513 66 (969,071 04) 109,828 05 (87,862 44) 18,089 33 (15,505 14) 2,608,739 24 35,400 00 80,000 00 52,000 00 167,400 00 3,644,476 00 38,232 00 86,400 00 56,160 00 180,792 00 3,936,034 08 42,819 84 96,768 00 62,899 20 202,487 04 4,408,358 17 46,673 63 105,477 12 68,560 13 220,710 87 4,805,110 40 45,740 15 103,367 58 67,188 93 216,296 66 4,709,008 20 2 56 0 Wed 7 01 PM Kaleb Punga W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 11 AF A ab Wrap Text Share Comments Insert General $ Ov 5 Paste BIU A Ideas 3 V 33X Delete It Format Merge Center Conditional Format Cell Formatting as Table Styles Sensitivity Sort Filter X Find Select 028 D E F G H 1 K L M N O P Q R S S T U 31 680,980 00 10,000 00 18,795 00 709,775 00 735,458,40 10,800 00 20,298 60 766,557 00 823,713 41 12,096 00 22,734 43 858,543 84 897,847 61 13,184 64 24,780 53 935,812 79 879,890 66 12,920 95 24,284 92 917,096 53 30 LIABILITIES STOCKHOLDER'S EQUITY LIABILITIES 32 Current Liabilities 33 Accounts Payable 34 Unearned Rent 35 Customer Refunds Payable 36 Total Current Liabilities 37 Long term Liabilities 38 Notes Payable Long Term 39 Bonds Payable 40 Premium on Bonds Payable 41 Discount on Bonds Payable 42 Mortgage (Warehouse) Payable 43 Total Long term Liabilities 44 Total Liabilities 650,000 00 1,300,000 00 56,000 00 (16,000 00) 300,000 00 2,290,000 00 2,999,775 00 702,000 00 1,404,000 00 60,480 00 (17,280 00) 324,000 00 2,473,200 00 3,239,757 00 786,240 00 1,572,480 00 67,737 60 (19,353 60) 362,880 00 2,769,984 00 3,628,527 84 857,001 60 1,714,003 20 73,833 98 (21,095 42) 395,539 20 3,019,282 56 3,955,095 35 839,861 57 1,679,723 14 72,357 30 (20,673 52) 387,628 42 2,958,896 91 3,875,993 44 45 46 STOCKHOLDER'S EQUITY Common Stock, $1 Par, 100,000 Authorized 47 50,000 shares Issued Outstanding 48 Paid In Capital Excess of Par, Common Stock 49 Treasury Stock 50 Cash Dividends 51 Ending Retained Earnings 52 Total Stockholder's Equity 53 54 Total Liabilities and Stockholder's Equity 50,000 00 347,575 25 (50,000 00) (250,000 00) 547,125 75 644,701 00 50,000 00 381,694 33 (60,000,00) (300,000 00) 624,582 75 696,277 08 50,000 00 361,143 16 (20 000,00) (200,000 00) 588,687 17 779,830 33 50,000 00 490,813 03 (20,000 00 (200,000 00 529,202 03 850,015 06 50,000 00 484,375 09 (10,000 00) (200 000 001 508,639 67 833,014 76 3,644,476 00 3,936,034 08 4,408,358 17 4,805, 110 41 4,709,008 19 56 57 TOTAL ASSETS 58 TOTAL LIAB EQUITY 59 VARIANCE 3,644,476 00 3,644,476 00 3,936,034 08 3,936,034 08 0 4,408,358 17 4,408,358 17 0 Ratios Historical 4,805,110 40 4,805,110 41 0 4,709,008 20 4,709,008 19 0 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ready 90 FEB 5 W 24 55 0 Wed 7 02 PM Kaleb Pung QE W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF $55 Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AA ab Wrap Text Share Comments General Insert 338 Delete It Format Ov 5 Paste B IV Ideas V A V Merge Center Sensitivity $ ) Conditional Format Cell Formatting as Table Styles X Sort Filter Find Select K K L M N O P R S T U V w Y J14 A XV fox B C D E G H 1 2 Ratio Analysis 3 July 31, 2020, 2019, 2018 Instructions Calculate each of the below ratios for the JUST THE current year FOLLOW the below steps to complete the ratio analysis 1 Round ALL answers 2 decimal places 2 Must USE ratio descriptions including how averages are calculated For example if average A R is required, students would input beginng A R, AN 3 MUST use excel formulas and links to IS comparative worksheets, BS comparative worksheets, or adjusted trial balance within this workbook (see 4 MUST indicate if current year answer is favorable, unfavorable, or same in realtion to prior year AND industry (see current ratio for example) 9 5 USE information at bottom of this worksheet to calculate certain ratios 10 6 If a portion of a ratio is not available, still use in ratio description and use 0 as part of formula 11 8 USE ratios and comparative statements to write a memo to Davenport Docking management explaining your findings 12 7 31 20 7 31 19 7 31 18 INDUSTRY Prior Year Industry Favorable, Unfavorable, Same 14 Liquidity 15 Current Ratio 2 05 1 2 10 2 10 2 25 Prior Year Unfavorable 16 Current Assets 1,458,076 1,574,722 1,763,689 Industry Unfavorble 17 Current Liabilities 709,775 766,557 858,544 18 19 Quick Ratio 2 1 39 1 39 1 20 20 361,264 32 404,616 04 21 575,732 88 644,820 83 22 129,600 00 145,152 00 23 766,557 00 858,543 84 24 25 Accounts Receivable Turnover 3 7 39 7 37 8 50 26 4,512,208 00 4,963,428 80 27 644,820 83 702,854 70 28 575,732 88 644,820 83 29 610,276 85 673 837 76 30 31 Inventory Turnover 4 3 06 3 05 2 50 32 1,574,839 88 1,732,323 87 33 543,590 61 592,513 77 34 485,348 76 543,590 61 35 514,469 69 568,052 19 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical Ready 80 FEB 5 W 24 55 0 Wed 7 02 PM Kaleb Punga W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AF A ab Wrap Text Share Comments General Insert Ov 5 Paste B IV a Av V 3 Merge Center v Ideas 33X Delete It Format $ Conditional Format Cell Formatting as Table Styles Sort Filter Sensitivity X Find Select J14 X fx 8 C D E F G H 1 K L M M N O P R s T U V w Y 37 Profitability 38 39 Times Interest Earned 40 5 15 88 9 90 235,895 58 26,500 00 26,500 00 9 90 259,485 14 29,150 00 29,150 00 42 4) 44 Asset Turnover 45 6 1 02 46 47 1 08 4,512,208 00 4,408,358 17 3,936,034 08 4,172,196 12 1 08 4,963,428 80 4,805, 110 40 4,408,358 17 4,606,734 29 7 10 50 6 29 235,895 58 26,500 00 4,408,358 17 3,936,034 08 4,172,196 12 6 27 259,485 14 29,150 00 4,805, 110 40 4,408 358 17 4,606,734 29 8 35 50 48 49 50 Return on Assets 51 52 53 54 55 56 57 Return on Common Shareholders' Equity 58 59 60 61 62 63 64 Earnings per Share (EPS) 65 66 67 31 96 235,895 58 31 84 259,485 14 779,830 33 696,277 08 738,053 71 850,015 06 779,830 33 814,922 70 9 $ $ $ 3 05 4 72 235,895 58 5 19 259 485 14 50,000 50,000 10 9 95 69 Price Earnings (P E) Ratio 70 71 11 66 13 87 55 00 72 00 4 72 5 19 IS Comparative Instructions Adjusted Trial Balances BS Comparative Ratios Historical Ready 80 FEB 5 W 24 55 0 Wed 7 02 PM Kaleb Punga W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AA ab Wrap Text Share Comments General Insert 33X Delete Ft Format Ov 5 Paste I U Ideas A 3 Merge Center v $ ) Sensitivity Conditional Format Cell Formatting as Table Styles X Sort Filter Find Select C D E G H 1 1 K L M N P R s T T U V w Y J14 4 x 77 73 Solvency Ratios 79 80 Ratio of Liabilities to Stockholder's Equity 12 0 80 4 65 3,239,757 696,277 4 65 3,628,528 779,830 7 31 20 250,000 50,000 45 7 31 19 300,000 50,000 55 7 31 18 200,000 50,000 72 82 83 84 85 Common Dividends 86 Weighted Average Common Sha 87 Market Price per Share 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 102 108 109 110 111 112 113 114 115 116 117 118 119 120 121 1122 123 124 125 126 127 128 129 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical Ready 80 FEB 5 W 24

Question

Not sure how to upload files on here  I don't think it's possible so here are screenshots of the pictures to have a clearer visual of what I need help with  THANK YOU   3 Part III   READ ALL INSTRUCTIONS   COMPLETELY SEPARATE FROM PARTS I   II 4 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)  You MUST use formulas and or linking to compute your ratios  You cannot simply 5 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 written 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 6 substance of your analysis  7 Help 56  0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF A SU Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert AF A Arial 14 29 Wrap Text Accounting 9X Delete V Paste BIU A Ideas           V Merge   Center $   94 90 Sensitivity Conditional Format Cell Formatting as Table Styles It Format Sort   Filter Find   Select F62 4 x fe 3 A 1 L M N o P R R C D July 31, 2020 Adjusted Trial Balance Dr  Cr  334,504 00 533 086 00 449,397 00 5,689 00 15 400 00 120 000 00 35,400 00 80,000 00 52 000 00 1,550,000 00 1,200,000 00 750,000 00 85,000 00 E F July 31, 2019 Adjusted Trial Balance Dr  Cr  361,264 32 575,732 88 485,348 76 6,144,12 16 632 00 129 600 00 38,232 00 86,400 00 56 160 00 1,674,000 00 1,296,000 00 July 31, 2018 Adjusted Trial Balance Dr  Cr  404,616 04 644,820 83 543,590 61 6,881 41 16,627 84 145,152 00 42,819 84 96 768 00 62,899 20 1,874,880,00 1 451,520 00 907,200 00 102,816 00 82,252 80 16,934,40 14,515 20 823 713 41 4 5 6 Aect  No  Account Title 7 100 Cash 8 102 Accounts Receivable, net 9 104 Merchandise Inventory net 10 106 Office Supplies 11 107 Prepaid Insurance 12 120 Investments   Equity Trading (Temporary) 13 121 Investments   HTM Long Term 14 122 Investments   Equity Long Term 123 Valuation Allowance 140 Land 17 145 Building 18 146 Accumulated Depreciation   Building 19 151 Equipment 20 152 Accumulated Depreciation   Equipment 21 153 Office Furniture 22 154 Accumulated Depreciation   Office Furniture 23 201 Accounts Payable 24 202 Wages Payable 25 203 Interest Payable 26 204 Dividends Payable 27 205 Unearned Rent 28 206 Customer Refunds Payable 29 250 Notes Payable   Long Term 30 251 Bonds Payable 31 3 252 Premium on Bonds Payable 32 253 Discount on Bonds Payable July 31, 2017 Adjusted Trial Balance Dr  Cr  441,031 48 702,854 70 592,513 77 7,500 74 20,304 35 158 215 68 46,673 63 105,477 12 68,560 13 2,043,619 20 1,582 156 80 988,848 00 112 069 44 89,655 55 18 458 50 15,821 57 897,847 61 July 31, 2016 Adjusted Trial Balance Dr  Cr  432,210 85 688,797 61 580,663 49 7,350 73 19,898 26 155,051 37 45,740 15 103,367 58 67,188 93 2,002,746 82 1,550,513 66 969,071 04 109 828 05 87,862 44 18,089 33 15,505 14 879,890 66 15 16 68,000 00 91,800 00 73,440 00 15 120 00 12,960 00 14,000 00 12,000 00 680 980 00 IN 10,800 00 20,298 60 10,000 00 18 795 00 650,000 00 1 300 000 00 56,000 00 12 096 00 22,734,43 786,240 00 1,572 480 00 67 737 60 13,184 64 24,780 53 857,001 60 1 714,003 20 73,833 98 12,920 95 24,284 92 839,861 57 1,679,723 14 72,357 30 60,480 00 16,000 00 17,280 00 19,353 60 21,095 42 20,673 52 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical   Enter   80  FEB 5 ME W 24 Help 56  0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF su Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert AF A Arial 14 29 Wrap Text Accounting V Paste BIU A       3 Merge   Center $ $   9 9X Delete It Format Ideas Conditional Format Cell Formatting as Table Styles Sensitivity X Sort   Filter Find   Select A M N P Q R July 31, 2017 Adjusted Trial Balance Dr  Cr  L July 31, 2016 Adjusted Trial Balance Dr  Cr  387,628 42 50,000 00 395,539 20 50,000 00 490,813 03 308,639 67 484 375 09 294,694 17 200,000 00 20,000 00 200,000 00 10,000 00 4,218,914 48 4,092,347 05 F62 Xv fx 3 D F G H July 31, 2020 July 31, 2019 July 31, 2018 Adjusted Adjusted Adjusted 5 Trial Balance Trial Balance Trial Balance 6 Acct  No  Account Title Dr  Cr  Dr  Cr Dr  Cr  252 Mortgage (Warehouse) Payable 300,000 00 362,880 00 300 Common Stock, S1 Par, 100,000 Authorized  50,000 shares Issued Outstanding 50,000 00 50 000,00 50,000 00 35 301 Paid In Capital   Excess of Par  Common Stock 347 575 25 361 143 16 36 330 Retained Earnings 324,582 75 329,202 03 37 331 Cash Dividends 250,000 00 300,000 00 200,000 00 38 340 Treasury Stock 50,000 00 60,000 00 20,000 00 39 500 Sales 4,256,800 00 4,963,428 80 40 600 Cost of Goods Sold 1,485,698 00 1,574,839 88 1,732,323 87 700 Wage Expense (hourly workers) 756,894 00 802 307 64 882,538 40 42 701 Salaries Expense (Exempt Staff) 800,000 00 848,000 00 932,800 00 702 Marketing Expense 295,000 00 312 700 00 343 970,00 703 Travel and Entertainment Expense 56 789 00 60 196 34 66 215 97 704 Bad Debt Expense 42,185 00 44 716 10 49 187 71 46 705 Property Tax Expense 500,000 00 530,000 00 583,000 00 47 706 Office Maintenance   Repair Expense 2,000 00 2,120 00 2,332 00 48 707 Accounting Expenses 10,000 00 10,600 00 11,660 00 49 708 Insurance Expense 12,000 00 12 720 00 13,992 00 50 709 Utilities Expense 65,450 00 69,377 00 76,314 70 51 710 Office Supplies Expense 546 00 578 76 636 64 52 711 Telecommunications Expense 5,425 00 5,750 50 6 325 55 53 713 Depreciation Expense   Building 30,000 00 31,800 00 34,980,00 54 713 Depreciation Expense   Equipment 8,500 00 9 010 00 9 911 00 55 714 Depreciation Expense   Office Furniture 2,000 00 2,120 00 2,332 00 56 800 Rent Income 8,000 00 8,480 00 9,328 00 57 801 Unrealized Gain   Trading Securities 5,500 00 5,830 00 6,413 00 58 802 Reaized Gain   Investment Securities 85,460 00 90,587 60 99 646 36 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical   44 45 1,472,475 29 750,157 64 792,880 00 292,374 50 56 283 58 41,809 55 495,550 00 1,982  20 9,911 00 11,893 20 64,867 50 541 14 5,376 72 29 733 00 8,424 35 1,428,301 03 727,652 91 769,093 60 283,603 27 54,595 07 40,555 27 480,683 50 1,922 73 9,613 67 11,536 40 62,921 47 524 91 5,215 42 28,841 01 8,171 62 1,922 73 1,982 20 7,928 80 5,451 05 84,699 41 7,690 94 5,287,52 82,158 42 Enter   80  FEB 5 AME W 24 Help 56  0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF su u Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert Arial AA 14 29 Wrap Text Accounting 9X Delete V Paste BIU A           9 Merge   Center V Ideas $   98 99 Sensitivity Conditional Format Cell Formatting as Table Styles It Format Sort   Filter Find   Select F62 4 x fe 3 A M N o P R R 4 5 5 6 58 59 C D July 31, 2020 Adjusted Trial Balance Dr  Cr  85,460 00 25,000 00 2,500 00 33 230 00 8 923 693 00 8,923,693 00 4 133 217 00 4,355,760 00 222,543 00 Acct  No  Account Title 802 Realized Gain   Investment Securities 900 Interest Expense 90 Unrealized Loss   Trading Securities 902 Realized Loss   Investment Securities Total Total Expenses and Revenue Net Income (Loss) E F July 31, 2019 Adjusted Trial Balance Dr  Cr  90,587 60 26,500 00 2,650 00 35,223,80 9,590,924 10 3 4 381 210 02 235,895 58 H July 31, 2018 Adjusted Trial Balance Dr  Cr  99,646 36 29,150 00 2 915 00 38,746 18 10,471,010 79 10,471,010,79 4 819,331 02 5,078,816 16 259,485 14 July 31, 2017 July 31, 2016 Adjusted Adjusted Trial Balance Trial Balance Dr  Cr  Dr  Cr  84,699 41 82,158 42 24,777 50 24,034 18 2,477 75 2 403 42 32 934 25 31,946 23 10,236,962 32 10 236,962 32 9 985,658 76 9,965,658 76 4,096 431 37 4,316,993 74 3,973,538 43 4,187 483 92 220 562 37 213,945 50 60 61 62 63 (0 00) (0 00) (0 00) (0 00) 0 00 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 84 85 86 87 88 90 91 92 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical   Enter   80  FEB 5 ME W 24 7 31 17 7 31 16 4,218,914 48 1,472,475 29 2,746,439 19 4,092,347 05 1,428,301 03 2,664,046 02 For the Years ended July 31, 2020, 2019, 2018, 2017, 2016 7 31 20 7 31 19 7 31 18 Sales Revenue Sales 4,256,800 00 4,512,208 00 4,963,428 80 Cost of Goods Sold 1,485,698 00 1,574,839 88 1,732,323 87 Gross Profit 2,771,102 00 2,937,368 12 3,231,104 93 Operating Expenses Wage Expense (hourly workers) 756,894 00 802,307 64 882,538 40 Salaries Expense (Exempt Staff) 800,000 00 848,000 00 932,800 00 Marketing Expense 295,000 00 312,700 00 343,970 00 Travel and Entertainment Expense 56,789 00 60,196 34 66,215 97 Bad Debt Expense 42,185 00 44,716 10 49,187 71 Property Tax Expense 500,000 00 530,000 00 583,000 00 Office Maintenance   Repair Expense 2,000 00 2,120 00 2,332 00 Accounting Expenses 10,000 00 10,600 00 11,660 00 Insurance Expense 12,000 00 12,720 00 13,992 00 Utilities Expense 65,450 00 69,377 00 76,314 70 Office Supplies Expense 546 00 578 76 636 64 Telecommunications Expense 5,425 00 5,750 50 6,325 55 Depreciation Expense   Building 30,000 00 31,800 00 34,980 00 Depreciation Expense   Equipment 8,500 00 9,010 00 9,911 00 Depreciation Expense   Office Furnitur 2,000 00 2,120 00 2,332 00 Total Operating Expenses 2,586,789 00 2,741,996 34 3,016,195 97 Income from Operations (EBIT) 184,313 00 195,371 78 214,908 96 Other Revenues and Gains Rent Income 8,000 00 8,480 00 9,328 00 Unrealized Gain   Trading Securities 5,500 00 5,830 00 6,413 00 Realized Gain   Investment Securities 85,460 00 90,587 60 99,646 36 Total Other Revenue and Gains 98,960 00 104,897 60 115,387 36 Other Expenses and Losses Interest Expense 25,000 00 26,500 00 29,150 00 Unrealized Loss Trading Securities 2,500 00 2,650 00 2,915 00 Realized Loss   Investment Securities 33,230 00 35,223 80 38,746 18 Total Other Expenses and Losses 60,730 00 64,373 80 70,811 18 750,157 64 792,880 00 292,374 50 56,283 58 41,809 55 495,550 00 1,982 20 9,911 00 11,893 20 64,867 50 541 14 5,376 72 29,733 00 8,424 35 1,982 20 2,563,766 58 182,672 61 727,652 91 769,093 60 283,603 27 54,595 07 40,555 27 480,683 50 1,922 73 9,613 67 11,536 40 62,921 47 524 91 5,215 42 28,841 01 8,171 62 1,922 73 2,486,853 58 177,192 44 7,928 80 5,451 05 84,699 41 98,079 26 7,690 94 5,287 52 82,158 42 95,136 88 24,777 50 2,477 75 32,934 25 60 189 50 24,034 18 2,403 42 31,946 23 58,383 82 Net Income (Loss) 222,543 00 235,895 58 259,485 14 220,562 37 213,945 50 Common Dividends Weighted Average Common Shares Market Price per Common Share 250,000 50,000 45 00 300,000 50,000 55 00 200,000 50,000 72 00 200,000 50,000 68 00 Balance Sheet July 31, 2020, 2019, 2018, 2017, 2016 7 31 19 7 31 20 7 31 18 7 31 17 7 31 16 334,504 00 533,086 00 449,397 00 5,689 00 15,400 00 120,000 00 1,458,076 00 361,264 32 575,732 88 485,348 76 6,144 12 16,632 00 129,600 00 1,574,722 08 404,616 04 644,820 83 543,590 61 6,881 41 18,627 84 145,152 00 1,763,688 73 441,031 48 702,854 70 592,513 77 7,500 74 20,304 35 158,215 68 1,922,420 72 432,210 85 688,797 61 580,663 49 7,350 73 19,898 26 155,051 37 1,883,972 30 ASSETS Current assets Cash Accounts Receivable, net Merchandise Inventory, net Office Supplies Prepaid Insurance Investments   Equity Trading (Temporary) Total Current Assets Property, plant   equipment Land Building Accumulated Depreciation   Building Equipment Accumulated Depreciation   Equipment Office Furniture Accumulated Depreciation   Office Furniture Total Property, plant   equipment Other Assets Investments   HTM Long Term Investments   Equity Long Term Valuation Allowance Total Other Assets Total Assets 1,550,000 00 1,200,000 00 (750,000 00) 85,000 00 (68,000 00) 14,000 00 (12,000 00) 2,019,000 00 1,674,000 00 1,296,000 00 (810,000 00) 91,800 00 (73,440 00) 15,120 00 (12,960 00) 2,180,520 00 1,874,880 00 1,451,520 00 (907,200 00) 102,816 00 (82,252 80) 16,934 40 (14,515 20) 2,442,182 40 2,043,619 20 1,582,156 80 (988,848 00) 112,069 44 (89,655 55) 18,458 50 (15,821 57) 2,661,978 82 2,002,746 82 1,550,513 66 (969,071 04) 109,828 05 (87,862 44) 18,089 33 (15,505 14) 2,608,739 24 35,400 00 80,000 00 52,000 00 167,400 00 3,644,476 00 38,232 00 86,400 00 56,160 00 180,792 00 3,936,034 08 42,819 84 96,768 00 62,899 20 202,487 04 4,408,358 17 46,673 63 105,477 12 68,560 13 220,710 87 4,805,110 40 45,740 15 103,367 58 67,188 93 216,296 66 4,709,008 20 2 56  0 Wed 7 01 PM Kaleb Punga  W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 11 AF A    ab Wrap Text Share Comments Insert General $   Ov 5 Paste BIU A Ideas 3 V 33X Delete It Format Merge   Center Conditional Format Cell Formatting as Table Styles Sensitivity Sort   Filter X Find   Select 028 D E F G H 1 K L M N O P Q R S S T U 31 680,980 00 10,000 00 18,795 00 709,775 00 735,458,40 10,800 00 20,298 60 766,557 00 823,713 41 12,096 00 22,734 43 858,543 84 897,847 61 13,184 64 24,780 53 935,812 79 879,890 66 12,920 95 24,284 92 917,096 53 30 LIABILITIES   STOCKHOLDER'S EQUITY LIABILITIES 32 Current Liabilities 33 Accounts Payable 34 Unearned Rent 35 Customer Refunds Payable 36 Total Current Liabilities 37 Long term Liabilities 38 Notes Payable   Long Term 39 Bonds Payable 40 Premium on Bonds Payable 41 Discount on Bonds Payable 42 Mortgage (Warehouse) Payable 43 Total Long term Liabilities 44 Total Liabilities 650,000 00 1,300,000 00 56,000 00 (16,000 00) 300,000 00 2,290,000 00 2,999,775 00 702,000 00 1,404,000 00 60,480 00 (17,280 00) 324,000 00 2,473,200 00 3,239,757 00 786,240 00 1,572,480 00 67,737 60 (19,353 60) 362,880 00 2,769,984 00 3,628,527 84 857,001 60 1,714,003 20 73,833 98 (21,095 42) 395,539 20 3,019,282 56 3,955,095 35 839,861 57 1,679,723 14 72,357 30 (20,673 52) 387,628 42 2,958,896 91 3,875,993 44 45 46 STOCKHOLDER'S EQUITY Common Stock, $1 Par, 100,000 Authorized  47 50,000 shares Issued Outstanding 48 Paid In Capital   Excess of Par, Common Stock 49 Treasury Stock 50 Cash Dividends 51 Ending Retained Earnings 52 Total Stockholder's Equity 53 54 Total Liabilities and Stockholder's Equity 50,000 00 347,575 25 (50,000 00) (250,000 00) 547,125 75 644,701 00 50,000 00 381,694 33 (60,000,00) (300,000 00) 624,582 75 696,277 08 50,000 00 361,143 16 (20 000,00) (200,000 00) 588,687 17 779,830 33 50,000 00 490,813 03 (20,000 00 (200,000 00 529,202 03 850,015 06 50,000 00 484,375 09 (10,000 00) (200 000 001 508,639 67 833,014 76 3,644,476 00 3,936,034 08 4,408,358 17 4,805, 110 41 4,709,008 19 56 57 TOTAL ASSETS 58 TOTAL LIAB   EQUITY 59 VARIANCE 3,644,476 00 3,644,476 00 3,936,034 08 3,936,034 08 0 4,408,358 17 4,408,358 17 0 Ratios Historical 4,805,110 40 4,805,110 41 0 4,709,008 20 4,709,008 19 0 Instructions Adjusted Trial Balances IS Comparative BS Comparative   Ready 90  FEB 5 W 24 55  0 Wed 7 02 PM Kaleb Pung QE W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF $55 Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AA ab Wrap Text Share Comments General Insert 338 Delete It Format Ov 5 Paste B IV Ideas V A         V Merge   Center Sensitivity $   ) Conditional Format Cell Formatting as Table Styles X Sort   Filter Find   Select K K L M N O P R S T U V w Y J14 A XV fox B C D E G H 1 2 Ratio Analysis 3 July 31, 2020, 2019, 2018 Instructions  Calculate each of the below ratios for the JUST THE current year  FOLLOW the below steps to complete the ratio analysis  1  Round ALL answers 2 decimal places 2  Must USE ratio descriptions   including how averages are calculated  For example if average A R is required, students would input beginng A R, AN                      3  MUST use excel formulas and links to IS comparative worksheets, BS comparative worksheets, or adjusted trial balance within this workbook (see 4  MUST indicate if current year answer is favorable, unfavorable, or same in realtion to prior year AND industry (see current ratio for example) 9 5  USE information at bottom of this worksheet to calculate certain ratios 10 6  If a portion of a ratio is not available, still use in ratio description and use 0 as part of formula 11 8  USE ratios and comparative statements to write a memo to Davenport Docking management explaining your findings 12 7 31 20 7 31 19 7 31 18 INDUSTRY Prior Year Industry  Favorable, Unfavorable, Same 14 Liquidity 15 Current Ratio 2 05 1 2 10 2 10 2 25 Prior Year   Unfavorable 16 Current Assets 1,458,076 1,574,722 1,763,689 Industry   Unfavorble 17 Current Liabilities 709,775 766,557 858,544 18 19 Quick Ratio 2 1 39 1 39 1 20 20 361,264 32 404,616 04 21 575,732 88 644,820 83 22 129,600 00 145,152 00 23 766,557 00 858,543 84 24 25 Accounts Receivable Turnover 3 7 39 7 37 8 50 26 4,512,208 00 4,963,428 80 27 644,820 83 702,854 70 28 575,732 88 644,820 83 29 610,276 85 673 837 76 30 31 Inventory Turnover 4 3 06 3 05 2 50 32 1,574,839 88 1,732,323 87 33 543,590 61 592,513 77 34 485,348 76 543,590 61 35 514,469 69 568,052 19   Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical   Ready   80  FEB 5 W 24 55  0 Wed 7 02 PM Kaleb Punga  W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AF A    ab Wrap Text Share Comments General Insert Ov 5 Paste B IV a  Av V         3 Merge   Center v Ideas 33X Delete It Format $   Conditional Format Cell Formatting as Table Styles Sort   Filter Sensitivity X Find   Select J14 X fx 8 C D E F G H 1 K L M M N O P R s T U V w Y 37 Profitability 38 39 Times Interest Earned 40 5 15 88 9 90 235,895 58 26,500 00 26,500 00 9 90 259,485 14 29,150 00 29,150 00 42 4) 44 Asset Turnover 45 6 1 02 46 47 1 08 4,512,208 00 4,408,358 17 3,936,034 08 4,172,196 12 1 08 4,963,428 80 4,805, 110 40 4,408,358 17 4,606,734 29 7 10 50  6 29  235,895 58 26,500 00 4,408,358 17 3,936,034 08 4,172,196 12 6 27  259,485 14 29,150 00 4,805, 110 40 4,408 358 17 4,606,734 29 8 35 50  48 49 50 Return on Assets 51 52 53 54 55 56 57 Return on Common Shareholders' Equity 58 59 60 61 62 63 64 Earnings per Share (EPS) 65 66 67 31 96  235,895 58 31 84  259,485 14 779,830 33 696,277 08 738,053 71 850,015 06 779,830 33 814,922 70 9 $ $ $ 3 05 4 72 235,895 58 5 19 259 485 14 50,000 50,000 10 9 95 69 Price Earnings (P E) Ratio 70 71 11 66 13 87 55 00 72 00 4 72 5 19 IS Comparative Instructions Adjusted Trial Balances BS Comparative Ratios Historical   Ready 80  FEB 5 W 24 55  0 Wed 7 02 PM Kaleb Punga  W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AA    ab Wrap Text Share Comments General Insert 33X Delete Ft Format Ov 5 Paste I U Ideas A         3 Merge   Center v $   ) Sensitivity Conditional Format Cell Formatting as Table Styles X Sort   Filter Find   Select C D E G H 1 1 K L M N P R s T T U V w Y J14 4 x 77 73 Solvency Ratios 79 80 Ratio of Liabilities to Stockholder's Equity 12 0 80 4 65 3,239,757 696,277 4 65 3,628,528 779,830 7 31 20 250,000 50,000 45 7 31 19 300,000 50,000 55 7 31 18 200,000 50,000 72 82 83 84 85 Common Dividends 86 Weighted Average Common Sha 87 Market Price per Share 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 102 108 109 110 111 112 113 114 115 116 117 118 119 120 121 1122 123 124 125 126 127 128 129 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical   Ready   80  FEB 5 W 24 3 Part III   READ ALL INSTRUCTIONS   COMPLETELY SEPARATE FROM PARTS I   II 4 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)  You MUST use formulas and or linking to compute your ratios  You cannot simply 5 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 written 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 6 substance of your analysis  7 Help 56  0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF A SU Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert AF A Arial 14 29 Wrap Text Accounting 9X Delete V Paste BIU A Ideas           V Merge   Center $   94 90 Sensitivity Conditional Format Cell Formatting as Table Styles It Format Sort   Filter Find   Select F62 4 x fe 3 A 1 L M N o P R R C D July 31, 2020 Adjusted Trial Balance Dr  Cr  334,504 00 533 086 00 449,397 00 5,689 00 15 400 00 120 000 00 35,400 00 80,000 00 52 000 00 1,550,000 00 1,200,000 00 750,000 00 85,000 00 E F July 31, 2019 Adjusted Trial Balance Dr  Cr  361,264 32 575,732 88 485,348 76 6,144,12 16 632 00 129 600 00 38,232 00 86,400 00 56 160 00 1,674,000 00 1,296,000 00 July 31, 2018 Adjusted Trial Balance Dr  Cr  404,616 04 644,820 83 543,590 61 6,881 41 16,627 84 145,152 00 42,819 84 96 768 00 62,899 20 1,874,880,00 1 451,520 00 907,200 00 102,816 00 82,252 80 16,934,40 14,515 20 823 713 41 4 5 6 Aect  No  Account Title 7 100 Cash 8 102 Accounts Receivable, net 9 104 Merchandise Inventory net 10 106 Office Supplies 11 107 Prepaid Insurance 12 120 Investments   Equity Trading (Temporary) 13 121 Investments   HTM Long Term 14 122 Investments   Equity Long Term 123 Valuation Allowance 140 Land 17 145 Building 18 146 Accumulated Depreciation   Building 19 151 Equipment 20 152 Accumulated Depreciation   Equipment 21 153 Office Furniture 22 154 Accumulated Depreciation   Office Furniture 23 201 Accounts Payable 24 202 Wages Payable 25 203 Interest Payable 26 204 Dividends Payable 27 205 Unearned Rent 28 206 Customer Refunds Payable 29 250 Notes Payable   Long Term 30 251 Bonds Payable 31 3 252 Premium on Bonds Payable 32 253 Discount on Bonds Payable July 31, 2017 Adjusted Trial Balance Dr  Cr  441,031 48 702,854 70 592,513 77 7,500 74 20,304 35 158 215 68 46,673 63 105,477 12 68,560 13 2,043,619 20 1,582 156 80 988,848 00 112 069 44 89,655 55 18 458 50 15,821 57 897,847 61 July 31, 2016 Adjusted Trial Balance Dr  Cr  432,210 85 688,797 61 580,663 49 7,350 73 19,898 26 155,051 37 45,740 15 103,367 58 67,188 93 2,002,746 82 1,550,513 66 969,071 04 109 828 05 87,862 44 18,089 33 15,505 14 879,890 66 15 16 68,000 00 91,800 00 73,440 00 15 120 00 12,960 00 14,000 00 12,000 00 680 980 00 IN 10,800 00 20,298 60 10,000 00 18 795 00 650,000 00 1 300 000 00 56,000 00 12 096 00 22,734,43 786,240 00 1,572 480 00 67 737 60 13,184 64 24,780 53 857,001 60 1 714,003 20 73,833 98 12,920 95 24,284 92 839,861 57 1,679,723 14 72,357 30 60,480 00 16,000 00 17,280 00 19,353 60 21,095 42 20,673 52 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical   Enter   80  FEB 5 ME W 24 Help 56  0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF su Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert AF A Arial 14 29 Wrap Text Accounting V Paste BIU A       3 Merge   Center $ $   9 9X Delete It Format Ideas Conditional Format Cell Formatting as Table Styles Sensitivity X Sort   Filter Find   Select A M N P Q R July 31, 2017 Adjusted Trial Balance Dr  Cr  L July 31, 2016 Adjusted Trial Balance Dr  Cr  387,628 42 50,000 00 395,539 20 50,000 00 490,813 03 308,639 67 484 375 09 294,694 17 200,000 00 20,000 00 200,000 00 10,000 00 4,218,914 48 4,092,347 05 F62 Xv fx 3 D F G H July 31, 2020 July 31, 2019 July 31, 2018 Adjusted Adjusted Adjusted 5 Trial Balance Trial Balance Trial Balance 6 Acct  No  Account Title Dr  Cr  Dr  Cr Dr  Cr  252 Mortgage (Warehouse) Payable 300,000 00 362,880 00 300 Common Stock, S1 Par, 100,000 Authorized  50,000 shares Issued Outstanding 50,000 00 50 000,00 50,000 00 35 301 Paid In Capital   Excess of Par  Common Stock 347 575 25 361 143 16 36 330 Retained Earnings 324,582 75 329,202 03 37 331 Cash Dividends 250,000 00 300,000 00 200,000 00 38 340 Treasury Stock 50,000 00 60,000 00 20,000 00 39 500 Sales 4,256,800 00 4,963,428 80 40 600 Cost of Goods Sold 1,485,698 00 1,574,839 88 1,732,323 87 700 Wage Expense (hourly workers) 756,894 00 802 307 64 882,538 40 42 701 Salaries Expense (Exempt Staff) 800,000 00 848,000 00 932,800 00 702 Marketing Expense 295,000 00 312 700 00 343 970,00 703 Travel and Entertainment Expense 56 789 00 60 196 34 66 215 97 704 Bad Debt Expense 42,185 00 44 716 10 49 187 71 46 705 Property Tax Expense 500,000 00 530,000 00 583,000 00 47 706 Office Maintenance   Repair Expense 2,000 00 2,120 00 2,332 00 48 707 Accounting Expenses 10,000 00 10,600 00 11,660 00 49 708 Insurance Expense 12,000 00 12 720 00 13,992 00 50 709 Utilities Expense 65,450 00 69,377 00 76,314 70 51 710 Office Supplies Expense 546 00 578 76 636 64 52 711 Telecommunications Expense 5,425 00 5,750 50 6 325 55 53 713 Depreciation Expense   Building 30,000 00 31,800 00 34,980,00 54 713 Depreciation Expense   Equipment 8,500 00 9 010 00 9 911 00 55 714 Depreciation Expense   Office Furniture 2,000 00 2,120 00 2,332 00 56 800 Rent Income 8,000 00 8,480 00 9,328 00 57 801 Unrealized Gain   Trading Securities 5,500 00 5,830 00 6,413 00 58 802 Reaized Gain   Investment Securities 85,460 00 90,587 60 99 646 36 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical   44 45 1,472,475 29 750,157 64 792,880 00 292,374 50 56 283 58 41,809 55 495,550 00 1,982  20 9,911 00 11,893 20 64,867 50 541 14 5,376 72 29 733 00 8,424 35 1,428,301 03 727,652 91 769,093 60 283,603 27 54,595 07 40,555 27 480,683 50 1,922 73 9,613 67 11,536 40 62,921 47 524 91 5,215 42 28,841 01 8,171 62 1,922 73 1,982 20 7,928 80 5,451 05 84,699 41 7,690 94 5,287,52 82,158 42 Enter   80  FEB 5 AME W 24 Help 56  0 Excel File Edit View Insert Format Tools Data Window AutoSave OFF su u Wed 7 00 PM Kaleb Pungas Q W21 DD Student File P3 Home Insert Draw Page Layout Formulas Data Review View Share Comments Insert Arial AA 14 29 Wrap Text Accounting 9X Delete V Paste BIU A           9 Merge   Center V Ideas $   98 99 Sensitivity Conditional Format Cell Formatting as Table Styles It Format Sort   Filter Find   Select F62 4 x fe 3 A M N o P R R 4 5 5 6 58 59 C D July 31, 2020 Adjusted Trial Balance Dr  Cr  85,460 00 25,000 00 2,500 00 33 230 00 8 923 693 00 8,923,693 00 4 133 217 00 4,355,760 00 222,543 00 Acct  No  Account Title 802 Realized Gain   Investment Securities 900 Interest Expense 90 Unrealized Loss   Trading Securities 902 Realized Loss   Investment Securities Total Total Expenses and Revenue Net Income (Loss) E F July 31, 2019 Adjusted Trial Balance Dr  Cr  90,587 60 26,500 00 2,650 00 35,223,80 9,590,924 10 3 4 381 210 02 235,895 58 H July 31, 2018 Adjusted Trial Balance Dr  Cr  99,646 36 29,150 00 2 915 00 38,746 18 10,471,010 79 10,471,010,79 4 819,331 02 5,078,816 16 259,485 14 July 31, 2017 July 31, 2016 Adjusted Adjusted Trial Balance Trial Balance Dr  Cr  Dr  Cr  84,699 41 82,158 42 24,777 50 24,034 18 2,477 75 2 403 42 32 934 25 31,946 23 10,236,962 32 10 236,962 32 9 985,658 76 9,965,658 76 4,096 431 37 4,316,993 74 3,973,538 43 4,187 483 92 220 562 37 213,945 50 60 61 62 63 (0 00) (0 00) (0 00) (0 00) 0 00 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 84 85 86 87 88 90 91 92 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical   Enter   80  FEB 5 ME W 24 7 31 17 7 31 16 4,218,914 48 1,472,475 29 2,746,439 19 4,092,347 05 1,428,301 03 2,664,046 02 For the Years ended July 31, 2020, 2019, 2018, 2017, 2016 7 31 20 7 31 19 7 31 18 Sales Revenue Sales 4,256,800 00 4,512,208 00 4,963,428 80 Cost of Goods Sold 1,485,698 00 1,574,839 88 1,732,323 87 Gross Profit 2,771,102 00 2,937,368 12 3,231,104 93 Operating Expenses Wage Expense (hourly workers) 756,894 00 802,307 64 882,538 40 Salaries Expense (Exempt Staff) 800,000 00 848,000 00 932,800 00 Marketing Expense 295,000 00 312,700 00 343,970 00 Travel and Entertainment Expense 56,789 00 60,196 34 66,215 97 Bad Debt Expense 42,185 00 44,716 10 49,187 71 Property Tax Expense 500,000 00 530,000 00 583,000 00 Office Maintenance   Repair Expense 2,000 00 2,120 00 2,332 00 Accounting Expenses 10,000 00 10,600 00 11,660 00 Insurance Expense 12,000 00 12,720 00 13,992 00 Utilities Expense 65,450 00 69,377 00 76,314 70 Office Supplies Expense 546 00 578 76 636 64 Telecommunications Expense 5,425 00 5,750 50 6,325 55 Depreciation Expense   Building 30,000 00 31,800 00 34,980 00 Depreciation Expense   Equipment 8,500 00 9,010 00 9,911 00 Depreciation Expense   Office Furnitur 2,000 00 2,120 00 2,332 00 Total Operating Expenses 2,586,789 00 2,741,996 34 3,016,195 97 Income from Operations (EBIT) 184,313 00 195,371 78 214,908 96 Other Revenues and Gains Rent Income 8,000 00 8,480 00 9,328 00 Unrealized Gain   Trading Securities 5,500 00 5,830 00 6,413 00 Realized Gain   Investment Securities 85,460 00 90,587 60 99,646 36 Total Other Revenue and Gains 98,960 00 104,897 60 115,387 36 Other Expenses and Losses Interest Expense 25,000 00 26,500 00 29,150 00 Unrealized Loss Trading Securities 2,500 00 2,650 00 2,915 00 Realized Loss   Investment Securities 33,230 00 35,223 80 38,746 18 Total Other Expenses and Losses 60,730 00 64,373 80 70,811 18 750,157 64 792,880 00 292,374 50 56,283 58 41,809 55 495,550 00 1,982 20 9,911 00 11,893 20 64,867 50 541 14 5,376 72 29,733 00 8,424 35 1,982 20 2,563,766 58 182,672 61 727,652 91 769,093 60 283,603 27 54,595 07 40,555 27 480,683 50 1,922 73 9,613 67 11,536 40 62,921 47 524 91 5,215 42 28,841 01 8,171 62 1,922 73 2,486,853 58 177,192 44 7,928 80 5,451 05 84,699 41 98,079 26 7,690 94 5,287 52 82,158 42 95,136 88 24,777 50 2,477 75 32,934 25 60 189 50 24,034 18 2,403 42 31,946 23 58,383 82 Net Income (Loss) 222,543 00 235,895 58 259,485 14 220,562 37 213,945 50 Common Dividends Weighted Average Common Shares Market Price per Common Share 250,000 50,000 45 00 300,000 50,000 55 00 200,000 50,000 72 00 200,000 50,000 68 00 Balance Sheet July 31, 2020, 2019, 2018, 2017, 2016 7 31 19 7 31 20 7 31 18 7 31 17 7 31 16 334,504 00 533,086 00 449,397 00 5,689 00 15,400 00 120,000 00 1,458,076 00 361,264 32 575,732 88 485,348 76 6,144 12 16,632 00 129,600 00 1,574,722 08 404,616 04 644,820 83 543,590 61 6,881 41 18,627 84 145,152 00 1,763,688 73 441,031 48 702,854 70 592,513 77 7,500 74 20,304 35 158,215 68 1,922,420 72 432,210 85 688,797 61 580,663 49 7,350 73 19,898 26 155,051 37 1,883,972 30 ASSETS Current assets Cash Accounts Receivable, net Merchandise Inventory, net Office Supplies Prepaid Insurance Investments   Equity Trading (Temporary) Total Current Assets Property, plant   equipment Land Building Accumulated Depreciation   Building Equipment Accumulated Depreciation   Equipment Office Furniture Accumulated Depreciation   Office Furniture Total Property, plant   equipment Other Assets Investments   HTM Long Term Investments   Equity Long Term Valuation Allowance Total Other Assets Total Assets 1,550,000 00 1,200,000 00 (750,000 00) 85,000 00 (68,000 00) 14,000 00 (12,000 00) 2,019,000 00 1,674,000 00 1,296,000 00 (810,000 00) 91,800 00 (73,440 00) 15,120 00 (12,960 00) 2,180,520 00 1,874,880 00 1,451,520 00 (907,200 00) 102,816 00 (82,252 80) 16,934 40 (14,515 20) 2,442,182 40 2,043,619 20 1,582,156 80 (988,848 00) 112,069 44 (89,655 55) 18,458 50 (15,821 57) 2,661,978 82 2,002,746 82 1,550,513 66 (969,071 04) 109,828 05 (87,862 44) 18,089 33 (15,505 14) 2,608,739 24 35,400 00 80,000 00 52,000 00 167,400 00 3,644,476 00 38,232 00 86,400 00 56,160 00 180,792 00 3,936,034 08 42,819 84 96,768 00 62,899 20 202,487 04 4,408,358 17 46,673 63 105,477 12 68,560 13 220,710 87 4,805,110 40 45,740 15 103,367 58 67,188 93 216,296 66 4,709,008 20 2 56  0 Wed 7 01 PM Kaleb Punga  W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 11 AF A    ab Wrap Text Share Comments Insert General $   Ov 5 Paste BIU A Ideas 3 V 33X Delete It Format Merge   Center Conditional Format Cell Formatting as Table Styles Sensitivity Sort   Filter X Find   Select 028 D E F G H 1 K L M N O P Q R S S T U 31 680,980 00 10,000 00 18,795 00 709,775 00 735,458,40 10,800 00 20,298 60 766,557 00 823,713 41 12,096 00 22,734 43 858,543 84 897,847 61 13,184 64 24,780 53 935,812 79 879,890 66 12,920 95 24,284 92 917,096 53 30 LIABILITIES   STOCKHOLDER'S EQUITY LIABILITIES 32 Current Liabilities 33 Accounts Payable 34 Unearned Rent 35 Customer Refunds Payable 36 Total Current Liabilities 37 Long term Liabilities 38 Notes Payable   Long Term 39 Bonds Payable 40 Premium on Bonds Payable 41 Discount on Bonds Payable 42 Mortgage (Warehouse) Payable 43 Total Long term Liabilities 44 Total Liabilities 650,000 00 1,300,000 00 56,000 00 (16,000 00) 300,000 00 2,290,000 00 2,999,775 00 702,000 00 1,404,000 00 60,480 00 (17,280 00) 324,000 00 2,473,200 00 3,239,757 00 786,240 00 1,572,480 00 67,737 60 (19,353 60) 362,880 00 2,769,984 00 3,628,527 84 857,001 60 1,714,003 20 73,833 98 (21,095 42) 395,539 20 3,019,282 56 3,955,095 35 839,861 57 1,679,723 14 72,357 30 (20,673 52) 387,628 42 2,958,896 91 3,875,993 44 45 46 STOCKHOLDER'S EQUITY Common Stock, $1 Par, 100,000 Authorized  47 50,000 shares Issued Outstanding 48 Paid In Capital   Excess of Par, Common Stock 49 Treasury Stock 50 Cash Dividends 51 Ending Retained Earnings 52 Total Stockholder's Equity 53 54 Total Liabilities and Stockholder's Equity 50,000 00 347,575 25 (50,000 00) (250,000 00) 547,125 75 644,701 00 50,000 00 381,694 33 (60,000,00) (300,000 00) 624,582 75 696,277 08 50,000 00 361,143 16 (20 000,00) (200,000 00) 588,687 17 779,830 33 50,000 00 490,813 03 (20,000 00 (200,000 00 529,202 03 850,015 06 50,000 00 484,375 09 (10,000 00) (200 000 001 508,639 67 833,014 76 3,644,476 00 3,936,034 08 4,408,358 17 4,805, 110 41 4,709,008 19 56 57 TOTAL ASSETS 58 TOTAL LIAB   EQUITY 59 VARIANCE 3,644,476 00 3,644,476 00 3,936,034 08 3,936,034 08 0 4,408,358 17 4,408,358 17 0 Ratios Historical 4,805,110 40 4,805,110 41 0 4,709,008 20 4,709,008 19 0 Instructions Adjusted Trial Balances IS Comparative BS Comparative   Ready 90  FEB 5 W 24 55  0 Wed 7 02 PM Kaleb Pung QE W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF $55 Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AA ab Wrap Text Share Comments General Insert 338 Delete It Format Ov 5 Paste B IV Ideas V A         V Merge   Center Sensitivity $   ) Conditional Format Cell Formatting as Table Styles X Sort   Filter Find   Select K K L M N O P R S T U V w Y J14 A XV fox B C D E G H 1 2 Ratio Analysis 3 July 31, 2020, 2019, 2018 Instructions  Calculate each of the below ratios for the JUST THE current year  FOLLOW the below steps to complete the ratio analysis  1  Round ALL answers 2 decimal places 2  Must USE ratio descriptions   including how averages are calculated  For example if average A R is required, students would input beginng A R, AN                      3  MUST use excel formulas and links to IS comparative worksheets, BS comparative worksheets, or adjusted trial balance within this workbook (see 4  MUST indicate if current year answer is favorable, unfavorable, or same in realtion to prior year AND industry (see current ratio for example) 9 5  USE information at bottom of this worksheet to calculate certain ratios 10 6  If a portion of a ratio is not available, still use in ratio description and use 0 as part of formula 11 8  USE ratios and comparative statements to write a memo to Davenport Docking management explaining your findings 12 7 31 20 7 31 19 7 31 18 INDUSTRY Prior Year Industry  Favorable, Unfavorable, Same 14 Liquidity 15 Current Ratio 2 05 1 2 10 2 10 2 25 Prior Year   Unfavorable 16 Current Assets 1,458,076 1,574,722 1,763,689 Industry   Unfavorble 17 Current Liabilities 709,775 766,557 858,544 18 19 Quick Ratio 2 1 39 1 39 1 20 20 361,264 32 404,616 04 21 575,732 88 644,820 83 22 129,600 00 145,152 00 23 766,557 00 858,543 84 24 25 Accounts Receivable Turnover 3 7 39 7 37 8 50 26 4,512,208 00 4,963,428 80 27 644,820 83 702,854 70 28 575,732 88 644,820 83 29 610,276 85 673 837 76 30 31 Inventory Turnover 4 3 06 3 05 2 50 32 1,574,839 88 1,732,323 87 33 543,590 61 592,513 77 34 485,348 76 543,590 61 35 514,469 69 568,052 19   Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical   Ready   80  FEB 5 W 24 55  0 Wed 7 02 PM Kaleb Punga  W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AF A    ab Wrap Text Share Comments General Insert Ov 5 Paste B IV a  Av V         3 Merge   Center v Ideas 33X Delete It Format $   Conditional Format Cell Formatting as Table Styles Sort   Filter Sensitivity X Find   Select J14 X fx 8 C D E F G H 1 K L M M N O P R s T U V w Y 37 Profitability 38 39 Times Interest Earned 40 5 15 88 9 90 235,895 58 26,500 00 26,500 00 9 90 259,485 14 29,150 00 29,150 00 42 4) 44 Asset Turnover 45 6 1 02 46 47 1 08 4,512,208 00 4,408,358 17 3,936,034 08 4,172,196 12 1 08 4,963,428 80 4,805, 110 40 4,408,358 17 4,606,734 29 7 10 50  6 29  235,895 58 26,500 00 4,408,358 17 3,936,034 08 4,172,196 12 6 27  259,485 14 29,150 00 4,805, 110 40 4,408 358 17 4,606,734 29 8 35 50  48 49 50 Return on Assets 51 52 53 54 55 56 57 Return on Common Shareholders' Equity 58 59 60 61 62 63 64 Earnings per Share (EPS) 65 66 67 31 96  235,895 58 31 84  259,485 14 779,830 33 696,277 08 738,053 71 850,015 06 779,830 33 814,922 70 9 $ $ $ 3 05 4 72 235,895 58 5 19 259 485 14 50,000 50,000 10 9 95 69 Price Earnings (P E) Ratio 70 71 11 66 13 87 55 00 72 00 4 72 5 19 IS Comparative Instructions Adjusted Trial Balances BS Comparative Ratios Historical   Ready 80  FEB 5 W 24 55  0 Wed 7 02 PM Kaleb Punga  W21 DD Student File P3 Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF A SU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body 15 AA    ab Wrap Text Share Comments General Insert 33X Delete Ft Format Ov 5 Paste I U Ideas A         3 Merge   Center v $   ) Sensitivity Conditional Format Cell Formatting as Table Styles X Sort   Filter Find   Select C D E G H 1 1 K L M N P R s T T U V w Y J14 4 x 77 73 Solvency Ratios 79 80 Ratio of Liabilities to Stockholder's Equity 12 0 80 4 65 3,239,757 696,277 4 65 3,628,528 779,830 7 31 20 250,000 50,000 45 7 31 19 300,000 50,000 55 7 31 18 200,000 50,000 72 82 83 84 85 Common Dividends 86 Weighted Average Common Sha 87 Market Price per Share 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 102 108 109 110 111 112 113 114 115 116 117 118 119 120 121 1122 123 124 125 126 127 128 129 Instructions Adjusted Trial Balances IS Comparative BS Comparative Ratios Historical   Ready   80  FEB 5 W 24

Accepted Answer

The Answer is in the image, click to view ...

Question

Not sure how to upload files on here. I don't think it's possible so here are screenshots of the pictures to have a clearer visual

Step by Step Solution

Step: 1

Get Instant Access with AI-Powered Solutions

Step: 2

Step: 3

Ace Your Homework with AI