12 month rolling calculator or spreadsheet for vat

vat threshold

Didn't find your answer?

Hi 

I am a small business with just me working in it, I want to know if there is a spreadsheet or if anyone can help me with one that I can use to keep a check of keeping under the vat threshold.  It would need to be a rolling 12 month has anyone any idea how i can make one please or help me it would be much appreciated.

  

Replies (16)

Please login or register to join the discussion.

avatar
By SXGuy
14th Mar 2022 13:10

The simplest way would be to add a colum and input each turnover figure for each month down each row.

Then total the last 12 months of entries. Rince and repeat each month.

Thanks (0)
Replying to SXGuy:
avatar
By lancashirelass
14th Mar 2022 18:38

thankyou x

Thanks (0)
Scalloway Castle
By scalloway
14th Mar 2022 13:10

I would take each of the last 12 months turnover from your accounts package and each month add the current month and delete the oldest one.

Thanks (3)
Replying to scalloway:
RLI
By lionofludesch
14th Mar 2022 18:15

scalloway wrote:

I would take each of the last 12 months turnover from your accounts package and each month add the current month and delete the oldest one.

That's what I used to do.

Ten seconds on a calculator. Why set up a spreadsheet for something so quick ?

Thanks (1)
Replying to lionofludesch:
avatar
By lancashirelass
14th Mar 2022 18:38

Thankyou x

Thanks (0)
avatar
By paulwakefield1
14th Mar 2022 13:26

Umpteen ways as indicated.

Here are two.

a) enter each month in either a row or column. Highlight the last 12 and the total will be displayed in the status bar at the bottom right of the screen. tip: Right click on status bar and tick "Count" or "Numerical count" (safer) and that way you can tell when 12 months are highlighted as well as the sum.

b) As before, enter the totals in a column - say in Column B (then Col A can be used for dates) and the following formula somewhere on the spreadsheet: =SUM(OFFSET(INDEX(B:B,COUNTA(B:B)),,,-12)). Do not have anything else in col B except the monthly turnover figures.

Thanks (0)
Replying to paulwakefield1:
avatar
By lancashirelass
14th Mar 2022 18:39

Thankyou x

Thanks (0)
paddle steamer
By DJKL
14th Mar 2022 13:38

If listing all invoices with their dates and amounts I suspect (have never done this) you might use an IF/THEN command using the invoice dates to add the corresponding cells with the numbers by using the latest date as end date of the range and the start date of the range being 365 days before. The sum of this population could likely auto calculate each time an invoice was added to the list.

If I get bored later I may have a play about to explore the art of the possible, it may be a two/three stage calc first creating the date range for each calc and then performing the sums.

Thanks (0)
Replying to DJKL:
avatar
By lancashirelass
14th Mar 2022 18:39

Thankyou x

Thanks (0)
avatar
By paulwakefield1
14th Mar 2022 14:41

A variation on a theme which also picks up DJKL's point is:

=SUMIFS(B:B,A:A,"<="&MAX(A:A),A:A,">="&EOMONTH(MAX(A:A),-12)+1)

where Col A has dates and B has amounts.

The formula sums from the latest month or invoice back to the start of the 12th preceding month. e.g. if the latest invoice is 14 March, it will sum from 1 April to 14 March.

The use of EDATE would allow for a precise 12 month period to the day.

Thanks (0)
Replying to paulwakefield1:
avatar
By lancashirelass
14th Mar 2022 18:40

Thanks x

Thanks (0)
Replying to Heliosflower:
avatar
By lancashirelass
14th Mar 2022 18:40

Thanks x

Thanks (0)
avatar
By lancashirelass
14th Mar 2022 18:42

I think I may have a clue how to do it and will work through the responses you have sent to me. I dont understand all the jargon but will try to work through what yu have all said and try the various ways

Thanks (0)
avatar
By Watson Associates
15th Mar 2022 13:44

Excel Spreadsheet:
Col 1 list Year & Month
Col 2 list sales value for the month
After you have done 12 months: Col 3 SUM the 12 figures in col 2 to give 12 month total
Copy/Drag the formula down to give a rolling 12 month total
Col 4 use the formula: £84999 minus figure to the left (12 month total) plus 1st month sales figure of that 12 month total. This gives the maximum sales you can invoice next month.

Thanks (0)
Replying to Watson Associates:
avatar
By lancashirelass
16th Mar 2022 00:09

Thanks so much for
Your reply it was very helpful x

Thanks (0)