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.  

1. 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.
2. 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).

   Save Time On Research and Writing

   Hire a Pro to Write You a 100% Plagiarism-Free Paper.

   Get My Paper

   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

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 identif ied. Remember, you will likely refer to these three financial statements, as
well as the trial balance, when composing your summary.