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