Business Finance - Accounting Looking for Help Completing an Government & Not-For-Profit Accounting Excel Assignment/Analysis

angeleyez2022

CGWO_Excel_Template.xlsx

Home >Business & Finance homework help >Accounting homework help >Business Finance - Accounting Looking for Help Completing an Government & Not-For-Profit Accounting Excel Assignment/Analysis

Chart of Accounts

Capitol Good Works Organization
Chart of Accounts and Other Useful Reference Information
General Ledger Code	Account Description	General Ledger Code	Account Description
11000	Cash	41001	Contributions	11000
12000	Investments	41002	In kind contributions	12000
13000	Pledges receivable	42000	Net Assets released from restriction	13000
14000	Grants receivable	43001	State grant revenues	14000
15000	Other receivables	43002	Federal grant revenues	15000
16000	Prepaid expenses	44001	Membership dues	16000
17001	Land	44002	Program service fees	17001
17002	Buildings and improvements	45000	Investment earnings	17002
17003	Leasehold improvements	51001	Salaries	17003
17004	Equipment	51002	Payroll taxes	17004
17500	Accumulated depreciation	51003	Employee benefits	17500
18000	Investments endowment	52001	Professional Services	18000
21000	Line of credit	52002	Supplies	21000
22000	Accounts payable	52003	Telephone	22000
24000	Lease incentive liability	52004	Postage and shipping	24000
25000	Notes payable	52005	Rent	25000
31000	Net Assets	52006	Printing and artwork	31000
		52007	Local transportation	41001
		52008	Conferences, Meeting and Conventions	41002
		52009	Scholarships, assistance to individuals	42000
		52011	Depreciation expense	43001
		52012	Interest expense	43002
		52013	Other expense	44001
		60000	Gain or Loss	44002
				45000
Program Codes				51001
100	Physical and Social Development			51002
200	Supplementary Education	Restriction Codes		51003
300	Community Development and Organization	1	Without donor restrictions	52001
400	Scholarships	2	With donor restrictions	52002
500	Management and General			52003
600	Fundraising			52004
				52005
				52006
				52007
				52008
				52009
				52011
				52012
				52013
				60000

Trial Balance

Capitol Good Works Organization
Trial Balance
General Ledger Code	Account Description	Beginning Balances		Adjustments during the Period		Ending Balances
		Debit	Credit	Debit	Credit	Debit	Credit
11000	Cash	1,480,367.50		- 0	- 0	1,480,367.50
12000	Investments	5,905,361.25		- 0	- 0	5,905,361.25
13000	Pledges receivable	1,847,387.50		- 0	- 0	1,847,387.50
14000	Grants receivable	1,524,171.25			- 0	1,524,171.25
15000	Other receivables	- 0		- 0	- 0		- 0
16000	Prepaid expenses	107,907.50		- 0	- 0	107,907.50
17001	Land	410,198.75		- 0	- 0	410,198.75
17002	Buildings and improvements	23,854,611.25		- 0	- 0	23,854,611.25
17003	Leasehold improvements	769,532.50		- 0	- 0	769,532.50
17004	Equipment	1,144,778.75		- 0	- 0	1,144,778.75
17500	Accumulated depreciation		20,728,211.25	- 0	- 0		20,728,211.25
18000	Investments endowment	6,996,445.00		- 0	- 0	6,996,445.00
21000	Line of credit		1,651,120.00	- 0	- 0		1,651,120.00
22000	Accounts payable		1,568,010.00	- 0	- 0		1,568,010.00
24000	Lease incentive liability		665,000.00	- 0	- 0		665,000.00
25000	Notes payable		2,729,375.00	- 0	- 0		2,729,375.00
31000	Net Assets		16,699,045.00	- 0	- 0		16,699,045.00
41001	Contributions			- 0	- 0		- 0
41002	In kind contributions			- 0	- 0		- 0
42000	Net Assets released from restriction			- 0	- 0		- 0
43001	State grant revenues			- 0	- 0		- 0
43002	Federal grant revenues			- 0	- 0		- 0
44001	Membership dues			- 0	- 0		- 0
44002	Program service fees			- 0	- 0		- 0
45000	Investment earnings			- 0	- 0		- 0
51001	Salaries			- 0	- 0		- 0
51002	Payroll taxes			- 0	- 0		- 0
51003	Employee benefits			- 0	- 0		- 0
52001	Professional Services			- 0	- 0		- 0
52002	Supplies			- 0	- 0		- 0
52003	Telephone			- 0	- 0		- 0
52004	Postage and shipping			- 0	- 0		- 0
52005	Rent			- 0	- 0		- 0
52006	Printing and artwork			- 0	- 0		- 0
52007	Local transportation			- 0	- 0		- 0
52008	Conferences, Meeting and Conventions			- 0	- 0		- 0
52009	Scholarships, assistance to individuals			- 0	- 0		- 0
52011	Depreciation expense			- 0	- 0		- 0
52012	Interest expense			- 0	- 0		- 0
52013	Other expense			- 0	- 0		- 0
60000	Gain or Loss			- 0	- 0		- 0
						44,040,761.25	44,040,761.25

WK9 - Homework

		Capitol Good Works Organization
		Beginning Trial Balance
		As of January 1, 2021
General Ledger Code	Restriction Code	Account Description	Balances, 1/1/2021
			Debit	Credit
11000		Cash	1,480,367.50
12000		Investments	5,905,361.25
13000		Pledges receivable	1,847,387.50
14000		Grants receivable	1,524,171.25
15000		Other receivables	- 0
16000		Prepaid expenses	107,907.50
17001		Land	410,198.75
17002		Buildings and improvements	23,854,611.25
17003		Leasehold improvements	769,532.50
17004		Equipment	1,144,778.75
17500		Accumulated depreciation		20,728,211.25
18000		Investments endowment	6,996,445.00
21000		Line of credit		1,651,120.00
22000		Accounts payable		1,568,010.00
24000		Lease incentive liability		665,000.00
25000		Notes payable		2,729,375.00
31000	1	Net assets - without donor restrictions		2,007,913.00
31000	2	Net assets - with donor restrictions		14,691,132.00
			44,040,761.25	44,040,761.25

WK10 - Record Transactions 1-25

	Transactions for the Fiscal Year Ended 12/31/2021
								Cash Transaction Flg	PPE Transaction Flag		Cash PPE
	Transaction 1
	GL Code	Program	Restriction	GL + Prog	GL + Restr	Debit	Credit
				0	0	1,500,000.00		0	0	0	0
				0	0		1,500,000.00	0	0	0	0
	42000		2	42000000	420002			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 2										0
											0
	GL Code	Program	Restriction			Debit	Credit	0	0	0	0
				0	0	474,966.25		0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 3										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0	4,815,977.50		0	0	0	0
				0	0	694,786.25		0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 4										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 5										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
	Transaction 6										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 7										0
											0
	Part A: Release the assets from restriction										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
	Part B: Record expenditures										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0	1,887,455.94		0	0	0	0
				0	0	1,348,182.80		0	0	0	0
				0	0	808,909.69		0	0	0	0
				0	0	539,273.13		0	0	0	0
				0	0	647,127.75		0	0	0	0
				0	0	161,781.94		0	0	0	0
				0	0	191,720.18		0	0	0	0
				0	0	137,071.56		0	0	0	0
				0	0	82,242.94		0	0	0	0
				0	0	54,828.63		0	0	0	0
				0	0	65,974.35		0	0	0	0
				0	0	16,448.59		0	0	0	0
				0	0	191,439.50		0	0	0	0
				0	0	136,742.50		0	0	0	0
				0	0	82,045.50		0	0	0	0
				0	0	54,697.00		0	0	0	0
				0	0	65,636.40		0	0	0	0
				0	0	16,409.10		0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 8										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0	83,475.88		0	0	0	0
				0	0	59,625.62		0	0	0	0
				0	0	35,775.37		0	0	0	0
				0	0	23,850.30		0	0	0	0
				0	0	28,620.30		0	0	0	0
				0	0	7,155.03		0	0	0	0
				0	0						0
											0
											0
	Transaction 9										0
											0
	GL Code	Program	Restriction			Debit	Credit	0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 10										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 11										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0						0
											0
	Transaction 12										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0	235,700.50		0	0	0	0
				0	0	168,357.50		0	0	0	0
				0	0	101,014.50		0	0	0	0
				0	0	67,343.00		0	0	0	0
				0	0	80,811.60		0	0	0	0
				0	0	20,202.90		0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 13										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0	37,119.68		0	0	0	0
				0	0	26,514.06		0	0	0	0
				0	0	15,908.44		0	0	0	0
				0	0	10,605.63		0	0	0	0
				0	0	12,726.75		0	0	0	0
				0	0	3,181.69		0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 14										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 15										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0	251,986.86		0	0	0	0
				0	0	179,990.63		0	0	0	0
				0	0	107,994.38		0	0	0	0
				0	0	71,996.25		0	0	0	0
				0	0	86,395.50		0	0	0	0
				0	0	21,598.88		0	0	0	0
				0	0		719,962.50	0	0	0	0
											0
											0
	Transaction 16										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 17										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 18										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 19										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 20										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0	6,142.50		0	0	0	0
				0	0	4,387.50		0	0	0	0
				0	0	2,632.50		0	0	0	0
				0	0	1,755.00		0	0	0	0
				0	0	2,106.00		0	0	0	0
				0	0	526.50		0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 21										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 22										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 23										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 24										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
											0
											0
	Transaction 25										0
											0
	GL Code	Program	Restriction			Debit	Credit				0
				0	0			0	0	0	0
				0	0			0	0	0	0
				0	0			0	0	0	0
Rev. 3/23

Review Bal. Sheet

Capitol Good Works Organization
STATEMENT OF FINANCIAL POSITION
Fiscal Years Ended December 31, 2021 and 2020
ASSETS
					2021	2020
CURRENT ASSETS
	Cash				1,480,367.50	1,480,367.50
	Investments				5,905,361.25	5,905,361.25
	Accounts receivable:
		Pledges receivable			1,847,387.50	1,847,387.50
		Grants receivable			1,524,171.25	1,524,171.25
		Other receivables				- 0
	Prepaid expenses				107,907.50	107,907.50
			Total current assets		10,865,195.00	10,865,195.00
PROPERTY AND EQUIPMENT					5,450,910.00	5,450,910.00
OTHER ASSETS
	Investments endowment				6,996,445.00	6,996,445.00
			Total other assets		6,996,445.00	6,996,445.00
				TOTAL ASSETS	23,312,550.00	23,312,550.00
LIABILITIES AND NET ASSETS
CURRENT LIABILITIES
	Line of credit				1,651,120.00	1,651,120.00
	Current portion of notes payable				798,000.00	791,250.00
	Accounts payable				1,568,010.00	1,568,010.00
		Total current liabilities			4,017,130.00	4,010,380.00
LONG-TERM LIABILITIES
	Lease incentive liability				665,000.00	665,000.00
	Notes payable				1,931,375.00	1,938,125.00
		Total long-term liabilities			2,596,375.00	2,603,125.00
				TOTAL LIABILITIES	6,613,505.00	6,613,505.00
NET ASSETS
	Without donor restrictions				2,007,913.00	2,007,913.00
	With donor restrictions				1,181,875.75	14,691,132.00
		Total net assets			3,189,788.75	16,699,045.00
				TOTAL LIABILITIES AND NET ASSETS	9,803,293.75	23,312,550.00

Statement of Activities

			Capitol Good Works Organization
			STATEMENT OF ACTIVITIES
			Fiscal Year Ended December 31, 2021
								Without Donor Restrictions	With Donor Restrictions	2021 Total
			OPERATING PUBLIC SUPPORT AND REVENUE
				Public support
41001	410011	410012			Contributions			- 0	- 0	- 0
41002	410021	410022			In-kind contributions			- 0	- 0	- 0
41003	410031	410032			Special events revenue			- 0	- 0	- 0
41004	410041	410042			Special events expense			- 0	- 0	- 0
42000	420001	420002			Net assets released from restrictions			- 0	- 0	- 0
						Total public support		- 0	- 0	- 0
43001	430011	430012						- 0	- 0	- 0
43002	430021	430022						- 0	- 0	- 0
				Grants from governmental agencies				- 0	- 0	- 0
				Revenue
44001	440011	440012			Membership dues			- 0	- 0	- 0
44002	440021	440022			Program service fees			- 0	- 0	- 0
45000	450001				Investment income			- 0	(13,509,256.25)	(13,509,256.25)
46000	460001	460002			Other			- 0	- 0	- 0
						Total revenue		- 0	(13,509,256.25)	(13,509,256.25)
							Total public support and revenue	- 0	(13,509,256.25)	(13,509,256.25)
			OPERATING EXPENSES
				Program services				- 0	(13,509,256.25)	(13,509,256.25)
				Management and general				- 0	(13,509,256.25)	(13,509,256.25)
				Fundraising				- 0	(13,509,256.25)	(13,509,256.25)
						Total operating expenses		- 0	(13,509,256.25)	(13,509,256.25)
			NON-OPERATING ACTIVITIES
	45000			Unrealized gains on investments				- 0	(13,509,256.25)	(13,509,256.25)
	60000			Gain (Loss) on sale of equipment				- 0	(13,509,256.25)	(13,509,256.25)
					Total non-operating activities			- 0	(13,509,256.25)	(13,509,256.25)
										- 0
				Change in net assets				- 0	(13,509,256.25)	(13,509,256.25)
				Net assets at beginning of year				2,007,913.00	14,691,132.00	16,699,045.00
					NET ASSETS AT END OF YEAR			2,007,913.00	1,181,875.75	3,189,788.75

Statement of Functional Exp.

		Capitol Good Works Organization
		STATEMENT OF FUNCTIONAL EXPENSES
		Fiscal Year Ended December 31, 2021
		100	200	300	400		500	600
		Physical and Social Development	Supplementary Education	Community Development and Organization	Employment Services	Total Program Services	Management and General	Fundraising	Total
51001	Salaries	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
51002	Payroll taxes	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
51003	Employee benefits	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52001	Professional Services	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52002	Supplies	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52003	Telephone	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52004	Postage and shipping	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52005	Rent	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52006	Printing and artwork	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52007	Local transportation	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52008	Conferences, Meeting and Conventions	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52009	Scholarships, assistance to individuals	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52011	Depreciation expense	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52012	Interest expense	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
52013	Other expense	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
	TOTAL FUNCTIONAL EXPENSES	- 0	- 0	- 0	- 0	- 0	- 0	- 0	- 0
		51001100	51001200	51001300	51001400		51001500	51001600
		51002100	51002200	51002300	51002400		51002500	51002600
		51003100	51003200	51003300	51003400		51003500	51003600
		52001100	52001200	52001300	52001400		52001500	52001600
		52002100	52002200	52002300	52002400		52002500	52002600
		52003100	52003200	52003300	52003400		52003500	52003600
		52004100	52004200	52004300	52004400		52004500	52004600
		52005100	52005200	52005300	52005400		52005500	52005600
		52006100	52006200	52006300	52006400		52006500	52006600
		52007100	52007200	52007300	52007400		52007500	52007600
		52008100	52008200	52008300	52008400		52008500	52008600
		52009100	52009200	52009300	52009400		52009500	52009600
		52011100	52011200	52011300	52011400		52011500	52011600
		52012100	52012200	52012300	52012400		52012500	52012600
		52013100	52013200	52013300	52013400		52013500	52013600

Statement of Cash Flows

Capitol Good Works Organization
STATEMENT OF CASH FLOWS
Fiscal Year Ended December 31, 2021
CASH FLOWS FROM OPERATING ACTIVITIES
Change in net assets				(13,509,256.25)
Adjustments to reconcile change in net assets to net cash flows from operating activities
	Depreciation			- 0
	Loss (Gain) on the sale of equipment			- 0
	Unrealized losses (gains) on investments			- 0
	Amortization of lease incentive liability			- 0
	Contributions with donor restrictions			- 0
	Changes in operating assets and liabilities
		Pledges receivable		- 0
		Grants receivable		- 0
		Other receivables		- 0
		Prepaid expenses		- 0
		Accounts payable		- 0
			Net cash flows from operating activities	(13,509,256.25)
CASH FLOWS FROM INVESTING ACTIVITIES
	Purchase of property and equipment			- 0
	Sale of property and equipment			- 0
			Net cash flows from investing activities	- 0
CASH FLOWS FROM FINANCING ACTIVITIES
	Payments on line of credit			- 0
	Payments on notes payable			- 0
	Contributions with donor restrictions			- 0
			Net cash flows from financing activities	- 0
			Net change in cash	(13,509,256.25)
		CASH, BEGINNING OF YEAR		1,480,367.50
			CASH, END OF YEAR	(12,028,888.75)