Recovery Starts During Budget Season with Statistics and a Flair for Business

By Hugh Breckenridge Little, CPA

Click Here for Document Version

Yearning for that nonprofit organization or client to turnaround financially?  The word nonprofit, contrary to popular belief, does not infer a preference of having a deficit over a surplus.  Board members don’t want to hear that line as a reason to volunteer or contribute to annual or capital campaigns – they’re much too sophisticated.  Nonprofit means no ownership, no dividends and no one profits financially from the venture – it’s only a distinction, pure and simple.  Beyond convincing the skeptics otherwise, all organizations should be run in a business-like manner and have a surplus.   And there is no better time for the CPA to begin the resuscitation than budget season.  Sell it as a consulting engagement if desired or chat, respectfully, with the boss or an understanding board member.

Recommend a Smarter Budget

Budget season requires more than the documentation of revenues and expenses, as they are to occur in the upcoming fiscal year or years.  Put on your analytical, managerial hat and step into a CEOs shoes – it’s time to contribute your entire skill set and contribute more than just developing the next year’s typical spending plan.  Building a top-down or bottom-up budget can be done in several ways.  Upper management may have a top-down approach while operational or program managers create a bottom-up budget.  Use a legal pad, an electronic spreadsheet or networkable software - the tools chosen by the CPA depend upon the size of the client or organization and the level of detail desired.

CPAs should recommend to the Client a mix of the bottom-up method with use of statistics.  Ah, statistics.  Memories of having a root canal may first come to mind.  But wait, adding statistics to the recipe is easy and enlightening.  Tell your boss that statistical techniques add a measure of confidence to forecasts.  Most importantly, understand these techniques reduce variances (or catch accounting irregularities - maybe even an embezzlement or two) – and that means submitting fewer narratives to the board each month.  Pick up the defibrillator paddles  – CLEAR!

To begin, CPAs need ample history on every revenue and expense.  Obtain this information from prior financial reports or download it from accounting software in the form of an ASCII file to a spreadsheet.  A minimum of three years of information is necessary to use trend analysis, the statistics of our operating room.

Educate the Organization or Client on the Value of Statistics

Trend analysis for budgeting purposes is simply regression analysis using time periods.  It’s as easy as eyeballing three years of revenue or expense history to gauge where the organization is going.

Wish to spend a little more effort and be more precise, then sharpen that pencil (literally) and graph the organization’s last three years of history.  With dollars on the vertical axis and years on the horizontal axis, fit (as closely as possible) a straight line through the three points on the graph.  Exhibit 1, page 6 shows an elementary trend line.

Now add a fourth year (2003) to the horizontal axis.  Follow a line straight from 2003 to where it intersects with the trend line that you drew.  Draw a point.  Follow a straight line from that point to the vertical axis to determine the dollar amount projected for 2003.

Infuse Some Business Acumen – It’s Expected

But can the organization actually bring in that revenue or afford that expenditure just plotted?  If consulting a program manager, he will not know until the budget is constructed from the bottom-up.  For example, if the revenue item plotted is $30,000 in 2003 for workshop participants, does that equate to a manageable number of participants at a reasonable rate increase over 2002?  If the $30,000 revenue trend is much lower that the budget built, a business plan is in order.  Business as usual will neither suffice for the program manager nor the organization collectively.

When it’s mulled over, the organization is in a much better position.  The client’s knowledge about their budget has expanded.  Together the organization and CPA realize what is achievable and what is not from the use of statistics or trending.  Plus, the CPA now has an idea where to focus his or her future management efforts.

The same theory applies to larger organizations.  The only difference is the volume of work necessary for trending.  For example, to establish a trend analysis on three years of workshop revenue, the CPA would once again regress this information to the related time periods.  With larger organizations, convince your client to be even more sophisticated and use marketing efforts instead of time periods.  If 2000’s revenue was $15,000, 2001’s revenue was $20,000, and 2002 is projected to be $25,000, what would be the trend in revenues for the year 2003?  For a small organization, simply eyeball or plot it.  But for a larger organization, with hundreds if not thousands of accounts, a CPA needs to use a different tool.

Is Your Organization Trendy and Automated?

Use a Microsoft Excel (or Lotus123) spreadsheet or recommend that the client use one.  As in Exhibit 2, page 6, create columns headed with the years and rows of the revenue information.  The trend formula will go in the revenue cell under the column headed 2003.  The formula or function to paste is called TREND.  Excel’s definition of the TREND function is that it “returns values along a linear trend by fitting a straight line using the least squares method to the known values.”  In layman’s terms, it’s exactly like the trend analysis in the previous section – connecting the last three years with a straight line and extending it to the point of intersection with the year 2003.

The TREND formula returns a value of $30,000.  The CPA can now anticipate the workshop manager submitting $30,000 in budgeted revenues for the year 2003 – given that no extraneous factors come into play.  But how reliable or strong is this trend?  Remember, this is linear trending, not curvilinear.  (Excel provides other formulas and variations with higher levels of required expertise, e.g., polynomial trending.  See “Project future values and perform regression analysis” in Excel.)  Suppose the revenue history is not linear – for example, $15,000, $19,000 and $26,000.  How does the CPA measure the strength of such a trend for the year 2003?  Is it so weak that it will be materially different from the submission of the workshop manager?

A Financial Crystal Ball

Using the TREND formula, the trend for 2003 now would be $31,000.  To establish the strength of the trend we use the RSQ formula.  According to Excel, RSQ “returns the square of the Pearson product moment correlation coefficient through the given data points.”  Setting up this formula requires only the first two components of the TREND formula as demonstrated in Exhibit 3, page 6.  What’s important is the value the formula returns – the RSQ value is 0.98.  Guess what the prior RSQ was with the Trend of $30,000?  That’s right, it was 1.00.  Don’t forget, the goal is to have little to no budgetary variances.  To some degree, a CPA can predict the future with statistics.

An RSQ of 1.00 means that all the data points reside on a straight line.  With a 0.98 RSQ, historical points may or may not be on this straight line.  So, what RSQ value indicates that the CPA should ignore the trend analysis?  When does the CPA pitch statistics out the window and go with the workshop manager’s submission?  Several factors will contribute to the decision.  Generally, have faith with an RSQ of 0.75 or higher.  That’s a strong trend.  Anything less probably points to something that has happened in the past few years that affected your workshops, such as a strong economy, an accounting irregularity (to be investigated) or a popular workshop leader.  The closer to 1.00 the RSQ is, the more confidence can be had that the projection is accurate and the trend will continue.

Manage During Construction of the Budget

With a 0.98 RSQ and a TREND of $31,000 in workshop revenue, what is the acceptable range of a budget submission from the workshop manager?  Again, it depends, but an excellent guide is materiality.  Materiality begins in the 5 to 10 percent variance ranges.  Thus, the workshop manager could submit anything in the range of $29,450 to $32,550 and the submission wouldn’t be materially different from the TREND of $31,000.  Any submission beyond this range would require an explanation; for example, marketing efforts will be intensified, business plans will be adopted or a community competitor has emerged.

So, with trend analysis and RSQ evaluation, the organization can manage the budget even as it is being constructed.  With statistical confidence, the CPA can evaluate budget submissions based on predetermined trends and strengths.

Recommend an Annual Global Financial Exam

Trending can also help perform a quick, easy global examination of finances.  CPAs should use this exam annually in order to be a good steward of the client’s or organization’s funds and to ensure the success of the nonprofit’s mission and programs for years to come.

Using at least three years’ of history for every revenue and expense line item, trend each one out and calculate a surplus or deficit for 2003.  Disregard each line item’s trend strength at this point and simply calculate a surplus or deficit.  What conclusions should be drawn?  First compare the trended surplus or deficit to that of the prior year amounts.  Is 2003 a surplus?  A deficit?  Is it materially different?  Heck, run an RSQ on the historical surplus and/or deficit figures.  Is it a strong or weak trend?

If the trend from 2000 to 2003 is toward a deficit, your client or organization has a problem.  Conversely, a progression to a surplus is golden.  Regardless, the CPA needs to investigate and drill down into why.  For that deficit, lagging program revenues, compounding salary expenses, uncontrollable expenses and mature membership bases are typical culprits.  Other usual suspects are unsound business practices and a lack of internal controls, e.g., hard-copy timesheets require multiple verification functions that are rarely completely performed that must be automated.  Reporting each program’s operating margin is critical.  Follow up with corrective action is essential.  The CPA should infuse a business-like attitude not just number crunching.  Recommend tying performance to responsibility, customer satisfaction to caring, reporting to honesty and camaraderie to respect - all will bind a nonprofit’s mission with success.

Turnaround Takes Time and Passion

For the Tuckahoe Family YMCA, a suburban Y with a budget in excess of $7.5 million for the year 2002, over 50 full-time and 500 part-time staff serving a customer base of 20,000 plus, the 2000 trend (calculated in late 1999) pointed to a material deficit.  After this analysis, recommendations were implemented in key areas of operations, budget and internal controls, e.g., managing with a program profit center focus, requiring a budgeted contingency of 2% of total revenue, instituting ADP in lieu of timesheets, etc. – instilling the all but absent business flair.  Leading these changes required the cooperation, hard work and dedication of the staff.  Instead of a deficit for 2000, this organization had a surplus of $262,000 – resulting in a complete business and financial turnaround.  In the nearly 40-year existence of this organization, never had such a surplus in size or percent-to-revenue been achieved.  Never had a branch in the Richmond Association contributed so much to the greater good of other outreach branches, which posted deficits.  In 2001 the surplus was yet another record in the amount of $287,000.  Business turnaround requires operational vision, business flair and the scrutiny of every line item.  Having consistent, consecutive surpluses of exactly 4.3% of total revenue and posting 10% annual revenue growth are the results to be had – over time. 

With this new tool, the CPA can take budgeting a step further.  Consider a trend that’s based on the stronger of three or four years of history.  This will diminish the linear effects.  So will using polynomial trending that will seek out curvilinear trends.  Build spreadsheets that provide options of the strongest trend (linear or curvilinear based on varied years of history).  Even explore regressions to things other than time periods, i.e., events on which revenues depend.  The organization will “profit” from this approach.  Expect to see physical plant improvements, better bond ratings, enhanced endowments and investment balances, the recruitment of that former eluding board member and advances in information technology.  Maybe even a new bus or two – if that new, savvy board member approves of the organization getting into the transportation business.  Outreach to the community will be infused with once unavailable finances.  Spring and fall soccer teams will no longer be wearing tee shirts as jerseys but will be sporting complete Score uniforms.  The recovery of the organization will be systemic and cyclical.  Its mission will survive to serve yet another day.  But for me, a CPA and father of three young daughters, the true joy is knowing this recovered YMCA organization will be there for the Little girls I use to see running down its halls – and for their children too.

Hugh Breckenridge Little CPA, a former director at the Tuckahoe Family YMCA from 1999 to 2002, is currently the Supervisor of Internal Audit for Eastern Savings Bank in Hunt Valley, Maryland.  He received his undergraduate degree from Shepherd College and an MBA from the University of Richmond.  He has over seven years of nonprofit experience and was formerly an international consultant with PricewaterhouseCoopers in McLean, Virginia.  His email address is hlittlecpa@aol.com and he lives in Hunt Valley, Maryland and Richmond, Virginia.

AAFM, LC FINANCIAL CERTIFIED WEALTH MANAGEMENT DISCLAIMER & CERTIFIED PLANNER INFORMATION
AAFM ™ Copyright 1996-2004 © Financial Planner Global Society