Use Excel to complete the problem

Complete the four questions in the document using an Excel spreadsheet.

Problem:

The Muncie Arts Federation is a not-for-profit organization dedicated to promoting the arts within the greater Muncie community. There are two programs conducted by the Muncie Arts Federation: (1) exhibition and sales of members’ art (referred to as Exhibition) and (2) Community Art Education. Activities of the Muncie Arts Federation are conducted by a part-time administrator, a part-time secretary-bookkeeper, and several part-time volunteers. The volunteers greet visitors, monitor the security of the exhibit hall, and handle the sales of art to the public. Art on exhibit is considered the property of the member artists, not the Muncie Arts Federation.

The post-closing trial balance for the Paint Muncie as of

June 30, 2020

, is shown here.

For the year ended June 30, 2021, Paint Muncie had the following transactions:

1. During the year, unrestricted cash was received from: grants, $11,600, of which $4,600 had been reported as receivable on June 30, 2020; annual contributions and other unrestricted gifts, $13,861; membership dues, $16,285; tuition and fees for educational workshops, $6,974; and sales of members’ art, $12,010. Paint Muncie keeps 20% of sales as a commission and pays the remaining 80% to artists.

2. Interest on investments without donor restrictions totaled $686. Interest on investments restricted for programs totaled $925. Interest on investments in the permanent endowment totaled $344. Earnings from the permanent endowment are restricted for program use.

3. Grants receivable as of year-end totaled $5,300, of which $3,300 was earned in the current year (without donor restrictions) and $2,000 was reported as deferred revenue.

4. Paint Muncie leases a building from the city for $1 per year. The estimated value of this lease is $18,000 a year.

5. Expenses incurred during the year included salaries and fringe benefits, $46,900; utilities $3,080; printing and postage, $1,310; and miscellaneous, $640. As of year-end, the balances of the following accounts were Prepaid Expenses, $840, and Accounts Payable and Accrued Expenses, $2,746.

6. During the year, $2,900 of short-term investments were sold, with the proceeds used to purchase two computers and a printer at a cost of $2,835. The resources used were restricted for the purchase of equipment.

7. In accordance with the terms of the permanent endowment, income earned by the endowment for the provision of free art instruction for children with disabilities was provided at a cost of $825. This amount was allocated to community art education.

8. Depreciation on equipment in the amount of $1,642 was recorded.

9. Expenses for the year were allocated 30 percent to Ball Gallery Program, 30 percent to Community Art Education, 25 percent to Management and General Expenses, and 15 percent to Fund-Raising. (Round to the nearest dollar.)

10. Proceeds of art sales, net of commissions charged by Paint Muncie, totaled $9,608. This amount was paid to member artists during the year.

11. All temporary accounts were closed at year-end. (These closing entries will not be graded, but are essential to correctly preparing a Statement of Financial Position.)

         

Required

a. Make all necessary journal entries to record these transactions. Expense transactions should be initially recorded by object classification (i.e. “normal” line item) unless otherwise instructed; in entry 9, expenses will be allocated to functions. (Each bullet point is worth 3 points – 30 points total)

b. Prepare a statement of financial position as of June 30, 2021. (10 points – 3 points for accurate balances, 3 points for accurate for presentation, and 4 points for well-formatted excel)

c. Prepare a statement of activities for the year ended June 30, 2021. (10 points – 3 points for accurate balances, 3 points for accurate for presentation, and 4 points for well-formatted excel)

d. Prepare a schedule of functional expenses for the year ended June 30, 2021. (10 points – 3 points for accurate balances, 3 points for accurate for presentation, and 4 points for well-formatted excel)

DebitsCredits

Cash5,000$

Short-term Investments16,145

Grants Receivable4,600

Prepaid Expense2,000

Equipment9,345

Allowance for Depreciation—Equipment2,426$

Long-term Investments—With Donor Restrictions5,767

Accounts Payable and Accrued Expenses2,649

Deferred Revenue2,800

Net Assets Without Donor Restrictions15,970

Net Assets With Donor Restrictions—Programs13,245

Net Assets With Donor Restrictions—Permanent Endowment 5,767

Total42,857$ 42,857$

Post-closing Trial Balance

June 30, 2020