Complete the working paper by computing the financial ratios for 20X5 using Excel. you will need to use at least two tabs on an Excel spreadsheet. Set up the account balances for each year on the first tab and name the tab "account balances" (include all the accounts from the financial statements that you will need to calculate your ratios). Perform the calculations on the second tab named "workpaper" by linking to the accounts from the account balances tab. Your results on the workpaper tab should look similar to the presentation below. You are just adding the calculations for 12/31/X5. The results for 12/31/X5 should be formatted to look similar to what has already been calculated for 12/31/X4 (see below). For example, the current ratio and times interest earned are expressed as numerical results carried out to one decimal place, while return on total assets and return on net worth are expressed as percentages carried out to one decimal place.

WL KEYSTONE COMPUTERS & NETWORKS, INC. Analytical Review Ratios For the Period Ended December 31, 20x5 Prepared by Reviewed by Ending Ending 12/31/X5 12/31/X4 Industry 1.2 1.3 33.2 37.0 Ratio Current ratio Days' sales in accounts receivable, computed with average accounts receivable Allowance for doubtful accounts/accounts receivable Bad debt expenseet sales Total liabilitieset worth Return on total assets Return on net worth Return on net sales Gross profitet sales Selling, operating, and administrative expenseet sales Times interest earned 1.1% 0.2% 2.7 8.3% 30.5% 1.0% 23.2% 21.4% 4.1 2.9 9.0% 29.0% 2.3% 24.0% 23.9% 5.5 Appendix 6C Illustrative Audit Case: Keystone Computers & Networks, Inc. Part I: Audit Planning The Keystone Computers & Networks, Inc. (KCN), case is used throughout the text to illustrate audit procedures and methodology. KCN is a company that sells and installs computer workstations and networking software to business customers. The CPA firm of Adams, Barnes & Co. has audited the financial statements of KCN for the past three years. This part of the case illustrates selective audit planning working papers prepared by the staff of Adams, Barnes & Co. for this year's audit. You should read through the information to obtain an understanding of the nature of the information that is important to planning an audit engagement. The working papers include The balance sheet and income statement for the company for the prior year, 20X4. A trial balance for 12/31/X5, with comparative amounts for 12/31/X4. The analytical ratios working paper, partially completed. The overall audit strategy for the audit of the financial statements for the year ended 12/31/X5. A fraud risk assessment. The engagement letter for the audit, presented in Figure 6.2 of this chapter. KEYSTONE COMPUTERS & NETWORKS, INC. Balance Sheet December 31, 20X4 Assets Current Assets Cash Trade receivables, less allowance for doubtful accounts of $96,000 Accounts receivable-officers Inventory Prepaid expenses Total current assets Equipment and leasehold improvements, at cost Equipment and furniture Leasehold improvements $ 53,964 8,438,524 57,643 1,234,589 156,900 $9,941,620 Less accumulated depreciation $1,090,634 98,900 $ 1,189,534 (250,987) $ 938,547 $1,000,000 $11,880,167 Intangible assets net of amortization $6,612,550 1,349,839 43,200 178,900 $ 8,184,489 Liabilities and Stockholders' Equity Current Liabilities Line of credit Accounts payable Current maturities of capital lease obligations Accrued expenses Total current liabilities Capital lease obligations, less current maturities Total liabilities Stockholders' equity Common stock, $1 par value: 1,000,000 shares authorized: 200,000 shares issued and outstanding Additional paid-in capital Retained earnings $ 456,700 $ 8,641,189 $ 200,000 423,500 2,615,478 $11,880,167 $96,459,566 74,122,435 $ 22,337,131 KEYSTONE COMPUTERS & NETWORKS, INC. Statements of Income and Retained Earnings Year Ended December 31, 20X4 Net sales Cost of goods sold Gross profit Selling expenses: Salaries $ 3,167,889 Payroll benefits and taxes 913,456 Advertising and promotion 1,200,786 Travel and entertainment 609,788 Miscellaneous 334,890 $ 6,226,809 Operating and administration expenses: Operating salaries $4,878,900 Administrative salaries 4,234,234 Payroll benefits and taxes 1,812,344 Rent 797,800 Utilities 210,495 Insurance 356,890 Legal and accounting 457,577 Bad debt 234,500 Supplies 556,345 Depreciation and amortization 334,565 Software development 289,100 Miscellaneous 234,556 $ 14,397,306 Total selling, operating, and administrative expenses Operating income Interest expense Income before income taxes Income taxes: Current $ 256,765 Deferred 45,632 $ 20,624,115 $ 1.713,016 421,344 $ 1,291,672 Net income Retained earnings, January 1, 20X4 Retained earnings, December 31, 20X4 302,397 $ 989,275 $ 1,626,203 $ 2,615,478 KEYSTONE COMPUTERS & NETWORKS, INC. Working Trial Balance For the Period Ended December 31, 20X5 Prepared by WL Reviewed by Page 1 Adjusted Balance Dr (Cr) Account # Adjustments Dr (Cr) Account Name Ref # 1000.10 1000.20 1000.30 1000.40 1050.10 1050.40 1050.90 1100.10 1300.10 2050.10 2050.30 2050.80 2050.90 2100.00 2200.00 3050.10 3100.00 3200.10 3300.30 3400.50 4400.10 5050.10 5100.10 5700.10 5900.00 Cash-General Account Cash-Special Account Cash in Register Petty Cash Accounts receivable-trade Accounts receivable-officers Allowance for bad debts Inventories Prepaid expenses Furniture & fixtures Office equipment Leasehold improvements Accumulated depreciation Software development cost Intangible assets Accounts payable-trade Capital lease obligations-current Accrued liabilities Unearned service revenue Line of credit Capital lease obligations-noncurrent Capital stock Paid-in capital Retained earnings Dividends Prior Period Balance 12/31/X4 42,754 9,960 1,200 50 8,534,524 57,643 (96,000) 1,234,589 156,900 300,980 789,654 98,900 (2,50,987) Unadjusted Trial Balance Dr (Cr) 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) (200,000) (423,500) (2,615,478) 415,000 229,877 (229,877) 0 1,000,000 (1,349,839) (43,200) (178,900) (6.612,550) (456,700) (200,000) (423,500) (1,626,203) 989,275 (989,275) 0 KEYSTONE COMPUTERS & NETWORKS, INC. Working Trial Balance For the Period Ended December 31, 20x5 Prepared by WL Reviewed by Page 2 Adjusted Balance Dr (Cr) Adjustments Dr (Cr) Account # Ref # 6000.10 6010.10 6020.10 6030.10 7020.10 7070.10 7070.50 7075.10 7080.10 7080.30 7090.10 7090.30 7090.50 7100.10 7140.10 7200.10 7260.30 7320.10 7410.10 7600.10 7650.10 7700.10 7800.10 7900.10 7900.70 9000.00 Account Name Sales of computers Software licenses Service revenue Consulting revenue Cost of sales Salaries-sales Payroll benefits-sales Advertising & promotion Travel & entertainment Miscellaneous exp.-sales Salaries-operations Salaries-administrative Payroll benefits-admin Rent Utilities Insurance Legal & accounting Bad debt expense Supplies Depreciation and amort. Software development Miscellaneous exp.-administrative Interest expense Current income taxes Deferred income taxes P&L Summary Prior Period Balance 12/31/X4 (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 0 Unadjusted Trial Balance Dr (Cr) (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 0 (229,877) 229,877 0 WL KEYSTONE COMPUTERS & NETWORKS, INC. Analytical Review Ratios For the Period Ended December 31, 20X5 Prepared by Reviewed by Ending 12/31/35 Ending 12/31/X4 Industry Ratio 1.1 37.0 12 33.2 1.3 37.0 Current ratio Days' sales in accounts receivable, computed with average accounts receivable Allowance for doubtful accounts/accounts receivable Bad debt expenseet sales Total liabilitieset worth Return on total assets Return on net worth Return on net sales Gross profitet sales Selling, operating, and administrative expenseet sales Times interest earned 1.0% 0.3% 3.5 1.7% 7.5% 0.2% 22.19 21.2% 1.7 1.1% 0.2% 2.7 8.3% 30.5% 1.0% 23.2% 21.4% 4.1 2.9 9.0% 29.0% 2.3% 24.0% 23.9% 5.5 KEYSTONE COMPUTERS & NETWORKS, INC. Audit Strategy December 31, 20X5 Date Prepared by Reviewed by: Reviewed by: Warren Love (Senior) Karen West (Manager) Charles Adams (Partner) August 14, 20X5 August 28, 20X5 September 5, 20X5 OVERALL AUDIT STRATEGY OBJECTIVES OF THE ENGAGEMENT Audit of the financial statements of Keystone Computers & Networks, Inc. (KCN), for the year ended December 31, 20X5. Also, the company's debt agreement with Western Financial Services requires the company to furnish the lender a report by our firm on KCN's compliance with various restrictive debt covenants. BUSINESS AND INDUSTRY CONDITIONS KCN sells and installs computers and networking hardware and software to business customers and provides other information technology consulting services. KCN also has begun developing its own computer networking software to be sold as a product to its customers. The company's primary competitive strategy is to maintain a high level of technical expertise and a broad range of services. KCN's long-term success is contingent on its ability to attract and retain qualified information technology personnel. The market for such individuals is very competitive. However, the company has a competitive advantage because of its desirable geographic location (Phoenix), which has a large number of colleges with technology programs. The market for computers and related products is extremely competitive. KCN com- petes with large retailers of computers, such as Dell, Hewlett Packard, and Apple. The company also competes with other value-added resellers who provide computers and soft- ware products and consulting services directly to customers. To effectively compete, the WL KEYSTONE COMPUTERS & NETWORKS, INC. Analytical Review Ratios For the Period Ended December 31, 20x5 Prepared by Reviewed by Ending Ending 12/31/X5 12/31/X4 Industry 1.2 1.3 33.2 37.0 Ratio Current ratio Days' sales in accounts receivable, computed with average accounts receivable Allowance for doubtful accounts/accounts receivable Bad debt expenseet sales Total liabilitieset worth Return on total assets Return on net worth Return on net sales Gross profitet sales Selling, operating, and administrative expenseet sales Times interest earned 1.1% 0.2% 2.7 8.3% 30.5% 1.0% 23.2% 21.4% 4.1 2.9 9.0% 29.0% 2.3% 24.0% 23.9% 5.5 Appendix 6C Illustrative Audit Case: Keystone Computers & Networks, Inc. Part I: Audit Planning The Keystone Computers & Networks, Inc. (KCN), case is used throughout the text to illustrate audit procedures and methodology. KCN is a company that sells and installs computer workstations and networking software to business customers. The CPA firm of Adams, Barnes & Co. has audited the financial statements of KCN for the past three years. This part of the case illustrates selective audit planning working papers prepared by the staff of Adams, Barnes & Co. for this year's audit. You should read through the information to obtain an understanding of the nature of the information that is important to planning an audit engagement. The working papers include The balance sheet and income statement for the company for the prior year, 20X4. A trial balance for 12/31/X5, with comparative amounts for 12/31/X4. The analytical ratios working paper, partially completed. The overall audit strategy for the audit of the financial statements for the year ended 12/31/X5. A fraud risk assessment. The engagement letter for the audit, presented in Figure 6.2 of this chapter. KEYSTONE COMPUTERS & NETWORKS, INC. Balance Sheet December 31, 20X4 Assets Current Assets Cash Trade receivables, less allowance for doubtful accounts of $96,000 Accounts receivable-officers Inventory Prepaid expenses Total current assets Equipment and leasehold improvements, at cost Equipment and furniture Leasehold improvements $ 53,964 8,438,524 57,643 1,234,589 156,900 $9,941,620 Less accumulated depreciation $1,090,634 98,900 $ 1,189,534 (250,987) $ 938,547 $1,000,000 $11,880,167 Intangible assets net of amortization $6,612,550 1,349,839 43,200 178,900 $ 8,184,489 Liabilities and Stockholders' Equity Current Liabilities Line of credit Accounts payable Current maturities of capital lease obligations Accrued expenses Total current liabilities Capital lease obligations, less current maturities Total liabilities Stockholders' equity Common stock, $1 par value: 1,000,000 shares authorized: 200,000 shares issued and outstanding Additional paid-in capital Retained earnings $ 456,700 $ 8,641,189 $ 200,000 423,500 2,615,478 $11,880,167 $96,459,566 74,122,435 $ 22,337,131 KEYSTONE COMPUTERS & NETWORKS, INC. Statements of Income and Retained Earnings Year Ended December 31, 20X4 Net sales Cost of goods sold Gross profit Selling expenses: Salaries $ 3,167,889 Payroll benefits and taxes 913,456 Advertising and promotion 1,200,786 Travel and entertainment 609,788 Miscellaneous 334,890 $ 6,226,809 Operating and administration expenses: Operating salaries $4,878,900 Administrative salaries 4,234,234 Payroll benefits and taxes 1,812,344 Rent 797,800 Utilities 210,495 Insurance 356,890 Legal and accounting 457,577 Bad debt 234,500 Supplies 556,345 Depreciation and amortization 334,565 Software development 289,100 Miscellaneous 234,556 $ 14,397,306 Total selling, operating, and administrative expenses Operating income Interest expense Income before income taxes Income taxes: Current $ 256,765 Deferred 45,632 $ 20,624,115 $ 1.713,016 421,344 $ 1,291,672 Net income Retained earnings, January 1, 20X4 Retained earnings, December 31, 20X4 302,397 $ 989,275 $ 1,626,203 $ 2,615,478 KEYSTONE COMPUTERS & NETWORKS, INC. Working Trial Balance For the Period Ended December 31, 20X5 Prepared by WL Reviewed by Page 1 Adjusted Balance Dr (Cr) Account # Adjustments Dr (Cr) Account Name Ref # 1000.10 1000.20 1000.30 1000.40 1050.10 1050.40 1050.90 1100.10 1300.10 2050.10 2050.30 2050.80 2050.90 2100.00 2200.00 3050.10 3100.00 3200.10 3300.30 3400.50 4400.10 5050.10 5100.10 5700.10 5900.00 Cash-General Account Cash-Special Account Cash in Register Petty Cash Accounts receivable-trade Accounts receivable-officers Allowance for bad debts Inventories Prepaid expenses Furniture & fixtures Office equipment Leasehold improvements Accumulated depreciation Software development cost Intangible assets Accounts payable-trade Capital lease obligations-current Accrued liabilities Unearned service revenue Line of credit Capital lease obligations-noncurrent Capital stock Paid-in capital Retained earnings Dividends Prior Period Balance 12/31/X4 42,754 9,960 1,200 50 8,534,524 57,643 (96,000) 1,234,589 156,900 300,980 789,654 98,900 (2,50,987) Unadjusted Trial Balance Dr (Cr) 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) (200,000) (423,500) (2,615,478) 415,000 229,877 (229,877) 0 1,000,000 (1,349,839) (43,200) (178,900) (6.612,550) (456,700) (200,000) (423,500) (1,626,203) 989,275 (989,275) 0 KEYSTONE COMPUTERS & NETWORKS, INC. Working Trial Balance For the Period Ended December 31, 20x5 Prepared by WL Reviewed by Page 2 Adjusted Balance Dr (Cr) Adjustments Dr (Cr) Account # Ref # 6000.10 6010.10 6020.10 6030.10 7020.10 7070.10 7070.50 7075.10 7080.10 7080.30 7090.10 7090.30 7090.50 7100.10 7140.10 7200.10 7260.30 7320.10 7410.10 7600.10 7650.10 7700.10 7800.10 7900.10 7900.70 9000.00 Account Name Sales of computers Software licenses Service revenue Consulting revenue Cost of sales Salaries-sales Payroll benefits-sales Advertising & promotion Travel & entertainment Miscellaneous exp.-sales Salaries-operations Salaries-administrative Payroll benefits-admin Rent Utilities Insurance Legal & accounting Bad debt expense Supplies Depreciation and amort. Software development Miscellaneous exp.-administrative Interest expense Current income taxes Deferred income taxes P&L Summary Prior Period Balance 12/31/X4 (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 0 Unadjusted Trial Balance Dr (Cr) (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 0 (229,877) 229,877 0 WL KEYSTONE COMPUTERS & NETWORKS, INC. Analytical Review Ratios For the Period Ended December 31, 20X5 Prepared by Reviewed by Ending 12/31/35 Ending 12/31/X4 Industry Ratio 1.1 37.0 12 33.2 1.3 37.0 Current ratio Days' sales in accounts receivable, computed with average accounts receivable Allowance for doubtful accounts/accounts receivable Bad debt expenseet sales Total liabilitieset worth Return on total assets Return on net worth Return on net sales Gross profitet sales Selling, operating, and administrative expenseet sales Times interest earned 1.0% 0.3% 3.5 1.7% 7.5% 0.2% 22.19 21.2% 1.7 1.1% 0.2% 2.7 8.3% 30.5% 1.0% 23.2% 21.4% 4.1 2.9 9.0% 29.0% 2.3% 24.0% 23.9% 5.5 KEYSTONE COMPUTERS & NETWORKS, INC. Audit Strategy December 31, 20X5 Date Prepared by Reviewed by: Reviewed by: Warren Love (Senior) Karen West (Manager) Charles Adams (Partner) August 14, 20X5 August 28, 20X5 September 5, 20X5 OVERALL AUDIT STRATEGY OBJECTIVES OF THE ENGAGEMENT Audit of the financial statements of Keystone Computers & Networks, Inc. (KCN), for the year ended December 31, 20X5. Also, the company's debt agreement with Western Financial Services requires the company to furnish the lender a report by our firm on KCN's compliance with various restrictive debt covenants. BUSINESS AND INDUSTRY CONDITIONS KCN sells and installs computers and networking hardware and software to business customers and provides other information technology consulting services. KCN also has begun developing its own computer networking software to be sold as a product to its customers. The company's primary competitive strategy is to maintain a high level of technical expertise and a broad range of services. KCN's long-term success is contingent on its ability to attract and retain qualified information technology personnel. The market for such individuals is very competitive. However, the company has a competitive advantage because of its desirable geographic location (Phoenix), which has a large number of colleges with technology programs. The market for computers and related products is extremely competitive. KCN com- petes with large retailers of computers, such as Dell, Hewlett Packard, and Apple. The company also competes with other value-added resellers who provide computers and soft- ware products and consulting services directly to customers. To effectively compete, the