6C-4 with Excel This file is used with Problem 6C-4, and contains the three worksheet tabs listed below. Simply click on the tab you wish to view. 1. "Working Trial Balance" contains the KCN Working Trial Balance for the year ended December 31, 20X5 (unadjusted) and 20X4 (adjusted). 2. "Financial Statements" contains comparative KCN Financial Statements which are the results of the adjusted working trial balance amounts for 20x5 and 20X4. 3. "Ratios" contains the Financial Statement Ratios calculated from KCN Financial Statements, both for 205 and for 20X4. Required: a. Recalculate the 12/31/X5 and 12/31/X4 ratios using Excel on the Ratios schedule. b. Identify financial statement accounts that should be investigated because the related ratios are not what one would expect as compared to the prior-year ratios, industry averages, or your knowledge of th company c. For each account identified in part b. list potential reasons for the unexpected account balances and related ratios. KEYSTONE COMPUTERS & NETWORKS, INC. Working Trial Balance For the Year Ended December 31, 20X5 Ref. No. Prep'd by Date WTB-1 WL 1/10/X6 Cr. Acct. No Account Name 1000.10 Cash-First National Bank 1000.20 Cash-Special Account 1000.30 Cash in register 1000.40 Petty Cash 1050.10 Accounts receivable-trade 1050.40 Accounts receivable-officers 1050.90 Allowance for bad debts 1100.10 Inventories 1300.10 Prepaid expenses 2050.10 Furniture & fixtures 2 2050,30 Equipment 3 2050.80 Leasehold improvements 4 2050.90 Accumulated depreciation 5 2100.00 Software development cost -6 2200.00 Intangible assets 27 3050,10 Accounts payable-trade 28 3100.00 Capital lease oblig-current 29 3200.10 Accrued liabilities 30 3300.30 Unearned service revenue 31 13400.50 Line of credit 32 4400.10 Capital lease oblig-noncur PAPALA Introduction and Requirement 20X4 20X5 Adjustments Adjusted Unadjusted Ref# Dr. 42,754 66,034 9,960 10.150 1,200 1,200 50 50 8,534,524 10,235,457 57,643 84,670 -96,000 -104,000 1,234,589 1,375,835 156.900 176,456 300,980 344,900 789,654 974,676 98.900 91,230 -250,987 -404,560 178,000 1,000,000 800,000 -1,349,839 -1.429,033 -43,200 -45,675 -178,900 203.450 -42.300 -6,612.550 -8.632,105 -456,700 -423,680 th to tinh Working That Balance Financial Statements 20X5 Adjusted 66,034 10,150 1,200 50 10.235,457 84,670 -104,000 1,375,835 176,456 344.900 974,676 91,230 404,560 178,000 800,000 -1,429,033 -45,675 -203,450 -42,300 -8,632,105 -423,680 55 AAAAA Ratios E F G I 6000.10 Sales of computers 6010.10 Software licenses 6020.10 Service revenue 6030.10 Consulting revenue 7020.10 Cost of sales-prod. & serv. 7070.10 Salaries-sales 7070.50 Payroll benefits-sales 7075.10 Advertising & promotion 7080.10 Travel & entertainment 7080.30 Miscellaneous exp. -sales 7090.10 Operating salaries 7090.30 Administrative salaries 7090.50 Payroll benefits-admin. 7100.10 Rent 7140.10 Utilities 7200.10 Insurance 7260.30 Legal and accounting 7320.10 Bad debt expense 7410.10 Supplies 7600.10% Depreciation and amort. 3 7650.10 Software development 7700.10 Miscellaneous exp-admin 7800.10 Interest expense 2 7900.10 Current income taxes 3 7900.70 Deferred income taxes 4 9000.00 P&L Summary 5 Total Balance Sheet 56 Total Income Statement 57 Out-of-balance Introduction and Requirements -44,890,788 -248,900 -4,567,888 -46,751,990 74,122,435 3,167,889 913,456 1,200,786 609,788 334,890 4,878.900 4,234,234 1,812,344 797,800 210,495 356,890 457.577 234,500 556,345 334,565 289,100 234.556 421,344 256,765 45,632 989.275 989,275 -989,275 -42,345,675 -236,700 -4,325,777 -45,677,899 72,134,566 2,765,677 857,368 1,567,889 445,600 278,656 4,544,860 3,945.670 1.734,565 721,345 234,839 378,677 485,767 256,678 478,900 367.867 345,645 245,456 476,899 80,100 9,150 -42,345,675 -236.700 -4,325,777 45,677,899 72,134,566 2,765,677 857,368 1,567,889 445,600 278,656 4,544,860 3,945,670 1.734,565 721,345 234,839 378,677 485.767 256,678 478,900 367,867 345,645 245,456 476,899 80.100 9,150 0 -229,877 229,877 0 + 5 229 877 229,877 Working Trial Balance Financial Statements Ratios KEYSTONE COMPUTERS & NETWORKS, INC. Balance Sheet December 31, Increase (Decrease) Assets 20XS 20X4 S Current assets Cash Trade receivables Allowance for doubtful accounts Receivables officers Inventory Prepaid expenses Total current assets 67 234 10,235,457 -104,000 84,670 1,375,835 176.456 11,835,652 0.5% 74.1% -0.8% 0.6% 10.0% 1.3% 85.6% 53,964 8,534,524 -96,000 57,643 1,234,589 156.900 9.941,620 0.5% 71.8% -0.8% 0.5% 10.4% 1.3% 83.7% 24.6% 19.9% 8.3% 46.9% 11.4% 12.5% 19.1% 3 s Equipment and leasehold improvements Equipment and furniture Leasehold improvements Less: accumulated depreciation 1,319,576 91.210 1,410,806 404.560 1,006,246 978,000 9.5% 0.7% 10.2% 2.9% 7.3% 7.1% 1,090,634 98.900 1,189,534 250.987 938,547 1,000,000 9.29 0.8% 10.0% 2.1% 7.9% 8.4% 21.0% -7.8% 18.6% 61.2% 7.29 2.29% Intangible assets (net of amort.) S 13.819,898 100.0% 11,880, 167 100.0% 16.3% Liabilities and Stockholders' Equity 30.59 5 3 7 8 9 0 1 2 3 4 5 -6 7 28 19 30 3 32 3 14 35 36 37 28 39 40 4 42 42 44 45 46 47 8,632,105 1,429,033 62.5% 10.3% 6,612.550 1.149.819 55.7% 11.494 5.9% Current Liabilities Line of credit Accounts payable Current maturities of capital lease obligations Uncarned service revenue Accrued expenses Total current liabilities 43.200 5.79 45.675 42,300 203.450 10.352,563 0.3% 0.3% 1.59 74.9% 0.494 0.09 1998 68.9% 178.900 8.184.489 5 13.79 26.598 Capital Lease Obligations Less current maturitics 469.255 45.675 3.4% 0.79 499.900 43,200 4.294 0.496 6.196 5.7% Total Liabilities 5 10.776,243 78.0% 8,641,189 72.7% 24.7% Stockholders' equity Common stock Additional paid in capital Retained carings 200.000 421.500 2.410.135 1.051.RS 13.10.09% 1.494 3.1% 17.69 22.193 10019 200,000 421 500 2.615.478 218.978 11.880.167 1.79 1.8 22.09 27-19 100.094 0.09 0.03 7.19 5.7% 16.49 49 50 KEYSTONE COMPUTERS & NETWORKS, INC. Statement of Income and Retained Earnings For the Years Ended December 31, Increase (Decrease) 20X5 20X4 S Net sales Cost of goods sold Gross profit 92,586,051 72.134,566 20,451,485 100.0% 77.9% 22.1% 96,459,566 74.122.435 22,337,131 100.0% 76.8% 22.2% 4,0% 2.7% 8.4% s 2.765,677 857,368 1,567.889 445,600 278.656 5,915,190 3.0% 0.9% 1.7% 0.5% 0.396 6.4% 3,167.889 913.456 1,200,786 609,788 334.890 6,226,809 3.3% 0.9% 1.2% 0.6% 0.3% 6.5% -12.7% -6.1% 30.6% 26.9% -16.8% 5.0% Selling expenses: Salaries Payroll benefits and taxes Advertising and promotion Travel and entertainment Miscellaneous S Operating and administrative expenses Operating salaries Administrative salaries Payroll benefits and taxes Rent Utilities Insurance Legal and accounting Bad debts Supplies Depreciation & Amortization Software development Miscellaneous 5 Total selling, operating and administrative expenses s 4,544,860 3.945,870 1,734,565 721.145 234,819 378,677 485,767 256,678 478,900 367,867 345,645 245.456 13,740.269 4.9% 4.3% 1.9% 0.8% 0.3% 0.4% 0.5% 0.39 0.5% 0.4% 0.4% 0.3% 14.8% 4,878,900 4,234 234 1,812,344 797,800 210,495 356,890 457.577 234,500 556,345 334,565 289,100 274 556 14.197.306 5.1% 4.496 1.9% 0.8% 0.2% 0.4% 0.5% 0.29 0.69 0.39% 0.79 0.296 14.99 -6.89 -6.8% 4.3% -9.6% 11.6% 6.1% 6.29 9.59 13.9% 10.0% 19.8% 4.69 4.693 19,655,459 21.298 20.624.115 21.494 4.79 $ Operating income Interest expense 796,026 476,899 0.9% 0.5% 1,711,016 421 344 1.89 0.496 53.596 12.29 Income before income taxes 5 319,127 0.1% 1.291.672 1394 -7539 Income taxes: Current Deferred 80,100 9,150 0.1% 0.0% 256 765 45.632 0.394 0.054 683 79.99 Net income 5 229.877 0.29 989,275 1.00 176.89 1,626.203 $0.99 Retained earnings, January Less: Dividends Retaned earnings. December 31 2.615.478 415.000 2.410.155 2.615878 7.1964 KEYSTONE COMPUTERS & NETWORKS, INC. Analytical Procedures Ratios For the Years Ended December 31 Ref. No. Prepared by Date Plan 1 204 1/10/X6 20X5 20X4 Industry 1.1 2 1.3 37.0 Current ratio Days sales in average accounts receivable Allowance for doubtful accounts / accounts receivable Bad debt expensesot sales Total liabilities to net worth Retum on total assets Retum on net worth Rotum on net salos Gross profilinet sales Selling, operating and admin expense / not sales Times interest earned 2.9 9.0% 29.0% 2.3% 24.0% 23.9% 5.5 6C-4 with Excel This file is used with Problem 6C-4, and contains the three worksheet tabs listed below. Simply click on the tab you wish to view. 1. "Working Trial Balance" contains the KCN Working Trial Balance for the year ended December 31, 20X5 (unadjusted) and 20X4 (adjusted). 2. "Financial Statements" contains comparative KCN Financial Statements which are the results of the adjusted working trial balance amounts for 20x5 and 20X4. 3. "Ratios" contains the Financial Statement Ratios calculated from KCN Financial Statements, both for 205 and for 20X4. Required: a. Recalculate the 12/31/X5 and 12/31/X4 ratios using Excel on the Ratios schedule. b. Identify financial statement accounts that should be investigated because the related ratios are not what one would expect as compared to the prior-year ratios, industry averages, or your knowledge of th company c. For each account identified in part b. list potential reasons for the unexpected account balances and related ratios. KEYSTONE COMPUTERS & NETWORKS, INC. Working Trial Balance For the Year Ended December 31, 20X5 Ref. No. Prep'd by Date WTB-1 WL 1/10/X6 Cr. Acct. No Account Name 1000.10 Cash-First National Bank 1000.20 Cash-Special Account 1000.30 Cash in register 1000.40 Petty Cash 1050.10 Accounts receivable-trade 1050.40 Accounts receivable-officers 1050.90 Allowance for bad debts 1100.10 Inventories 1300.10 Prepaid expenses 2050.10 Furniture & fixtures 2 2050,30 Equipment 3 2050.80 Leasehold improvements 4 2050.90 Accumulated depreciation 5 2100.00 Software development cost -6 2200.00 Intangible assets 27 3050,10 Accounts payable-trade 28 3100.00 Capital lease oblig-current 29 3200.10 Accrued liabilities 30 3300.30 Unearned service revenue 31 13400.50 Line of credit 32 4400.10 Capital lease oblig-noncur PAPALA Introduction and Requirement 20X4 20X5 Adjustments Adjusted Unadjusted Ref# Dr. 42,754 66,034 9,960 10.150 1,200 1,200 50 50 8,534,524 10,235,457 57,643 84,670 -96,000 -104,000 1,234,589 1,375,835 156.900 176,456 300,980 344,900 789,654 974,676 98.900 91,230 -250,987 -404,560 178,000 1,000,000 800,000 -1,349,839 -1.429,033 -43,200 -45,675 -178,900 203.450 -42.300 -6,612.550 -8.632,105 -456,700 -423,680 th to tinh Working That Balance Financial Statements 20X5 Adjusted 66,034 10,150 1,200 50 10.235,457 84,670 -104,000 1,375,835 176,456 344.900 974,676 91,230 404,560 178,000 800,000 -1,429,033 -45,675 -203,450 -42,300 -8,632,105 -423,680 55 AAAAA Ratios E F G I 6000.10 Sales of computers 6010.10 Software licenses 6020.10 Service revenue 6030.10 Consulting revenue 7020.10 Cost of sales-prod. & serv. 7070.10 Salaries-sales 7070.50 Payroll benefits-sales 7075.10 Advertising & promotion 7080.10 Travel & entertainment 7080.30 Miscellaneous exp. -sales 7090.10 Operating salaries 7090.30 Administrative salaries 7090.50 Payroll benefits-admin. 7100.10 Rent 7140.10 Utilities 7200.10 Insurance 7260.30 Legal and accounting 7320.10 Bad debt expense 7410.10 Supplies 7600.10% Depreciation and amort. 3 7650.10 Software development 7700.10 Miscellaneous exp-admin 7800.10 Interest expense 2 7900.10 Current income taxes 3 7900.70 Deferred income taxes 4 9000.00 P&L Summary 5 Total Balance Sheet 56 Total Income Statement 57 Out-of-balance Introduction and Requirements -44,890,788 -248,900 -4,567,888 -46,751,990 74,122,435 3,167,889 913,456 1,200,786 609,788 334,890 4,878.900 4,234,234 1,812,344 797,800 210,495 356,890 457.577 234,500 556,345 334,565 289,100 234.556 421,344 256,765 45,632 989.275 989,275 -989,275 -42,345,675 -236,700 -4,325,777 -45,677,899 72,134,566 2,765,677 857,368 1,567,889 445,600 278,656 4,544,860 3,945.670 1.734,565 721,345 234,839 378,677 485,767 256,678 478,900 367.867 345,645 245,456 476,899 80,100 9,150 -42,345,675 -236.700 -4,325,777 45,677,899 72,134,566 2,765,677 857,368 1,567,889 445,600 278,656 4,544,860 3,945,670 1.734,565 721,345 234,839 378,677 485.767 256,678 478,900 367,867 345,645 245,456 476,899 80.100 9,150 0 -229,877 229,877 0 + 5 229 877 229,877 Working Trial Balance Financial Statements Ratios KEYSTONE COMPUTERS & NETWORKS, INC. Balance Sheet December 31, Increase (Decrease) Assets 20XS 20X4 S Current assets Cash Trade receivables Allowance for doubtful accounts Receivables officers Inventory Prepaid expenses Total current assets 67 234 10,235,457 -104,000 84,670 1,375,835 176.456 11,835,652 0.5% 74.1% -0.8% 0.6% 10.0% 1.3% 85.6% 53,964 8,534,524 -96,000 57,643 1,234,589 156.900 9.941,620 0.5% 71.8% -0.8% 0.5% 10.4% 1.3% 83.7% 24.6% 19.9% 8.3% 46.9% 11.4% 12.5% 19.1% 3 s Equipment and leasehold improvements Equipment and furniture Leasehold improvements Less: accumulated depreciation 1,319,576 91.210 1,410,806 404.560 1,006,246 978,000 9.5% 0.7% 10.2% 2.9% 7.3% 7.1% 1,090,634 98.900 1,189,534 250.987 938,547 1,000,000 9.29 0.8% 10.0% 2.1% 7.9% 8.4% 21.0% -7.8% 18.6% 61.2% 7.29 2.29% Intangible assets (net of amort.) S 13.819,898 100.0% 11,880, 167 100.0% 16.3% Liabilities and Stockholders' Equity 30.59 5 3 7 8 9 0 1 2 3 4 5 -6 7 28 19 30 3 32 3 14 35 36 37 28 39 40 4 42 42 44 45 46 47 8,632,105 1,429,033 62.5% 10.3% 6,612.550 1.149.819 55.7% 11.494 5.9% Current Liabilities Line of credit Accounts payable Current maturities of capital lease obligations Uncarned service revenue Accrued expenses Total current liabilities 43.200 5.79 45.675 42,300 203.450 10.352,563 0.3% 0.3% 1.59 74.9% 0.494 0.09 1998 68.9% 178.900 8.184.489 5 13.79 26.598 Capital Lease Obligations Less current maturitics 469.255 45.675 3.4% 0.79 499.900 43,200 4.294 0.496 6.196 5.7% Total Liabilities 5 10.776,243 78.0% 8,641,189 72.7% 24.7% Stockholders' equity Common stock Additional paid in capital Retained carings 200.000 421.500 2.410.135 1.051.RS 13.10.09% 1.494 3.1% 17.69 22.193 10019 200,000 421 500 2.615.478 218.978 11.880.167 1.79 1.8 22.09 27-19 100.094 0.09 0.03 7.19 5.7% 16.49 49 50 KEYSTONE COMPUTERS & NETWORKS, INC. Statement of Income and Retained Earnings For the Years Ended December 31, Increase (Decrease) 20X5 20X4 S Net sales Cost of goods sold Gross profit 92,586,051 72.134,566 20,451,485 100.0% 77.9% 22.1% 96,459,566 74.122.435 22,337,131 100.0% 76.8% 22.2% 4,0% 2.7% 8.4% s 2.765,677 857,368 1,567.889 445,600 278.656 5,915,190 3.0% 0.9% 1.7% 0.5% 0.396 6.4% 3,167.889 913.456 1,200,786 609,788 334.890 6,226,809 3.3% 0.9% 1.2% 0.6% 0.3% 6.5% -12.7% -6.1% 30.6% 26.9% -16.8% 5.0% Selling expenses: Salaries Payroll benefits and taxes Advertising and promotion Travel and entertainment Miscellaneous S Operating and administrative expenses Operating salaries Administrative salaries Payroll benefits and taxes Rent Utilities Insurance Legal and accounting Bad debts Supplies Depreciation & Amortization Software development Miscellaneous 5 Total selling, operating and administrative expenses s 4,544,860 3.945,870 1,734,565 721.145 234,819 378,677 485,767 256,678 478,900 367,867 345,645 245.456 13,740.269 4.9% 4.3% 1.9% 0.8% 0.3% 0.4% 0.5% 0.39 0.5% 0.4% 0.4% 0.3% 14.8% 4,878,900 4,234 234 1,812,344 797,800 210,495 356,890 457.577 234,500 556,345 334,565 289,100 274 556 14.197.306 5.1% 4.496 1.9% 0.8% 0.2% 0.4% 0.5% 0.29 0.69 0.39% 0.79 0.296 14.99 -6.89 -6.8% 4.3% -9.6% 11.6% 6.1% 6.29 9.59 13.9% 10.0% 19.8% 4.69 4.693 19,655,459 21.298 20.624.115 21.494 4.79 $ Operating income Interest expense 796,026 476,899 0.9% 0.5% 1,711,016 421 344 1.89 0.496 53.596 12.29 Income before income taxes 5 319,127 0.1% 1.291.672 1394 -7539 Income taxes: Current Deferred 80,100 9,150 0.1% 0.0% 256 765 45.632 0.394 0.054 683 79.99 Net income 5 229.877 0.29 989,275 1.00 176.89 1,626.203 $0.99 Retained earnings, January Less: Dividends Retaned earnings. December 31 2.615.478 415.000 2.410.155 2.615878 7.1964 KEYSTONE COMPUTERS & NETWORKS, INC. Analytical Procedures Ratios For the Years Ended December 31 Ref. No. Prepared by Date Plan 1 204 1/10/X6 20X5 20X4 Industry 1.1 2 1.3 37.0 Current ratio Days sales in average accounts receivable Allowance for doubtful accounts / accounts receivable Bad debt expensesot sales Total liabilities to net worth Retum on total assets Retum on net worth Rotum on net salos Gross profilinet sales Selling, operating and admin expense / not sales Times interest earned 2.9 9.0% 29.0% 2.3% 24.0% 23.9% 5.5