excel workbook

Scenario

You are a senior governmental accountant employed by the City of Manchester, New Hampshire. You have been tasked with preparing projected financial statements for Manchester based on actuals from the last two years to support the budgeting process for the next fiscal year. You will need to apply governmental accounting standards, as required by the Governmental Accounting Standards Board (GASB), to these financial statements, and you will identify activities related to all five governmental funds, proprietary funds, and government-wide financial statements. Due to economic tightening, no new major budget expenditures are projected. The financial statements will go to city legislators as input for next year’s budget proposal.

Directions

Complete and submit the Excel financial statements workbook in the template listed below.

Specifically, you must address the following rubric criteria:

Record the annual reporting of capital assets, supplies, and prepaid expenses. Consider the following:

The proper timing for the recognition of revenues generated by various types of nonexchange transactions

Explain the various types of monetary transfers that occur within funds maintained by a state or local government.

The City of Manchester, New Hampshire, has only one activity, its school system. The school system is accounted fo
that at the start of 20X4, the school system and the city have no assets. During the year, the city assesses property
$320,000 during the year and $50,000 within a few weeks after the end of the year and expects the remainder to b
the following payments during 20X4: salary expense, $100,000; rent expense, $70,000; equipment (received on Jan
$50,000; land, $30,000; and maintenance expense), $20,000.
In addition, on the last day of the year, the city purchases a $200,000 building by signing a long-term note payable.
and the liability accrues interest at a 0% annual rate. The city also buys two computers on the last day of the year f
the other in 90 days. The computers should last four years and have no expected residual value. During the year, th
fees and collects the entire amount. The city determines depreciation using the straight line method.
Record the annual reporting of capital assets, supplies, and prepaid expenses.
The most difficult aspect of this problem is gathering the information for both the government‑wide financial sta
financial statements. One way to overcome this difficulty is to make journal entries for the transactions that are
Government‑wide financial statements:
1
Property Tax Receivable
Cash
General Revenues—Property Taxes
2
Salary Expense
Rent Expense
Equipment
Land
Maintenance Expense
Cash
Depreciation Expense
Accumulated Depreciation—Equipment
3
Building
Noncurrent Liability
Neither depreciation nor interest is recognized on these two balances because this
transaction took place on the last day of the year.
4
Computers
Vouchers Payable
No depreciation is recognized on the computer because this transaction occurred on the last
day of the year.
5
Cash
Program Revenues—Student Fees
Fund financial statements:
1
Property Tax Receivable
Cash
Revenues—Property Taxes
Unavailable Revenues
The $30,000 is not viewed as revenue in 20X4 because it will not be available within 60 days
after year-end to pay claims against current financial resources. This balance is not a liability
because there is no chance of repayment. Instead, it is a deferred inflow of resources.
2
Expenditures—Salaries
Expenditures—Rent
Expenditures—Equipment
Expenditures—Land
Expenditures—Maintenance
Cash
3
No entry is made on the building acquisition because there was no impact on current
4
Expenditures—Computer
Vouchers Payable
The second computer is not included here because payment will not be made within 60 days
of the end of the year, so there is no effect on current financial resources.
5
Cash
Revenues—Student Fees
Part One
GOVERNMENT‑WIDE FINANCIAL STATEMENTS
STATEMENT OF ACTIVITIES
For the Year Ended December 31, 20X4
Governmental Activities:
—School System
General Revenues:
—Property Taxes
Change in Net Position
Beginning Net Position
Ending Net Position
STATEMENT OF NET POSITION
12/31/20X4
Assets
—Cash
—Property Tax Receivable
—Computers
—Building
—Equipment
Less: Accumulated Depreciation
—Land
Total Assets
Liabilities
—Vouchers Payable
—Long‑Term Liabilities
Net Position
—Net Investment in Capital Assets
—Unrestricted
Part Two: FUND FINANCIAL STATEMENTS
STATEMENT OF REVENUES, EXPENDITURES,
AND CHANGES IN FUND BALANCE
For the Year Ended December 31, 20X4
Revenues
—Property Taxes
—Student Fees
Total Revenues
Expenditures
—Salaries
—Rent
—Equipment
—Land
—Maintenance
—Computer
Total Expenditures
Change in Fund Balance
Fund Balance—Beginning of Year
Fund Balance—End of Year
BALANCE SHEET
12/31/20X4
Assets
—Cash
—Property Tax Receivable
Total Assets
Liabilities
—Voucher Payable
Deferred Inflows of Resources
—Unavailable Revenues
Total Deferred Inflows and Liabilities
Fund Balance
—Unassigned
Total Liabilities, Deferred Inflows of
Resources, and Fund Balance
ystem is accounted for within the general fund. For convenience, assume
ity assesses property taxes of $400,000. Of this amount, it collects
cts the remainder to be collected about six months later. The city makes
ment (received on January 1 with a five-year life and no salvage value),
g-term note payable. The building has a 20-year life and no salvage value,
last day of the year for $4,000 each. One will be paid for in 30 days and
ue. During the year, the school system charges students $3,000 for school
method.
ent‑wide financial statements and the fund
transactions that are described in the question.
Debit
Credit
Direct Expenses
Program Revenues
Governmental
Activities
Governmental
Activities (Net
Expense)
Explain the various types of monetary transfers that occur within funds maintained by a state or local government.
In the City of Manchester, New Hampshire, Fund A transfers $20,000 in cash to Fund B. For each of the following, indicate w
1. If Fund A is the general fund and Fund B is an enterprise fund, nothing is shown for this transfer on the statement of acti
2. If Fund A is the general fund and Fund B is a debt service fund, nothing is shown for this transfer on the statement of act
3. If Fund A is the general fund and Fund B is an enterprise fund, a $20,000 reduction is reported on the
statement of revenues, expenditures, and other changes in fund balance
4. If Fund A is the general fund and Fund B is a special revenue fund (which is not considered a major fund), no
changes are shown on the statement of revenues, expenditures,
and other changes in fund balance for the governmental funds within the fund financial statements.
5. If Fund A is the general fund and Fund B is an internal service fund and this transfer is to pay for work done,
the general fund will report an expense of $20,000 within the fund
financial statements.
he following, indicate whether the statement is true or false, and if false, explain why.
n the statement of activities within the government-wide financial statements.
on the statement of activities within the government-wide financial statements.
The City of Manchester, New Hampshire, has only one activity, its school system. The school system is accounted fo
that at the start of 20X4, the school system and the city have no assets. During the year, the city assesses property
$320,000 during the year and $50,000 within a few weeks after the end of the year and expects the remainder to b
the following payments during 20X4: salary expense, $100,000; rent expense, $70,000; equipment (received on Jan
$50,000; land, $30,000; and maintenance expense), $20,000.
In addition, on the last day of the year, the city purchases a $200,000 building by signing a long-term note payable.
and the liability accrues interest at a 0% annual rate. The city also buys two computers on the last day of the year f
the other in 90 days. The computers should last four years and have no expected residual value. During the year, th
fees and collects the entire amount. The city determines depreciation using the straight line method.
Record the annual reporting of capital assets, supplies, and prepaid expenses.
The most difficult aspect of this problem is gathering the information for both the government‑wide financial sta
financial statements. One way to overcome this difficulty is to make journal entries for the transactions that are
Government‑wide financial statements:
1
Property Tax Receivable
Cash
General Revenues—Property Taxes
2
Salary Expense
Rent Expense
Equipment
Land
Maintenance Expense
Cash
Depreciation Expense
Accumulated Depreciation—Equipment
3
Building
Noncurrent Liability
Neither depreciation nor interest is recognized on these two balances because this
transaction took place on the last day of the year.
4
Computers
Vouchers Payable
No depreciation is recognized on the computer because this transaction occurred on the last
day of the year.
5
Cash
Program Revenues—Student Fees
Fund financial statements:
1
Property Tax Receivable
Cash
Revenues—Property Taxes
Unavailable Revenues
The $30,000 is not viewed as revenue in 20X4 because it will not be available within 60 days
after year-end to pay claims against current financial resources. This balance is not a liability
because there is no chance of repayment. Instead, it is a deferred inflow of resources.
2
Expenditures—Salaries
Expenditures—Rent
Expenditures—Equipment
Expenditures—Land
Expenditures—Maintenance
Cash
3
No entry is made on the building acquisition because there was no impact on current
4
Expenditures—Computer
Vouchers Payable
The second computer is not included here because payment will not be made within 60 days
of the end of the year, so there is no effect on current financial resources.
5
Cash
Revenues—Student Fees
Part One
GOVERNMENT‑WIDE FINANCIAL STATEMENTS
STATEMENT OF ACTIVITIES
For the Year Ended December 31, 20X4
Governmental Activities:
—School System
General Revenues:
—Property Taxes
Change in Net Position
Beginning Net Position
Ending Net Position
STATEMENT OF NET POSITION
12/31/20X4
Assets
—Cash
—Property Tax Receivable
—Computers
—Building
—Equipment
Less: Accumulated Depreciation
—Land
Total Assets
Liabilities
—Vouchers Payable
—Long‑Term Liabilities
Net Position
—Net Investment in Capital Assets
—Unrestricted
Part Two: FUND FINANCIAL STATEMENTS
STATEMENT OF REVENUES, EXPENDITURES,
AND CHANGES IN FUND BALANCE
For the Year Ended December 31, 20X4
Revenues
—Property Taxes
—Student Fees
Total Revenues
Expenditures
—Salaries
—Rent
—Equipment
—Land
—Maintenance
—Computer
Total Expenditures
Change in Fund Balance
Fund Balance—Beginning of Year
Fund Balance—End of Year
BALANCE SHEET
12/31/20X4
Assets
—Cash
—Property Tax Receivable
Total Assets
Liabilities
—Voucher Payable
Deferred Inflows of Resources
—Unavailable Revenues
Total Deferred Inflows and Liabilities
Fund Balance
—Unassigned
Total Liabilities, Deferred Inflows of
Resources, and Fund Balance
ystem is accounted for within the general fund. For convenience, assume
ity assesses property taxes of $400,000. Of this amount, it collects
cts the remainder to be collected about six months later. The city makes
ment (received on January 1 with a five-year life and no salvage value),
g-term note payable. The building has a 20-year life and no salvage value,
last day of the year for $4,000 each. One will be paid for in 30 days and
ue. During the year, the school system charges students $3,000 for school
method.
ent‑wide financial statements and the fund
transactions that are described in the question.
Debit
Credit
Direct Expenses
Program Revenues
Governmental
Activities
Governmental
Activities (Net
Expense)
Explain the various types of monetary transfers that occur within funds maintained by a state or local government.
In the City of Manchester, New Hampshire, Fund A transfers $20,000 in cash to Fund B. For each of the following, indicate w
1. If Fund A is the general fund and Fund B is an enterprise fund, nothing is shown for this transfer on the statement of acti
2. If Fund A is the general fund and Fund B is a debt service fund, nothing is shown for this transfer on the statement of act
3. If Fund A is the general fund and Fund B is an enterprise fund, a $20,000 reduction is reported on the
statement of revenues, expenditures, and other changes in fund balance
4. If Fund A is the general fund and Fund B is a special revenue fund (which is not considered a major fund), no
changes are shown on the statement of revenues, expenditures,
and other changes in fund balance for the governmental funds within the fund financial statements.
5. If Fund A is the general fund and Fund B is an internal service fund and this transfer is to pay for work done,
the general fund will report an expense of $20,000 within the fund
financial statements.
he following, indicate whether the statement is true or false, and if false, explain why.
n the statement of activities within the government-wide financial statements.
on the statement of activities within the government-wide financial statements.