Splitting revenue by month

[SageABuser]

Hi - We currently raised many variable invoices. Monthly, Quaterly, Annually + Bi Annually. We are having to do a manual task of downloading the nominal code and then splitting the revenue over the revelant months. Is there a smarter/automatic way that Sage will already do this please. Ideally we would like to run reports directly from Sage to show the revenue by month per client. Thank you!!!

[brucedenney]

There are prepayments and accruals in Sage ... BUT it is more work to set them up than it is worth.

You can do the same thing as sage does and automate it if you want with excel.

To do this you need to put a flag on each invoice to identify a) it is one you want to make a provision for and b) how many period you want to spread the revenue over.

Each month you export the sales invoices, the easy way to do this is to link excel with odbc to "pull" them from Sage, If the invoices are generated in the invoicing module, you could link the tables and get the flags from the invoice in the invoicing module as there is little space for anything in the audit trail.

You end up with a table something like this, you filter out the monthly invoices that do not need to be handled.

Invoice No, Date, Nominal, Details, Net Amount, Periods

For each invoice you create these transactions using formulas

Remove the future income
=========================
the monthly income (Amount divided by the number of periods)
multiplied by the number of future periods (No period minus one)
out of the Nominal Code
and put it into a "Work In Progress" nominal code in the balance sheet)
You date this the first day of the month of the date of the invoice.

Put the future income back, but date it in the future so it appears in the correct period
================================================================================
the monthly income (Amount divided by the number of periods)
out of "Work In Progress" nominal code
and put it back into the Nominal Code
You date this the date of the invoice + 1 month and the first day.

Repeat for the process for the number of periods needed.

Yes, that is all you do, you take the future stuff out now and put it straight back in several future bits

I guess rather than adding lots of rows, you could do it all in one row. You need to be a little wary about rounding issues in excel, I would probably make CurrentPeriod+1 the wip amount less the sum of CurrentPeriod+2 to the end.

Invoice No, Date, Nominal, Details, Net Amount, Periods,WipAmont, CurrentPeriod+1 amount,CurrentPeriod+2 amount,CurrentPeriod+3 amount,CurrentPeriod+4 amount,CurrentPeriod+5 amount,CurrentPeriod+n amount

You need to do a summary of what goes out in the current month to Wip for each Nominal
and what goes back to each nominal each month in the future months.

If you are smart you could make this a Sage audit trail import file and import the values to Sage.

This would work really well if you had really large numbers of invoices as you can save the sheet each month as your Journal notes.