|
- Member PM.Dip
- Practice Licence
- 481 posts
|
Bit of background....
I am trying to find a formula that will look up a list of dates and if it is within a one year range will give a running total for another column. Maybe if I explain the reason it will become clearer what I am after. I have a client that is getting very close to the VAT threshold. They don't want t go over this threshold because most of their purchases are at zero rate (food) but sold at standard rate (when heat applied). If they get too close they will likely shut the shop for a few days). I have explained their profit will be stuck at a certain level if they are insistent on not going above this threshold)
I have a spreadsheet myself where I have all the months as separate tabs and a summary tab with the running total. As it is a bi-monthly client it would be better if they entered these figures themselves and I'm not sure how confident they are with creating new tabs and enetering these new tabs into summary sheet going forward.
What I would like is to be able to give the client a spreadsheet where all they need to fill in is the sales for each day.
Each column would be as follows
A Date
B Sales
C Total for current month
D Cumulative total for previous 12 months
Other possible columns could be
E £73,000 (or whatever relevent threshold is at the time)
F (E minus D)
What would be better (for my own learning of Excel) is if I was given pointers as to which formulas I should be looking into with a view to adapting them to suit my needs.
|
|
- Member PM.Dip
- Practice Licence
- 481 posts
|
An insight into my thought process
So far I've managed to have a column where a date (day) is then converted into a month so that whatever date I have in column A the value in this new column will always be the first of that particular month. Not sure how this will help me yet.
At this rate maybe by 365 days minus 1 day I will have figured out what I need.
Edited at 21 Nov 2011 01:29 PM GMT
|
|
|
I'll help with the monthly running totals then.
So, if A is the date put a new column in G with the following, you can make it hidden if you like.
=MONTH(A1) & YEAR(A1) then in your monthly running total from the second row use a conditional calculation like =IF(G2=G1, C1+B2, B2)
Hopefully this will help a bit. I'll have a think about the annual totals.
Kris
|
|
|
This is a bit longwinded but I've just tried it and it works. First I found the last date in column A by using the formula =MAX(C:C) where C was the Date Column. I then found the start date by removing 12 months from this.
I then used the match function to find what row the start date was on and what row the end date was on, and using the indirect function I calculated the sum of these and everything inbetween for a running annual total.
Hope this helps.
Kris
Edited at 21 Nov 2011 02:28 PM GMT
|
|
- Member PM.Dip
- Practice Licence
- 481 posts
|
Thanks Kris - kind of funny that you should reply as I got onto this idea following a link of yours on another post. The link with the car expenses - I was looking at their other Excel files and it put me onto this idea.
EDIT : Haven't tried the second part yet. The reply had been sitting there waiting to be posted so I hadn't seen it when I hit the reply.
Edited at 21 Nov 2011 02:40 PM GMT
|
|
- Member PM.Dip
- Practice Licence
- 481 posts
|
kjmccullochsaid:
“This is a bit longwinded but I've just tried it and it works. First I found the last date in column A by using the formula =MAX(C:C) where C was the Date Column. I then found the start date by removing 12 months from this.
I then used the match function to find what row the start date was on and what row the end date was on, and using the indirect function I calculated the sum of these and everything inbetween for a running annual total.
Hope this helps.
Kris
Edited at 21 Nov 2011 02:28 PM GMT”
I'm completely lost now. I think my next course will need to be on Excel rather than Level IV ICB.
|
|
|
Would you like me to email you what I have. It's a bit rough, but if you drop me a blank message to kris@kmbookkeeping.co.uk I'll send it over.
Kris
|
|
|
Peasie
I won't try and help you with the spreadsheet because I know nothing about them - I am still using Lotus 123.
But one of your comments sets up a point which I think will probably open a debate.
You say that if your client gets too close to the threshold they will shut up shop for a couple of days.
Firstly of course there is the 30 day rule -
"You must also register for VAT if the following applies:
- you think your VAT taxable turnover may go over the threshold in the next 30 days alone"
When your client does shut up shop for a couple of days - are you also going to apportion the expenses of the business so that only those days on which he trades are claimed as expenses?
What about holiday closures? I hear everyone shouting. Yes those scenarios do exist - but this is not, in my opinion, a scenario whereby HMRC would accept a closure here and there as being reasonable.
You may have already taken this into account but if you haven't - and don't - then in my opinion it could be seen as tax evasion.
|
|
- Member PM.Dip
- Practice Licence
- 481 posts
|
Geoffsaid:
“But one of your comments sets up a point which I think will probably open a debate.”
I had a funny feeling it might and when I saw your name as the "Last post" I knew what was coming. I think they probably will close the shop more often throughout the year. If they see themselves as being punished for working more then they may just take more holidays they feel entitled to. There are only two of them so when one is off they both will take the day/week off rather than find a replacement. Up until a few months ago they worked every day of the week but are now shutting on Sundays.
|
|
- Member PM.Dip
- Practice Licence
- 481 posts
|
I'll hijack my own thread and give my own views on the VAT threshold....
In my opinion everyone should be registered for VAT if they are in business. So therefore the threshold should be set around £10,000. At the moment when someone is hovering around the threshold there is no incentive for them to increase sales. What's the point if you're going to be worse off?
I'm sure I've read that the UK has one of the highest thresholds in Europe.
|
|
|
I agree with you to an extent Peasie. When you hover around the threshold there can seem little point of the extra work and loss of profit. It doesn't really get any better until your turnover increases substantially.
For a business like the one you are talking about, having very little VAT to claim on purchases can make it a lot worse.
If I'm honest I know a few businesses who close their doors a few times a year to avoid hitting the VAT threashold.
Kris
|
|
|
Geoffsaid:
Firstly of course there is the 30 day rule -
"You must also register for VAT if the following applies:
- you think your VAT taxable turnover may go over the threshold in the next 30 days alone"
Hi Geoff,
The 30day rule confuses me.
Does it mean if a busines believes it will sell £73,000 in the next 30days then it must register or does it mean if owners believe the cumulative turnover in the next 30days will hit £73,000 the business must register.
I allways read it as the former but you've got me thinking.
Thanks
|
|
|
I think I should let Peasie answer this as he knows what is coming just by seeing my name
This is from HMRC web site regarding VAT1 completion
How do I calculate my turnover when answering question 19?
You must declare the date on which the value of your taxable supplies in the last 12 months or less (or your turnover in any previous 12 month period) exceeded the threshold. The 12 month figure is based on a rolling calculation, ie after the end of any rolling 12 month period you should add the value of your taxable supplies for month 13 and deduct the value of supplies made in month 1. If the new cumulative turnover figure exceeds the registration threshold, you are required to register.
When calculating if I will exceed the VAT threshold within the next 30 days, do I need to include previous turnover?
No - you only need to consider if the total value of the taxable supplies you expect to make in the next 30 days ALONE will exceed the VAT threshold.
I have identified a date on which the expectation arose that I would exceed the VAT threshold within 30 days of that date. From what date will my registration take effect?
Your date of registration will be the date on which you declare that the expectation arose. You cannot be registered any later than this date, but you can request an earlier date.
|
