New Year – new spreadsheet?

[JacquiLewis]

Having started on our annual inspections I want to ensure more comprehensive, searchable, tracking of the 16 flats we have. I don’t mean tracking progress of jobs – I mean tracking what has happened in which Flat when (and obviously fro grounds and communal areas eg laundry rooms). Please would you share ideas of how to keep track? I am a Volunteer Clerk (on a small honorarium) and have neither the time nor expertise. I used a google sheets last year and tried to manage everything on one sheet, but I suspect individual sheets for each Flat would be easier. I have information for the last 18 months so intend to back fill when time permits so we have a record moving forward. For example, no one knows when the washing machine in Laundry room 2 was replaced, and neither do we know when a room has been painted.

[clerk.stjohnswilton]

Jacqui

I run a cash account on an Excel spreadsheet. I analyse expenditure by I&E area (eg Clerk’s salary, Grounds Maint, Maintenance (Electrical), Legal Fees etc etc.). For each payment or receipt I record the date, the type of payment (eg Bank Payment, DD, SO etc), the Payee, the details of the payment, the PV/RV, the Flat/Cottage associated, the Budget Month of the spend/receipt, the amount (obviously), the spend receipt analysis., and whether the transaction has been reflected in the bank statement.

It’s a simple job to cut and paste all maintenance or refurbishment transactions to a separate spreadsheet, and record what’s been done, when, where and at what cost.

Nick Stiven
clerk@stjohnswilton.org.uk

[JacquiLewis]

Ah, I have been running a single spreadsheet for all the properties and wondered whether to have one for each. I am trying to put together a maintenance plan as everything has been done on an ad hoc basis with little recorded. I think I spoke to you last year and that is when I started everything on a single google sheet, but it has become unwieldy and disorganised. Need to think!

[clerk.stjohnswilton]

Jacqui

Several worksheets would certainly be complicated to coordinate. Have you thought of running one worksheet but organising/filtering (and then presenting) your data using pivot tables?

Happy to discuss if you have the time/interest!

Nick Stiven

[JacquiLewis]

Pivot? I know something called pivoting exists but…

I really need templates that i can just use. I do not have such computer skills.

[Sally Bertram]

Hi Jackie,
I keep a really simple excel spreadsheet that doesn’t require ‘better than average’ spreadsheet knowledge!
I use a different tab along the bottom for each almshouse flat (we only have 4) and manually record when any maintenance / decoration work is done, who by and cost. At the top of each page I have typed a reminder to myself that e.g. Trustees have agreed flats should be redecorated every 7 years; bathrooms x years and kitchens x years. Then I can easily check each flat to see if anything is needed.
I also have a tab for communal area, which in our case is just a hallway and stairwell, as well as external building and garden.
I tend to forget to keep it updated every time some work is done, but going on it every six months or so it’s easy to fill in a couple you may have missed and easily check where things are up to.
May be a bit clunky for those who are more spreadsheet savvy, but it works for me!

[JacquiLewis]

Simple, albeit it clunky, is just what I need. I had not even thought of using tabs within a spreadsheet (embarrassed face). We have 16 flats and communal areas as well as the grounds and I definitely need a way of recording everything. I compile a monthly report for the Treasurer which includes a table listing expenditure so I may be able to use that to back fill.

[LWhite]

Have you tried setting up a compliance calendar on Outlook as an additional diary ?

[JacquiLewis]

I am not sure what you mean by compliance calendar. I have set up a google calendar (we now have a G Drive and use the rest of the software to keep documents – there was no digital storage of anything until I joined, and the paper storage is somewhat difficult to navigate). and note when things need doing (with repeats where necessary). Is that what you mean?

The practical difficulty is we know the stairwells have not been painted in the last 22 years, we have no idea when Flats were painted or showers in stalled. We do not even know how old the white goods are. I obviously want to rectify this – and plan a schedule for getting things done.

[LWhite]

HI Jacqui – If you use an online diary/Calendar on your work email account ( i use outlook for example ) you can often add another calendar , i have done this and called it a compliance calendar and i use it to note when checks are required for addresses and when things are due, so basically an annual electronic plan for when things are needed. It isn’t ideal but it may be an easy way to put in annual checks for example and reminders that you can check daily by looking at the month view to see what is coming up. You do have to just remember to check it each week though . hope this makes sense, Lisa

[JacquiLewis]

Makes perfect sense, I use a gmail attached to my ‘work’ email and can easily add another calendar.

Thank you

[William Clemmey]

Hi Jacqui
We have a number of schedules which our Scheme Manager keeps
Painting and decorating schedule
Eicr and EPCs
Refurbishment schedule which shows when the residents flat was last psinted, had new shower, was painted, new kitchen and new carpets
PAT testing

We repaint every 7 years
Happy to share any of them with you
William Clemmey
Chiefofficer@municipal-charities.org.uk

[Author]

Posts