Thanks to some excellent interchanges with Robert Nordland, PE, RS, founder of Association Reserves as we were rolling out the Building the Budget series, we decided to create a budget template In Excel for anyone to use. CLICK HERE to get to the Helpful Links page at www.associationbridge.com and click on “Budget Template here!” to download the file.
Excel is a useful format for budgeting. It provides flexibility in formatting, line items, and data. When the formulas are set up for you (like they are in the template), it can also help to maintain some of the disciplines we discussed in the blog series. For instance, the “Proposed” column in the comparative worksheet is set to maintain a balanced budget. The fee income line item will automatically change anytime you change an expense or other income line item. This helps you to maintain discipline and resist the urge to fee target.
Use What You Need, Chuck the Rest
The template may include more than you need. It is a workbook of two linked worksheets. Don’t worry if you are not an Excel whiz. If you don’t want to use the narrative worksheets, don’t use it. The comparative worksheet can stand on its own in conjunction with any narrative or other supporting materials you already use.
If a line item or expense category doesn’t apply to your association, leave it at zero or delete the row(s). If you do delete or add rows, just make sure you adjust the formulas in the subtotals or totals as needed.
How to Use the Template & Write Your Budget, Step by Step
1. Preparation – A Few Tips
- Gather everything you need in advance. This may include the latest adopted budget and narrative/line item explanations, contracts, proposals, most recent financial statements, most recent audit, reserve study, utility usage information, and wage and benefit information.
- This is a good time to collaborate with those who serve the association. Think through and discuss potential projects for the next year with your business partners and professional service providers to get realistic data to plug in.
- Try to schedule uninterrupted time blocks to complete the work.
2. Populate The Comparative Worksheet
- Take your financial statements and edit line items in the template to match the format of your financials. We’ve included some typical line items and spaces to add others. All the total and subtotal cells have formulas plugged in for you already. Hint: Don’t plug data into the cells shaded in yellow – that’s where formulas have been plugged in for you. If you add or delete categories or line items, you may need to edit the formulas in the affected totals and subtotal cells. It may take a little while to set up, but it makes everything easier when you start plugging in the data. We’ve even created a column where you can also include general ledger numbers for each line item if you like.
- 2019 Adopted Budget Column: Plug in the data from the budget.
- Audit Column(s): Plug in the data from your last audit or audits if you choose to include multiple years. Hint: The audit may not have all the line item detail included in your financial statements. If that’s the case, get the Adjusted Trial Balance from the auditors. That will have all the line items.
- Year-to-Date Column: Plug in the data form your financial statements.
- Projected Column: Take the year to date figure and add what you think will happen for the rest of the year.
- Woo hoo! You just helped to take a snapshot of the past and present. This will help you budget for the future.
- If you are NOT using the narrative worksheet, populate the Proposed Column, taking into consideration the historical information and all current information. We strongly recommend the use of a detailed narrative, but you don’t have to use the template. If you would like to use it, go to Step 3.
3. Populate the Narrative
- Format the line items and categories to match the comparative worksheet.
- Time for brain work. Describe each line item as best you can. The worksheet includes some samples you can use if you wish, including a calculation of the percentage fee increase.
- Review the description and the history for the line item shown in the comparative worksheet, and input a dollar figure considering everything you know. Hint: The narrative line item values are already linked to the comparative worksheet. If you plug values in here, the Proposed column on the comparative worksheet will populate automatically.
- The template includes space for calculations you can plug in to make sure you are calculating things like contract escalations correctly. You can just cut and paste the sample provided into as many line items as makes sense.
4. Supplementary Worksheets: In some cases, supplementary worksheets can be very useful.
5. Take a Step Back: Take a break, and take a look at the draft with fresh eyes. Edit as needed.
6. Roll It Out: Presentation and communications will be important from here on out. A few tips for doing so are available here.
7. Final Tip: Once the budget is adopted one of the last steps will be completing the 12-month spread that will find its way into next years; monthly financials statements. Make sure the spread is in line with the plan described in the budget. If you’ve done a solid job writing your narrative, calling out notable expenses and detailing contract escalations, this will be easy.
Congratulations! You are now a certified budgeteer! Welcome to the club and best wishes for a successful budget season.