What's In Your Spreadsheet?

MisterMotivated

Hi all,

After my first few posts, it seems apparent that there are a lot of members on here who have a nice healthy obsession with using spreadsheets to help manage their personal finances. I've been doing this for a few years and it seems I've created a monster, with 99 tabs at the last count :eek:. If I hadn't changed my mortgage, it would've been well over 100 as last year's had various tabs for mortgage forcasts/repayment schedule/interest savings from overpayments, etc. A lot of the tabs aren't strictly necessary, but I do enjoy adding to the spreadsheet and finding new ways to interpret the data on there.

Out of curiosity, I thought I'd start a thread to see what the wider MSE population have on their spreadsheets. Hopefully, I can then assess whether I need professional medical help . To start off, I've listed my main tabs below, along with a brief description. I'll leave out a lot of the repetitive graphs. Apologies for the mixed up order too, I really need to go through and have a tidy up

1. Balance Sheet - used to compare my recorded income/expenditure and compare it to my actual bank/money jar/credit card balances. Very handy for flagging when I've forgotten to add something
2. Budget - a forecast of the current year's income/expenditure, with one side for forecast and the other showing actual spend, along with 'better/worse' indicators
3. Up Your Income - a list of all different survey/cashback/comp sites I'm registered with, along with occasionally updated balances. It also includes notes on other possible money-making opportunities
4. Weekly Balance - a table (updated manually every Friday) showing my overall balance at the end of each week. Includes a line graph and a "Weekly Average" cell.
5. Weekly Spend - another table/graph combo, which automatically totals my spending for the week. Includes Daily and Weekly Average cells.
6. Totals - my main 'overview' tab, showing two tables - monthly income/expenditure/difference and comparison against monthly budget. Also includes a graph and various other info, including current "Overall Balance"
7. Interest - table showing interest from various accounts
8. Income - 6 tables showing different income types, including a 'miscellaneous' one for random income
9. Fuel - two tables - one for car, one for bike - showing date, brand, number of litres, price and total. A third table shows monthly totals and a fourth calculates miles per gallon, £ per mile, etc
10. Annual Outgoings - one table showing monthly spend against 34 budget categories, with Total/Average/Remaining columns at the end
11. All Expenditure - my spending diary, listing everything bought over the year, with date/location/category/brand/payment method info. The centrepoint of the whole spreadsheet
12. January - December - 12 tabs - one per month - showing daily totals against each of the 34 categories. Includes totals, plus daily average spend
13. Blocks (1-56) - :eek: using simple formulae and conditional formatting, 56 variations on a graphical mortgage motivator detailed in my MFW thread (sorry I can't post a link yet). I'd be interested to hear of any similar techniques people use to motivate themselves.
14. Car Costs - tables showing all car running costs, with historical totals of cost per mile, etc. Another table shows distances of common journeys alongside the cost of the journey
15. Bike Costs - exact same as above but for my bike
16. 2013 Budget - that was me trying to plan ahead
17. Yearly Comparison - table showing year on year comparison of things like salary/other income/monthly expenditure/fuel litres/£ etc, with red/green lights for better/worse comparison
18. Shopping & Utilities - a large line graph showing gas/elec/phone/bb/groceries monthly spend
19. Shopping Comparison - List of all shops/places I've ever spent money, showing year on year how many items bought and the total spend. A second table does the same for the 34 budget categories (might work better as a pivot table, so will look into this when I have some time)
20. Daily Spend - one simple table showing total spend for each day of the year. Also shows total No Spend Days per month
21. Daily Utilities - Daily estimate of gas and electricity consumption/cost based on meter readings. This has been somewhat abandoned as I got fed up going out in the cold/rain/snow to take readings and the times were not consistent.
22. Utility Tracker - monthly estimates based on meter readings, then compared with online bills. It's not 100% accurate due to possibly having the wrong gas conversion factor, along with some online discount/VAT uncertainties. It's close enough for now
23. Price comparison - another, fairly unused, table showing various food/home consumables and the normal cost in each of the local supermarkets. This became quite a nuisance to keep up to date, so I may just get rid of it
24. Notes - a dumping ground for random finance related notes and scribblings of my ideas for new tabs
25. Relocation - another obsolete tab with info on my spending during relocation, plus some lists such as which companies I needed to update my address with
26. CC Manager - an overview of all my credit cards, showing rates and interest free period expiry dates, credit limits and available balance.
27. Credit Cards - more detailed info on each card, with opening balance, spend spend (totalled from "All Expenditure" based on payment method), current balance, available credit and next payment amount
28. Mortgage Payments - the remnants of my once impressive mortgage section, shows payments/interest so far, plus two tables showing A) a forecast of interest rates at each £500 capital reducation, and how much I'd need to pay to reduce monthly interest (shown in £1 increments)
29. Property Manager - an overview of all my Buy-to-let related income/expenditure. I had several more tabs but these have been moved to a separate spreadsheet
30. Target - a huge font tab simply showing savings target, savings amount and percentage of progress
31. Bare Bones Budget - another budget tab showing best case scenario for all income/expenditure, to give me an idea what would be possible if I cut back even more (current budget is fairly conservative as I'd rather be surprised than disappointed at the end of each month:rotfl:)
32. Food Price Tracker - something I created when I was bored over winter, showing 'cost per portion' of things like a block of cheese/loaf of bread/box of cereal. This feeds into the next tab
33. Meal Cost - as above, when I was bored, I'd started to wonder about the actual cost of making a sandwich for work, or making dinner, etc. So I started to work out cost per portion (above) of certain things, then added them together to show the real total cost of different meals/snacks

So.....that outlines most of my spreadsheet as it currently stands. I'll need to set aside some time during the evenings this week to go through and tidy/delete a lot of the tabs (and perhaps find the number for a good, reasonably priced counsellor:rotfl:).

MisterMotivated

With regards to point 13, here's my comment from the other thread about the "Blocks" tabs

MisterMotivated wrote: »

Basically it involved making the cells square and (through use of conditional formatting and some simple formulae) using them to make patterns. I'd assign them all the same value (say, £25) and for every £25 I paid off, I'd de-colour one of the boxes, building up a pattern as I went (sort of like a geeky, digital crochet). The problem is, I got a little carried away, so I now have about 30 variations, with different patterns (and some with different amounts). Sadly, these aren't getting used at the moment, as my main focus is on rebuilding savings first.

missile

Wow that is being extreme, talk about OCD

jen245

Yikes, that sounds like a nightmare to manage! I personally don't have one, with online banking, I really don't see the need, I know what I need to pay and when, but to each their own

MisterMotivated

Actually, it's surprisingly easy to manage. I normally plug the figures into the spending diary tab and the rest updates automatically (bar one or two little bits here and there).

Sharon87

I don't use tabs on one excel spreadsheet. I use multiple files. I have:

Debt Spreadsheet - update once a month with all my balances

Income spreadsheet - put my pay in and the deductions and then it works out if I'm owed or owe any tax at the end of the tax year

Spending Diary (sometimes I use it sometimes I don't) which I record every income and outgoing, then use a code for each thing, so bills, food, rent.etc are all a different code and then a formula works out how much I've spent on each category and takes it off my budget for that category. This is a monthly thing when I can be bothered doing it lol.

Also a Work Diary, to keep track of what days I work and where as I do short term contracts, so I know if I get paid correctly

Chrisblue1962

99 tabs seems like 89 too many....

Surely file that size would take ages to load up and I hope you have a good backup procedure!! - sounds like a vast amount of data just waiting to be lost..:D

My monthly spreadsheet has two monthly budget pages (current month and previous month), savings accounts records / balances page, one for gas & electricity meter readings, one for a credit card and one for vets bills.

Gonzo33

Blimey. I have a tab for every month of the year, did have a loan tab but that is repaid in full now, a house save and expense sheet and a Christmas/Birthday Expense sheet too. So 14 in total. I then have the same for our overseas account because that is where we currently live.

My UK spreadsheet is reconciled monthly, and I have a projected budget for every month between now and the end of the financial year.

MoneySavingUser

Sounds very detailed!!

I just have a spreadsheet with totals for my accounts - and look on bank statements if I need to check something specifically

cleggie

I only started my spreadsheet a few days ago, and i love it already!!

I have 7 tabs, but am thinking about Gonzo33's idea, above, about having one for each month of the year, it sounds like a good plan!
Might go and have a play around with it, thanks Gonzo33!

gollygosh

Heck! that's a lot:D I feel very inadequately tabbed!
I only have one. It's got 12 months on it and has d.d.'s ,spends, petrol and other, savings pots does a couple of calc's and works out how much I've spent and how much is left each month.
All the other info can be found elsewhere and although I do like everything to be together I hate duplicating.