Earlier quoted context omitted.
Yeah this. After futzing with Excel for over a decade, I switched to GNUcash and the budgeting didn't work, then two other commercial products which didn't work, then I tried to design a product which would solve the problem and that didn't work either. So here I am back on a spreadsheet. The problem is sometimes things aren't ideal or simple to represent. A spreadsheet forms no restrictions so you can ad-hoc futz wi…
Do you have a template you could recommend? I have really tried to fit my records into spreadsheets and I just never seem to get something working for some reason. I think my brain is just not wired up for spreadsheets.
One sheet per pay cycle / month labelled accordingly.
One column for Transaction name, one for each account. I have two bank accounts and a credit card.
First row is the summary header which contains the sum of each column (balance), The second row is the column titles, the third row is the starting balance for each of the accounts at the date that the period starts.
All transactions are listed one per row. Name the transaction "shopping" and put the amount as a negative value in the relevant account column. If you are given money it's a positive amount instead. If you transfer money between accounts you put a minus in one column and a positive in the other one.
All reconciled transactions are marked with green background. All waiting for reconciliation transactions (just spent the money) are marked with a yellow background.
All budgeted items are added as rows at the start of the period as formulas so for example in my case `"food", "=-350"`. If I spend something on food, say `"corner shop", "-8.90"` and this value lands in cell B16, then the food budget item is changed to `"food", "=-350-B16"`. This reduces the food budget. The column sum balance doesn't change.
Been using this for about 5 years now. It's currently residing in Apple Numbers because I'm too cheap to pay for Excel and I have an iPad and it works entirely offline so I can use it when travelling to keep an eye on spending.
Once you have regular budget items identified keep a "template" sheet and just duplicate that at the start of the period/month or whenever. Mine has: approx salary, rent, taxes, internet, mobile phone, subscriptions, food, energy and travel budgets on it.