Chat with us, powered by LiveChat Financial Management-excel | All Paper
+1(978)310-4246 credencewriters@gmail.com
  

attachment has all the information and questions to be answered
2019_financial_management_.xlsx

Unformatted Attachment Preview

Assigned:
Due:
Name:
May 4, 2019
May 11, 2019
Please note that this is the same introduction from the Midterm on
Introduction: After years of ice melt, previously uninhabited regions of the North Pole began to attract
thousands of migrants from all over the world. Some of these migrants settled with the indigenous
people in existing villages and other established new villages across vast swaths of newly available land.
The 1996 Ottawa Declaration established the Arctic Council as a forum for promoting cooperation,
coordination, and interaction among the Arctic nations, with the involvement of the Arctic indigenous
communities and other Arctic inhabitants on issues such as sustainable development and
environmental protection. Only nations with territory in the Arctic were allowed to become members
of the Council. The eight member nations of the Arctic Council are Canada, Denmark, Finland, Iceland,
Norway, Russia, Sweden, and the United States.
The new residents of the North Pole, dissatisfied with a patchwork of representation through the Arctic
Council, formed a working committee to craft a constitution for the establishment of the Republic of
the North Pole. The constitution defined the boundaries of the 600,000 square miles of the Republic of
the North Pole (roughly the size of Alaska) and its form of government.
The 1,300,000 residents within the 600,000 square mile boundary overwhelming voted to ratify the
new constitution, officially creating the Republic of the North Pole on May 1, 2015.
The new constitution establishes a Federal Republic style of government. The Republic of the North
Pole shall have three branches of government, with an Executive Branch, Legislative Branch and Judicial
Branch.
Despite protests from all of the eight member nations of the Arctic Council, the United Nations
recognized the Republic of the North Pole as its 194th member nation in 2016. Please note that RNP
local currency is pegged to the US Dollar at a one to one rate. Therefore, all of RNP data is in US
Midterm on March 9, 2019.
Please note that this is the same information from the Midterm on
The RNP has selected a May 1 to April 30 Fiscal Year to correspond with the annual spring melt.
RNP, in its constitution, specified that the nation shall be divided into four Districts or States. The
Districts shall be known as Northeast (NE), Southeast (SE), Northwest (NW) and Southwest (SW). The
four Districts all have the power to hold elections to form their own governments and the power to
raise revenue and spend funds. Also, per the RNP constitution, all District governments must have
Executive, Legislative and Judicial branches. All four Districts of RNP have ratified their own
constitutions. Each District constitution has specified that it must adopt a balanced budget.
To maintain uniformity, all four Districts in RNP have adopted Fiscal Years from July 1 to June 30.
The SE District constructed a state of the art seaport and this attracted tremendous investment and
growth. Soon the land area around the seaport became densely populated. The population grew to
approximately 650,000 or 50% of the entire population of the RNP. In 2018, there was a referendum
and the residents of the SE District elected to create the City of the North Pole within a newly defined
boundary of 60,000 square miles around the seaport that was a subsection of the District’s 150,000
square miles area. The City of the North Pole commenced a Charter Commission to write its Charter.
Midterm on March 9, 2019.
New information for Final Exam assigned on May 4, 2019.
One of the first acts of the newly formed City of the North Pole was to petition the SE District for the
creation of an independent school district named the City of the North Pole School District (NPSD). The
independent school district would serve all the children of the City of North Pole and provide
kindergarten through twelfth grade. The NPSD enrollment is projected to be 80,000.
The NPSD was approved by the SE District and began operation on January 1, 2020. Its Fiscal Year was
established as January 1 to December 31. As an independent school district, the NPSD has the power
to levy real property taxes, issue debt and spend funds. The NPSD is governed by a five member board
that is elected by the citywide electorate of the City of the North Pole to serve staggered four year
terms. The School Board members are non-paid. The five member School Board on January 1 each
year votes to select a School Board President. The School Board selected a Superintendent who is the
Chief Executive Officer (CEO) of the NPSD.
As defined in the language approved by the SE District, the teachers of the NPSD are provided the right
of collective bargaining. The 4,000 newly hired teachers of the NPSD formed a union call the North
Pole Teachers Union (NPTU) and agreed to a four year contract with the NPSD which runs from January
1, 2020 to December 31, 2023.
Assignment: Because of your extensive financial management expertise you have been hired by the
City of North Pole School District (NPSD) Actual Expenditures for January 1, 2020 to December 31, 2020
($USD)
Management Salaries
Management Benefits
Teacher Salaries
Teacher Benefits
Support Salaries
Support Benefits
Materials and Supplies
Capital Outlay
Building Lease
Debt Service
Total Expenditure
Amount
20,625,000
6,187,500
200,000,000
60,000,000
50,000,000
15,000,000
8,000,000
40,500,000
100,000,000
500,312,500
Detail on Specific Expenditure Items
Management Salaries
Superintendent
Assistant Superintended K-6
Assistant Superintendent 7-12
CFO
Treasurer/Comptroller
Principals
Assistant Principals
Total
Count
1
1
1
1
1
80
160
245
Rate
150,000
125,000
125,000
125,000
100,000
100,000
75,000
Total Salaries
150,000
125,000
125,000
125,000
100,000
8,000,000
12,000,000
20,625,000
Teacher Salaries
Grades K-6
Grades 7-12
Total
Count
2,150
1,850
4,000
50,000
50,000
Total Salaries
107,500,000
92,500,000
200,000,000
Support Salaries
Grades K-6
Grades 7-12
Total
Count
1,075
925
2,000
25,000
25,000
Total Salaries
26,875,000
23,125,000
50,000,000
Benefits (Health and Pension) for Management, Teachers and Support equal to 30% of Salaries.
Materials and Supplies (Books and other items) equal to $100/student.
Capital Outlay only comprised of purchase of buses. In 2020, purchased 900 and used 900 in 2020.
Each bus has cost of $90,000. The total cost of the 900 buses paid over two years.
The cost of 900 buses paid in equal installments in 2020 and 2021 with 0% interest charged.
Building Lease equal to 25 square feet per student at rate of $50 per square foot. One year lease.
Lease term is January 1, 2020 to December 31, 2020 with rolling one year option to extend at same rate.
Debt Service equals zero in 2020 because no debt issued thus far by NPSD.
1, 2020 to December 31, 2020
30% of Salaries.
d used 900 in 2020.
est charged.
t. One year lease.
n to extend at same rate.
School Buildings and Staffing Levels

Grades K-6
Grades 7-12
Total
School Buildings
43
37
80
Students
43,000
37,000
80,000
Teachers
Grades K-6
Grades 7-12
Total
Teachers
2,150
1,850
4,000
Teacher/Student
20
20
Principals
Grades K-6
Grades 7-12
Total
Principals
43
37
80
Principal/Teacher
50
50
Vice Principals
86
74
160
Vice Principal/Teacher
25
25
Vice Principals
Grades K-6
Grades 7-12
Total
Support
Grades K-6
Grades 7-12
Total
Maintenance (5 per school)
Bus Drivers (1 per 100 students)
215
430
185
370
400
800
North Pole Teachers Union Collective Bargaining Agreement
Using 2020 as the base salary, increase of 2% per year over base salary for three consecutive years.
For 2021 all teachers salaries equal 2020 level * 1.02
For 2022 all teachers salaries equal 2021 level * 1.02
For 2023 all teachers salaries equal 2022 level * 1.02
Total increase due over four years of contact is 6%.
els
Students/School Building
1,000
1,000
Teacher/School Building
50
50
Principal/School Building
1
1
Vice Principal/School Building
2
2
ing Agreement
ree consecutive years.
Admin (10 per school)
430
370
800
City of North Pole School District (NPSD) Actual Revenues for January 1, 2020 to December 31, 2020
($USD)
Real Property Tax Levy
SE District General Operating Aid
SE District Building Aid
SE District Transportation Aid
RNP Special Education Aid
RNP Child Nutrition Aid
Total Revenue
Amount
314,000,000
118,000,000
10,000,000
10,125,000
20,000,000
28,800,000
500,925,000
Detail on Specific Revenue Items
Square Miles
City of North Pole/NPSD
Total Square Miles
60,000
Assessed Value
City of North Pole/NPSD
Total Assessed Value
114,000,000,000
Real Property Tax Levy
NPSD
Total Assessed Value
114,000,000,000
SE District General Operating Aid Formula = General Operating Aid Subtotal – Local Tax Effort
General Operating Subtotal = [$5,000 per K-12 enrollment + $2,500 per special education enrollment + $1,000 per Child Nu
Local Tax Effort = [(Tax Rate Per $1,000 of $3.00) * Assessed Value]
NPSD
Total K-12 Enrollment
80,000
NPSD
Total K-12 Enrollment
80,000
General Operating Aid Subtotal
Local Tax Effort
SE District General Operating Aid
$5,000 per Total K-12 Enrollment
400,000,000
Assessed Value
114,000,000,000
Local Tax Effort
342,000,000
SE District Building Aid is equal to 25% of debt service and 10% of Building lease expense
SE District Building Aid
Building Lease Expense (10%)
10,000,000
SE District Transportation Aid is equal to 25% of annual capital outlay for buses
SE District Transportation Aid
Capital Outlay Buses (25%)
10,125,000
RNP Special Education Aid equal to $2,500 per Special Education enrollment
RNP Special Education Aid
$2,500 per Special Education Enrollment
20,000,000
RNP Child Nutrition Aid equal to $4 per child (from family with AGI less than $50,000) per day (180 school days per year)
RNP Child Nutrition Aid
$4 per Eligible Child per Day
28,800,000
2020 to December 31, 2020
Square Miles Next to Port
6,000
Square Miles Not Next to Port
54,000
Assessed Value Next to Port
57,000,000,000
Assessed Value Not Next to Port
57,000,000,000
Tax Rate Per $1,000
2.75
Tax Levy
314,000,000
otal – Local Tax Effort
special education enrollment + $1,000 per Child Nutrition Enrollment]
Special Education (10%)
8,000
Regular Education
72,000
Eligible for Child Nutrition (50%)
40,000
Not Eligible for Child Nutrition
40,000
$2,500 per Special Education Enrollment
20,000,000
$1,000 per Child Nutrition Enrollment
40,000,000
Tax Rate Per $1,000
3.00
Local Tax Effort
342,000,000
General Operating Aid Subtotal
460,000,000
SE District General Operating Aid
118,000,000
Debt Service (25%)
0
Total SE District Building Aid
10,000,000
ng lease expense
City of North Pole School District (NPSD) Capital Plan
Spending
School Buses
School Buildings
Maintenance and Issuance Costs
Grand Total
2020
40,500,000
40,500,000
2021
40,500,000
40,500,000
Financing Sources
Pay As You Go
Long Term Debt
Grand Total
2020
40,500,000
40,500,000
2021
40,500,000
330,000,000
370,500,000
2022
2023
150,000,000 150,000,000
14,000,000
8,000,000
164,000,000 158,000,000
2022

2023

Issue $330 million in Long Term Debt on July 1, 2021 with first debt service payment due on December 31, 2021.
Debt is 30 year fixed rate at 4% with level debt service payments twice per year (this means all debt service payments are
Debt Service of $9.5 million paid twice a year on June 30 and December 31.
No investment of excess bond proceeds during construction.
Accelerated maintenance in 2022, 2023 and 2024 then funded by pay as you go.
2024
8,000,000
8,000,000
2024

Total
81,000,000
300,000,000
30,000,000
411,000,000
Total
81,000,000
330,000,000
411,000,000
December 31, 2021.
Enrollment
Square Feet Needed
School Board Approves 1,500,000 square feet in new school building construction
The remaining school building space shall be leased every year.
Square Feet
$200 Cost Per Square Foot
all debt service payments are equal).
Necessary Long Term Debt
School Board and voter of City of North Pole approve $330 million long term deb
Square feet needed (25 per child)
Building Lease Square Feet
New Construction Square Feet
Enrollment Projected to Grow by 10% per Year
2020
80,000
2,000,000
2021
88,000
2,200,000
2022
96,800
2,420,000
2023
106,480
2,662,000
0 square feet in new school building construction or approximately 50% of need in 2024.
ace shall be leased every year.
School Building Construction Schedule
2020

2021

2022
750,000
150,000,000
2023
750,000
150,000,000
School Construction
300,000,000
Maintenance (8%)
24,000,000
Issuance Fees (2%)
6,000,000
Total Long Term Debt
330,000,000
2022
2,420,000
1,670,000
750,000
2023
2,662,000
1,162,000
1,500,000
North Pole approve $330 million long term debt issuance
Schedule of Necessary Building Leases
2020
2,000,000
2,000,000

2021
2,200,000
2,200,000

2024
117,128
2,928,200
2024

2024
2,928,200
1,428,200
1,500,000
Question 1 (4 Points): Please prepare the NPSD Statement of Activities (Income Statement) for the 2020 Fiscal Year.
Answer:
January 1, 2020 to December 31, 2020
Revenue:
Local Revenue
District Revenue
Central Government Revenue
Total Revenue
Expenditures:
Salaries and Benefits
Materials and Supplies
Capital Outlay
Building Lease
Debt Service
Total Expenditures
Net Change in Fund Balance
Question 2 (5 Points): Please prepare the NPSD Statement of Net Assets (Balance Sheet) as of December 31, 2020.
Note: There is no depreciation. Buses are a Capital Asset.
Materials and Supplies are not Assets and are all consumed during the Fiscal Year.
Benefits (Health and Pension) are not long term obligations and not on the Balance Sheet.
The NPSD does not own any buildings or land and NPSD began 2020 with no cash.
The only Assets are Cash (which is from the Net Change in Fund Balance) and Buses.
The only Liabilities are the Bus Payable.
Answer:
As of December 31, 2020
ASSETS
Cash
Capital assets, net of depreciation
All other assets
Total assets
LIABILITIES
Accounts Payable
Long Term Obligations:
General Obligation Debt
Revenue Bonds
All other liabilities
Total liabilities
NET ASSETS
Capital assets, net of related debt or liabilities
All other net assets
Total net assets
Liabilities plus net assets
Question 3 (2 Points): For 2020 Total Expenditure what percentage was spent on Salaries and Benefits.
Answer:
Question 4 (2 Points): For 2020 Total Revenue what percentage was from Local (Real Property Tax Levy) Revenue.
Answer:
Question 5 (5 Points): Based on information provided in the Expenditure, Revenue and Debt tabs please prepare the estim
Note: Enrollment projected to increase by 10% to 88,000 in 2021. With Grades K-6 growing to 47,000 and Grades 7-12 growin
Assume Teacher/Student, Principal/Teacher, Vice Principal/Teacher and Bus Driver/Student ratios stay the same. All other sta
Thus, number of Teachers, Principals, Vice Principals and Bus Drivers need to increase in 2021, while all other staff positions d
Assume Management Salaries remain the same level as 2020.
Assume Teacher Salaries increase based on Collective Bargaining agreement.
Assume Support Salaries remain the same level as 2020.
Assume Benefits remain at 30% of Salaries.
Assume that Materials and Supplies formula on Expenditure tab remains the same as 2020.
Capital Outlay is only for buses according to schedule on Expenditure tab, which is also defined as pay as you go spending on
Capital Outlay does not include spending on buildings, maintenance and issuance costs on the Debt tab for the purposes of th
Building Lease is according to schedule on Debt tab.
Debt Service is according to schedule on Debt tab.
Answer:
($USD)
Management Salaries
Management Benefits
Teacher Salaries
Teacher Benefits
Support Salaries
Support Benefits
Materials and Supplies
Capital Outlay
Building Lease
Debt Service
Total Expenditure
2021 Amount
Question 6 (2 Points): What is the percentage change in Total Expenditure from 2020 to 2021 for the NPSD.
Answer:
Question 7 (4 Points): Assuming enrollment grows to 88,000 in 2021, please estimate RNP Funding for NPSD in 2021.
Assume 9% of NPSD students are in Special Education in 2021.
Assume RNP Special Education Aid Funding Formula remains the same.
Assume 55% of NPSD students are eligible for Child Nutrition Aid in 2021.
Assume RNP Child Nutrition Aid Funding Formula remains the same.
Answer:
($USD)
RNP Special Education Aid
RNP Child Nutrition Aid
Total RNP Funding
2021 Amount
Question 8 (4 Points): Assuming enrollment grows to 88,000 in 2021, please estimate SE District Funding for NPSD in 2021.
Assume SE District General Operating Aid Funding Formula remains the same.
Assume SE District Building Aid Funding Formula remains the same.
Assume the SE District Transportation Aid Funding Formula remains the same.
Assume assessed value next to port grows by 10% over 2020 level, assessed value not next to port remains the same as 2020
Answer:
($USD)
SE District General Operating Aid
SE District Building Aid
SE District Transportation Aid
Total SE District Funding
2021 Amount
Question 9 (5 Points): Assuming enrollment grows to 88,000 in 2021, assessed value next to port grows by 10%, assessed v
using estimates for SE District Funding, RNP Funding and 2021 Expenditure levels, please calculate property tax rate and pr
Make sure 2021 Total Revenue perfectly equals 2021 Total Expenditure.
Note: Please include 2020 Net Fund Balance as additional revenue in 2021.
Answer:
Total Assessed Value
2021 NPSD
($USD)
Net Fund Balance from 2020
Real Property Tax Levy
SE District General Operating Aid
SE District Building Aid
SE District Transportation Aid
RNP Special Education Aid
RNP Child Nutrition Aid
Total Revenue
2021 Amount
Question 10 (4 Points): Unfortunately, the proposed property tax rate and property tax levy necessary to balance the budg
To appease the voters the five member School Board created a new property tax class for the assessed value next to port.
Now, assessed value next to the port will have a $4 Tax Rate per $1,000. Using your answers from Question 9 (all assumpt
please provide the new tax rates and tax levies generated from the assessed value next to port and from the assessed valu
Answer:
Total Assessed Value
2021 NPSD Next to Port
2021 NPSD Not Next to Port
Total
Question 11 (4 Points): In reviewing an invoice from the vendor for the purchase of 900 buses it was determined that NPSD
The NPSD under SE District law is tax exempt because it is a government entity. This error was identified after the first pay
What is the amount of reimbursement in 2020 that the NPSD is eligible for from the vendor and what is the actual remaini
Answer:
Reimbursement due in 2020
Actual payment due in 2021
Question 12 (2 Points): On May 1, 2021, the SE District adopted new Charter School legislation at the beginning of its fiscal
On July 1, 2021, the SE District approved a 1,000 student Charter School named New Arctic School located within the NPSD
Under the Charter School legislation all Charter Schools are funded by the school district where they are located at that sch
The school district must pay the Charter School the entire amount due one month before the Charter School opens and at t
However, the Charter School is responsible for solely funding its school building needs. The school district is only required
Please calculate the total funding due to the New Arctic School from the NPSD in 2021.
Assume 2021 NPSD revenue and expenditure still based on enrollment of 88,000.
Answer:
Question 13 (2 Points): Assuming that the New Arctic School is paid the full amount due from the NPSD on August 1, 2021
Please provide Yes or No answer and explain answer. Please calculate new difference, if any, between Total Revenue and
Assume 2021 NPSD revenue and expenditure still based on enrollment of 88,000.
Answer:
Budget balanced (Yes or No)
Calculate new difference, if any, between Total Revenue and Total Expenditure of NPSD in 2021 after payme …
Purchase answer to see full
attachment