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.
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.
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.
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 ?
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.
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.
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.
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.