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

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.