This is Public sector accounting, I need help to prepare for budget. I will use the tutors work for my knowledge not submit it as my own work.
Group Case ACCT 3352 100 Marks Note: This case has two sections. Read the required carefully and answer the questions that follow. Section ! 12 Marks Q1. Many jurisdictions in Canada have enacted balanced budget legislation. What is a balanced budget legislation? Identify two advantages and two disadvantages of this type of legislation taking examples from two different provinces. Section I 88 Marks You have recently graduated and earned your BBA with emphasis in Accounting from NAIT. You have been hired by the Town of New Sampleford on a temporary position to cover for their budget analyst who is away on a temporary leave. Prior to leaving, the budget analyst was working on 3 year operating budget and 5 year capital budget plan as per the provincial legislation. You have been provided the information which the budget analyst was using to prepare the budget plan as well as the excel file started by the analyst. As you go through the information and the excel file you identify some issues. There were some areas that were not incorporated by the budget analyst in the operating and capital budget plan excel file. Look at the excerpts of issues given below. These issues have been provided to you from the Financial Planning exercise document. You are required to read the Financial Planning Exercise document in conjunction with the case document. Use this along with the case information to update the excel file. Provide your assumptions and explanations in the space provided in the excel file. The excel file contains four tabs. 1. The first tab contains costing calculation for existing employees. This tab can be used as the basis for calculating the salaries and benefits of new employees. 2. The second tab is the operating budget worksheet that was started by the previous budget analyst who is away on temporary leave. Working in group you are required to update the missing information to complete the operating budget worksheet. 3. The third tab is the 5 year capital plan worksheet that was prepared by the previous analyst but no work has been done so far. You have to complete the 2020 annual budget and the 5 year (2021 to 2025) projected capital budget. 4. The fourth tab contains information about the employees such as classification, cost center, Union, status, stat pay and vacation pay etc. Notes: 1. All costs increase annually by 1.5% unless otherwise specified. 2. The Fiscal year runs from Jan 1" to Dec 31" . 3. CAO is Chief Administrative Officer.Group Case ACCT 3352 100 Marks Issue # 1 - Inter-municipal Development Plan (IDP) 20 Marks Intermunicipal Development Plan (IDP]: To date the region has not developed on IDP, Council noted that with the economy poised to rebound. now is the time to ensure the region grows in a coordinated and thoughtful manner. The municipalities will engage a consultant to assist with the development of an IDP. The budget analyst is required to calculate how much it will cost to hire the services of a consultant for the development of an IDP. The development must happen in the fiscal year 2020. The project plan shows the consultant will need approximately 2 months to deliver the IDP (Assume working 5 days a week). In the first month there will be consultations, interviews and priority setting with other regional municipal governments and will require full time commitment (that is working & hrs. per day)- The first two weeks of the second month will be spent in discussing information derived from the data collected in the first month, creating consensus and draft reports for council review. This exercise is expected to require 6 hrs. per day. The last two weeks will require incorporation of feedback received from council review, preparation of the final report and public presentation during council meeting. This exercise is expected to require 5 hrs. per day. The average consultant rate is $225 per hour. Note that contractors do not receive any stat pay, vacation pay or benefits. After development, the consultant may be hired on full time for period of 1 year in order to implement the IDP? The budget analyst was asked to do some cost/benefit considerations of paying a consultant vs. hiring in house. The average pay rate for an in-house consultant is $35 an hour. Issue # 2 - Other Regional Services 20 Marks Other Regional Services: A shared bylaw officer and consolidated development services are under consideration. The bylaw officer was agreed upon by all parties; the town's share of the annual costs is $25,000. You investigate and analyze the statistics of the revenue raised from bylaw tickets in a town of similar size and population as Town of New Sampleford. In your review you find out that the bylaw ticket revenue in those towns has been approximately $32,000, $28,000 and $22,000 in 2017, 2018 and 2019. It is expected that the bylaw ticket revenue will be high initially in 2021 but decline by 12.5% in 2022 and by 21.43% in 2023. The town is also considering setting up a photo radar system and has reached out to the budget analyst to provide an analysis on all costs and then compare this with having a bylaw officer. Once the analyst has done the cost analysis, analyst must submit a summary of pros and cons of each along with a recommendation that would be discussed at the next council meeting.Group Case ACCT 3352 100 Marks Sampleburg has also expressed interest in contracting with the town for financial services. The town has agreed to provide financial services to the village for an annual fee of $15,000. Both the bylaw officer and the financial services contracts will start in 2021. The CAO believes the current staff complement can handle the workload as a second position was recently approved. Issue # 3 - Council and Administration 6 Marks The council chamber is old and needs some repair. The repair is estimated to cost $25,000 and will be done in the year 2023. The photocopier in the administration office is near its useful life after which it will be written off in 2022 and a new photocopier will be purchased. The cost of the new photocopier is ascertained to be $7,000. Municipality is planning to purchase a new asset management software in 2020. The asset management software was costed to be $150,000 based on the requirements set up by the department. The IT department of the town has mentioned that there is a need to buy three file servers because the existing servers will not have vendor support due to end of useful life by 2023. The financial advisor supporting the IT infrastructure needs has costed that one file server will cost about $10,000 and the costing included hardware purchase, set up and licensing cost. The planned obsolescence of computer equipment (laptops and desktops) is set to happen in 2022 and 2025 at which time, the old computer equipment will be replaced by new ones. The estimated cost of replacement in 2022 is about $15,000 and $20,000 in 2025. Issue # 4 - Public Works Department 4 Marks The needs of Public Works Department are given below: Year Asset Budget 2024 Pickup truck $50,000 2022 Shop Energy Efficiency Upgrades $61,000 2023 Grader $285,000 2025 Public Works roof $100,000 Issue #5 -Streets and Water/Wastewater 14 Marks The planning document reveals that 50" street paving will be done in 2020 and is budgeted to be about $200,000. This will be followed by 50" Avenue paving. Assume that each year the cost of pavement will go up by $25,000. The Street and avenue pavement will happen in alternate years starting with 50*h street pavement. The Town of New Sampleford has the following streets and avenues that need pavement. 50" street and 50" avenue 51" street and 51" avenue 49" street and 49 avenueGroup Case ACCT 3352 100 Marks The street paving is scheduled to be done in the same year as the water line repair. The upgrade of the 50" Street water line will happen in 2020 and is budgeted to be around $75,000 followed by the upgrade of 50" Avenue water line in 2020 and so on and so forth. Each year the cost of repair for the waterline will go up by 10%. The street and avenue water line upgrades will happen in alternate years to cause minimum disruption. The water reservoir construction has been budgeted to be $1,500,000 and will begin in 2024.Lagoon dredging in 2021 has been costed to be $250,000 Issue # 6 - Recreation and Cultural Centre Marks 22 The repair of the curling link has been costed out to be $800,000 and is scheduled to happen in 2021. Based on the feedback received from the residents, it has been proposed to have one part time receptionist working from 5pm to 9pm on Mondays to Friday and from 5pm to 7pm on weekends and public holidays. It is also been proposed that one casual receptionist is available to cover the front desk if the part time employee calls in sick or takes a day off. Anticipated start date will be March 1" 2021. The following employees will also be added to the recreation center to support the extended times. 2 part time (0.50 FTE) and one casual (0.08 FTE) Fitness Trainer at the gym. Anticipated start date will be March 15th, 2021. 2 part time (0.50FTE) and one casual (0.08 FTE) Life guard in the swimming area. Anticipated start date will be March 15, 2021. A change in the shift of the security personnel was proposed. The first shift would begin from 6am and end at 2pm. The second shift would start from 2pm and end at 10 pm. Each shift would have 2 security personnel. The chief security officer has put forward a need to hire full time 2.0 FTE Security Personnel to cover the extended hours. Anticipated change in the shift will happen on Feb 1", 2021. Laundry services contract will be negotiated with the existing vendor. It is estimated that there will be 20% increase in used towels as many people would like to use the sauna after a good work out at the gym. The budgeted expense in the Laundry category rolled up into Contracted and General Services for 2019 was $25,000 and the actual for 2019 was $26,320. Based on the usage it is estimated that laundry expense will go up by 12%%. The new contract will come into place on Jan 1", 2021. Contracted and General Services (including laundry and cleaning services) was budgeted for $156,000 and the actual expense was $157,650 for 2019. Due to the proposed extended hours the materials, supplies and goods cost will go up by 15%. The budgeted cost for 2019 is $24,000 and the actual for 2019 is $26,500. The budget analyst has been asked to prorate the costs effective Feb 1", 2021 when the extended hours will come into effect. Using the Costing and Data tab of the excel spreadsheet provided cost out the following and update the Operations Budget Plan under Recreation and Cultural Centre: 1. Salaries, Wages and Benefits 2. Contracted and General ServicesGroup Case ACCT 3352 100 Marks 3. Materials, Goods and Supplies You have been provided the salaries and benefits information about the increase in employees due to extended hours of the recreation center below. Salaries and Benefits details Description Receptionist Receptionist Fitness Fitness Life Guard Life Guard Security Trainer Trainer Personnel Classification 132 X32 FIND1 FIND1 LGO1 LGO1 SECO1 Cost Centre RECCEN RECCEN GYMN GYM SWMI SWMP RECCEN Union AUPE AU AUPE AU FPU FPU LA LA UFCW Status Part Time(PT) Casual C Part Time Casual Part time Casual Full Time FTE .52 10 50 50 1.00 Rate $18.00 $16.00 $20.00 $20.00 $16.93 $16.20 $15.74 Annual Hours 2015 2015 2015 2015 2015 2015 2015 Statutory Pay 4.60% 4.60% 4.609 4.60 4.60% 4. 602 Vacation Pay 8.00% Total Benefit % | 18% 18. 006 18.009% 10.00%% Note: Supplies are budgeted to be $500 per 1.00 FTE (FT) Review the Revenue section under the Recreation and Cultural Centre portfolio and ensure that the revenue is budgeted correctly. Assume that information given in the spreadsheet for each revenue category is correct. Update the revenue budget based on the inflation rate that has been provided in the excel spreadsheet wherever required. Current staffing (applicable to the 2019 year) is as below. 1 FT 1.00 FTE receptionist 2 FT 1.00 FTE Fitness Trainer and one PT, .80 FTE Fitness Trainer 2 FT 1.00 FTE Life Guards and 4 PT .50 FTE Life Guards 2 FT, 1.00 FTE Security personnel, one PT .80 FTE and one .20 FTE Casual Security Personnel. Note: Typically number of working days and hours are established for the purpose of calculating the semi annualized budget of salaries and benefits. However for the sake of simplicity and for the purpose of this case students can prorate it on a monthly basis to calculate the budget. If the start date is between 1" and 15" of a month (including 15") calculate it for the entire month. Provide your assumptions in the notes column for your supervisor to review and approve.Group Case ACCT 3352 100 Marks Issue # 7 General Government 2 Marks There has been no change in the expenses side of General Government except for the contracted and general services. As well as no changes in the revenue section of General Government portfolio with the exception of "Other revenue" End
