Use an Excel spreadsheet to organize the background information provided here in such a manner as to allow you to answer the questions noted under the “Quizzes” tab for “Government Activity 3. For this assignment you are required to prepare journal entries and post them to T accounts as well as to use the encumbrance worksheet approach if applicable. Please clearly label all rows and columns in your spreadsheet and show all calculations. Remember, your formulas in a cell are not visible when uploaded; therefore, please show and properly label all work using the rows and columns approach. Your work will be completed more efficiently if you thoroughly review the questions you are required to answer before attempting to set up your Excel spreadsheet.

Submission of your Excel spreadsheet is a required part of this assignment and is worth a maximum of 50 points. The remaining 75 points for this assignment are related to the questions (located under the “Quizzes” tab) that you will need to answer using the background information provided here. In order to receive points for your spreadsheet, you will need to attach it and then submit it via the “Assignments” tab in Webcourses.

Please save your file as an Excel 97-2003 Workbook as webcourses does not like newer versions of Windows or Microsoft Office. Files saved in a newer version of Excel are not likely to properly load and will present a problem with grading.

Guidelines for the preparation of your Excel spreadsheet are as follows:

·Use only one “sheet” for your work.

·Your name and ACG 3501 section number should appear at the top of the spreadsheet.

·Any journal entries are to be in good form (i.e. balanced, debit and credit amounts clearly delineated, no abbreviations, brief description of the entry, etc.) and posted to T accounts and/or an encumbrance worksheet as applicable or appropriate.

·Order any journal entries in the same order as the background information.

·Order any T accounts in proper order by type of accounts. For example, asset accounts will appear before liabilities, revenues before expenditures, etc. Order any encumbrance worksheets in the same order as the functions listed in the background information.

·Clearly label any columns and rows and show ALL of your work. This means all components of a formula are to be clearly labeled on separate lines rather than simply including all the relevant cells and functions in a single cell. In addition, please avoid using abbreviations that are not generally accepted in the accounting vernacular.

·Enable the spreadsheet to allow the instructor to view the formulae used in each cell.

·Use commas for all numbers but do not use decimals or places (i.e. one thousand ten dollars and 20 cents should show as 1,010 not $1,010.20).

The information on your spreadsheet will be used to determine if any partial credit should be awarded for questions that are missed on the “quiz”. Therefore it is very important that you make the information on your spreadsheet easy for someone not familiar with how your brain thinks to follow the work and easily find your answers.

Background Information

The following information relates to the Town of Johnsonville for the fiscal years ending 9/30/14 and 9/30/15.

Following is selected information from the Statement of Net Position prepared for the Town’s Water and Sewer Utility Fund for the year ended 9/30/14.

1. Cash and investments $ 1,800,000
2. Restricted cash and investments 1,700,000
3. Accounts receivable – customers 450,000
4. Unbilled water and sewer revenues 400,000
5. Accrued interest receivable 50,000
6. Property, plant and equipment
   1. Land 500,000
   2. Buildings 1,500,000
   3. Water Plant 5,000,000
   4. Sewer Plant 8,000,000
   5. Water Lines 15,000,000
   6. Sewer Lines 20,000,000
   7. Machinery and equipment 700,000
   8. Construction in progress – sewer plant upgrade 500,000
7. Accumulated depreciation 15,000,000
8. Accounts payable 100,000
9. Liabilities payable from restricted assets – customer deposits 200,000
10. Accrued interest payable 400,000
11. Accrued salaries and benefits 105,000
12. Bonds payable – current portion 600,000
13. Bonds payable – long-term portion 20,000,000
14. Net position
    1. Net investment in capital assets 17,100,000
    2. Restricted - unspent bond proceeds – sewer plant upgrade 1,500,000
    3. Unrestricted 595,000

The following transactions occurred in the Town’s Water and Sewer Utility Fund during the fiscal year ending 9/30/15.

15. Water and sewer revenues billed to customers throughout the year $ 6,000,000
16. Salaries and benefits paid throughout the year - operations $ 1,800,000
17. Salaries and benefits paid throughout the year – general/administrative $ 400,000
18. Operating expenses paid throughout the year $ 600,000
19. General/administrative expenses paid throughout the year $ 150,000
20. Investment income received during the year – unrestricted investments $ 50,000
21. Investment income received during the year – invested customer deposits $ 10,000
22. Recorded estimated depreciation based on actual from prior year $ 1,600,000
23. Completed the sewer treatment plant upgrades in progress 9/30/14 $ 1,500,000
24. Paid interest on bonds $ 1,200,000
25. Paid principal on bonds $ 600,000
26. Information about water and sewer billings for October andNovember 2015

1. 
   
   1. Amounts billed

i.10/1/15 for water and sewer service 8/11/15 - 9/10/15 $ 155,200

ii.10/15/15 for water and sewer service 8/25/15 - 9/24/15 $ 140,500

iii.11/1/15 for water and sewer service 9/11/15 - 10/13/15 $ 176,550

iv.11/15/15 for water and sewer service 9/25/15 – 10/27/15 $ 143,000

27. Interest earned but not received on unrestrictedfixed income investments $ 15,000
28. Interest incurred but not paid on bonds payable $ 200,000
29. Accrued salaries and benefits incurred but unpaid at 9/30/15 $ 60,000
30. Unrealized gains due to increase in fair value of
    1. Unrestricted investments $ 25,000
    2. Restricted investments $ 35,000
31. Actual depreciation expense $ 1,700,000
32. Additional background information
    1. During the current year, $5,800,000 was collected from customers for amounts billed.
    2. There was no net change in the number of utility customers from 9/30/14 to 9/30/15.
    3. All bonds were issued to construct or acquire property, plant, or equipment for the Water and Sewer Utility Fund.
    4. The Town issued $2,000,000 in bonds in June 2014 to provide funds for sewer system upgrades estimated to cost $2,000,000.
    5. All investments consist of U.S. Treasury bonds, municipal bonds, and stocks actively traded on U.S. exchanges.
    6. No assets were retired, traded, or sold during the year ended 9/30/15.
    7. The Town is not required to, and therefore does not, allocate/pay interest earned on invested customer deposits to customers.
    8. For purposes of this assignment, ignore the effects of capitalized interest.
    9. Water and sewer services are consumed equally throughout a month.

The following information relates to the Town’s governmental funds.

Total change in fund balance for all governmental funds at 9/30/15 $ 100,000

The following transactions occurred during the year related to governmental funds and government-type activities.

1. General obligation bonds of $5,000,000 were issued on 11/1/14 at 105 the proceeds of which were used to purchase park land on 12/15/14.All proceeds from the issuance of the bonds were deposited to the General Fund.
2. An additional $250,000 of general capital assets was purchased during the year.
3. Amounts paid on outstanding general obligation bonds were $500,000 for principal and $400,000 for interest.
4. Depreciation expense for general capital assets for the year ending 9/30/15 is $3,000,000.
5. At 9/30/15, $90,000 of property taxes receivable was expected to be collected in the spring of 2016.

Answer rating: 100% (QA)

General Fund Capital Projects Fund Debt Service Fund 1 101513 OFUinterfund transfer outCPF 9500000 1 ... View the full answer