In the early 1980’s most budgeting and analysis was being done on 16 column accounting pads with a pencil and eraser. As we moved through the 80s the first spreadsheets appeared. Slowly but surely a transformation took place that changed the way budgeting and statistical analysis was being done forever. Making a change in a physical 16 column accounting pad and then chasing that change all the way through could take hours. Making the same change or running “what ifs” takes only seconds in a spreadsheet.
The efficiency and amount of time saved was incredible and it was the beginning of technology that started eliminating jobs in the office environment. As spreadsheet technology advanced it slowly took over most manual procedures that were being done with pad, pencil and eraser.
As we moved into the 90s spreadsheets evolved and had become extremely flexible, allowing for very creative and useful functions for budgeting and performance analysis. Though this great asset did much to change the way budgeting and analysis was being done, it soon became apparent that there were some draw backs:
- A spreadsheet is really just a piece of paper and pencil that is susceptible to human error
- Due to the complexities of a user generated spreadsheet it can’t be easily shared or delegated
- What happens when an employee leaves an organization and they are the only one who understands the spreadsheet that they have generated?
- How does management protect themselves so as not to become a victim when a spreadsheet is held for ransom?
- Multiple users cannot be working in a spreadsheet at the same time
The initial spreadsheet successes and subsequent draw backs began to generate a whole new field of software development. Spreadsheets became a rudimentary platform for designing specific user-friendly, menu-driven software and the user did not have to have any spreadsheet knowledge or experience. The benefits became immediate:
- Customized software is much more efficient and accurate when compared to spreadsheets
- Customized software provides flexibility in the sorting and organizing of data that spreadsheets do not
- Customized software can be delegated with no concerns of it being held for ransom by the user
- Most of todays customized software is predominately Internet based so the developer/licenser can be contacted for access, training and support, so there is no need to rely on a departing employee
- Multiple users can be working at the same time in customized software
Another limitation of spreadsheets is that they are primarily two dimensional. When attempting to surpass the limitations of spreadsheets, users encounter the dreaded “circular reference” warning which can’t exist if the spreadsheet is to function properly. Development of customized software provides much more flexibility and function without limitations.
Following is an example of a specific application that was initially tackled in spreadsheets but soon the limitations became apparent when compared with the features and reporting when converted to customized software:
Reserve Analysis, Budgeting and Management:
After initially producing spreadsheets in the 1980’s for budgeting and managing reserves it became apparent that the analysis became more and more complex and challenging when updating from year to year. A group of 30 refrigerators were placed into service on the same date and were being tracked together as a group. This proved to be a simple and efficient exercise for the spreadsheet where the refrigerators were given an estimated useful life of 10 years. Unfortunately, a couple of the refrigerators only lasted three or four years and had to be replaced.
Where there were 30 refrigerators being tracked together in one row of the spreadsheet, two additional rows had to be added to track the two replaced refrigerators individually. The other 28 refrigerators continued to be tracked together. Imagine tracking 40 to 50 reserve items that experience the same fate as the refrigerators and you have a spreadsheet that continues to expand. This obviously becomes more time consuming from year to year.
The spreadsheet proved that it could still handle tracking the data with sufficient user experience. Unfortunately tracking the data was the easiest part of analysis. The challenge was taking the data and projecting for each group or individual reserve items when the reserve funds would be expended over the next 30 years. If a reserve item has a four-year estimated useful life it could be projected to be replaced or maintained seven times over the next 30 years. Once this was done the 30 years of projected expenditures would be placed into a cash flow projection that would take into account the beginning reserve fund balance. Various contribution amounts would be entered into the cash flow until a proven contribution would be sufficient that the reserve funds would project a positive balance for the next 30 years.
If the spreadsheet developer were able to get this far and accomplish the above, they would have to be an accomplished spreadsheet developer. The next areas to be addressed pushes the spreadsheet capabilities to its capacity when taking into account the following into the analysis over the projected 30 years:
- Inflation percentage on reserve items
- Interest earned percentage on reserve funds
- Annual increase to the contribution
The next twist, what if any of the above financial parameters would be different from year to year? The complexities that a have been presented so far is really just the beginning. We have encountered the difficulties with the reserve item data and producing a cash flow, but what about management of the reserve funds themselves.
It is not unusual for high end or large timeshare resorts to have millions of dollars in multiple financial instruments investing the reserve funds. How are they being tracked in a spreadsheet? First, the spreadsheet would have to be designed and capable of tracking on a monthly basis due to the term and how interest is compounded for the individual financial instruments. The following would need to be tracked:
- Issue date
- Maturity date
- Principal amount
- Interest rate
- Compounding term
- When interest is paid
The last part of reserve budgeting and management is reporting. The ability to manipulate the data to produce a variety of managerial reports that allows for informed decision making. Based on what State the timeshare resort is located, there are reporting requirements as there are by the AICPA for what disclosures and reports must be included in the annual audited financial statements.
A proper reserve budget and management analysis is multi-multi-multi-dimensional. Spreadsheets should not be relied upon when budgeting and managing reserves due to the results obtained may end up over or under charging the reserve contribution from owners in the maintenance fees. To be assured of the results produced by customized reserve management software, check to see if it has received a Quality Evaluation from a third party.
We recommend that management take a hard look how spreadsheets are still being utilized and review the marketplace for customized solutions which relieve any concerns that were addressed above.
