Slide 1 Revenue Forecasting In Local Government: Hitting .
Revenue Forecasting in Local GovernmentSlide 1Revenue Forecasting inLocal Government:Hitting the Bulls EyeDale RoenigkSlide 2North Carolina requires local governmentsto adopt balanced budgetsExpendituresSlide 3Slide 4RevenuesBudget officers have the responsibility toassemble the budget including expensesand revenuesOur challenge is to forecast revenuesin the face of uncertainty andcomplexity.ExpendituresSchool of GovernmentDale RoenigkKey objectives for this session.1. Understand the importance and difficulties ofrevenue estimation2. Learn six best practices for forecasting andrevenue estimation3. Learn different forecasting methods and thetradeoffs between themTo balance the budget, we need to know bothexpenditures and revenues. We need to projectrevenues to understand the level of fundingavailable for services and capital spending.Preparing revenue projections also helps usunderstand: trends, the sensitivity of different assumptions,and what effect those factors a localgovernment can control – tax rates andfees – can have.The responsibility for preparing the balancedbudget rests with the budget officer.Uncertainty that cannot be controlled. Expensesare relatively within control. Revenues oftendepend on outside forces over which there is littlecontrol. There is also complexity due to manydifferent revenue sources and different estimationtechniques. Consequences can be large if you getit wrong.Revenues1
Revenue Forecasting in Local GovernmentSlide 5Slide 6Dale RoenigkWe want revenue forecasts that areon the mark leading to balancedbudgets.We will learn a set of best practices andsimple techniques to help us hit the target .The ultimate objective is for our revenue forecaststo be consistent and as accurate as possible. ReadyAimFireSlide 7READYTo get ready, weneed to understandour revenue sourcesto know what we areaiming at.Slide 8Ready – Knowing our revenue sourcesand how they work.Aim – Knowing what estimationtechniques and methods to use.Fire – Learning what to do with estimatesover time.NC Law requires us to makereasonable estimates.“Estimated revenue shall include only thoserevenues reasonably expected to be realized inthe budget year, including amounts to berealized from collections of taxes levied inprior fiscal years.”First step in revenue estimation, know yourrevenues. Most local governments have manysources of revenues. These revenues aregenerated in different ways and there is a need toknow them and how they work if we hope to hitthe target. We have a responsibility to bereasonable and accurate.By statute, local governments must makereasonable estimates of expected revenues. Theexperience of revenue realizations in past years isa good benchmark for “reasonable”. In practicemost local government jurisdictions in NorthCarolina tend to be conservative for a variety ofreasons.(G.S. 159-13(b)(7)School of Government2
Revenue Forecasting in Local GovernmentSlide 9Dale RoenigkThere are consequences on both sidesof missing revenue estimates.Overestimate Optimistic “Best”Guess ConservativeUnderestimate Overestimate– Insufficient Revenues– Mid-year cutbacks– Tapping into fund balanceThere are important consequences for missing themark in both directions. The optimist will overestimate the actualrevenues. The conservative will underestimate. Both positions have implications. Underestimate– Excess Revenues– Needless Tax Hikes– Cuts or Limits on Services atbudget timeSlide 10To improve our forecasting, we should have amodel for the factors driving revenue.Developing a basic understanding of what drivesrevenues for local governments is an importantpart of knowing your revenues. What influencesthe amounts of revenues that are collected?Slide 11Every revenue source has a basic setof factors which determines itsamountNot a complete model but population andeconomy key drivers. Population is not only totalnumbers but may also include otherdemographics such as persons per household.Economy is somewhat cyclical. Key variables arethe tax base or fee base with tax rates andcollection rates being important policy andadministrative variables.PopulationTax Base(Fee Base)Tax Rate(Fee licy ChoiceSlide 12AdministrationThe tax base for many revenue sourcesvaries with the economic cycle.As economygrows, itfluctuates or goesthrough cycles.EconomyEconomySales Tax RevenueSales Tax RevenuePropertyTax RevenueEconomySchool of GovernmentRevenues tend to vary with fluctuations in theeconomy. Some will have greater swings andothers will be less sensitive.Elasticity 1 meansthe tax base is morevariable than theeconomyElasticity 1 meansthe tax base is lessvariable than theeconomy3
Revenue Forecasting in Local GovernmentSlide 13Slide 14We need to first know where ourrevenue comes from.Best Practice #1 is to create and use arevenue manualSale TaxParks and Recreation FeesIntergovernmentalRevenue ManualProperty TaxProperty TaxRevenue DescriptionSample text describing revenue source as authorizedLegal AuthorizationGeneral Statute 123-45, Ordinance 439FundGeneral Fund, Account 678-098-123-009SourceMunicipal Property OwnersFee ScheduleCollected AnnuallyMethod of PaymentPayment made to County Tax oryFrequency of Colleciton Collected AnnuallyExemptionsExemption for Homestead Act qualified property owners.Revenue CollectorCollected by County Tax CollectionsGraphSlide 15Dale RoenigkWe need to know how revenue is generated.Many revenue sources in most local governments(40-60). Focus most attention on largest sources.What laws and rules, state and local, apply?What amounts of revenue are generated?What factors drive the revenue source?A comprehensive document containing criticalinformation about each of the revenue sources ina jurisdiction. Revenue Manual good for electedofficials, new managers, new finance/budgetofficers, everyone.Possible items that could go into a revenuemanual. Description of the Revenue Source Authority (Statutes, Ordinances) Use and Restrictions Rates (Current and Changes) Possible Exemptions Special Events in History Drivers of the Revenue Forecasting Method and Assumptions Multiple Years of Data GraphShould be ComprehensiveMay need to implement over time, focus first onbiggest revenue sourcesStart off with a simple spreadsheet matrixRevenuemanualexamples atend ofslides.School of Government4
Revenue Forecasting in Local GovernmentSlide 16Dale RoenigkSecond step in revenue estimation is usingmethods or techniques. We should useappropriate techniques to help us be as accurateas possible. We have in our spreadsheets, tools tohelp us do this better.AIMWe need a set oftools or methods formaking revenueestimations.Slide 17Estimation methods can be classifiedas qualitative or quantitativeQualitativeQuantitativeSlide 18BestEstimateQualitative methods relay on some expertjudgment to make revenue estimatesWill more sales tax revenuebring me inner peace?School of GovernmentGoal of estimation methods should beAccuracyManageable effort with time andresourcesTransparent, understood by othersQualitative and Quantitative MethodsBlend of bothQuantitative and Qualitative are notnecessarily different domainsCan be combined usefully togetherMost employ some of bothSurveys show expert judgment is most commonmethod, particularly for smallerjurisdictions.Examples of Expert JudgmentLocal Jurisdiction Service Experts Parks and Recreation Departmentestimation of users Utility Director estimate of revenuesFinance/Budget Officer ExpertiseOutside Financial Experts State Economists-Office of FiscalResearch Other Economists, Finance Experts, LocalBusiness Experts5
Revenue Forecasting in Local GovernmentSlide 19Slide 20Slide 21Expert Judgment has pluses andminuses.Quantitative Methods rely on thenumbers to produce estimates.There are two types of quantitativemodels: trend and causal models. Trend ModelsDale RoenigkPlusesExpert Judgment based on experience can begood. Particularly critical when lackingdata or when circumstances changesignificantly.Cost is low and this method requires fewresources.MinusesDanger of relying on expert judgment includebiases, selective perceptions, wishfulthinking, anchoring, political pressure,inconsistency, inability to replicate byothers.Generally easy to present but we may notunderstand the basis for the judgment.Research suggests expert judgment may besomewhat less accurate than otherestimation methods in most cases.Quantitative methods depend on using numbersto estimate revenues. Ideally should have severalyears of data to do well. Multiple techniques ofvarying complexity and resource requirements.Trend Models are fundamentally historical,extending the trend.Causal models, may be based on historicalpatterns, but do not need to be. Require factorsto be identified.–Incremental–Moving Average–Time Series-SimpleRegression Causal ModelsSchool of Government6
Revenue Forecasting in Local GovernmentSlide 22We need to decide are we projectingthe base or the collected revenue.Tax Base(Fee Base)Slide 23Dale RoenigkTax Rate(Fee al models for revenueestimation add an increment to prioryears to produce the estimate.Expectedt 1 Actualt incrementSlide 24Common incremental models1.2.3.4. Expectedt 1 Actualt(last year’s revenue) Expectedt 1 Actualt increment (t vs. t-1) or %(last year’s increment) Expectedt 1 Actualt increment (average over x years) or %(average increment) Expectedt 1 Actualt increment (max/min over x years) or %(largest/smallest increment)Examples using data in ExcelSlide 25Incremental Models say here was the actual, letsadjust it by some increment. The increment canbe or %, varying levels of change, using varyingamounts of history. Simple, easy to understand,and easy to adjust. Can be “tuned” with expertjudgment. Most commonly used quantitativemodels. Only modest data needed, accuracyuncertain during times of significant change.IncrementYear tYear t 1Estimation can be done on either the tax base orthe collected revenue amounts. However,recognize that if tax rates and collection efficiencyhas changed over time, this will affect theaccuracy of estimates based directly on actualcollected revenues.Moving average models use averages ofprior years which “move” over time.Uses average of last “n” years. The average “moves”as it goes forward each year.Expectedt 1 Average (Actualt, Actualt-1, ,Actualt-n-1 )Use Historical Data to Determine IncrementIncrement based on Absolute Dollar Change orPercentage Change.Increment may be zero (no change) or based onaverages, minimum or maximum change, or lastchangeMay use expert judgment to select or adjustincrementIncremental models are simple and easilyunderstood.Easy to calculate, only modest data needed.More complex moving averages are possible butcalculation effort increases.Moving Averages will lag strongly trending data.For calculations of monthly data, seasonaladjustments may be necessary.AverageYear t 1Year tYear t-1Year t-2Year t-n-1Examples using data in ExcelSchool of Government7
Revenue Forecasting in Local GovernmentSlide 26Time series models use regression toproject estimates forwardUses a simple statistical regression to estimate an equationExpectedt intercept (slope * year)Dale RoenigkNow easy to do with tools in Excel.High variation or lack of trend may produceimprecise estimates.Regressions may be “black box” to some andharder to sell.4.54.03.53.02.52.02000200520102015– The slope is the estimated increment for a single year– Regression estimates are usually linear but can take other formsExamples using data in ExcelSlide 27Causal models are complex statisticalmethods to estimate revenueStatistical deterministic models using regression withone or more factors other than time deemed tocause or explain expected levels of revenue.Expectedt intercept b1x1 b2x2 b3x3 Factors may be economic, demographic, or socialin nature. Complex models don’t necessarily workbetter. Models require some expertise to develop andlots of data to estimate accurately. Also requirefuture estimates of the causal factors to estimatethe future revenues. Good causal models can be accurate if strongrelationships are found. Surveys indicate limited use, particularly in smalljurisdictions or those not under fiscal stress.Slide 28To make our aim true, we need to decidewhat method to use and how best to usethese methods to make projections.Selecting which estimation method to usedepends on resources available includingexpertise, stability in revenue sources, andunderstanding of revenue sources.Slide 29Best Practice #2 is to select theappropriate method to use forestimation.Selecting a method depends heavily on theavailability of data, one’s resources or skills, andtime. Estimation is strongest when using acombination of quantitative methods withqualitative judgments to sharpen. Most tend toblend methods based on conditions.Comparing Estimation ow to ModerateMay be reasonablyhigh if expert is goodLowLow to Moderate asit depends on“expert”TrendModerate to highwhen stableLow toModerateModerate to HighCausalBest if good modelModerateto HighModerateDepends onComplexitySchool of Government8
Revenue Forecasting in Local GovernmentSlide 30Dale RoenigkBest Practice #3 is to test assumptionsfor forecasts.Of course it seems like Ishould cross the road,but I wonder .Slide 31Best Practice #4 is to project financesfor multiple Expenses0.1-0.1-0.5-1As part of the projection process, alternativeassumptions should be tested to understand therange of possibilities and sensitivity of results tokey assumptions. Examples might include whatshould be the growth rate in property values growth in health care costsAssumptions causing wider variation may warrantadditional analysis and discussion among decisionmakers. Consider best and worst case scenarios.To provide understanding of the future, revenueestimation should be extended for multiple yearsinto the future (3-5). When combined withprojection of expenses for the future, examiningthe balance can indicate future capability to fundservices and capital expenditures along with thepossible need for tax rate changes to maintain abalanced budget and healthy financial condition.-0.3 -0.42005 2006 2007 2008 2009 2010 2011 2012 2013 2014 Slide 32See Example Page for CarrboroThe third step for revenue estimation is what todo after the budget estimates have been made.The forecaster’s job is not over but needs toconsider monitoring accuracy of the forecastsboth for current needs and for improving theaccuracy of future projections.FIREAfter we havefired off ourrevenueestimates,our job is notdone.Slide 33Once we’ve done our budget forecast,our job is not over.BudgetForecastForecastUpdateSchool of GovernmentFutureForecastsThe revenue forecast that goes out with thebudget is not the end of the process.We need learning feedback loops for the shortand long term. Short term (within the next year)we need to monitor and update our forecaststhroughout the year. If forecasts are coming inlow or high, adjustments should be made toupdate the budget. Longer term, we also need tolearn from our annual forecasts to improve ouraim for the future.9
Revenue Forecasting in Local GovernmentSlide 34Slide 35Dale RoenigkIn the next fiscal year we need tomonitor forecasts to determine ifrevenue projections are on the mark.Best Practice #5 is to track actualrevenues against estimates.Variance of RevenuesActual to Forecast108642PercentVariance from 0Actual-2-4-6-8-10Revenue 1Revenue 2We need to monitor revenue forecasts as the newbudget year unfolds. Forecasts are just estimates,they are not guarantees. If we monitor theactuals compared to the forecast, we candetermine early if there appear to be errors whichwould leave more time for adjustments.As the budgeted year unfolds, actual revenuesreceived should be tracked against projections.Adjustments to budget can be made if projectionsprove inaccurate. Need to adjust for seasonalityfor monthly revenue numbers such as sales tax orwhen taxes are paid. As the year progresses, ourforecast estimate will “funnel” into the actual ifwe adjust as we go.Accuracy should funneldown as year progresses.1 2 3 4 5 6 7 8 9 10 11 12Month in Fiscal yearSlide 36Slide 37We need to assess the accuracy of ourforecasts and learn what works.Best Practice #6 is to verify accuracyof estimates against actual resultsComparison of Actual Revenuesto Budgeted RevenuesGeneral Fund10%8%7.3%6.5%5.8%6%3.6%4%2%3.4%3.6%20042005If we fail to study the accuracy of our forecasts,the likelihood of improvement is very low.Experience does not produce wisdom. We haveto make a conscious effort to learn.Annual estimates should be compared to actualsto determine accuracy. Over time, knowledgeabout the precision of estimates for differentrevenue sources and different methods can beused to increase accuracy. Determine aconfidence band for estimates. Inherentconservatism may mean estimates are generallylow.-1.4%0%-2%1999School of Government200020012002200310
Revenue Forecasting in Local GovernmentSlide 38ReadyAimBest Practices1.Revenue Manual2.Use appropriate estimationmethodsTest different assumptionsProject multiple years3.4.5.FireSlide 396.Dale RoenigkThree steps and six best practices. Goodforecasting should be based on a consideredapproach and attention to good practices whichshould lead to better forecasts over time.Update estimates over theyear for adjustmentVerify accuracy of estimatesfor future projectionsRevenue estimation is science and art Uncertainty and variability cannot beeliminated. A degree of conservatism is warrantedparticularly for more volatile revenue sources. Adequate reserves can help with theinevitable misses. Adopt best practices where possible.Uncertainty still reigns. There is a need tounderstand that no forecast will be perfect andplanning for adverse circumstances will berequired.Some additional references National Advisory Council on State and Local Budgeting, Recommended Budget Practices,Government Finance Officers Association, 1998. http://www.gfoa.org/services/nacslb/ Barry Blom and Salomon A. Guajardo, Revenue Analysis and Forecasting, Government FinanceOfficers Association, 2001. Salomon A. Guajardo and Rowan Miranda, An Elected Official’s Guide to Revenue Forecasting,Government Finance Officers Association, 2000. Thomas A. Garrett and John C. Leatherman, An Introduction to State and Local Public Finance,Part 2-Selected Applications in Public Finance: Revenue Forecasting, rfour.htm North Carolina Local Government Budget Association. Has a udget/index.html North Carolina Government Finance Officers Association. Has a cgfoa/index.htmlSchool of Government11
Practical Analytic Techniques Using Excel“What If Analysis in Excel” -- Death and Tax Rates in Blue HeavenIt is time to balance the budget in the Town of Blue Heaven. This year’s expenditures were 10 million and are expected to grow 5% a year forthe next five years. The current assessed property tax base is 2.5 billion, the town collects 97% of theassessed property taxes and the current tax rate is 41.3 cents per 100 of valuation.The property tax base is expected to grow 3% a year for the next five years. There are no other revenue sources. The town collected 10,015,250 this year so it has a small surplus equal to less thanone-sixth of one percent. The town board says they want to keep the same tax rate for the next three years.Given these assumptions, how would you decide:1. What property tax rate is needed to:a. Balance the budget in Year 1?b. Balance the cumulative budget over Years 1-3 assuming you have to keep thesame tax rate for all three years?2. What would the effect be of different constant revenue growth rates ranging from 0% to6%?3. What would be the effects of different revenue growth rates ranging from 0% to 6% anddifferent expenditure growth rates ranging from 4% to 10%?What If Spreadsheet ExampleCurrentAssessed Property 2,500,000,000GrowthTax Rate per 100Collection RateYear 1 2,575,000,0003% 0.4130 97.0%Year 2 2,652,250,000Year 3 2,731,817,5003%0.4130 97.0%0.4130 97.0%5 year Budget 7,959,067,5003%0.413097.0%Revenues 10,015,250 10,315,708 10,625,179 10,943,934 31,884,820Expenses 10,000,000 10,500,000 11,025,000 11,576,250 33,101,250GrowthBalancePct of ExpensesDale RoenigkNovember 3, 20155% 15,2500.2%- 184,293-1.8%5%5%- 399,821- 632,316-3.8%-5.8%- 1,216,430-3.8%UNC School of GovernmentPage 1
Practical Analytic Techniques Using Excel“What If” Analysis with ExcelTOOLWhat it doesExampleWhat you needGOAL SEEK Goal Seek allows you towork backwards andfind the necessarynumber to produce afinal answer you want.“What does the tax rateneed to be to balance thebudget”A target or goal cell(must be a formula)Allows you to testmultiple values for 1 or2 variables all at once toanswer “what if”questions.“What effect does varyinggrowth inexpenditures from 3% to10% have onthe budget?”One or two changingcells (must be actualnumbers)DATATABLESSCENARIOS Scenarios allow you tosave different versionsof your spreadsheet soyou can put togethersets of assumptions andsave them so that youcan easily movebetween scenarios aswell as see the effect ofdifferent scenarios inone table.“How do mydifferentbudgetscenarioscompare?”A changing cell thataffects the target cellindirectly or directly(must be an actualnumber)One or more outcomecells to show the effectof varying the changingcells (should be formula) Changing cells that youmay substitute withdifferent values Results cells that showparticular outcomes youwant to see.Where to find these tools:In Excel 2007 to 2013 – Under “DATA”, “Data Tool”, “What If Analysis”In Excel 2003 or MAC Excel – Under “Tools”, “Goal Seek”, “Data Table”, or “Scenarios”Dale RoenigkNovember 3, 2015UNC School of GovernmentPage 2
Revenue Forecasting in Local Government Dale Roenigk School of Government 1 Slide 1 Revenue Forecasting in Local Government: Hitting the Bulls Eye . Examples using data in Excel Year t 1 Year t Year t-1 Year t-2 Year
Payroll Factor [Marianne Evans] Specific Industry Apportionment [Kelly Brown] Combined/Consolidated Return Issues [Richard Call] Latest Important Developments Slide 51 [Kelly Brown] Slide 8 - Slide 10 Slide 40 - Slide 44 Slide 45 - Slide 50 Slide 11-Slide 14 Slide 15 - Slide 26 Slide 27 - Slide 33 Slide 34 - Slide 39
San Francisco, California Los Angeles, California Orlando, Florida. Slide 3 What do we do? Slide 4. Slide 5. Slide 6. Slide 7. Slide 8. Slide 9. Slide 10. Slide 11. Slide 12. . IFMA: 2013. What is The Goal. Who or What is Steering the Ship? HIPAA HCAHPS LEED ASHE/FGI Slide 34. Slide 35
2011 report on revenue forecasting in the states produced in collaboration with The Pew Charitable Trusts. It supplements these data with additional data, including the results of a survey of state forecasting officials conducted by the Rockefeller Institute. In addition to examining how revenue forecasting errors have
Forecasting with R Nikolaos Kourentzesa,c, Fotios Petropoulosb,c aLancaster Centre for Forecasting, LUMS, Lancaster University, UK bCardi Business School, Cardi University, UK cForecasting Society, www.forsoc.net This document is supplementary material for the \Forecasting with R" workshop delivered at the International Symposium on Forecasting 2016 (ISF2016).
Importance of Forecasting Make informed business decisions Develop data-driven strategies Create proactive, not reactive, decision making 5 6. 4/28/2021 4 HR & Forecasting “Putting Forecasting in Focus” –SHRM article by Carolyn Hirschman Forecasting Strategic W
Introduction to Forecasting 1.1 Introduction What would happen if we could know more about the future? Forecasting is very important for: Business. Forecasting sales, prices, inventories, new entries. Finance. Forecasting financial risk, volatility forecasts. Stock prices? Economics. Unemplo
Although forecasting is a key business function, many organizations do not have a dedicated forecasting staff, or they may only have a small team. Therefore, a large degree of automation may be required to complete the forecasting process in the time available during each forecasting and planning cycle.
IELTS Academic Writing Task 2 Activity – teacher’s notes Description An activity to introduce Academic Writing task 2, involving task analysis, idea generation, essay planning and language activation. Students are then asked to write an essay and to analyse two sample scripts. Time required: 130 minutes (90–100 minutes for procedure 1-12. Follow up text analysis another 30–40 mins .
