[excel]Accounting for Liabilities, Equity and Expense

part_1_solution.xlsx

Home >Business & Finance homework help >Accounting homework help >[excel]Accounting for Liabilities, Equity and Expense

General Journal

General Journal - January Through May
	** Transactions are group by month and by impacts to income statement and balance sheet
	IS = Income Statement impact, BS = Balance Sheet impact
			Account	Debits	Credits	Notes
	January
		#1-1 To Produce Inventory:
			BS: Inventory	$ 90,400		Manufactured 400 new units. Includes the cost for Direct Labor of $50 per unit ($50,000 / 1,000 ) or $50 X 400 units = $20,000	See Inventory and Depreciation Cost Tab for calculations
			BS: Cash		$ 90,400	Cash paid for raw material purchases and direct labor
	February
		#2-1 to Produce Inventory:
			BS: Inventory	$ 82,200		Manufactured 300 new units. Includes the cost for Direct Labor of $50 per unit ($50,000 / 1,000 ) or $50 X 300 units = $15,000	See Inventory and Depreciation Cost Tab for calculations
			BS: Cash		$ 82,200	Cash paid out for raw materials and direct labor costs needed to produce 300 units
		#2-2 to recognize sale to Xplex:
			BS: Cash	$ 98,000		Cash Collected from Xplex from sale of 200 units
			IS: Sales Discount	$ 2,000		Early payment by Xplex for 200 units
			IS: Revenue		$ 100,000	From sale of 200 units to Xplex, less sales discount of $2,000 from early paymenmt by Xplex for 200 units
		#2-3 to recognize expenditure:
			IS: Cost of Goods Sold	$ 45,200		Sale of 200 units (using FIFO to value inventory)
			BS: Inventory		$ 45,200	Sale of 200 units (using FIFO to value inventory)
	March
		#3-1 to Produce Inventory:
			BS: Inventory	$ 77,400		Manufactured 300 new units. Includes the cost for Direct Labor of $50 per unit ($50,000 / 1,000 ) or $50 X 300 units = $15,000	See Inventory and Depreciation Cost Tab for calculations
			BS: Cash		$ 77,400	Cash paid for raw material purchases and direct labor
		#3-2 to recognize sale to Integrative Design and Digital Solution:
			BS: Accounts Receivable	$ 100,000		Accounts receivable extended to Digital Solutions for purchase of 200 units at $500 per unit
			BS: Cash	$ 73,500		Cash Collection from Integrative Design
			IS: Sales Discount	$ 1,500		Sales discount taken by Integrative Design for early payment on 150 units
			IS: Revenue		$ 175,000	150 units sold to Integrative Design and 200 units sold to Digital Solution, net of $1,500 sales discount taken by integrative design for early payment on 150 units
		#3-3 to recognize expense:
			IS: Cost of Goods Sold	$ 86,300		Sale of 350 units (using FIFO to value inventory)
			BS: Inventory		$ 86,300	Reduction in inventory for March sales
	April
		#4-1 to recognize sale to Acore:
			BS: Accounts Receivable	$ 50,000
			IS: Revenue		$ 50,000	Sale of 100 units to Acore
		#4-2 when Acore paid early:
			BS: Cash	$ 49,000		Cash received from Acore for sale of 100 units
			IS: Sales Discount	$ 1,000		Sales discount taken by Acore for early payment on 100 units
			BS: Accounts Receivable		$ 50,000	decrease AR when collect cash
		#4-3 to recognize expenses:
			IS: Cost of Goods Sold	$ 27,400		Sale of 100 units to Acore (using FIFO to value inventory)
			BS: Inventory		$ 27,400	Reduction in inventory for April sales
		#4-4 receive 50% cash payment from Digital Solution:
			BS: Cash	$ 50,000		Received 50% of A/R balance from Digital Solution
			BS: Accounts Receivable		$ 50,000	Reduction for Cash Collection from Digital Solution
		#4-5 purchase equipment
			BS: Equipment	$ 500,000		purchase equipment with $500,000
			BS: Cash		$ 500,000
	May
		#5-1 to recognize sale to Vector Graphics:
			BS: Accounts Receivable	$ 175,000
			IS: Revenue		$ 175,000	Sale of 350 units to Vector Graphics
		#5-2 when Vector paid early:
			BS: Cash	$ 175,000		Payment made by Vectore Graphics for purchase of 350 units
			BS: Accounts Receivable		$ 175,000	decrease AR when collect cash
		#5-3 to recognize expenses:
			IS: Cost of Goods Sold	$ 91,100		Sale of 350 units to Vector Graphics (using FIFO to value inventory)
			BS: Inventory		$ 91,100	Sale of 350 units to Vectore Graphics (using FIFO to value inventory)
		#5-4 to write off uncollectible accounts receivable
			IS: Bad Debt	$ 50,000		Write off for Digital Solutions' Bad Debt using direct method
			BS: Accounts Receivable		$ 50,000	Write off for Digital Solutions' Bad Debt using direct method
		#5-5 to produce inventory in May
			BS: Inventory	$ 115,000		Manufactured 500 units
			BS: Cash		$ 115,000	Cash paid out for raw material and direct labor cost needed to manufacture 500 units
		#5-6 equipment installation
			BS: Equipment	$ 100,000		Equipment installation of $100,000
			BS: Cash		$ 100,000
		#5-7 to record depreciation for May
			IS: Depreciation Expense	$ 4,583		Depreciation Base = 600,000 - 50,000 = 550,000
			BS: Accumulated Depreciation		$ 4,583	Depreciation expense for May = 550,000/10*(1/12)=4,583
			Total	$ 2,044,583	$ 2,044,583

Ledger

General Ledger for January to May
					Balance
Account Title			Debit	Credit	Debit	Credit
	Cash
		Begin:	735,000
		#1-1		90,400
		#2-1		82,200
		#2-2	98,000
		#3-1		77,400
		#3-2	73,500
		#4-2	49,000
		#4-4	50,000
		#4-5		600,000
		#5-2	175,000
		#5-5		115,000	215,500
	Accounts Receivable
		Begin:	75,000
		#3-2	100,000
		#4-1	50,000
		#4-2		50,000
		#4-4		50,000
		#5-1	175,000
		#5-2		175,000
		#5-4		50,000	75,000
	Inventory
		Begin:	0
		#1-1	90,400
		#2-1	82,200
		#2-3		45,200
		#3-1	77,400
		#3-3		86,300
		#4-3		27,400
		#5-3		91,100
		#5-5	115,000		115,000
	Equipment
		Begin:	0
		#4-5	500,000
		#5-6	100,000		600,000
	Accumulated Depreciation
		Begin:	0
		#5-7		4,583		4,583
	Sales Revenue
		#2-2		100,000
		#3-2		175,000
		#4-1		50,000
		#5-1		175,000		500,000
	Sales Discount
		#2-2	2,000
		#3-2	1,500
		#4-2	1,000		4,500
	COGS
		#2-3	45,200
		#3-2	86,300
		#4-3	27,400
		#5-3	91,100		250,000
	Bad Debt Expense
		#5-4	50,000		50,000
	Depreciation Expense
		#5-7	4,583		4,583

TB

Trial Balance For January to May
Account Title		Debit	Credit
	Cash	215,500
	Accounts Receivable	75,000
	Inventory	115,000
	Equipment	600,000
	Accumulated Depreciation		4,583
	Accounts Payable		275,000
	Dividend Payable		50,000
	Long Term Debt		20,000
	Common Stock		465,000
	Sales Revenue		500,000
	Sales Discount	4,500
	COGS	250,000
	Bad Debt Expense	50,000
	Depreciation Expense	4,583
	Total	1,314,583	1,314,583

Inventory and COGS

			January	February	March	April	May
		Beginning Inventory		400	500	450	350
		Purchases	400	300	300	0	500
		Sold		200	350	100	350
		Ending Inventory	400	500	450	350	500
	Direct material cost Per Unit		$176	$224	$208	$0	$180
	Direct Labor Cost Per Unit		$50	$50	$50	$50	$50
	Inventory Cost per unit		$226	$274	$258	$50	$230
	Inventory production each month		$90,400	$82,200	$77,400	$0	$115,000
		Units Delivered at:						Total Units
	jan	$176		200	200			400
	Feb	$224			150	100	50	300
	March	$208					300	300
	Total Units Delivered per month		0	200	350	100	350	1000
FIFO	Raw Material Cost of Good Sold at:
		$176		$35,200	$35,200	$0	$0
		$224		$0	$33,600	$22,400	$11,200
		$208		$0	$0	$0	$62,400
	Total direct material each month		$0	$35,200	$68,800	$22,400	$73,600
	Plus Direct Labor ($50/unit)			$10,000	$17,500	$5,000	$17,500
	Total Cost of Goods Sold			$45,200	$86,300	$27,400	$91,100

BB

Beginning Balance on Jan 1st, 2012
		Debit	Credit
	Cash	735,000
	Accounts Receivable	75,000
	Inventory	0
	Equipment	0
	Accumulated Depreciation	0
	Accounts Payable		275,000
	Dividend Payable		50,000
	Long Term Debt		20,000
	Common Stock		465,000
		810,000	810,000	0