Two weeks ago, I created a cool, nifty spreadsheet to do most of the heavy lifting. Previously, I used to download 2 sets of data, one from the prior week and one for the current week, and try to look at what has been happening and what has changed. On sites that undergoes a lot of activity during the week, that’s a lot of data to look at, sometimes too much such that I miss information. I usually create some basic formulas to do some of the searching for me, but I was recreating it every week. Now, with this cool, new spreadsheet, I can do the usual plus more. The goal is to build some intelligence into the spreadsheet to help find nuggets of information that I would normally miss. These nuggets of information will help me find out why we have imbalances on Monday mornings.
This week I used the spreadsheet for the first time and it is really making a difference! I’m so excited; I’ll be able to solve problems so much faster. Now, I’m going to have my boss try it out and maybe, combining our brainpower, we can install some more intelligence into the spreadsheet.
For those of you who do reconciliation of data, you really should consider making excel do the work for you. To give some ideas on what to consider, my spreadsheet does the following:
1) It offers a choice on what you want to look at such as notices given, pending contracts, moved out and moved in, cancellations/denials, and renewals.
2) Once you’ve chosen what you want to look at, the spreadsheet lists out last week data, including names, date of application, date of notice, date of move in and date of move out. Next to that list is a list of current week data, displaying that same set of data.
3) The real power lies in its ability to sift out what has changed. The file will compare last week’s list to this week’s list and tell you who has dropped off and who has been added.
4) Another powerful thing is that the file will advise you to look at certain items because it appears that something is off. For example, under notices, if today is May 7th and the notice to move out date is May 2nd, then that person should no longer be under the notices category. That person should have either already moved out (be under the move out category), or have postponed the move out date (so the move out date should have changed) or have cancelled the notice (in which case the person is renewing and should be under renewal). In any case, my spreadsheet should warn you to “check this out” if the person is still being listed under notices. It’s here that I often miss things.
You can make excel do so much for you. Think of ways that excel does so much better than you (compare data, compare dates, determine when you are past a certain date, compare lists, fast calculations, etc.) and design your work so that excel does that part. Elevate your job by doing away the grunt work and create time to do higher level thinking work.