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

Need help completing the Excel assignment and writing an analysis here is the information given. Will also include files to complete everything.

Follow the instructions in the Word doc to complete the Week 10 Assignment tab in the Excel template. Note: The data you enter in this tab will auto-populate the remaining tabs in the Excel template. Review these additional tabs and amend your findings if needed.
When you are finished with the Excel template, write a one-page assessment of the fiscal condition of CGWO, evaluating what each ratio (that you computed) tells you about the organization's fiscal health. Ensure your response covers the following details:
- Typical financial indicators:
  - Liquidity ratio (current ratio – current assets / current liabilities).
  - Burden of debt ratio (total debt / total assets).
  - Adequacy of available resources ratio (net assets without donor restrictions / expenses).
  - Current fiscal performance (operating surplus or deficit / total revenues).
- Financial indicators specific to not-for-profits:
  - Fund-raising ratio (fund-raising expense + administrative costs) / total contributions and grants revenue).
  - Program ratio (program expenses / (program expenses + fund-raising expenses + administrative costs).
  - Contribution and grants ratio (revenue from contributions and grants / total revenues).
- Revenues from services ratio (revenue from program fees / total revenues).
- Ensure that your explanation is supported by at least two appropriate sources. CAPITOL GOOD WORKS ORGANIZATION (CGWO)

Fiscal Year Ended December 31, 2021

WEEK 10 ASSIGNMENT: RECORD TRANSACTIONS

As you will see listed below these instructions, CGWO has summary transactions for its fiscal year ended December 31, 2021.

To complete your Week 10 Assignment, enter the correct amounts for the correct accounts into the Excel template. Because these are summary transactions (that is, each transaction reflects the annual effect of a particular type of activity), all effective dates are presumed to be December 31, 2021. Although this simplification does not allow you to see all the details associated with the timing of individual transactions throughout the year, the resulting end-of-the-year trial balance and financial statements will be complete.

Important Notes:

• In the Excel template, you will notice that all fields requiring responses have a shaded highlight. They are also marked for students using assistive technology. Make sure you don’t miss any fields!

• Restriction codes must be selected for all revenues, and program codes must be selected for all expenses.

• For your convenience, the CGWO ending trial balance and the ledger, program, and restriction code tables from the Week 9 Homework file have been added below. The values have not changed.

FINAL STEP: REVIEW FINANCIAL STATEMENTS AND COMPOSE SUMMARY:

Review and analyze the following statements:

1. Statement of Financial Position

2. Statement of Activities

3. Statement of Functional Expenses

4. Statement of Cash Flows

These statements are found in the last three tabs in the Excel template. The amounts in the financial statements will be automatically populated based on amounts you entered in Week 9 Homework and Week 10 Assignment. Below are some check figures from the financial statements; if your figures are not the same as these follow the recommendations for reviewing your responses.

o Cash as of December 31,2021 = $1,042,515.43

o 2021 Total Assets = $22,433,104.18

o Change in Net Assets without Donor Restrictions for 2021 = $1,303,561.25

o Change in Net Assets with Donor Restrictions for 2021 = ($155,213.75)

o Total functional expenses for Program 100 – Physical and Social Development = $3,039,569.64

o Total Management and General expenses (Program Code 500) = $1,420,659.83

o Net cash flows from operating activities = ($273,388.32)

If your figures do not agree with the check figures do the following:

1. Ensure all your journal entries are balanced.

2. Review all revenues and ensure you entered the correct restriction code.

3. Review the expenses and ensure you entered the correct program code.

4. Make sure all the amounts entered are consistent with the instructions.

Once you have reviewed the financial statements against the check figures, correct any errors you may have identified. Remember, you will likely refer to these three financial statements, as well as the trial balance, when composing your summary. All information from General Ledger codes, Program Codes and Restriction Codes are located in the Chart of Accounts tab in Excel. There are 25 Transactions to input.

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)

Adapted from Government and Not-for-Profit Accounting: Concepts & Practices, 9th edition, by Granof, Khumawala, and Calabrese, Wiley Publishing

CAPITOL GOOD WORKS ORGANIZATION (CGWO) Fiscal Year Ended December 31, 2021

WEEK 10 ASSIGNMENT: RECORD TRANSACTIONS

As you will see listed below these instructions, CGWO has summary transactions for its f iscal year ended December 31, 2021. To complete your Week 10 Assignment, enter the correct amounts for the correct accounts into the Excel template. Because these are summary transactions (that is, each transaction reflects the annual effect of a particular type of activity), all effective dates are presumed to be December 31, 2021. Although this simplif ication does not allow you to see all the details associated with the timing of individual transactions throughout the year, the resulting end-of- the-year trial balance and financial statements will be complete. Important Notes:

• In the Excel template, you will notice that all f ields requiring responses have a shaded highlight. They are also marked for students using assistive technology. Make sure you don’t miss any fields!

• Restriction codes must be selected for all revenues, and program codes must be selected for all expenses.

• For your convenience, the CGWO ending trial balance and the ledger, program, and restriction code tables from the Week 9 Homework file have been added below. The values have not changed.

ACC410 – Week 10 Assignment

General Ledger Code

Account Description

General Ledger Code

Account Description

11000 Cash 41001 Contributions

12000 Investments 41002 In kind contributions

13000 Pledges receivable 42000 Net Assets released from restriction

14000 Grants receivable 43001 State grant revenues

15000 Other receivables 43002 Federal grant revenues

16000 Prepaid expenses 44001 Membership dues

17001 Land 44002 Program service fees

17002 Buildings and improvements 45000 Investment earnings

17003 Leasehold improvements 51001 Salaries

17004 Equipment 51002 Payroll taxes

17500 Accumulated depreciation 51003 Employee benefits

18000 Investments endowment 52001 Professional Services

21000 Line of credit 52002 Supplies

22000 Accounts payable 52003 Telephone

24000 Lease incentive liability 52004 Postage and shipping

25000 Notes payable 52005 Rent

31000 Net assets 52006 Printing and artwork

52007 Local transportation

52008 Conferences, Meetings and Conventions

52009 Scholarships, assistance to individuals

52011 Depreciation expense

52012 Interest expense

52013 Other expense

60000 Gain or loss

Program Codes

100 Physical and Social Development

200 Supplementary Education

300 Community Development and Organization

400 Scholarships

500 Management and General

600 Fundraising

Restriction Codes

1 Without donor restrictions

2 With donor restrictions

Transaction 1: CGWO receives $1,500,000 in cash associated with a donor-restricted pledge made in the prior year. At this point, all restrictions have been deemed to have been met. Therefore, in addition to the entry to record the cash, you should also recognize the net assets released from restriction. Transaction 2: CGWO receives $474,966.25 in cash related to grants receivable. Transaction 3: CGWO receives various cash contributions, pledges, and in-kind contributions. The following table presents these contributions:

Contribution Type: Without donor restrictions

With donor restrictions

Cash $4,165,977.50 $650,000.00 Pledges receivable 0.00 694,786.25 In-kind contributions 122,641.25 0.00 Total $4,983,405.00 $1,344.786.25

The in-kind contributions represent donations of services by licensed professionals. These services would have otherwise been purchased and should therefore result in an expense for professional services related to management and general activities of the organization. Transaction 4: CGWO receives total grant revenues of $4,048,455—a state grant of $195,000 and the balance from federal grants. There are no restrictions associated with the grants. Transaction 5: CGWO received cash payments of $82,057.50 for membership dues and $377,996.25 for program service fees. Transaction 6: CGWO has an unrealized gain of $866,673.75 on its main operating investment account, not the one associated with an endowment.

Transaction 7: CGWO pays salaries and benefits for the year (assume all amounts are paid in cash). The following table presents the allocation of the costs to the various programs, management and general, and fundraising. Since expenses cannot be recognized from amounts with donor restrictions create a journal entry to release the full amount from restriction before entering the expenses.

Programs Physical and

Social Development

Supplementary Education

Community Development

and Organization

Scholarships Management and General

Fundraising

Salaries $1,887,455.94 $1,348,182.80 $808,909.69 $539,273.13 $647,127.75 $161,781.94 Payroll taxes

191,720.18 137,071.56 82,242.94 54,828.63 65,974.35 16,448.59

Employ ee benef its

191,439.50 136,742.50 82,045.50 54,697.00 65,636.40 16,409.10

Total $2,270,795.62 $1,621,996.86 $973,198.13 $648,798.76 $778,558.50 $194,639.63 Transaction 8: Depreciation on CGWO’s fixed assets totals $238,502.50. These amounts are allocated to the program codes as follows:

Program Depreciation Amount

Physical and social development $83,475.88 Supplementary education 59,625.62 Community development and organization 35,775.37 Scholarships 23,850.30 Management and General 28,620.30 Fundraising 7,155.03 Total $238,502.50

Transaction 9: CGWO purchased equipment in the amount of $250,000 with cash. Transaction 10: CGWO sells equipment with a historical cost of $175,000 and accumulated depreciation of $139,500. Cash proceeds equal $62,000. Any gain or loss on the transaction should be recognized as Gain or Loss in GL Account #60000, as an unrestricted amount allocated to Management and General.

Transaction 11: Transactions 11 through 20 record the non-salary expenses for CGWO for the year. For all expenses, ensure that the correct program is identif ied, and the correct program code is entered in the journal entry. Transaction 11 reflects professional fees incurred.

Professional fees Program Amount

Supplementary Education 544,888.12 Management and General 159,833.85 Fundraising 21,795.53

Total $ 726,517.50 Portion paid in cash $ 233,866.64

Portion credited to accounts payable $ 492,650.86 Transaction 12: Transaction 12 reflects supplies expense.

Supplies Program Amount

Physical and Social Development $ 235,700.50 Supplementary Education 168,357.50 Community Development and Organization 101,014.50 Scholarships 67,343.00 Management and General 80,811.60 Fundraising 20,202.90

Total $ 673,430.00 Portion paid in cash $ 355,817.05

Portion credited to accounts payable $ 317,612.95

Adapted from Government and Not-for-Profit Accounting: Concepts & Practices, 9th edition, by Granof, Khumawala, and Calabrese, Wiley Publishing

Transaction 13: Transaction 13 reflects telephone expense.

Telephone Program Amount

Physical and Social Development $ 37,119.68 Supplementary Education 26,514.06 Community Development and Organization 15,908.44 Scholarships 10,605.63 Management and General 12,726.75 Fundraising 3,181.69

Total $ 106,056.25 Portion paid in cash $ 56,036.44

Portion credited to accounts payable $ 50,019.81 Transaction 14: Transaction 14 reflects postage and shipping expenses. All postage and shipping expenses are paid in cash.

Postage and shipping Program Amount

Community Development and Organization 4,316.44 Scholarships 2,877.63 Management and General 10,647.21 Fundraising 10,934.97

Total $ 28,776.25 Transaction 15: Transaction 15 reflects occupancy (for example, rent) expense. Rent is paid directly out of the bank account and will be drawn directly from the cash balance.

Rent Expense Program Amount

Physical and Social Development $ 251,986.86 Supplementary Education 179,990.63 Community Development and Organization 107,994.38 Scholarships 71,996.25 Management and General 86,395.50 Fundraising 21,598.88

Total $ 719,962.50

Transaction 16: Transaction 16 reflects printing and artwork expense.

Printing and artwork Program Amount

Community Development and Organization 6,303.44 Management and General 9,329.09 Fundraising 9,581.22

Total $ 25,213.75 Portion paid in cash $ 13,322.07

Portion credited to accounts payable $ 11,891.68 Transaction 17: Transaction 17 reflects local transportation expense. All local transportation costs are paid in cash.

Local transportation Program Amount

Physical and Social Development $ 135,223.49 Community Development and Organization 56,343.13 Management and General 33,805.88

Total $ 225,372.50 Transaction 18: Transaction 18 reflects Conferences, Conventions, and Meetings expense.

Conferences, conventions, and meetings Program Amount

Physical and Social Development $ 19,305.11 Supplementary Education 13,789.38 Community Development and Organization 8,273.63 Scholarships 5,515.75 Management and General 6,618.90 Fundraising 1,654.73

Total $ 55,157.50 Portion paid in cash $ 9,143.31

Portion credited to accounts payable $ 46,014.19

Transaction 19: Transaction 19 reflects amounts paid out as scholarships directly to individual recipients through the scholarship program and a smaller portion through supplementary education. All scholarship amounts are paid directly from the bank account and are reflected as cash expenses.

Scholarships, assistance to individuals Program Amount

Supplementary Education 92,796.88 Scholarships 278,390.62

Total $ 371,187.50 Transaction 20: Transactions 11 through 20 record the non-salary expenses for CGWO for the year. Transaction 20 reflects the last category Other expense.

Other Expenses Program Amount

Physical and Social Development $ 6,142.50 Supplementary Education 4,387.50 Community Development and Organization 2,632.50 Scholarships 1,755.00 Management and General 2,106.00 Fundraising 526.50

Total $ 17,550.00 Portion paid in cash $ 9,272.81

Portion credited to accounts payable $ 8,277.19 Transaction 21: CGWO keeps legal counsel on retainer. At the end of the year CGWO adjusts prepaid expenses downward by $4,085 to reflect the 12 months of services provided on retainer. This is considered a Management and General expense. Transaction 22: CGWO pays down its line of credit by $530,000, and interest expense of $27,800 is recognized. The line of credit and associated interest payments are considered a Management and General expense. Transaction 23: CGWO pays accounts payable in the amount of $1,568,010 with available cash.

Transaction 24: CGWO has a lease incentive liability associated with its current landlord paying their lease termination fee associated with an old lease. The landlord will charge CGWO the full amount of the outstanding lease liability if CGWO breaks their current lease. The liability is amortized over the life of the current lease. In the current period the liability account is amortized in the amount of $65,000 and the offsetting account is rent expense. The expense is considered a Management and General expense. Transaction 25: CGWO pays down its note payable by $791,250, and interest expense of $121,500. Similar to the line of credit, the note payable is considered a Management and General expense.

FINAL STEP: REVIEW FINANCIAL STATEMENTS AND COMPOSE SUMMARY: Review and analyze the following statements:

1. Statement of Financial Position 2. Statement of Activities 3. Statement of Functional Expenses 4. Statement of Cash Flows

o Cash as of December 31,2021 = $1,042,515.43 o 2021 Total Assets = $22,433,104.18 o Change in Net Assets without Donor Restrictions for 2021 = $1,303,561.25 o Change in Net Assets with Donor Restrictions for 2021 = ($155,213.75) o Total functional expenses for Program 100 – Physical and Social Development =

$3,039,569.64 o Total Management and General expenses (Program Code 500) = $1,420,659.83 o Net cash flows from operating activities = ($273,388.32)