Capital Planning is a critical job for nearly any board. Planning for expected expenses, understanding the effects of inflation and the costs of borrowing is involves numerous calculations, estimations and judgment calls. While understanding a balance sheet and having the staff or professional advisors is part of the basic fiduciary responsibility of a board, presenting the information in a way that the average person or new board member can get a good feeling for the future is not always the imperative.
To that end we have created a Reserve Forecasting tool that uses free software available online to help board members or staff sketch out a financial future. This of course cannot predict the future nor can it replace sound financial advising but it can help enrich conversations. The calculations in this tool are generalized and tend toward evocative over comprehensive.
One may use this tool to augment an existing or upcoming Capital Improvement Plan, or simply to understand how a board may need to raise income (taxes, levies, assessments, fees) to build up a reserve to face an upcoming major project or purchase.
Free Online Tools
This tool uses free software that can either be downloaded for free or used directly online. However for the purposes of this explanation, I’ll be sharing a Microsoft Excel file rather than Google Sheets as many people have access to that tool, if you are an aficionado of Google Sheets then the translation is pretty easy. The spreadsheet is the place to collect the information about the current situation and upcoming events, and Tableau Public is used to present the data and allow individuals to manipulate variables to create forecasts and scenarios.
What data do you need?
At a minimum you will need your organizations balance sheet or annual budget. How in deep you feel you need to go of course varies with the size and complexity of the organization and the audience for which the forecast is intended. You also need some knowledge about your current infrastructure and anticipated projects. You will put this information into the spreadsheets
We primally have four tables,
Expenses- the recurring items that you can easily forecast and know reoccur. You can create or delete lines in this table to match your situation. An item to note about this table is the “Additional Item Inflation” column, this column lets you address items whose costs can be expected to change at a rate beyond the general inflation rate (the inflation rate is a value that can be manipulated in the visualization). Primary among these types of costs would be salaries, as an organization may have both cost of living and tenure-based influences involved in this budget item. We also have a column for share, this column lets you record if your organization is responsible for only a part of an expense.
Revenues- This table is effectively identical to the Expenses Table. You may change the names of these items to address the types of regular incomes you raise.
Initial- if you have a current reserve or debt you can enter the values here changing the names as necessary to address your situation. Keep in mind that this is an estimation tool and trying to get overly percise with the exact position can miss the point.
Capital Expenditures- this final major table is where you can develop the schedule of items to be replaced or repaired. Each item has two sets of variables, one is to help you develop the replacement/repair cost in today’s dollars and the second is to develop the schedule for replacement/repair events. To develop the replacement/repair schedule we assume that you know how often the item must be addressed to ensure satisfactory service and the expected life remaining in the current items. You may also identify items that are addressed in consecutive years using the Phase column. You also have the same share and itemized inflation columns found in the previous tables.
A sample project.
The files we are sharing include data for a completely imaginary township. This township seems running along fine but with a couple major upcoming projects that need addressing.
- Download the files attached to this post, save the files and note where you have placed them on your computer.
- Install Tableau Public and create an online account. Tableau lets you save and share visualizations online. Keep in mind that with the free tool all the data will be available to the public, for this reason we do not have a place where your organization’s name or location would be identified. However, the visualization will be tied to account you have created using Tableau Public, if privacy is a concern you may want to use an anonymous account.
- Using Tableau public, open the file .twbx file you have downloaded.
- This is the trickiest part, connecting the
Tableau file to your new spreadsheet. Right now the Tableau file is using data
that from inside the file itself, you need to change the data connection so
that it is connected to the Microsoft Excel file.
Navigate to: Data > Simple Reserve Planning > Edit Data Source and click, from here locate your file a and select,
Now navigate to Data > Simple Reserve Planning > Edit Connection and browse to and select the file you just downloaded.
You have just recreated the connection and can now use all your own data.
- Editing the Spreadsheets:
- There a few things to note about the
spreadsheets. Because we are doing some data reshaping in there are a few
hidden columns that should not be changed, unless you are savvy with Tableau
and willing to chase down values in the visualization. To avoid conflicts in
the visualization do not change the values or title in the grey text, these are
used in the visualization.
- Setup is simply the starting year, if you need to show your calculations in the future simply change replace the ‘=Year(Today())’ with the year you want to show as the first year.
- Expenses- use this table for your annual expenses. Note, the row for Debt Service, this column is necessary for the visualization, values are calculated in Tableau.
- Revenues- These are income items, in this table you are limited to adding specific new items by adding rows or changing the names for the regular and special income types. In the sample the titles are Special Assessments and Regular Assessments, you may choose to change the names in the Item column to match the special types of special or regular incomes established or familiar in your community.
- Capital Expenditures- this table holds the list of upcoming items for repair or replacement. You may add as many items as needed and add the values that determine the replacement/repair schedule. For those that are curious you may want to look at the hidden columns on the right side of the table, these columns show the years for repair or replacement. Items that cannot properly be configured existing parameters can be entered manually.
- Save the Spreadsheet
- There a few things to note about the spreadsheets. Because we are doing some data reshaping in there are a few hidden columns that should not be changed, unless you are savvy with Tableau and willing to chase down values in the visualization. To avoid conflicts in the visualization do not change the values or title in the grey text, these are used in the visualization.
- Refresh the connection in the Tableau file. In your Tableau application you will want to update the data by navigating to Data > Simple Reserve Planning > Extract > Refresh
- Review and customize your Tableau Dashboard
The Tableau dashboard is now ready to upload but you may want to review the data and set the parameters.
- Global Variables
These settings apply to all the calculations in the dashboard. Align to your best forecasts.
- Inflation rate, as stated this is the base inflation rate used across the entire dashboard. You may want to exercise your scenarios with this variable.
- Interest Rate, this is the value you may receive on positive balances on an endowment, trust or generous operating account.
- Loan Rate, this is the generalized rate you may expect to pay on borrowed funds. Note, the interest calculations are based on gross end of year balances and can’t really capture the full detail of a set accounts.
- Sample Share, is a scalar to use to help scale numbers to an individual to help them understand the how changes and rates effect an individual in the community. It is possible to use this scalar to match first year expenses to known tax or assessment values to give a general estimate of how scenarios will affect individuals.
- Plan Settings
You may use set two different types income plans (taxes, fees, assessment)
- Specials, this category of income is defined in a specific schedule. You may enter the years and values with the two fields in the lower left of the dashboard by using semicolon separated values such as 2025;2043 with a matching values field 1,000,000;2,000,000.
- Regular, you may define a regular funding plan
with the following variables.
- Adjust with inflation, if set to true, the regular income will adjust with the chosen inflation rate
- Period of Adjustment, in some cases it is politically easier or necessary to adjust regular income slowly.
- Accelerated Regular Adjustment, you may set a period of years where the regular incomes are adjusted at a rate different from the inflation rate for a period of years.
- Global Variables