You’ve probably asked yourself a ‘What If’ question before. What if you increased your cost per door by $0.10, but stopped charging the community printing costs? Would you lose money or gain? Asking ourselves ‘What If’ is a common aspect of doing business. And up until the 1970s it wasn’t possible without great expense.
The difference maker was the invention of the electronic spreadsheet (VisiCalc). If you enjoy origin stories as much as I do, I encourage you to take out 20 minutes to check out this podcast on the invention of electronic spreadsheets. It’s informative and entertaining!
Spreadsheets play an important role in any business but particularly community association management. Not only can spreadsheets help you ask ‘What If’ questions about your CAM management business, they can also help in managing communities.
1) Use it for monthly financial reports
I like to export my [reports] to Excel once they are finished in TOPS to dress them up with special fonts and borders along with additional information."
- Donna Barbee CPM, ARM, SCM, Regional Vice President
Griswold Real Estate Management
Your monthly financials are being generated by your property management software, but that doesn't mean you can't spend a little time in Excel to get some more bang for your report buck. From generating a cover sheet to adding charts and graphs, Excel can really help make you look great in the eyes of your board.
One really simple thing you can do to dress up a bunch of dull data is to use the "Format as a Table" option. Excel has a number of predefined designs that you can choose from. Format as a Table is also great if you need to do any data manipulation, as it makes it super easy to sort or filter your data by just clicking the header. (This is the design we modeled TOPS iQ grids on, so if you have iQ, you will already be familiar with this interface!)
Format as Table
2) Use it to track action items
We use Excel for our Action Item Report. Whenever something is done, or an updated message about the specific action item, it is added to the [spreadsheet], with the author’s initials in parentheses. Some items go on and on, until they are closed. When I’m printing the report for the board, I generally make the last entry in bold. That way we can see the most current status. When an item is completed, it is marked “closed” and moved to the [closed] section. "
- Nancy Stephens Carter, President
An action items list is another great addition to your monthly board packet. Instead of making board members pour through old meeting minutes to see what was assigned and to whom, this worksheet not only puts the info at their fingertips, it also gives them a status synapses right away.
In a worksheet like this, you may find yourself frequently needing to add a number of rows for more action items. Excel has a really easy way to do it. Simply click and drag on the row numbers - however many rows you wish to add. When you have them highlighted, right click on one of the selected row numbers and click Insert. This will add that many blank rows above the first row you selected. Even better, Excel automatically copies the formatting from the selected row.
Insert Rows Fast
3) Use it for building and tracking your budget
I use Excel in too many ways to tell you! But most important is my Budget Worksheet. When finished and balanced, it is published to the owners in the Annual Budget in the Excel format.
An owner makes a fixed payment each month. We are required to provide the dollar amount each owner is paying as their share of each line item expense. In addition, we have to show the last years budget for comparison.
Every manager has their way to create a budget. But I love to do budgets so that they balance. Wish our government did the same."
- Nancy Sterling
Budgets play such a huge role in community association management, it's no wonder that almost everyone I reached out to said they use Excel for their budgets in one form or another. The thing about budgets is you have to keep referring back to them, and it can get unwieldy if you have a lot of categories.
One trick that I like to use to make the budget easier to navigate is grouping in Excel. To set it up, simply put your cursor anywhere in your budget and go to the Data > Group (click the down arrow expander) > Auto Outline. Excel will evaluate your layout and automatically organize your budget into appropriate hierarchies. Now you can expand or contract individual categories to get around in your budget quickly. Here's a tutorial on Grouping in Excel.
4) Use it for annual meeting sign in sheets & tallying election votes
I like to export my annual meeting sign in sheets to Excel from TOPS so I can track proxy’s received and tally owner percentages for condos right on my sign in."
- Andrea Meyer-Smith, PCAM
Taking data from 2 reports and merging them into one expanded report is easy to do in Excel. Simply make sure that both your reports are sorted in the same order (such as Lot/Unit number or account number) before you export them to Excel. Open the two reports in Excel, and create a new worksheet for the merged report. Then, simply click and drag on the column headers you want to keep, copy, and then paste them into the new worksheet for your new, expanded report.
It's easy to take that expanded report and add another column to it to help you tally your election results when it comes time to count the votes. As an added bonus, you can create a quick pie chart from the voting results that you can publish in your newsletter for the community to see result percentages without exposing private voting data.
Create a Chart in 2 Clicks
Pro tip: Once you have created a chart in Excel, click on it and copy, then open a Powerpoint presentation and paste it. Next, simply right click on it and choose "Save as a picture". Now you have it as an image file you can use in your newsletter, or even online.
5) Use it to track projects and maintenance
We use Excel for so many things. My bookkeeper uses it to create water use reports. Maintenance uses it to track safety logs, irrigation inspections, and pressure test on our wells."
- Jane Jarlenski, CMCA, AMS, PCAM
Community Manager for Americana Cove
I love how versatile Excel is for the kinds of tracking Jane mentions. Even if you are using your management software to track maintenance and custom data, you can still export the information to Excel for further details like safety logs or water usage.
A feature I just love in Excel that is good for this type of logging is called conditional formatting. Conditional formatting allows you to set a condition by which the spreadsheet will change the formatting to alert you.
Let's say for example that you are tracking water usage, and you want to be alerted when water usage in any unit exceeds 80 gallons per day. In the column where you are tracking the usage, you can add conditional formatting that colors the cell red if the current reading minus the previous reading is more than 80. A system like this can help alert you to emergencies, such as an undetected water leak before there is too much property damage. (Or maybe they just have a water bed!)
6) Use it to generate mailing lists
This one's a bonus tip from me :-) One of the things I need to do occasionally is generate a list of address information to upload to a mailing service or print house. Mailing label formats are generally useful for this but sometimes you will need to split up a column (such as first name and last name) or merge two columns (such as street number and street name). This can be incredibly time consuming if you try to do it by hand. Luckily, there is a better way:
To split up a column into more than one column, first add a blank column next to the column containing the data you want to split. Then, click and drag over the data you want to split and go to DATA > Text to Columns. Choose delimited by Space and click finish. Excel will automatically split the data into separate columns every time it sees a space!
Split Up a Column
If you have 2 columns you want to merge together (this is called concatenating) Excel also makes that easy. Let's say you have the street number in cell A2 and the street name in cell B2. You'll need to add a blank column where you want the merged data to be added, so let's call that column C. Just click in the first cell where you want the merged data (C2) and type this formula =A2&" "&B2. The secret ingredient is the ampersand which tells Excel to include all of the contents of the first cell address, plus a space and all of the contents of the second address.
Merge Two Columns
Don't limit your use of these hacks to just the scenarios listed here. All of these tips can be easily mixed and matched for any of the other uses mentioned and so much more!
More to come
When I set out to write this article, I reached out to our subscribers and asked how you all are using Excel for community association management. I have to tell you, I was blown away by your responses. So many people got back to me, not only with your feedback, but also with samples of the spreadsheets you are using.
Thank you all so much for sharing! I want to honor that spirit, so over the course of the next few months, I will be releasing several more articles with accompanying downloads of the amazing stuff you and your fellow CAM professionals are using to get the job done.
If you've been using Excel in an interesting way for community association management, or you have a tip or trick you'd like to share, please let me know!