'How many spreadsheets and graphs do you have?' blog discussion.

missrlr

I have the Daddy of all spreasheets that has been in existence since I graduated. It has a tab per year broken down per month with each account in different colours, income and expenditure separated and then subdivided into groups relating to what the source / final deisnation for each group, colour coordinated and group referenced with graphs showing %increase year on year trending for all bills,out goings, income and savings. It also references to pensions payments and projects expected income with added analysis of % saved into pensions versus total amount earnt, and cross checks on the bottom for savings accounts with interest added.
each year a new one is set up with project spend on it for all groups, which is effectively the budget and accounts for all expenditure regardless of monthly/quarterly / annual. Each month the statemetns of credit cards are added to this cross checked to the receipts and totalled with the balance confirmed as zero on each month as all surplus cash is syphoned off to a savings account.

There is also a mortgage sheet showing interesting overpayments etc on it

Anal ... oh yes! Amazing how many people want to have a blank copy for thier own stuff once they see it.

Oh and it has logic statements that prevent you from ending up with errors and cross check calculations for sources so you know the answer is always correct at the end.

kharyss

Ooooo I feel so much better now I know I'm not alone. My family think my spreadsheet useage is a form of OCD, but without it my life would grind to a screeching halt!

At the moment I track:

• my weight loss (actual and target) and body fat percentages for the last 4 years (calculating BMI/KG's) which gives me a lovely trend and forecast graph (5 stone lost so far!!).
• Weekly mileage/pence per mile/weekly & monthly average petrol cost/pence per litre for the last 3 years. It really brings it home looking at the petrol price rise in a graphical form.
• Smoking - or lack thereof! Plus amount saved by not smoking since quit day and in a rolling 12 months.
• Gym membership - how many times I go and therefore cost per visit, and time/activity while I'm there.
• Budget - a 4 year monthly budget and forecast of spending - although this does make some assumptions about inflation that will need revising soon.
• Really nice pie charts of my monthly spending by category eg food shopping/toiletries/entertaining/household bills/car expenses etc etc
• Income/source/tax paid/NI contributions/P60 and P11d data for each of the last 8 years.
• Credit card - debt/interest/payment schedule and forecast clearance dates
• databases of insurance renewals/premiums/quotations etc with annual percentage rises
• 5 years worth of a "presents" database, tracking average spend per person per event, type of item, where bought, warranty period etc
• Jogging times/distances/speeds
• And finally a list of my annual goals. What I want to achieve/progress/when it was completed/archive of previous years' etc.

I have loads of other databases for things as obtuse as what I wore on each date with my boyfriend, times I get in and leave the office, and what my blood pressure has been each week.

The only really compulsive bit is having to back it up to 2 different memory sticks every day in case I lose one!

Sigh - this board feels like group therapy "Excel Users Anonymous"

curlygirl1971

missrlr wrote: »

I have the Daddy of all spreasheets that has been in existence since I graduated. It has a tab per year broken down per month with each account in different colours, income and expenditure separated and then subdivided into groups relating to what the source / final deisnation for each group, colour coordinated and group referenced with graphs showing %increase year on year trending for all bills,out goings, income and savings. It also references to pensions payments and projects expected income with added analysis of % saved into pensions versus total amount earnt, and cross checks on the bottom for savings accounts with interest added.
each year a new one is set up with project spend on it for all groups, which is effectively the budget and accounts for all expenditure regardless of monthly/quarterly / annual. Each month the statemetns of credit cards are added to this cross checked to the receipts and totalled with the balance confirmed as zero on each month as all surplus cash is syphoned off to a savings account.

There is also a mortgage sheet showing interesting overpayments etc on it

Anal ... oh yes! Amazing how many people want to have a blank copy for thier own stuff once they see it.

Oh and it has logic statements that prevent you from ending up with errors and cross check calculations for sources so you know the answer is always correct at the end.

OMG I pratically hyperventilated when I read through this. If only I knew you in real life! I've shared blank copies of mine before now but it's not as sophisticated as yours by the sound of it.

curlygirl1971

kharyss wrote: »

Ooooo I feel so much better now I know I'm not alone. My family think my spreadsheet useage is a form of OCD, but without it my life would grind to a screeching halt!


At the moment I track:

• my weight loss (actual and target) and body fat percentages for the last 4 years (calculating BMI/KG's) which gives me a lovely trend and forecast graph (5 stone lost so far!!).
• Weekly mileage/pence per mile/weekly & monthly average petrol cost/pence per litre for the last 3 years. It really brings it home looking at the petrol price rise in a graphical form.
• Smoking - or lack thereof! Plus amount saved by not smoking since quit day and in a rolling 12 months.
• Gym membership - how many times I go and therefore cost per visit, and time/activity while I'm there.
• Budget - a 4 year monthly budget and forecast of spending - although this does make some assumptions about inflation that will need revising soon.
• Really nice pie charts of my monthly spending by category eg food shopping/toiletries/entertaining/household bills/car expenses etc etc
• Income/source/tax paid/NI contributions/P60 and P11d data for each of the last 8 years.
• Credit card - debt/interest/payment schedule and forecast clearance dates
• databases of insurance renewals/premiums/quotations etc with annual percentage rises
• 5 years worth of a "presents" database, tracking average spend per person per event, type of item, where bought, warranty period etc
• Jogging times/distances/speeds
• And finally a list of my annual goals. What I want to achieve/progress/when it was completed/archive of previous years' etc.

I have loads of other databases for things as obtuse as what I wore on each date with my boyfriend, times I get in and leave the office, and what my blood pressure has been each week.

The only really compulsive bit is having to back it up to 2 different memory sticks every day in case I lose one!

Sigh - this board feels like group therapy "Excel Users Anonymous"

:rotfl:I just sprayed my coffee across my desk when I read this. Yes, I've tracked some bizzarre things in the past. I do the memory stick thing too.

As for group therapy - no - I think we're just encouraging each other - I've gleaned a few ideas from this thread and am now looking at my current functionality as inadequate. I have spreadsheet envy

Percy1983

I am a proper Excel geek (being an accountant it comes with the job).

I have sheets to calculate ISA's exapect outcome as well as interest to date (changes every time you open it), as it is I have mine by better halfs and future mother in laws on there.

The I have my budget and debts sheet, of course the budget changes link live into the debts sheet to show my updated ability to clear. The geeky bit on this is has a bar which counts down the months to being debt free and as we update the figures the months light up green to show we are on target.

Of course there is the usaul ebay sheets which track every fee per item to show a real turnover.

curlygirl1971

Oh my god I don't think I can cope reading some of these posts. I either need to go on 'Advanced Excel' or we should all meet up with our laptops and set of SS's populated with faux-figures

Eco_Miser

I'm not a spreadsheet geek, and i hardly do graphs at all.
I had a spreadsheet for my daily weight, but I gave up weighing myself daily. I do speadsheets comparing features and prices when I'm considering a major purchase. Then I have the OpenOffice Calc workbook with 42 spreadsheets in it.

• One for each place I've put my money
• One for each organisation who sends me bills
• one each for gas and electricity consumption, as well as the one for the energy company showing the monthly payments.
• I dropped the salary sheet when I retired
• One collecting annual income by source, with tax credit/ tax paid.
• One budgeting monthly expenditure.
• One totalling actual monthly spending - with a graph showing moving averages over 2,3,6,& 12 months - all figures taken from the bank account sheets.
• one predicting monthly income, it varies a lot depending on dividend dates.
• Most importantly one telling me how much and when to move between accounts (esp. Lloyds Vantage and Halifax Reward) to meet funding requirements, have money available for payments, and maximise interest.
• and a summary showing the total in each account, and the grand total; and my spending over the past 12 months, and the result of dividing the grand total by the spending, giving the number of years I can live off my savings, plus my age now, giving my age when the savings run out, currently 110. This is a rough estimate, ignoring both inflation and the income generated, and also the pension I will be getting.

The live version isn't on my hard drive, or a USB stick, but on a titchy 16MB SD memory card that came free with my camera but is too small for a sensible number of pictures, but has plenty of room for spreadsheets.
I archive a version at the end of every year (as well as making regular backups), then take out unneeded sheets and add others (blank budgets).
I've been doing this since 2003, when I switched from a paper book going back to 1968.


Do you all put your totals at the bottom of the sheet, where you have to scroll to see them, or near the top in a consistent, easily seen position?
My totals are nearly always in row 3, with the sheet title in row 1, and column headers in row 3.

MSE_Martin

Hmmm reading the above I think maybe I should set up

spreadsheetdates dot com where we match up people on dates depending on what they graph

Percy1983

curlygirl1971 wrote: »

Oh my god I don't think I can cope reading some of these posts. I either need to go on 'Advanced Excel' or we should all meet up with our laptops and set of SS's populated with faux-figures

There is nothing I haven't managed to do in excel yet and there is other on here from the sound of it. If you want a template for something setting up I will be more than happy to put a few things together, the geekier the better.

curlygirl1971

Ah bless you. I'll have a think about it.