ACG 3501 Government Activity 2 Assignment

ACG 3501
Government Activity 2
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 2”. 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.

In March 2013, the citizens of Johnsonville
approved an additional ¼ penny sales tax to be used to fund debt service
requirements associated with the City’s transportation network. During the spring and summer of 2013, the
City Council, City Manager, Public Works Director, City Engineer, and a
citizen’s committee worked together to identify and prioritize the City’s
transportation needs.

Based on the recommendation of the citizen’s
committee, the City Council authorized an expansion and extension of Main
Street in an amount not to exceed $50,000,000 at its last meeting in September. The project is to be funded with bonds of
$39,000,000, a contribution of $9,500,000 from the General Fund, and a State
grant of $1,500,000 and a separate capital project fund will be used to account
for the project throughout its duration.
Upon completion of the project, any funds remaining in the Capital
Project Fund are to be transferred to the Debt Service Fund.

The following transactions occurred during
the fiscal years ending 9/30/14 and 9/30/15.

10/15/13 – General Fund transferred $9,500,000 to the newly
established Capital Projects Fund.
3/1/14 – Paid architect fees of $2,000,000 associated with the
road expansion.
3/31/14 – The City issued 5%, 20-year serial bonds dated
3/31/14 in the face amount of $39,000,000 at 102. Interest is payable semi-annually on March
31 and September 30. Equal
principal payments are due each March 31 with the first principal payment
due 3/31/15. According to the Bond
Indenture, any premium on the bonds is to be deposited into a debt service
fund and is restricted for debt service (for accounting purposes, assume
the premium is amortized using the straight-line method). The Bond Indenture also requires the
City to transfer any unspent bond proceeds at the end of the construction
project to the Debt Service Fund to be used for debt service.
4/15/14 – City Council awarded a $45,000,000 construction
contract to the ABC Construction Company.
A purchase order was issued for the full amount of the contract.
6/1/14 – The City paid the Electric Utility Fund $500,000 for
wiring associated with street lights to be placed in the right-of-way of
the expanded road.
7/15/14 – The City received a check from the State Department
of Revenue for $3,500,000 for the ¼ penny sales tax collected through
6/30/14.
7/31/14 – ABC Construction Company submitted its first progress
billing for $25,000,000. The City
Engineer inspects the work performed to date and approves the pay request
in full less 5% for retainage.
8/15/14 – The City pays ABC Construction Company the amount
billed less the 5% amount retained.
9/15/14 – The City receives $1,500,000 from the State
Department of Transportation as a grant in aid of construction.
9/30/14 – Amounts due the bondholders are paid.
1/15/15 – The City received a check from the State Department
of Revenue for $2,500,000 for the ¼ penny sales tax collected through
12/31/14.
2/12/15 – ABC Construction Company submitted a final bill for
the project in the amount of $20,000,000.
The City Engineer inspects the work performed and finds a 500 foot
section of the road needs to have the asphalt re-applied and approves the
pay request in full less 5% for retainage.
3/1/15 – City employees re-apply asphalt to the section of road
at a labor and materials cost of $500,000 which is paid with funds in the
Capital Projects Fund.
3/31/15 – Amounts due the bondholders are paid.
4/1/15 – Public Works employees purchase and install landscape
materials in the total amount of $1,300,000 which are paid with funds in
the Capital Projects Fund.
4/15/15 – All amounts due ABC Construction Company are paid.
6/1/15 – The expansion of Main Street is complete and all costs
incurred have been paid.
9/30/15 – Amounts due the bondholders are paid.