Governmental Activity 3

Please show all work on an excel or word document. 

 

ACG3501 – Fall 2013

Government Activity – Part Three

Instructions

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”. You may find it helpful to actually prepare journal entries and post them to T

accounts. Please clearly label all rows and columns in your spreadsheet and show all

calculations. Remember, your formulas in a cell may not always be visible when uploaded;

therefore, please show all work using the rows and columns approach. Your work will be

completed a lot 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 15 points.

The remaining 60 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 the total 15 points for your spreadsheet, you will need to attach it and then

submit it via the “Assignments” tab in Webcourses.

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.

 Order your spreadsheet in the same order as the questions are asked in the “quiz”.
Indicate the question number(s) from the “quiz” on your spreadsheet next to the related

work.

 Clearly label all columns and rows and show ALL of your work. 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 cells.

 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.

ACG 3501 – Fall 2013

Government Activity – Part Three

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.

 Cash and investments $ 1,800,000

 Restricted cash and investments 1,700,000

 Accounts receivable – customers 450,000

 Unbilled water and sewer revenues 400,000

 Accrued interest receivable 50,000

 Property, plant and equipment
o Land 500,000
o Buildings 1,500,000
o Water Plant 5,000,000
o Sewer Plant 8,000,000
o Water Lines 15,000,000
o Sewer Lines 20,000,000
o Machinery and equipment 700,000
o Construction in progress – sewer plant upgrade 500,000

 Accumulated depreciation 15,000,000

 Accounts payable 100,000

 Liabilities payable from restricted assets – customer deposits 200,000

 Accrued interest payable 400,000

 Accrued salaries and benefits 105,000

 Bonds payable – current portion 600,000

 Bonds payable – long-term portion 20,000,000

 Net position
o Net investment in capital assets 17,100,000
o Restricted

 Unspent bond proceeds – sewer plant upgrade 1,500,000
o Unrestricted 595,000

The following transactions occurred in the Town’s Water and Sewer Utility Fund during the

fiscal year ending 9/30/15.

1. Water and sewer revenues billed to customers throughout the year $ 6,000,000
2. Salaries and benefits paid throughout the year – operations $ 1,800,000
3. Salaries and benefits paid throughout the year – general/administrative $ 400,000
4. Operating expenses paid throughout the year $ 600,000
5. General/administrative expenses paid throughout the year $ 150,000
6. Investment income received during the year – unrestricted investments $ 50,000
7. Investment income received during the year – invested customer deposits $ 10,000
8. Recorded estimated depreciation based on actual from prior year $ 1,600,000
9. Completed the sewer treatment plant upgrades in progress 9/30/14 $ 1,500,000
10. Paid interest on bonds $ 1,200,000
11. Paid principal on bonds $ 600,000
12. Information about water and sewer billings for October and

November 2015

a. 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

13. Additional information at 9/30/15:
a. Interest earned but not received on unrestricted

fixed income investments $ 15,000

b. Interest incurred but not paid on bonds payable 200,000
c. Accrued salaries and benefits incurred but unpaid at 9/30/15 60,000
d. Unrealized gains due to increase in fair value of

i. Unrestricted investments 25,000
ii. Restricted investments 35,000

e. Actual depreciation expense 1,700,000
14. Additional background information

a. During the current year, $5,800,000 was collected from customers for amounts
billed.

b. There was no net change in the number of utility customers from 9/30/14 to
9/30/15.

c. All bonds were issued to construct or acquire property, plant, or equipment for the
Water and Sewer Utility Fund.

d. The Town issued $2,000,000 in bonds in June 2014 to provide funds for sewer
system upgrades estimated to cost $2,000,000.

e. All investments consist of U.S. Treasury bonds, municipal bonds, and stocks
actively traded on U.S. exchanges.

f. No assets were retired, traded, or sold during the year ended 9/30/15.
g. The Town is not required to, and therefore does not, allocate/pay interest earned

on invested customer deposits to customers.

h. For purposes of this assignment, ignore the effects of capitalized interest.
i. Water and sewer services are consumed equally throughout a month.

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

1. Total fund balance for all governmental funds at 9/30/15 $ 100,000
2. The following transactions occurred during the year related to governmental funds and

government-type activities

a. 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.

b. An additional $250,000 of general capital assets was purchased during the year.
c. Accounts payable at 9/30/14 amounted to $50,000 and at 9/30/15 amounted to

$75,000.

d. Amounts paid on outstanding general obligation bonds were $500,000 for
principal and $400,000 for interest.

e. Depreciation expense for general capital assets for the year ending 9/30/15 is
$3,000,000.

f. At 9/30/15, $90,000 of property taxes receivable was expected to be collected in
the spring of 2016.

Questions to be answered, please show your work.

1. What is the amount of unrestricted Cash and Investments at 9/30/15?

2. What is the amount restricted Cash and Investments at 9/30/15?

7. What is the total amount of Accounts Payable and accrued expenses at 9/30/15?

9. What is the amount of Net Investment in Capital Assets at 9/30/15?

10. What is the amount of Restricted Net Position at 9/30/15?

11. What is the amount of Unrestricted Net Position at 9/30/15?

12. What was the amount of Water & Sewer Revenues for the year ending 9/30/15?

13. What is the total amount of Investment Income for the year ending 9/30/15?

14. What is the amount of cash that was paid for expenses for the year ending 9/30/15?

15. What is the total amount of expenses excluding depreciation for the year ending 9/30/15?

16. What is the total amount of expenses including depreciation for the year ending 9/30/15?

17. What is the amount of total Net Position for the Governmental Activities at 9/30/15?

4) Interest Earned But Not Received 15,000

7) Interest Accrued but not paid 200,000

Accrued Salaries 60,000

Accounts Payable 75,000

335,000

8) 19,703,700 I was told the professor gave this answer somewhere.

I should find out by tomorrow

Sheet1

52,700,000

6,000,000

Capitol Assets 9/30/15

																										3)	A/R Beginning 9/30/14	450,000	Johnny James	ACG 3501	Section 2
Water Sewer Revenues Billed 9/30/15		6,000,000
Collected Amounts 9/30/15	-5,800,000
A/R Customers 9/30/15	650,000	#3
5)	Land 9/30/14	500,000
Buliding 9/30/14	1,500,000
Water Plant9/30/14	5,000,000
Sewer Plant 9/30/14	8,000,000
Water Line 9/30/14	15,000,000
Sewer Line 9/30/14	20,000,000
Machinery and Equipment 9/30/14	700,000
Total P-P-E 9/30/15		52,700,000	#5
6)	Total P-P-E 9/30/15
Acummulated Depreciation 9/30/14	-15,000,000
Actual Depreciation Expense 9/30/15	-1,700,000
Net Book Value P-P-E 9/30/15	36,000,000	#6
Revenues( 9/30/15)
12)	Beginning (9/30/14)	-400,000
Billings 10/1/15	155,200
Billings 10/15/15	140,500
Billings 11/1/15 (176,550/33) *20 days	107,000
Billings 11/15/15 (143,000/33) * 6 days	26,000
Investment Income for 9/30/15	6,028,700.00
17)		Capitol Assets 9/30/15	2,000,000
250,000
Fund Balance For Gov Funds 9/30/15	100,000
Taxes Recievable 9/30/15	90,000
Bonds Payable 9/30/15	-4,750,000
Accounts payable 9/30/15	-25,000
Net Position For Gov Activities 9/30/15	-2,335,000

Sheet2

Sheet3