Indexing (Estimating) Park Maintenance Costs
Purpose:
Predicting the costs associated with maintenance need not be a difficult or complicated task, but it will require some accurate record-keeping to determine. The cost of mowing an acre of grass, for example, can be estimated by knowing what equipment is needed; the time required for mowing the grass; the hourly wage of the machine operators; the cost of employee benefits; the time required to load, transport, unload, and maintain the equipment, etc. Sometimes, costs of maintenance may be related to the number of visitors or users of a facility—generally, the more who come, the more you need to budget for maintenance. For example, it may take 1000 man-hours of maintenance for 100,000 visitors to a park or facility. If visitation doubles, then you may need to budget for 2,000 man-hours. Knowing the average cost per man-hour permits you to determine the additional budgetary needs.
Product:
You will submit your workbook (linked worksheets) as an Excel file (.xlsx) as well as a report (either in Word or as a PDF) in which you present your findings. Your audience for the report will be a recreation and park advisory board which has been asked to report its findings to a municipal city council.
Explanation (Example—do not use these figures in the final analysis):
Central Park in Francisville had 35,000 visitors in 2010. Additional acreage is being purchased, new facilities are planned (more ball fields, courts, pools, trails, etc.) so that by 2015 the park will accommodate 150,000 visitors. Assuming the maintenance budget for 2010 was $63,000, how much should be budgeted for the year 2015?
Estimate #1: Index budget to # of visitors
This approach assumes that the cost of maintenance is directly related to the number of visitors (not actually true since there will be maintenance costs even if there are no visitors); and that the value of the services purchased by a dollar in 2015 will be the same as that purchased today (highly improbable—because of inflation it will usually cost more).
$63,000 / 35,000 = $1.80/visitor
$1.80 * 150,000 = $270,000 to be budgeted for 2015 (in 2010 dollars)
Estimate #2: Minimum Threshold Maintenance
This previous approach could be improved by defining the minimum threshold maintenance requirements (with no visitors), subtracting that amount from the 2010 budget, and identifying the remainder of the maintenance costs on a per visitor basis.
Thus, if the minimum budget were $12,500, the cost of maintenance per visitor would be calculated as:
($63,000 - $12,500) / 35,000 = $1.44/visitor
The 2015- budget would then be:
$12,500 + ($1.44 * 150,000) = $228,500 (again, in 2010 dollars)
This refinement yields a result that is lower by $41,500 (roughly 15 %).
You may further refine that estimate by factoring in the expected inflation rate.
Problem or Application:
Develop a spreadsheet template which will allow you to predict maintenance costs for the Francisville Festival Grounds for the next ten years. The anticipated number of visitors for 2019is 12,000, but improvements to parking and programs are expected to yield increases of from 5% (low estimate) to 12.5% (high estimate) per year. Your template should show calculated visitation for each of the next ten years (2019 to 2028) at both low and high estimates.
Assume that the budgeted amount for maintenance of the fairgrounds is $92,000 for 2019. Using Method #1 (above) what will be the annual low and high estimates for budgets for maintenance for each of next ten years (display this on your template). Assume that we will have annual inflation in costs of 5% per year.
As part of the same template, do a second set of calculations that will factor in (using Method #2, above) a minimum threshold (or lock-down level) of $34,000 in maintenance costs for the first year. Display the low and high estimates for each of the next ten years again with an annual inflation in costs of 5.25% per year applied to both minimum maintenance costs and cost per visitor.
Note: In both scenarios, the number of visitors will be increasing by 5% or 12.5% per year, while costs will also increase by 5.25% per year for labor, materials, supplies, etc.
What is the difference in the spread between low and high estimates for the year 2028 for each of the two differing methods of calculation, and the differences for both low and high estimates between the two types of calculations?
In building a template in Excel, you should have defined input areas for the information given, then reference your cell formulas (with both absolute and relative references) to those input variables. Using a set of linked worksheets within the template, you can post summary information on the first sheet and calculations on the subsequent ones.
You will submit your completed Excel workbook as well as the report (with a cover letter) to the assignment on eCampus.