Reserve Study Analysis Spreadsheet

Hello All,

I am on the board of our HOA and have been very disappointed with a reserve study that we recently engaged to have done. The company that performed the reserve study provided us with a document which is mostly boilerplate that they inserted our community name and details to make it "custom" just for us. It was obvious that 80% of the text was purely boilerplate.

Their analysis was second rate at best. They did not perform a sensitivity analysis to variable such as inflation rate. They identified a reserve item which in effect is annual expenditure ... actually, our community manager identified as such. Obviously, if an asset has a life of only one-year, it should be in the operational budget and not the reserve. This company did not even advise us of this.

I asked the company to provide a copy of the spreadsheet they used for the analysis and they did provide it for me. I found some logic errors in the spreadsheet. Not serious, but tended toward being more conservative than I think is necessary. Finally, the company recommended that our HOA have a special assessment to get us more on track with we need to be to cover some costly asset maintenance in about 14 years ... specifically, repaving our private road.

Well, long story short ... I am very skilled with Excel and prior to my retirement, worked as a consultant developing IT strategies for large corporations. My work involved understanding and developing sounds financial analysis for corporate IT expenditures. So, I developed an Excel workbook to do a sanity check of our HOA Reserve Study. My analysis is that our HOA needs to increase dues about 7% per year for about 6 years to get us to a better position to cover our repaving of the road. This is much easier pill to swallow than the special assessment the reserve study company recommends.

Now, here is where I would like some help from others here on the Forum. I would really like to provide the spreadsheet to one or two of you who can kick the tires on my Excel application. Looking for someone to use the tool and provide me some feedback ... good or bad. I am not looking to sell the workbook to you ... just want someone to kick it around and provide an opinion. Things like, it looks valid or not. Or change this or that. Etc.

If there are any takers ... your only requirement is that you have MS Excel on a PC. I developed the workbook using Excel 2010 and my PC is Windows 7.

Thanks for any help.

Steve

Excellent idea and easy to do in Arizona. State law allows up to a 20% increase over previous year's budget without a member vote. I hope your board understands the need for the increase and has the stones to follow through.

Hi Steve

First some comments about your reserve study.

Errors not uncommon. Reserve companies usually give you 30 days to comment before a final report is issued.

Surprise that you got spreadsheet. The reserve companies I've spoken to consider their spreadsheets to be their own trade secrets.

Wording is usually boilerplate.

Inflation rates and interest rates are the biggest unknowns, especially over a period of 30 yrs. They can do it all kinds of different ways. Always good to ask first what their approach is.

Our 2014 study said we had to ding everyone $1,500 over 3 years. Instead, we built our own spreadsheet to fully fund by 2024.

Willing to look at your spreadsheet if you can wait a couple of weeks for response. [email protected]

Thank you all for those who are willing to kick around the Excel workbook that I have created.

I will be contacting you all in the next couple of days. I have a couple more tweeks I want to incorporate and then will be contacting you.

I don't want to have too many people doing this check for me as I have some concern that there might be a bad assumption inside the workbook. I have been checking it and am 95% confident that all assumptions and formulas that I am using are valid ... but this small amount of uncertainty is why I am looking for some independent reviewers. Specifically, I would love if you could run my Excel workbook and compare the results to a recent study that you may have done previously.

At this point, I will only be sending the spreadsheet to those people who have already replied that they are willing to review.

Thanks to all of you.

Regards,

Steve

I provided a copy of the spreadsheet to several people who asked, but only received a review from one person. That said, the comments I did receive were very helpful.

I have made some mods in the workbook, corrected a few errors and modified several of the charts, graphs, and key indicators that an HOA would be interested in for conducting an analysis of their reserve funding planning. As an example, I am including a PDF file of the Reserve Study Financial Review I created for our HOA. All the charts, graphs, etc are taken directly from the workbook that I created.

I envision the workbook as good as or better than software that is used by professional reserve study consultants. I do not profess that one could use the workbook to replace a comprehensive reserve study, but in my capacity as a member of an HOA board, it more than satisfies the requirement to conduct a regular analysis of the reserve funding. The workbook I have developed is entirely sufficient for anyone who has a good understanding of the reserve components and their estimated costs / replacement schedule. For example, many HOAs have already had a reserve study conducted by a professional firm. The workbook I have developed can be used to conduct a financial analysis review by using the reserve components inventory that is already available to them in the professional reserve study. Some key components are the ability to evaluate the current "health" of the reserve funding and conduct different scenarios (what if analysis) for taking action to meet future spending requirements.

I want to offer anyone who would like a free copy of the analysis workbook to send me an email ...

Thanks and best regards,

Steve Bisel
[email protected]

Steve,

Thanks for sharing this. I have found it to be very useful and complete. I am looking forward to using the data it has provided me (as Treasurer) so I can present a true plan to the Board at our next meeting!

I made a modification to the Reserve Funding Analysis tool ...

Added internal code so you can use the built in reserve asset checklist to automagically copy the asset data into the analysis portion of the tool. This makes it easier to use the checklist as you then do not have to manually copy the items over.

The tool is free to download and use. I only ask that if you download the tool and use it, please send me feedback and comments.

Download here ...

https://www.dropbox.com/sh/ov726te0mv1l2qx/AAAn9Y97p8lXnynYmhcl27Hoa?dl=0

(Note: ... copy the URL above and paste into your browser's address line)

Steve

I received some feedback that the limit of 50 Reserve Asset Items was too restrictive. Per the reviewer's suggestion, I increased the number of reserve asset items to 250. I expect this is considerably more than most associations.

Also modest change to the General Data sheet layout.

Copy this link into your browser address bar to download ...

https://www.dropbox.com/sh/ov726te0mv1l2qx/AAAn9Y97p8lXnynYmhcl27Hoa?dl=0

Regards,

Steve

You. An download the spreadsheet. See posting above for the link

Steve

No. When you follow the link to Dropbox, there is a link to download without signing up for Dropbox.

Steve

I found some cells that were locked that should have been unlocked. Made the change to unlock them. New version is v8.2.

Go to the link to download ...

https://www.dropbox.com/sh/ov726te0mv1l2qx/AAAn9Y97p8lXnynYmhcl27Hoa?dl=0

Regards,

Steve

Steve,

I would like to use your workbook. However, I have excel 2003 and it appears it will only work with excel 2007 or later. Is there a way around this?

Mode some changes. Major change is the addition of additional sheet that summarizes all the depreciable assets by year with an annual total of the year and total for all selected years for each asset.

Version 8.4 available for download ... you must have Excel 2007 or later. Select either the 32-bit or 64-bit version.

Go to the link to download ...

https://www.dropbox.com/sh/ov726te0mv1l2qx/AAAn9Y97p8lXnynYmhcl27Hoa?dl=0

Regards,

Steve

Steve,

First, thanks for making the effort.

I have two issues.

On the General Data Entry worksheet, it won't let me change the defaulted data. If I try this error is produced: "The value entered is not valid..." I can enter new data below item 12 without a problem. If I clear the data using the Clear All Data button I am still denied entering new data.

Also, not sure about the use of the worksheet Reserve Assets Checklist. At first I though it might be tied back to the General Date Entry sheet but that doesn't seem to be so. Maybe you could point me in the right direction for its use? Also, in Cell A1 is Reserve Fund Expenses then beneath in Cell A2 is Complete this table of Operating Fund Expenses. Is the word Operating a typo - seems like it should be reserve?

Richard Morton

Thanks for comments. I am making corrections and will update as soon as I amend worksheet to allow reserve assets for years beyond current year.

Steve

You do not have to signup for Dropbox to access the files there. There is a link to decline signing up.

Steve

'our HOA needs to increase dues about 7% per year for about 6 years"

Let us know how that went over with the homeowners.

We made a big impact on funding our Reserve Fund by cutting back on annual expenditures. Did you factor in that idea?

Just to add my two cents worth:

Back in 2002, after we got rid of a rogue board, there was a lot of deferred maintenance.
A special assessment was passed for $841 for all 345 homeowners ($290K)

Also dues were raised:
42% on the base amount that everyone pays
18% for the extra dues paid by those who use "private streets"

We needed 2/3 of owners to pass and it did the first time around.

The only issue I had was the 18% increase to the extra dues. There was $94K that was misappropriated from their Reserve account, But if that had been paid back, an increase wouldn't have been necessary. I'm still fighting that - it's now $336K.

In my opinion, if the HOA Board were to perform due diligence in planning for operational and reserve expenses, there would not be a need for special assessments (except in extreme cases such as natural disaster) or for dues increases that would exceed 7% to 10% per year.

The tool that I have created for anyone's use provides a means to plan for appropriate dues increases (and special assessments) to meet the responsibility for maintaining association assets.

Regards,

Steve

Steve,

I have found your spreadsheet to be an excellent way to build/manage and analyze a reserve study. In the case of my HOA, we have been using a variety of rather primitive spreadsheets that worked okay but they were a pain. With your tool, it's now easy to manage the reserve assets and I think overall it's a must have tool for any HOA. Thanks for building it

Richard Morton

I have for the last 6 years served on a Legislative Action committee for my HOA. I have read the AZ statutes applying to condominums and planned communitities (33-1201 thru 33-1270). AZ has no requirements for conducting reserve studies nor restrictions of what can be accessed for reserve funding. Few states do. But I feel we need the state to make such requirements in order to protect Homeowners from large special accessments for replacement of worn out assets...reserve funds allow for payment of assets as they are used up by the homeowners who benefit from them. I asked rep Devid Fransworth to work on such a bill. Many of the HOAs in AZ are aging and need to replace major capital items. Our HOA board had the foresight about 10 years ago to establish a reserve fund and to have a new study conducted every 5 years. With those funds we have rebuilt a community center, fitness center swimming pool, and front entry security system.

We had a board member who felt we could save the money of having a professional company prepare our Reserve Study but he quickly learned it was more complex than he could handle. I am surprised be board member taking on such a fiscial responsible task with "a" spreadsheet. But our association has booked $10.5 million in property and equipment and our reserve study sets Fully Funded at 7.5 million...so maybe we are bigger than those who are considering doing a self prepared study. Our study gives useful life, remaining life, current cost estimate best/worst case of assets. They established Current Repair/Replacement Cost Estimates?
In this order…
1) Actual client cost history, or current proposals
2) Comparison to Association Reserves database of work done at similar associations
3) Vendor Recommendations
4) Reliable National Industry cost estimating guidebooks

But those who are anticipating funding for replacement of assets are certainly to be commended and are a head of the game.

Carol,

In my opinion, it more so depends on the type of capital components the Association has (vs. the size of the Association).

If you are a condominium, I strongly suggest a professional study done with additional costs of engineers checking the building every 10 years or so.

If you are an HOA and have a pool or a clubhouse I would lean to a professional study.

My Association has 2 playgrounds, the streets, a bus stop shelter, sidewalks and entrance monuments. We have done studies ourselves and paid for a professional study. I found little difference between the two as both the professional and the Board reached out to companies for assistance (which was provided for free).

For more information on Reserve Studies, see the following thread titled Subject: Reserve Studies/Funds 101:

http://www.hoatalk.com/Forum/tabid/55/forumid/1/postid/103517/view/topic/Default.aspx

I made some more cosmetic changes to the Reserve Study Analysis workbook.

You can download the analysis tool here ...

https://www.dropbox.com/sh/ov726te0mv1l2qx/AAAn9Y97p8lXnynYmhcl27Hoa?dl=0

Regards,

Steve

A member of HOATalk discovered a minor error that I have corrected. Most likely you would not have noticed the problem and it was quite small.

I have made a correction and created a new version.

Some of you using the workbook may have seen where the pie chart displaying the 30 year total expenditures would get quite crowded with so many pie slices that it makes the chart unreadable. I have included on that sheet instruction on how to consolidate pie slices so that you can reduce the number of slices that are displayed.

You can download the analysis tool here ... version 9.7

https://www.dropbox.com/sh/ov726te0mv1l2qx/AAAn9Y97p8lXnynYmhcl27Hoa?dl=0

Regards,

Steve