Government Accounting Activity
Use an Excel spreadsheet to organize the background information provided here in such a manner as to allow you to answer the questions noted below the background information. The following information relates to the City of Millersville for the fiscal year ending 9/30/17.
1. Beginning of year information
a. Total Fund Balance – All Unassigned at 10/1/16 $300,000
b. Accounts payable and accrued expenses - $30,000
2. Estimated revenues of $5,150,000
a. Property taxes $3,100,000
b. Licenses and permits $600,000
c. Intergovernmental revenue $1,300,000
d. Other financing sources – sale of park land $150,000
3. Current year property tax information
a. Taxable value of assessed property $775,000,000
b. Millage rate - 4 mills
c. Historical collection rate 97%
d. Collections from current year levy $2,900,000
e. It is expected 30% of the property taxes receivable at year end will be collected as of 11/30/17
4. Revenues other than property taxes - actual
a. Licenses and permits $615,000
b. Intergovernmental revenue $1,250,000
c. Other financing sources – sale of park land $145,000
5. Appropriations of $5,150,000 for the following functions
a. General government $890,000
b. Public safety $2,400,000
c. Public works $950,000
d. Parks and recreation $910,000
6. Total personnel costs of $4,100,000 charged to the following functions
a. General government $760,000
b. Public safety $1,880,000
c. Public works $777,000
d. Parks and recreation $683,000
7. Expenditure information
a. Amounts paid in current year for prior year expenditures $29,000
b. Total purchase orders issued $1,030,000
i. General government $125,000
ii. Public safety $515,000
iii. Public works $170,000
iv. Parks and recreation $220,000
c. Actual amounts billed for purchase orders issued $968,000 (there were no differences in the amounts billed and the amounts ordered)
i. General government $123,000
ii. Public safety $460,000
iii. Public works $168,000
iv. Parks and recreation $217,000
d. Total paid on bills/invoices received for purchase orders issued against current year non payroll appropriations $988,000
i. General government $115,000
ii. Public safety $450,000
iii. Public works $160,000
iv. Parks and recreation $200,000
e. The City Council passes a resolution at the last meeting in September 2016 to re-authorize a 2016/2017 purchase order in the amount of $50,000 as a 2017/2018 purchase order in the same amount. The purchase order was for a SUV the Public Safety department ordered on 8/15/17 which was not received until 10/25/17.
Quiz Questions
1. What is the amount of "Property Tax Revenues" for the year?
2. What is the balance of "Current Taxes Receivable" at the end of the year?
3. What is the amount of total "Revenues" for the year?
4. By how much are total "Revenues" over or under budgeted amounts?
5. What is the amount of total outstanding purchase orders at the end of the year?
6. What is the amount of total Accounts Payable at the end of the year?
7. What is the amount of total expenditures for the current year?
8. By how much are actual expenditures over or under the amounts appropriated?
9. By how much are actual expenditures in Public Safety over or under the amounts appropriated?
10. What is the amount of Unassigned Fund Balance at the end of the current year?
11. What is the amount of the tax levy for the 2016/2017 year?
When responding to answers, please provide excel sheet and specific breakdown on how you came up with the answers.