Excel formula for spend tracking

B0bbyEwing

getmore4less said:

Just recorded in a spreadsheet won't achieve the goal.

 

No but it does highlight where we can improve.
Like it's no good me thinking we overspend on certain areas. Well actually I know we do, but even that's a case of so what. Running a spreadsheet, a database, a PC program or even plain old pen and pad can help highlight how bad or not so bad a spend is. It could be a case of wow I didn't know we were spending that much or that's not so bad I thought we were spending much more, but we wont know until we track.

FWIW I largely agree with your post. We come from two different backgrounds. Both of us grew up with struggling parents but where my parents would save and spend occasionally, partners parents, or rather dad, would play hot potato with money. Get it spend it, then go back to struggling. 
When I met partner, naturally they were in debt. I helped them make a plan & work their way out of it without taking loans or going bankrupt etc like people around were doing. What I'm saying is saving is no problem for me when I have things noted down and a proper plan in place. Not so natural for partner.

Not that we're in debt but I'll head over to the debt forum to see if I can pick up any ideas. I'm always open to suggestions.

getmore4less said:

Do you have a proper budget?
I suspect not with this starting point

It's an essential part of financial planning.

Decide where you want your money to go then map that onto reality and try to get them to match.

A good trick is starting from scratch is do a retrospective of the last 12 months, work out where all your money went.

It easy to start, as a lot is big easy items often on DD.  When people do this they often can't account for a big chunk.

Using that for the basis of the plan of where you would like to get to.

Normalize on annual totals as that gives the big picture.

My old example was our weekly Friday  takeaway, when we analysed we changed the routine and used the money for an extra holiday.

It does not have to be all about saving (unless there is not enough) it's about maximum value.

Sorry we posted basically at the same time so I didn't see this.

No we don't have a proper budget. We have no kids, we don't earn great but we don't earn bad either. Combined probably in the 45-50 region. What we tend to do is just wing it. We'll go through not spending & then just spend. So long as the balance doesn't plummet continuously then we carry on. When big hits come we reign it in. 
It's not actually an approach I'm overly comfortable with. It's been working (by working I mean we've been able to build savings rather going in reverse) but it's not a good system at all and therefore our savings could build faster. 
What's sparked me pulling the finger out has been the upcoming energy increase as well as a realisation that we need to put more away for retirement.

We tend to have a lot of out of the blue payments which I'm not keen on. Some of it is poor planning such as forgot to get stuff sorted for tea so get a takeaway. Some of it is being lazy such as can't be bothered to make tea after a crap long day at work so get a takeaway (honestly this makes it sound like we have a lot. It's not as bad as I'm making out actually). Some of it is deviating from the list such as going to the supermarket for a weekly food shop on an empty stomach & then buying this snack & that snack. 
But as that makes it sound like everything is food related, some of it will be just browsing eBay for example and going oh I'll buy that when I don't really need that. 

The point is we need to just get things noted down & go from there. 

getmore4less

You have historical data use that to get started, it will take a few months to get the picture

Maybe do a 2022 budget 7m history 5m to work on it ready for a new start 2023.

Good planning and you don't get many unexpected events, most things can be planned/predicted just can't be predicted 100% on time and cost.
House and car are often ones that catch people out..

On the food front there are great ideas for home made ready meals faster than a takeaway.

Old-fashioned board.

[Deleted User]

B0bbyEwing said:

Deleted_User said:

Or what I would highly recommend is an app called YNAB that I've used for many years now for budgeting and spending tracking:

h t t p s : / / w w w . y o u n e e d a b u d g e t . c o m /

You can use it all online and use a mobile app to input spending immediately as it happens, or just import just transactions from your bank.

Thanks. Sounds promising. 
Not sure about importing transactions from the bank as we have that many accounts that it depends what's being spent on as to what account is being used. It may be the Amex C/Card, the Nationwide C/Card, my Chase acc, partners Chase acc or another acc. 
I can take care of the direct debits, that's easy enough. It's the going to a supermarket, cafe, sweet shop, market stall etc where it may fall down so am looking to reduce that.This YNAB you mention, can you share an account?

So for example, partner goes to the supermarket and buys food to make dinner tonight. They update this account in YNAB to say they just spent £9.99. At the same time though I'm also at the corner shop picking up a newspaper & a sly chocolate bar. Can I update that exact same account so we can both see the spends or do we each need individual ones to manage ourselves?

YNAB got me out of debt, I was heading towards a potential 2nd bankruptcy so it literally is the best thing I can recommend.

You can import transactions from multiple accounts or add manually using phone app or web browser. I have about 15 accounts / credit card / savings etc and YNAB handles them all but the trick is you put everything in YNAB, no point just tracking some things, you need to track every single transaction.

Yes you and your spouse can use the same app and update a spend in the scenario you mentioned which actually is the magic of this software - say for example you budget £50 a month for chocolate, there is £45 spent already, you and your spouse are in different shops, spouse enters £5 spent on chocolate and immediately you can see that reflected on your app and will not spend £5 on chocolate because there is no chocolate budget left.  (swap chocolate for wine / cigs / lottery tickets etc)

YNAB is a process more than a tracking app though and you have to get used to the philosophy.

1. Each month you enter your expected income.

2. From that income you divide it up into pots of your choosing such as gas, rent, diesel, food, takeaway, wine, taxi, pub, gym, birthdays, emergencies, interest payments, savings etc - all your choices.

3. You budget to zero - so every single penny of your income goes into a different pot. YNAB does most of it automatically after the first month based on the previous month, so 1st month is hard to set up but really easy after that.

4. Throughout the month you update YNAB with your spending from each pot so you can see how much you have left for each pot and the idea is you only spend that much, nothing more! You can move money between pots if say you decide to give up wine that month so move the £50 to the takeaway pot or the savings pot etc - really flexible.

5. You have an unexpected expense such as car breaks down, well in theory you should have an "emergency fund" pot but if you haven't you can cream off all your other pots, pay for the car repairs and see exactly where you have to tighten up - no wine and chocolate for the rest of the month but you will know all your bills will be paid.

YNAB really helps make good buying decisions. So you see your bank balance of £400 when you decide you have plenty to get a takeaway. Consult YNAB and look at your pots and if your takeaways pot has £2 left in it then you can't afford a takeaway despite having £400 in your bank account - because YNAB knows that £400 is already spoken for before next payday in other pots.

You don't look at your bank / saving / credit card balance once you get the hang of it because it doesn't matter - you look at your pots when you decide to buy something or go food shopping.

getmore4less

Proper budgets are about planning in advance.

You should already have the chocolate purchases planned and agreed.

When I looked at YNAB it was doing basic budget stuff like zero  in a way designed to tackle the debt prone people that struggled because they did not plan.


Pretty sure it has moved on to overcome some of the early problems like not being able to do decent annual planning and multiple income streams.


It works but the free options can do the same job.

There is nothing magic about planning in advance and tracking spending.

tempus_fugit

getmore4less said:

Proper budgets are about planning in advance.

You should already have the chocolate purchases planned and agreed.

When I looked at YNAB it was doing basic budget stuff like zero  in a way designed to tackle the debt prone people that struggled because they did not plan.


Pretty sure it has moved on to overcome some of the early problems like not being able to do decent annual planning and multiple income streams.


It works but the free options can do the same job.

There is nothing magic about planning in advance and tracking spending.

Absolutely agree with this. YNAB is expensive and whilst it has some bells and whistles that I like to have, it’s not necessary to employ the “budgeting to zero” method. This could be done with pen and paper or a simple spreadsheet. There is actually a good free option out there that I have used myself, it’s called Aspire Budgeting (www.aspirebudget.com) and uses Google Sheets, which is also free. There are loads of simpler options too but I think Aspire is worth a look as it essentially works the same way as YNAB, albeit it’s a bit of a learning curve TBH. As I say though, there are plenty of options out there.

[Deleted User]

getmore4less said:

You should already have the chocolate purchases planned and agreed.

This made me giggle. 

You are right but being able to make some  spontaneous purchases does make life more fun. 

Having a pot for such things is my way of not being a slave to a budget plan but also keeping spending under control. 

getmore4less

[Deleted User] said:

getmore4less said:

You should already have the chocolate purchases planned and agreed.

This made me giggle. 

You are right but being able to make some  spontaneous purchases does make life more fun. 

Having a pot for such things is my way of not being a slave to a budget plan but also keeping spending under control. 

It's a reasonable example of why the normalization over a year can be important.

If the budget has say £10week for chocolate that's £520py the trick is to track so the rolling 52w average stays close to £10, it can go up/down.

Also allows for stockpile when cheap.
Big fan of this strategy, buy more when cheap, ok for toilet rolls but a pile of chocs may be too tempting.

Same with the just because I'm worth it pot

spontaneous spends happen but need tracking.

Debt busting does not always allow this level of flexibility but can save more than the interest on debt.

I spent a lot of time on the fool and debt boards.

The biggest problem many start with is they don't have a clue where all their money goes.

That's why I recommend the retrospective look at the previous year try to work out where it went.

[Deleted User]

Hi,

jings this has gone on for 3 days now, numerous posts.

OP you are over complicating things.

You can simplify your sheet by taking out the non essentials, takeaways, coffee shop, rubbish spending.

Why not cook at home, make something to take at work.

You will save money, you may eat healthier.

getmore4less

frugalmacdugal said:

Hi,

jings this has gone on for 3 days now, numerous posts.

OP you are over complicating things.

You can simplify your sheet by taking out the non essentials, takeaways, coffee shop, rubbish spending.

Why not cook at home, make something to take at work.

You will save money, you may eat healthier.

Often it is the "essentials" where the bigger savings are.

Mobiles, TV packages, insurance, regular shopping, booze...

An easy save it to track what you throw out from groceries, that's stuff that did not need buying.

B0bbyEwing

frugalmacdugal said:

Hi,

jings this has gone on for 3 days now, numerous posts.

My last post was 2 days ago. I have only posted on the thread on 2 different days. Other peoples responses continuing the thread are out of my control.
Not that I'm complaining about people responding. Far from it. There's plenty of threads in this forum that don't interest me that've gone on for 1 day or many days. I just leave them be though, I don't comment & I only open or even comment on the threads that interest me

frugalmacdugal said:

You can simplify your sheet by taking out the non essentials, takeaways, coffee shop, rubbish spending.

Why not cook at home, make something to take at work.

You will save money, you may eat healthier.

You remind me of my boss who complains to me that those I'm in charge of make mistakes. 
He tells me to tell them how to do the job properly.
I inform him I've told them, many many times.
He tells me to tell them again.
Well why didn't I think of that? I've only told them 10 times. I hadn't thought of the idea that telling them 11 times would unlock the do-as-you're-told feature.  

But really though, what you said - I know this already. 
We do cook at home. Most weeks the meals are cooked at home 7 days out of 7. This may happen for example, 10 weeks in a row. Then we'll have say 2 consecutive weeks where we may end up having say 3-5 takeaways over that fortnight period. I'm just plucking numbers out of the air to illustrate so don't latch on to them too much.

It's basic common sense if you don't buy non-essentials then you will save more than if you do buy them. 
If you just live on rice and beans with no sauce for flavourings and only drink tap water then you'll make maximum savings. That's basic common sense. If we take it down to absolute base level and only spend a penny on anything that we actually need then of course we will save.
Some people will have to do that out of necessity. I suppose some people may even enjoy living like that. Not sure who but there must be some somewhere, but it doesn't have to be an extreme at either end of the scale. Most people I would say live in between somewhere on the sliding scale. 

All my work meals are made at home beforehand. Pasta, chicken, tomatoes, cucumber, mushroom, onion, herbs. Fairly healthy I'd say
But at the same time, I don't see anything wrong with looking at areas where we can make alterations.