BUDGETING
Part 2—Working with Budget Templates and Worksheets
It’s time to get down into the weeds!
Whether we’re talking about a nonprofit with two programs or two hundred, budgets involve wrestling massive amounts of data into rows and columns on various templates, worksheets, and reports. In this article we will consider Community Helpers (CH), a fictional nonprofit that provides a food pantry and a transportation program for seniors.
We will concentrate here on the payroll, master, and cost center-level budget templates and worksheets which form the bedrock of any budget. We will assume that all templates and worksheets are constructed with spreadsheet software.
Here are the terms that I will be referring to throughout this article.
- The payroll budget worksheet is a single spreadsheet where all payroll budget data for each employee in the entire organization is maintained.
- The master budget template is a spreadsheet that serves as the model for the cost center budget worksheets. My use of the term “template” here does not refer to the template function offered by Microsoft Office products. This template is a normal spreadsheet file that is formatted then replicated for use by all cost centers. The master budget template is comprised of rows, columns, and formulas but no data. (Note that dummy data is presented in Example 4 for illustrative purposes only.)
- A cost center budget worksheet starts out as a copy of the master budget template; each cost center’s budget will be constructed using the rows, columns, and formulas defined by the master budget template.
Here are the steps I take when using templates and worksheets to construct a budget.
Step 1: Create and populate the payroll budget worksheet for all employees.
Step 2: Construct the Master Budget Template.
Step 3: Create identical budget worksheets for each cost center by replicating the master template.
Step 4: Bring current year data into the cost center worksheets, complete the forecast columns, and hide Columns 2, 3, and 4.
Step 5: Transfer the payroll budget data from the payroll worksheet to the cost center budget worksheets.
Step 6: Complete next year’s budget (Column 3 on Examples 6 and 7; note that the budget worksheet is down to four columns when we get to this step.)
Step 7 : Combine all cost centers onto one worksheet and adjust M&G allocations.
Step 8: Create a consolidated budget with M&G allocated in total to the programs.
Step 1: Create and Populate the Payroll Budget Template for All Employees.
In Part 1 of this series I stressed the importance of budgeting for payroll expense by employee. With this method, at minimum, the payroll worksheet includes cost center, name, hours per week, hourly rate, annual salary, fringe benefits, and totals for each employee. If a cost of living increase (COLA) is planned this can also be calculated on the template.
A small nonprofit may elect to budget all fringe benefits in one column using the fringe rate. The fringe rate is calculated by dividing organization-wide actual total fringe by organization-wide actual total salary—probably from the most recent financial statements. Example 1 is a completed payroll worksheet with the calculation of the fringe rate shown in the shaded box.
For more accurate estimates, fringe benefits are itemized on the template. Example 2 uses a hybrid method with “mandated” fringe benefits aggregated in one column and “non-mandated” benefits itemized in separate columns. (Larger nonprofits may go as far as itemizing the mandated fringe benefits.)
Mandated fringe benefits are items such as:
- Social Security: FICA (6.2% of salary)
- Social Security: Medicare (1.45% of salary)
- State Unemployment (fixed by the State)
- Workers Compensation (fixed by the State)
All of these items are calculated as a fixed percentage of salary and combined into a rate that is usually around 15% depending on rates set by your state. 15% is used in Example 2.
Health insurance and retirement are non-mandated and therefore shown separately.
I recommend that health insurance be budgeted as a fixed amount for each FTE (full time equivalent) and prorated for part-time employees. The amount is calculated by dividing total expected health insurance cost by the number of eligible employees.
Example 3 shows how the calculation of the fixed health insurance allocation might go.
The calculation of retirement cost is usually the percentage of salary that will be given as a match but that percentage varies for each nonprofit.
Any other discretionary benefits such as long-term disability, life insurance, or other specialty benefit plans offered to all employees would also appear as columns on the spreadsheet.
Step 2: Construct the Master Budget Template.
Part 1: Elements of the Master Budget Template
Construction of the master budget template starts with these columns:
Column 1: This year’s annual budget
Column 2: This year’s actual data from the most recently closed month—May in our example (This column will eventually be hidden.)
Column 3: Projection of remaining months—June-December in our example (This column will eventually be hidden.)
Column 4: Adjustments based on your knowledge of the future (This column will eventually be hidden.)
Column 5: Year-end forecast (sum of columns 2, 3, and 4)
Column 6: Next year’s budget (data input column)
Column 7: Notes
Each cost center will carry out their budgeting process using this format. The template will include formulas for all calculated cells. The master template is shown in Example 4 with dummy data to show where the formulas are. The dummy data is only to display spreadsheet formulas and does not reflect actual data. Normally there would be no data in the master template.
In Examples 4 through 7 all lines are numbered identically.
Part 2: Management and General (M&G)
Let’s talk about Line 21 in Example 4—M&G Allocations. As we know from previous discussions, M&G is comprised of administrative (aka overhead) costs such as Human Resources, the executive director’s office, Information Technology, etc. that benefit the entire organization.
True to form, M&G brings additional complexity to the table during the budget process. All cost centers must absorb their fair share of M&G. In Step 7 we will allocate total M&G cost to each cost center according to its relative size, measured by total expenses. But in the meantime, M&G must be estimated on the cost center budget worksheet while data is being input so that our budgeters can get a good picture as they go along of how M&G is affecting the bottom line. As budget data is input, the formula on Line 21 will provide a real time estimate of the M&G allocation for next year by multiplying by Line 20 “Total expenses before M&G.”
In our examples the M&G rate is 20%. As a preliminary step to developing the master budget template, CH arrived at 20% by dividing this year’s M&G cost by this year’s organization-wide expense before M&G. This is by no means a perfectly accurate measure of M&G for next year, but it will suffice as a guess while the budgets are being developed.
Total M&G at 5/31/20×1 $50,000
Total expense before M&G at 5/31/20×1 $250,000
M&G Rate 20%
Step 3: Create a Budget Worksheet for Each Cost Center by Replicating the Master Template.
Each cost center budgets starts out as a copy of the master budget template. As data is entered the cost center budget worksheet emerges. Each cost center worksheet uses the format of the master budget template so that they can all eventually be merged into one document. (Take a peek at Example 8 to see what this looks like.)
Every formula in the master template must be correct, and all budget lines for all cost centers must be included so that the merge will work. You will see in Example 5 that the food pantry budget worksheet includes lines with zero expense that are used in other cost centers, such as “bus maintenance” (Line 12) and “legal and accounting (Line 17).
Here’s a tip that will save you untold misery down the road: Check, double-check, and triple-check the master before you declare it complete! If you have to add a row later on in one cost center worksheet you will have to add the same row in all of the others and adjust the formulas accordingly. (Or go back to the master and start over again—Heaven forbid!)
So, after you have followed my advice and created the perfect master, you can create the copies for each cost center.
Step 4: Bring Current Year Data into the Cost Center Worksheets; Complete the Forecast Columns; Hide Columns 2, 3, and 4.
Example 5 is the budget worksheet for CH’s food pantry. Anyone who has worked on a budget knows that we don’t pull numbers out of the air; we need comparative data. When we created the master template we decided to provide the current year budget and the current year-end forecast. We could have also included the prior year budget and/or prior year actuals, though I prefer the simplicity of three columns, and the forecast is the most critical data set in my opinion.
Here is how we construct the year-end forecast:
In Column 2 we have five months of actual data—January through May. Column 3 is our projection of June through December, arrived at by multiplying the monthly average of the actual data by the months remaining in the year. The forecast for food (Line 11)is calculated as follows:
Actual year-to-date cost as of 5/31/x1 $73,000
Monthly average (divide by 5 months) $14,600
Projected June-Dec (monthly avg. times 7 months) $102,200
Total Year-End Forecast $175,200
This model works well for revenue and expense items that occur evenly throughout the year. But some items, like contributions, do not come in evenly. We can dramatically improve the contribution forecast with a manual adjustment of $50,000 on Line 2 Column 4. Most nonprofits receive an influx of donations towards the end of the year. Without this adjustment, CH’s year-end forecast will be too low.
Another example of a manual adjustment is Property Rent—Line 10. The current year budget was $75,000—monthly rent of $6,250 times twelve. But in April the landlord increased the rent by $500 to $6,750, so the forecast must be $79,500:
January – March @ $6,250 = $18,750
April – May @ $6,750 = $13,500
June-December @ $6,750 = $47,250
Total = $79,500
The projected amount in Column 3 is incorrect and must be adjusted by $2,100 to arrive at $79,500.
You will not regret the time you invest in fine-tuning your forecasts. The forecast is indispensable for creating accurate budget estimates and will serve you well in your year-end planning and final budget presentation. (Your board will love it!) For more on forecasting, click here.
Once your forecast is complete, Columns 2, 3, and 4 are hidden and you are left with four columns. In Example 6 the food pantry budget worksheet is prepared and ready for data input. Columns 2, 3, and 4 have been hidden so you now have the current year budget, the year-end forecast, next year’s budget input, and notes.
Step 5: Transfer the Payroll Budget Data from the Payroll Worksheet to the Cost Center Budget Worksheets.
In example 6 the payroll lines have been populated. Once the cost center templates are created the payroll totals for each cost center can be copied there from the payroll worksheet.
This is an on-going process. In my experience, payroll data is updated on the payroll worksheet throughout the budget cycle and continuously fed into the cost center worksheets. Since Community Helpers is using spreadsheet software, the payroll worksheet and cost center worksheets can be linked, allowing the payroll data to automatically flow to the cost center worksheet as changes are made. This is hypothetically the ideal scenario, but linking can be tricky, and with only three cost centers, manual entry may be the more foolproof and less stressful option.
Step 6: Complete Next Year’s Budget—Column 3.
Part I: Non-ControllableCcosts
I have found that the most efficient way to populate the cost center budget worksheet is to start with the fixed, contracted amounts, and any other items out of the control of the program managers. You (or your staff) are in the best position to estimate the cost of items such as:
- Grant revenue contracts
- Utilities
- Insurance
- Maintenance contracts for cleaning, snow removal, trash pickup, etc.
- Telephone and internet
Part II: Other Costs
Once the non-controllable items are set, the remainder of the non-payroll lines will be completed with the involvement of the program managers. Whether the finance department or the program managers take the lead on this portion of the process varies from one organization to another. The CFO needs to be involved throughout the process. More on that in the next article in this series.
Hopefully you will be able to estimate many of your costs by referring to the current year budget or the year-end forecast. Sometimes, however, your best option is to use the most recent month as your basis for estimating next year’s cost. In our discussion of property rent above, we said that the cost for May of this year was $6,750. This is the best information we have, so we would get our budget number of $81,000 (Example 7 Line 10) by multiplying $6,750 by 12.
Inflation is always a consideration. You may not have the revenue growth to add an across-the-board inflation factor. Instead, you might adjust for inflation for large items and look for cost savings in other areas to mitigate the effects of inflation.
You should always be guided by the concept of materiality, spending more time on the largest cost items. You might use the forecast amount for dues and subscriptions but analyze the food bills to look for ways to achieve cost savings.
Example 7 is the completed food pantry budget.
Step 7: Combine All Cost Centers onto One Worksheet and Adjust M&G Allocations.
The first step in finalizing our M&G calculations is to combine all cost centers into a single document.
We now have four columns: Total Organization, M&G, Food Pantry, and Senior Transportation. Although M&G gets special treatment when we come to Step 7, initially the costs are budgeted in exactly the same manner as any other cost center. M&G has its own budget worksheet; salary costs are transferred from the payroll worksheet and all non-payroll costs are entered in the budget column just as they were for the food pantry in Example 7. The same is true for Senior Transportation.
We had no trouble merging column 3 from each cost center’s worksheet into one document because we succeeded in keeping their formats consistent. We now have a new worksheet that can be manipulated for M&G allocation adjustments.
Total M&G cost (Column 2, Line 19) is $125,220. CH is comprised of two program cost centers which will absorb exactly this amount. In our food pantry budget we estimated M&G to be 20% of total expenses (Example 7, Line 21). Senior Transportation also estimated its M&G as 20% of total expense (Example 8 Column 4). But now that all of the budgets are complete, we find that they have budgeted for a combined total $137,149. Our guess of 20% was a hair too high, and total M&G has been overestimated by $11,929 (Column 1 Line 23).
Now that we have final numbers, we can make an exact allocation between the two programs based on total expense before M&G. The calculation is shown in the box at the bottom of Example 8.
The new allocations appear on Example 8 Line 22. These add up to $125,220— total M&G budgeted expense. Since we have lowered M&G in each cost center the food pantry and Senior Transportation will have surpluses of $9,091 and $2,838 respectively unless they adjust their revenues and/or expenses accordingly.
Step 8: Make Final Adjustments and Create a Consolidated Budget with M&G Allocated in Total to the Programs.
Example 9 is the final product that will be brought to the Board.
Contribution revenue has been increased to adjust for the M&G allocations . Food Pantry contributions are now $468,034—an increase of $9,091, and Senior Transportation contributions are $147,162—an increase of $2,838.
How do we justify these seemingly arbitrary increases to contribution revenue? While we are always striving for precision in our estimates, we also take a pragmatic approach to achieving the goals we have set for ourselves. These are not material amounts, and contribution revenue does not lend itself to scientific estimation methodologies, so we can achieve our goal of balanced budgets in these two cost centers without sacrificing our principles.
Example 9 is tricky to interpret because M&G costs have been incorporated into Column 1 on each line and the individual costs are shown in Column 2, but total M&G is zero in Column 1, Line 19. By zeroing out M&G in Column 2, line 19, we can move the total to the two programs according to the allocations we calculated in Example 8 without double-counting M&G cost.
The end result is a consolidated budget showing all revenues and expenses for the total organization, the breakdown of M&G costs, and the allocation of M&G to the programs.
Bonus Step: Congratulate Yourself for Making it Through Another Budget Cycle!!
BUDGETING IS HARD!
I hereby resist the urge to apologize for the multitude of words and numbers in this article. Budgeting is a bear and there is more to say, so read on to the next article!