Accounting Audit Case: Keystone Computers & Networks, Inc.

Kevin121847009
02_Problem_6C_4_Excel.xlsx
Home>Business & Finance homework help>Accounting homework help>Accounting Audit Case: Keystone Computers & Networks, Inc.

Introduction and Requirements

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 20X5 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.  

Working Trial Balance

Ref. No. WTB-1
KEYSTONE COMPUTERS & NETWORKS, INC. Prep'd by WL
Working Trial Balance Date 1/10/X6
For the Year Ended December 31, 20X5
20X4 20X5 Adjustments 20X5
Acct. No. Account Name Adjusted Unadjusted Ref # Dr. Cr. Adjusted
1000.10 Cash-First National Bank 42,754 66,034 66,034
1000.20 Cash-Special Account 9,960 10,150 10,150
1000.30 Cash in register 1,200 1,200 1,200
1000.40 Petty Cash 50 50 50
1050.10 Accounts receivable-trade 8,534,524 10,235,457 10,235,457
1050.40 Accounts receivable-officers 57,643 84,670 84,670
1050.90 Allowance for bad debts (96,000) (104,000) (104,000)
1100.10 Inventories 1,234,589 1,375,835 1,375,835
1300.10 Prepaid expenses 156,900 176,456 176,456
2050.10 Furniture & fixtures 300,980 344,900 344,900
2050.30 Equipment 789,654 974,676 974,676
2050.80 Leasehold improvements 98,900 91,230 91,230
2050.90 Accumulated depreciation (250,987) (404,560) (404,560)
2100.00 Software development cost 178,000 178,000
2200.00 Intangible assets 1,000,000 800,000 800,000
3050.10 Accounts payable-trade (1,349,839) (1,429,033) (1,429,033)
3100.00 Capital lease oblig.-current (43,200) (45,675) (45,675)
3200.10 Accrued liabilities (178,900) (203,450) (203,450)
3300.30 Unearned service revenue (42,300) (42,300)
3400.50 Line of credit (6,612,550) (8,632,105) (8,632,105)
4400.10 Capital lease oblig.-noncurr. (456,700) (423,680) (423,680)
5050.10 Capital stock (200,000) (200,000) (200,000)
5100.10 Paid-in capital (423,500) (423,500) (423,500)
5700.10 Retained earnings (1,626,203) (2,615,478) (2,615,478)
5900.00 Dividends 415,000 415,000
Totals 989,275 229,877 229,877
6000.10 Sales of computers (44,890,788) (42,345,675) (42,345,675) (96,459,566)
6010.10 Software licenses (248,900) (236,700) (236,700)
6020.10 Service revenue (4,567,888) (4,325,777) (4,325,777)
6030.10 Consulting revenue (46,751,990) (45,677,899) (45,677,899)
7020.10 Cost of sales-prod. & serv. 74,122,435 72,134,566 72,134,566
7070.10 Salaries-sales 3,167,889 2,765,677 2,765,677
7070.50 Payroll benefits-sales 913,456 857,368 857,368
7075.10 Advertising & promotion 1,200,786 1,567,889 1,567,889
7080.10 Travel & entertainment 609,788 445,600 445,600
7080.30 Miscellaneous exp. -sales 334,890 278,656 278,656
7090.10 Operating salaries 4,878,900 4,544,860 4,544,860
7090.30 Administrative salaries 4,234,234 3,945,670 3,945,670
7090.50 Payroll benefits-admin. 1,812,344 1,734,565 1,734,565
7100.10 Rent 797,800 721,345 721,345
7140.10 Utilities 210,495 234,839 234,839
7200.10 Insurance 356,890 378,677 378,677
7260.30 Legal and accounting 457,577 485,767 485,767
7320.10 Bad debt expense 234,500 256,678 256,678
7410.10 Supplies 556,345 478,900 478,900
7600.10 Depreciation and amort. 334,565 367,867 367,867
7650.10 Software development 289,100 345,645 345,645
7700.10 Miscellaneous exp.-admin. 234,556 245,456 245,456
7800.10 Interest expense 421,344 476,899 476,899
7900.10 Current income taxes 256,765 80,100 80,100
7900.70 Deferred income taxes 45,632 9,150 9,150
9000.00 P & L Summary 989,275 0
Total Balance Sheet 989,275 229,877 (229,877)
Total Income Statement (989,275) (229,877) 229,877
Out-of-balance 0 0 0
Copyright © 2016 McGraw-Hill Education. All rights reserved. No reproduction or distribution without the prior written consent of McGraw-Hill Education.
macros
\p {CALC}{MENUBRANCH printmenu}
Printmenu WTB BALANCE SHEET INCOME STATEMENT RATIOS QUIT
Print Working Trial Balance Print Balance Sheet Print Income Statement Print Analytical Ratios Quit Print Menu
:prcrsWTB~g :prcrsBS~g :prcrsIS~g :prcrsRATIOS~g {QUIT}
{MENUBRANCH Printmenu} {MENUBRANCH Printmenu} {MENUBRANCH Printmenu}

Page &P

Financial Statements

KEYSTONE COMPUTERS & NETWORKS, INC.
Balance Sheet
December 31,
Increase
Assets 20X5 20X4 (Decrease)
Current assets
Cash $ 67,234 0.5% 53,964 0.5% 24.6% 33.18
Trade receivables 10,235,457 74.1% 8,534,524 71.8% 19.9% 11
Allowance for doubtful accounts (104,000) -0.8% (96,000) -0.8% 8.3%
Receivables-officers 84,670 0.6% 57,643 0.5% 46.9% 32.29
Inventory 1,375,835 10.0% 1,234,589 10.4% 11.4%
Prepaid expenses 176,456 1.3% 156,900 1.3% 12.5% 41
Total current assets $ 11,835,652 85.6% 9,941,620 83.7% 19.1% 9
Equipment and leasehold improvements
Equipment and furniture 1,319,576 9.5% 1,090,634 9.2% 21.0%
Leasehold improvements 91,230 0.7% 98,900 0.8% -7.8%
1,410,806 10.2% 1,189,534 10.0% 18.6% 9,384,991
Less: accumulated depreciation (404,560) -2.9% (250,987) -2.1% 61.2%
1,006,246 7.3% 938,547 7.9% 7.2%
Intangible assets (net of amort.) 978,000 7.1% 1,000,000 8.4% -2.2%
$ 13,819,898 100.0% 11,880,167 100.0% 16.3%
Liabilities and Stockholders' Equity
Current Liabilities
Line of credit 8,632,105 62.5% 6,612,550 55.7% 30.5%
Accounts payable 1,429,033 10.3% 1,349,839 11.4% 5.9%
Current maturities of capital
lease obligations 45,675 0.3% 43,200 0.4% 5.7%
Unearned service revenue 42,300 0.3% 0.0%
Accrued expenses 203,450 1.5% 178,900 1.5% 13.7%
Total current liabilities $ 10,352,563 74.9% 8,184,489 68.9% 26.5%
Capital Lease Obligations 469,355 3.4% 499,900 4.2% -6.1%
Less current maturities (45,675) -0.3% (43,200) -0.4% 5.7%
Total Liabilities $ 10,776,243 78.0% 8,641,189 72.7% 24.7%
Stockholders' equity
Common stock 200,000 1.4% 200,000 1.7% 0.0%
Additional paid-in capital 423,500 3.1% 423,500 3.6% 0.0%
Retained earnings 2,430,355 17.6% 2,615,478 22.0% -7.1%
$ 3,053,855 22.1% 3,238,978 27.3% -5.7%
$ 13,830,098 100.1% 11,880,167 100.0% 16.4%
KEYSTONE COMPUTERS & NETWORKS, INC.
Statement of Income and Retained Earnings
For the Years Ended December 31,
Increase
20X5 20X4 (Decrease) 24,114,892
Net sales $ 92,586,051 100.0% 96,459,566 100.0% -4.0%
Cost of goods sold 72,134,566 77.9% 74,122,435 76.8% -2.7%
Gross profit $ 20,451,485 22.1% 22,337,131 23.2% -8.4% 0.09 32.29
Selling expenses:
Salaries 2,765,677 3.0% 3,167,889 3.3% -12.7%
Payroll benefits and taxes 857,368 0.9% 913,456 0.9% -6.1%
Advertising and promotion 1,567,889 1.7% 1,200,786 1.2% 30.6%
Travel and entertainment 445,600 0.5% 609,788 0.6% -26.9%
MIscellaneous 278,656 0.3% 334,890 0.3% -16.8%
$ 5,915,190 6.4% 6,226,809 6.5% -5.0%
Operating and administrative expenses:
Operating salaries 4,544,860 4.9% 4,878,900 5.1% -6.8%
Administrative salaries 3,945,670 4.3% 4,234,234 4.4% -6.8%
Payroll benefits and taxes 1,734,565 1.9% 1,812,344 1.9% -4.3%
Rent 721,345 0.8% 797,800 0.8% -9.6%
Utilities 234,839 0.3% 210,495 0.2% 11.6%
Insurance 378,677 0.4% 356,890 0.4% 6.1% 0.24%
Legal and accounting 485,767 0.5% 457,577 0.5% 6.2%
Bad debts 256,678 0.3% 234,500 0.2% 9.5%
Supplies 478,900 0.5% 556,345 0.6% -13.9%
Depreciation & Amortization 367,867 0.4% 334,565 0.3% 10.0%
Software development 345,645 0.4% 289,100 0.3% 19.6%
Miscellaneous 245,456 0.3% 234,556 0.2% 4.6%
$ 13,740,269 14.8% 14,397,306 14.9% -4.6%
Total selling, operating and
administrative expenses $ 19,655,459 21.2% 20,624,115 21.4% -4.7%
Operating income $ 796,026 0.9% 1,713,016 1.8% -53.5%
Interest expense 476,899 0.5% 421,344 0.4% 13.2%
Income before income taxes $ 319,127 0.3% 1,291,672 1.3% -75.3%
Income taxes:
Current 80,100 0.1% 256,765 0.3% -68.8%
Deferred 9,150 0.0% 45,632 0.0% -79.9%
Net income $ 229,877 0.2% 989,275 1.0% -76.8%
Retained earnings, January 1 2,615,478 1,626,203 60.8%
Less: Dividends (415,000) 0
Retained earnings, December 31 $ 2,430,355 2,615,478 -7.1%

Ratios

Ref. No. Plan-1
KEYSTONE COMPUTERS & NETWORKS, INC. Prepared by WL
Analytical Procedures Ratios Date 1/10/X6
For the Years Ended December 31,
20X5 20X4 Industry
Current ratio 1.1 1.2 1.3 20x5 Average A/R 20x5 Sale Per Day
Days sales in average accounts receivable 37.0 32.3 37.0 9,384,991 253,660
Allowance for doubtful accounts / accounts receivable 1.0% 1.1% -
Bad debt expenses/net sales 0.3% 0.2% -
Total liabilities to net worth 3.5 2.7 2.9 264,273
Return on total assets 1.7% 8.3% 9.0%
Return on net worth 7.5% 30.5% 29.0%
Return on net sales 0.2% 1.0% 2.3%
Gross profit/net sales 22.1% 23.2% 24.0%
Selling, operating and admin expense / net sales 21.2% 21.4% 23.9%
Times interest earned 5.5