Get 2 months of free community web site hosting from Community123.com!
Friday, November 24, 2017
Get 2 months of free community web site hosting from Community123.com!


SBCA: Free education for HOAs and condos on satellite placement issues.
(National Trade Organization)
Helping HOAs, condos and property managers with satellite placement issues since 1986.
Only members have access to all features.
Click here to join HOATalk for Free! Members click here to login and access all features.
Subject: Reserve Study Analysis Spreadsheet
Prev Next
Please login to post a reply (click Member Login on the menu).
Page 1 of 212 > >>
Author Messages
SteveB25
(Arizona)

Posts:59


06/07/2016 2:01 PM  
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
RichardP13
(California)

Posts:2050


06/07/2016 3:02 PM  
Steve

I did a few reserve studies about 5 years ago. I have used three of the software packages that specialist use and yes, much of the language is boilerplate. So, I kind of did what you did, and that was develop, using excel, a much more understandable and usable analyst of an association's reserve components and their reserve accounts. All the components were linked to pictures.

While it may not have the bells and whistles yours has, the objective was something a board member could read and understand. Once the original study is prepared, annual maintenance and updates are relatively simple.
LarryB13
(Arizona)

Posts:4099


06/07/2016 3:27 PM  
Posted By SteveB25 on 06/07/2016 2:01 PM
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.


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.

AllisonD
(Florida)

Posts:444


06/07/2016 3:34 PM  
Steve I would love to test your spreadsheet. Please send to [email protected] gmail. I will give you great feedback.
NpS
(Pennsylvania)

Posts:3125


06/07/2016 4:07 PM  
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]

Je publie un degagement de toutes responsabilite. Read all posts at your own risk.
TimB4
(Virginia)

Posts:14860


06/07/2016 4:35 PM  
Steve,

We spent money on a reserve study this year as well. Even though our last (self) study was done on the component basis, the company suggested the cash flow basis. When I received the study, I didn't like it at all and I thought it would lead to a special assessment in the future. Therefore, I utilized info from both studies and created a third (based on the component basis). I presented both to the Board and they chose the self study I did simply because it made more sense to them.

I've got no problem looking over the spread sheet if you desire.

My e-mail is: [email protected]

Tim
SteveB25
(Arizona)

Posts:59


06/07/2016 5:59 PM  
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

GordonS1
(Washington)

Posts:18


06/07/2016 11:23 PM  
Steve-

Ended up in a similar situation as you, and had much the same response. I also asked for a copy of the spreadsheet from our reserve study professional, but the version I got was just the values (no formulas). The formulas are treated as a reserve professional's "crown jewels" - described usually as "proprietary" and heralded as some sort of magical/mystical oracle.

I was able to reverse engineer the formulas used for our reserve study in about an hour. It's honestly not very complicated - figure out what the cost would be to replace the item today, and what percent of its lifetime is "used up." Add an assumption for inflation. Repeat for each item.

I'm interested in seeing your spreadsheet, and would also be willing to share a copy of the one I created. My email is [email protected]
SteveB25
(Arizona)

Posts:59


06/13/2016 3:36 PM  
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]

Attachment: 1613362764471.pdf

SteveB25
(Arizona)

Posts:59


06/26/2016 9:59 PM  
I have pretty much reached a point where I do not see a lot more modifications to make.

I am offering up this workbook to anyone who wants to use it. But, this workbook is a .exe file so I can protect the content and especially the intellectual capital from unauthorized use. This workbook is the equal or better than anything that I have seen used by professional reserve study service providers. I do not want professional organizations to copy the logic or the programming code contained within this workbook and by creating a .exe (compiled version), the logic will be invisible, but the data entry and results will not be.

You can download a copy of the workbook here ...

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


Regards,

Steve
LarryE3
(Colorado)

Posts:26


06/27/2016 7:14 PM  
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!
SteveB25
(Arizona)

Posts:59


06/27/2016 8:17 PM  
Thanks for the feedback. If you have any comments or suggestions, please let me know. I want to make this freely available so that HOAs can become more independent from the reserve study "consultants" who offer their services at high prices and frequently provide limited value.

My caveat ... This tool I developed assumes you have reasonable knowledge of your reserve assets and their characteristics. Armed with that, what the tool gives you is an ability to conduct a what if analysis to examine different scenarios and visually see sensitivity to things such as inflation rate.

Please send me feedback and comments.

Regards,

Steve
SteveB25
(Arizona)

Posts:59


06/28/2016 2:11 PM  
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
SteveB25
(Arizona)

Posts:59


06/28/2016 8:19 PM  
Another small modification:

- Added check to not exceed 50 Reserve Assets in the analysis

- Added some additional items to the Reserve Asset Checklist

Download the Reserve Funding Analysis workbook here ...

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

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

Steve
SteveB25
(Arizona)

Posts:59


06/30/2016 8:54 PM  
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
KerryL1
(California)

Posts:4381


07/02/2016 1:18 PM  
I wanted to see your spreadsheets, but didn't have time to sign up, etc..

Quick note that we're adjusting our list from 150 to about 130 (twin tower high rise). A number of components were on it that we will never replace. Our PM also sought a lot of new bids on large components and nearly all are somewhat or a lot less than what our analysts estimated last year. Finally we eliminated many that were less than 1/2% of our operating budget as recommended by most analysts nationwide.
SteveB25
(Arizona)

Posts:59


07/02/2016 2:49 PM  
You. An download the spreadsheet. See posting above for the link

Steve
KerryL1
(California)

Posts:4381


07/02/2016 3:44 PM  
I did paste the link on my URL. but I don't want to sign up for dropbox. that seems to be the only way I can review your efforts, Steve, right?
SteveB25
(Arizona)

Posts:59


07/02/2016 6:39 PM  
No. When you follow the link to Dropbox, there is a link to download without signing up for Dropbox.

Steve
SteveB25
(Arizona)

Posts:59


07/06/2016 9:28 PM  
Received notice of some errors in the tool when certain conditions exist. Made changes and created new versions.

Go to this link and download new version ... https://www.dropbox.com/sh/ov726te0mv1l2qx/AAAn9Y97p8lXnynYmhcl27Hoa?dl=0

Regards,

Steve
SteveB25
(Arizona)

Posts:59


07/07/2016 8:38 AM  
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
JoyceR2
(Virginia)

Posts:112


07/29/2016 6:16 PM  
If you know the property then you would know what is in the validity of the study. KEY is having a maintenance schedule to record every thing by date that is fixed, replaced, changed etc.

2nd.. The study for all intents and purposes is only good for 5 years. So basically you need to be funded for what actually needs to be done 5 years out and fund all other reserves to be prepared beyond 5 years based on assessments and what should go to these funds to meet funding by year it is needed.

If properly funded there should not be a need for any special assessment unless there is some major emergency situation. Invest reserve funds more than 5 years out and fund by priority.

LindaS27
(Colorado)

Posts:236


08/11/2016 5:27 PM  
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?
SteveB25
(Arizona)

Posts:59


08/11/2016 8:56 PM  
I developed the workbook using Excel 2010. There are some features in Excel 2010 that are not backward compatible to Excel 2003.

Regards,

Steve
SteveB25
(Arizona)

Posts:59


08/13/2016 4:04 PM  
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
SteveB25
(Arizona)

Posts:59


08/18/2016 2:35 PM  
Made change to version 8.5 to correct potential problem with copy and paste.

Here is the link to download ...


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


Steve
RichardM19
(Illinois)

Posts:4


08/20/2016 2:54 PM  
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
RichardM19
(Illinois)

Posts:4


08/20/2016 6:31 PM  
Found my error/answer. Unprotecting the sheets helps...
SteveB25
(Arizona)

Posts:59


08/21/2016 9:10 PM  
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
SteveB25
(Arizona)

Posts:59


08/24/2016 8:16 AM  
I wish to thank the people who have sent me comments about the workbook. As a result of some comments I received, I have some changes:

Changes in version 8.6 ...

Corrected calculation so that the current year is not included when determining the future value of costs. In prior versions, this resulted in a slightly inflated future cost.

Made a change so that reserve assets can be listed with a year later than current year. In prior versions, this resulted in future costs which were too low. In version 8.6, the user can enter an asset that is not in the current inventory, but will have a first event in the future.

Adjusted the workbook so that the user has to enter the cost basis as either an actual cost or a current estimated. Future costs will then be different depending on the cost basis entry. For example, if the user enters actual cost, then the first future cost will be determined using the entire useful life of the asset. If the user enters current estimate as a replacement cost, the first future cost will be determined using the remaining life of the asset ... generally, resulting in a lower future cost as there are fewer years that inflation will be applied.

Again, I want to restate the use policy of this workbook. This workbook is for use by non-profit entities. The use by for-profit enterprises or any individual in a for-profit use is prohibited.


You can download the latest versions here ...

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


Best regards,

Steve
SteveB25
(Arizona)

Posts:59


08/24/2016 8:17 AM  
Posted By SteveB25 on 07/02/2016 6:39 PM
No. When you follow the link to Dropbox, there is a link to download without signing up for Dropbox.

Steve




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

Steve
SteveB25
(Arizona)

Posts:59


08/24/2016 8:18 AM  
Latest version posted ... Version 8.6. See details in prior post.

You can download the latest versions here ...

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


Steve
SueW6
(Michigan)

Posts:205


08/24/2016 12:02 PM  
'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?
TimB4
(Virginia)

Posts:14860


08/24/2016 2:41 PM  
Posted By SueW6 on 08/24/2016 12:02 PM

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




Sue,

Obviously, I didn't write the above. However, I wanted to share our story.

When we did our Reserve study we had to increase assessments by 20%.
We ran newsletter articles about the need and explaining what Reserves were. We provided a copy of the study and showed where we cut expenses when possible.99% of the membership approved the increase.
LindaS27
(Colorado)

Posts:236


08/24/2016 3:29 PM  
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.


SteveB25
(Arizona)

Posts:59


08/24/2016 5:02 PM  
As I see it, you can reduce outgoing expenses by reducing operational expenses or by reducing the cost of refurbishing / replacing the deppreciable assets. The workbook that I have created gives you a tool to visually see the effect of the latter. What you do with operational expenses is outside the scope of this tool.

Regards,

Steve
SteveB25
(Arizona)

Posts:59


08/24/2016 5:06 PM  
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
LindaS27
(Colorado)

Posts:236


08/24/2016 5:23 PM  
Steve, I agree! But ONLY if you have a responsible board.

Several years ago, that board was out of control but because there was so much apathy, no one cared so much until that board took the owners to court to force the declaration amendment. Then everyone took notice!

We replaced the board, the MC and the attorneys. For 15 years, we had a very responsible board; but here we go AGAIN with a board that doesn't know or care how much they are running our association into the ground.

Anyway, I would like to use your program but I have Excel 2003 and need to upgrade first. Maybe next month.

Thanks for all your work on the Reserve Study spreadsheets



RichardM19
(Illinois)

Posts:4


08/29/2016 5:38 PM  
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
SteveB25
(Arizona)

Posts:59


08/30/2016 9:20 PM  
Thanks for the feedback, Richard.

I made a couple of cosmetic changes since the workbook you and I worked on.

For others here on the forum ...

The latest changes are here available for download ...

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

Regards,

Steve
CarolP16
(Arizona)

Posts:7


09/06/2016 3:08 PM  
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.
StevenC6


Posts:0


09/08/2016 9:39 AM  
Brand new here - this topic (and AWESOME spreadsheet!) brought me here.

We'll be using this immediately!
CarolP16
(Arizona)

Posts:7


09/08/2016 11:20 AM  
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.
RichardP13
(California)

Posts:2050


09/08/2016 12:13 PM  
Posted By CarolP16 on 09/08/2016 11:20 AM
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

I process three different versions of Reserve Study software. Whether it's a spreadsheet or a database, make no difference. What's it's important is having the right calculations with reliable information.

I recently reviewed a reserve study here and 19 of the 57 pages were just fillers. If you were doing one study for your an association, a spreadsheet works just fine. If you are doing this as a business, you would want to be doing this with a database, with each association having their own file.

At the end of the day, if it isn't used by the decision makers, it's a waste of money.
TimB4
(Virginia)

Posts:14860


09/08/2016 3:34 PM  
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
SteveB25
(Arizona)

Posts:59


09/16/2016 8:21 AM  
In my opinion, a small to medium size community can likely do without the expense of hiring a professional company to perform a reserve study. This, of course, assumes that there members of the community who might possess some basic skills of financial principals and a reasonably good knowledge of the assets that they are responsible for maintaining. Our community has had a few reserve studies performed over our 21 years in existence. The first study was of value as it identified all the assets that will depreciate over time and would need repair, refurbish or replacement. Additionally, the study recommended and our community established a reserve fund to maintain these assets. The next two studies were of much less value. In both cases, they merely repeated what the original study did. Most of the text was boilerplate and the recommendations were banal. In the last study we had performed, the recommendations were flat wrong. The "consultant" doing the work had zero experience in construction, road repair or of anything mechanical. He was merely a recent hireling given some rudimentary skills in writing boilerplate.

The experience of the last study, was the catalyst for me to undertake to build my own tool for determining the financial needs for maintaining our reserve assets. I have steadily improved on the tool with the feedback from users here on HOATalk. It is as good as or better than any tool that is available from from other agencies or reserve study specialists.

Steve
SteveB25
(Arizona)

Posts:59


09/16/2016 8:29 AM  
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
SteveB25
(Arizona)

Posts:59


09/18/2016 10:53 AM  
Minor correction to method on how a new reserve asset funding is handled if asset is brand new requirement in the current year and has not yet been funded. Should not affect any situation where assets have already been established and funded in prior years.

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

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

Regards,

Steve
SteveB25
(Arizona)

Posts:59


09/23/2016 11:26 AM  
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
LarryE3
(Colorado)

Posts:26


09/25/2016 11:56 AM  
Hi Steve,

Quick question. I downloaded the 32-bit 9.7 version and am putting in my data from the earlier 8.2 version. Under the Current Income and Expenses, I entersd an income of $4224 and Annual expenses of #3420. No other income. Version 8.2 shows the Reserve contribution as $804 (which is correct or us), but the 9.7 version calculates $828. It don't know where the other $18 is coming from.

Thanks!
Larry
LindaS27
(Colorado)

Posts:236


09/25/2016 12:11 PM  
Divisible by 9 - sounds like you transposed a number somewhere along the line?
SteveB25
(Arizona)

Posts:59


09/25/2016 12:47 PM  
Posted By LarryE3 on 09/25/2016 11:56 AM
Hi Steve,

Quick question. I downloaded the 32-bit 9.7 version and am putting in my data from the earlier 8.2 version. Under the Current Income and Expenses, I entersd an income of $4224 and Annual expenses of #3420. No other income. Version 8.2 shows the Reserve contribution as $804 (which is correct or us), but the 9.7 version calculates $828. It don't know where the other $18 is coming from.

Thanks!
Larry




Hi Larry,

I would need your to send me a copy of the workbook ... or send me a screen shot of the data entry page so that I can diagnose if there is something amiss ...

Regards,

Steve
SteveB25
(Arizona)

Posts:59


09/25/2016 12:48 PM  
Posted By LindaS27 on 09/25/2016 12:11 PM
Divisible by 9 - sounds like you transposed a number somewhere along the line?




Not sure what you are saying ... ???

Steve
LindaS27
(Colorado)

Posts:236


09/25/2016 12:59 PM  
Steve,

Not always, but most often when a calculation is off and the difference is divisible by 9, a first check for the problem would be to verify all the numbers to see if one of them has been transposed - say the number 46 was input as 64 (difference 18).

But it could be any number: 28 input as 82 - difference is 54 which is divisible by nine.

Probably Larry transposed numbers when he retyped them into your program.

I do that all the time. Usually, that finds the error quick.
LarryE3
(Colorado)

Posts:26


09/27/2016 9:45 PM  
Steve,

I checked the numbers and they looked ok. I am upoading a PDF of the numbers from version 8.2 and v9.7. I like the new version, you have done a lot of work on it. Not sure why the Reserve contribution is not coming out. The calculated number in v8.2 is correct.

Thanks!


Attachment: 192745959671.pdf

SteveB25
(Arizona)

Posts:59


09/28/2016 7:01 AM  
Posted By LarryE3 on 09/27/2016 9:45 PM
Steve,

I checked the numbers and they looked ok. I am upoading a PDF of the numbers from version 8.2 and v9.7. I like the new version, you have done a lot of work on it. Not sure why the Reserve contribution is not coming out. The calculated number in v8.2 is correct.

Thanks!





It appears that you did not clear the cell D9 on the General Data sheet ... Annual interest Pct on Reserve Balance.

Steve
SteveB25
(Arizona)

Posts:59


09/28/2016 7:18 AM  
Posted By SteveB25 on 09/28/2016 7:01 AM
Posted By LarryE3 on 09/27/2016 9:45 PM
Steve,

I checked the numbers and they looked ok. I am upoading a PDF of the numbers from version 8.2 and v9.7. I like the new version, you have done a lot of work on it. Not sure why the Reserve contribution is not coming out. The calculated number in v8.2 is correct.

Thanks!





It appears that you did not clear the cell D9 on the General Data sheet ... Annual interest Pct on Reserve Balance.

Steve




To add to my comment ...

If you have a current reserve balance, then the interest rate on that balance is added to the reserve contribution. You can clear the annual pct interest or clear the reserve balance. (Cell D6 and D9) and both will have the same effect.

Steve
LarryE3
(Colorado)

Posts:26


10/01/2016 2:21 PM  
That's cool that it includes the interest!
You done a lot of great changes to the spreadsheet
Thanks again for sharing this, Steve!
SteveB25
(Arizona)

Posts:59


10/01/2016 5:29 PM  
I want to thank Richard for working with the workbook. Richard discovered that if you click the pink button at the top of the General Data sheet, that it introduced some error indications in a few cells. Upon examination, I discovered the cause and corrected it.

This problem only occurs if you click the pink "Clear All Data" button at the top of the General Data Entry sheet.

The new version is version 9.8 and you can download the analysis tool here ...

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

Regards,

Steve
SteveB25
(Arizona)

Posts:59


10/02/2016 12:14 PM  
Made small change to formatting of the Depreciable Asset Summary Report to hide any rows with a zero value. Prior versions would show the zero value rows. For example, if you "excluded" an asset or changes the range of years to show. A message window will appear when you make a change to year range that a reset is required.

No change in any calculations have been made. Prior version (v9.8) is still OK. You can download this new version (v 9.9) if you want to have the latest and greatest.

The new version is version 9.9 and you can download the analysis tool here ...

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

Regards,

Steve
Please login to post a reply (click Member Login on the menu).
Page 1 of 212 > >>
Forums > Homeowner Association > HOA Discussions > Reserve Study Analysis Spreadsheet



Get 2 months of free community web site hosting from Community123.com!



News Articles Provided by: Community Associations Network
News, articles and blogs about condos/HOA's

Only members have access to all features.
Click here to join HOATalk for Free! Members click here to login and access all features.







General Legal Notice:  The content of forum messages are from the posting member and have not been reviewed nor endorsed by HOATalk.com.  Messages posted by HOATalk or other members are for informational purposes only, are not legal or professional advice and do not constitute an attorney-client relationship.  Readers should not act upon this information without seeking professional counsel.  HOATalk is not a licensed attorney, CPA, tax advisor, financial advisor or any other licensed professional.  HOATalk accepts ads from sponsors but does not verify sponsor qualifications nor endorse/guarantee any sponsor's product or service.
HindmanSanchez Legal Notice:  (For messages posted by HindmanSanchez) This message has been prepared by HindmanSanchez for informational purposes only and does not constitute legal advice. This information is not intended to create, and receipt of it does not constitute an attorney-client relationship. Members of HOATalk.com should not act on this information without seeking professional counsel. Please do not send us confidential information unless you speak with one of our attorneys and get authorization to send that information to us. If you wish to initiate possible representation, please contact an attorney in our firm. Our attorneys are licensed to practice law in the state of Colorado only.

Legal Notice For Messages Posted by Sponsoring Attorneys: This message has been prepared by the sponsoring attorney for informational purposes only and does not constitute legal advice. This information is not intended to create, and receipt of it does not constitute an attorney-client relationship. Readers of HOATalk.com should not act on this information without seeking professional counsel. Please do not send any sponsoring attorney confidential information unless you speak with the sponsoring attorney or an attorney from the sponsoring attorney’s firm and get authorization to send that information to them. If you wish to initiate possible representation, please contact an attorney in the firm of the sponsoring attorney. Sponsoring attorneys that post messages here are licensed to practice law in a specific state or states as indicated in their message signature or sponsor’s profile page. (NOTE: A ‘sponsoring attorney’ is an attorney that is a HOATalk.com official sponsor and is identified as such in the posted message or on our sponsor page.)

Copyright HOA Talk.com, A Service of Community123 LLC ( Homeowners Association Discussions )   Terms Of Use  Privacy Statement