Counting Months

[mlv]

Guys, I need to do something clever with months - although right now I'm not
quite sure what the best approach is.

I'm developing a yearly Company Ledger workbook with 13 sheets, one sheet
per month, plus one 'Setup' sheet as the first sheet.

The idea is that the 'Setup' sheet is filled in with all the company
details, and this information is used to populate the appropriate cells in
the subsequent twelve monthly ledger sheets.

I'd like the monthly ledger sheets to calculate the (UK)VAT due to HM
Revenue & Customs (I don't have a problem with the calculation), but I'd
also like the VAT title cell on the appropriate monthly ledger sheets to
state when the VAT is due to be paid.

One of the cells (assume 'Setup!A1') on the 'Setup' sheet contains the date
that the first VAT Return is due (e.g. 30/06/2007). Subsequent VAT Return
due dates will be 'Setup!A1' + 3 months, 'Setup!A1' + 6 months and
'Setup!A1' + 9 months.

I guess all I need to do is have a default text, something like "Cumulative
VAT due to HMRC this period:' that is entered into the VAT title cell on the
monthly ledger sheets UNLESS the monthly ledger sheet date (assume this is
in cell 'Month 1!B1', 'Month 2!B1', 'Month 3!B1', etc.) has the same date as
cell 'Setup!A1' or as 'Setup!A1' + 3 months, 'Setup!A1' + 6 months or
'Setup!A1' + 9 months, in which case the text in the VAT title cell would
be changed to something like "Total VAT now due to HMRC:".

Unless someone has a better idea.

Please could someone help with the formula?

TIA

 

[Earl Kiosterud]

Mike,

If I've untangled your question properly, this might work:

=IF(OR(Setup!A1=B1,DATE(YEAR(Setup!A1), MONTH(Setup!A1)+3, DAY(Setup!A1))=B1), "Total VAT
now due to HMRC", "Cumulative
VAT due to HMRC this period:")

It only takes the case of the dates being the same, or 3 months hence. It'll be more of a
nightmare, I fear, to include 6 and 9 months.

If you can find your way around the VBA environment, a user-defined function will make a
cleaner, less-geeky, less-error-prone easier-to-maintain solution. We'll write one for you
if you're interested.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Roger Govier]

Hi Mike

You could use something like
=IF(MOD(MONTH(MONTH1!B1)-MONTH(Setup!a1),3)=0,"Total VAT now due to
HMRC","Cumulative VAT Due to HMRC this period")

 

[mlv]

Thanks Earl & Roger, I'll try the formulas out.

My requirements are a little more complicated than I first thought because
the VAT calculation I have to do also has to take account of the VAT periods
and only do the calculation for the appropriate three months, before
starting over for the next three month period.

If I have a date (e.g. formatted as '30/06/2007' in cell A1), how do I add
3 months to that date so that an adjacent cell (say B1) shows '30/09/2007'?

I think I need to buy a good book on Excel formulas. Any suggestions?

Thanks

 

[Roger Govier]

Hi Mike

A tip from someone who has dealt with UK VAT issues for a number of years.
Always calculate the total VAT payable and repayable from all transactions
on a cumulative basis.
Store the amount of VAT actually paid and received ( well the values for all
9 boxes of the VAT form).

Calculate your return (all 9 boxes) as the total due - total paid to date.
That way, if anyone has made amendments to previous entries (I know they
shouldn't, but you can't trust users unless you jump through hoops to stop
them<bg>), at least any amendments will all be dealt with on this basis and
your overall return for the year will be correct.

As for your question about stepping dates up. With start date in A1
=DATE(YEAR(A2),MONTH(A2)+4,0)
This relies on the fact that Day 0 of a month is the last day of the
previous Month.
Therefore if you start with 30 Jun 2007, you will get 30 Sep 2007, then 31
Dec 2007 - as opposed to 30 Dec 2007 if you were to use
=DATE(YEAR(A2),MONTH(A2)+3,DAY(A1))

 

[mlv]

A tip from someone who has dealt with UK VAT issues for a number
of years. Always calculate the total VAT payable and repayable from
all transactions on a cumulative basis.
Store the amount of VAT actually paid and received ( well the values for
all 9 boxes of the VAT form).

Thanks Roger, that's a very useful tip. It will also make my calculations
simpler.

I've just started using the VAT Flat Rate Scheme, so I don't have to account
for Input VAT. All I need to do is calculate the VAT payable to HMRC as the
Flat Rate percentage of the total gross invoice values to date and subtract
what has already been paid during the year.

The only problem might be if the Flat Rate Percentage is changed during the
year and it is not retrospective, thereby resulting in two percentage rates
being applied during the same year.

As for your question about stepping dates up. With start date in A1
=DATE(YEAR(A2),MONTH(A2)+4,0)
This relies on the fact that Day 0 of a month is the last day of the
previous Month.
Therefore if you start with 30 Jun 2007, you will get 30 Sep 2007, then 31
Dec 2007 - as opposed to 30 Dec 2007 if you were to use
=DATE(YEAR(A2),MONTH(A2)+3,DAY(A1))

Thanks for your help with the date formula.