Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

Sage Statements with Outstanding Sales Orders

Report design issues and solutions.
Post Reply
sbleitner
User
User
Posts: 4
Joined: 07 Jul 2013, 18:54
Sage Version: v2013 19 UK/Europe/Africa

Sage Statements with Outstanding Sales Orders

Post by sbleitner » 07 Jul 2013, 19:15

Hi

We are a UK registered charity and we have about 600 supporters on our database. The way we raise out funds is that we first receive pledges over the phone or in group meetings we must then chase the donors to setup a payment plan or send in their payments.

As we grow bigger, chasing funds is becoming a bit nightmarish and we need to start sending out statements by email or post however we face some interesting challenges in designing the right statement.

Whenever we get a "Pledge" we raise a sales order, then when payment is received or a standing order is setup we convert it to an invoice which acts as the charity receipt however on the statement we must show all outstanding pledges as well as the ledger's data, i.e. outstanding invoices, unallocated payments etc.... I'm having great difficulty trying to get this to work correctly. To make it even more complicated, for most donors we have more than one pledge or donation, so far whatever I've tried seems to only get the most recent sales order.

Furthermore we must deduct future standing order payments from the "Amount Due" on the statement, I'm able to create the calculations, so for many of the donors the amount due would be 0 even thought there is an outstanding balance on the sales ledger however I'm not able to filter these out on the reports.

I need some help in designing the correct report or a work around.

Any advise would be mostly appreciated.

Regards
S. Leitner

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Sage Statements with Outstanding Sales Orders

Post by brucedenney » 08 Jul 2013, 09:15

I don't understand.

Sales orders do not appear on the customer accounts, so I do not see how these appear on statements.

I am guessing that in your terminology, an invoice is a pledge and a sales receipt is a donation and that the donations match up to the pledges.

Sounds like you are looking to do a statement and exclude future dated transactions from the "amount due"

If you could post an example of the issue I might be able to understand better.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

sbleitner
User
User
Posts: 4
Joined: 07 Jul 2013, 18:54
Sage Version: v2013 19 UK/Europe/Africa

Re: Sage Statements with Outstanding Sales Orders

Post by sbleitner » 08 Jul 2013, 17:56

Hi Bruce

Thanks for your response.

Correct, I'm trying to do a table join but I'm running into lots of difficulties.

In my terminology. Sales Order = Pledge; Invoice = Donation.

The process up until now is, 'Invoice proforma' = pledge and 'Posted Invoice' = Donation however we think we might be better off as above, the main reason being is that if a pledge is only partly paid we still update the ledgers with the full amount but then when the donor makes a further donation towards the same pledge we don't have the straightforward capabilities of sending another receipt and this is done via a custom report in the Bank Window and we have to do a separate print batch for every bank account (34 in total!).

We don't post future dated transactions but rather make heavy use of the recurring bank transaction feature for the many standing orders we have setup from our donors, we think this is a much cleaner approach.

The main challenge is to be able to join the SALES_ORDER table to the statement filtering out only the outstanding pledges (i.e. un-despatched or partly despatched sales orders) and calculating this with the outstanding balance posted on the ledger, while at the same time also calculating any payments due to be made by standing order (i.e. recurring transactions).

BTW, just as I finished typing this message it has occurred to me, as I intend to split the pledges from the actual donations, (as sales order and invoices respectively), I'm thinking if it might actually be easier to run the so called statement based on the sales order tables and just make use of the balance field in sales ledger, in this case I just need to know if I would run this as a report in the sales order window or if this be achieved in the statement window too.

The last option would be to export the data in Excel and run a mail merge but then I would loose all the email functionality etc...

I hope all this makes sense.... Let me know if you need more clarification.

I know Sage is not the best solution when it comes to managing charity accounts, however I'm yet to find a solid platform at a reasonable price like sage to manage these accounts, however I'm all ears if anyone knows of a better software solution.

Rgds
SL

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Sage Statements with Outstanding Sales Orders

Post by brucedenney » 09 Jul 2013, 08:31

I think the problem was that you had not really though through the method of what you were doing, it appears that in answering my probing you are now getting more of an idea of how you want to do this.

My thoughts.

You do NOT want to be running custom reports for receipts.
You do NOT want to be using recurring bank transactions for your standing orders.

I would use sales orders for pledges.
I would raise invoices (from sales orders for pledged) donations.

To produce a list of "outstanding" pledges you can use the outstanding sales orders reports.

If someone makes a pledge to pay x amount on date y and amount a on date b then you can make a sales order with an item for amount x and set the "due date" for that item of y and another item for amount a with a due date of b

If they make several pledges they can all be on the one sales order all with different due dates.
If pledges / donations are for specific "things" then product codes could be used to set the eventual nominal code.
If you want a list of past due pledges, then you could tweak the outstanding sales order report to filter items it by "Due Date" to give you a list of overdue orders.

Note: There are 2 due dates on a sales order, one on the order and one one each of the items on an order. You need to be sure which one you are using and you need to be consistent in your use of it. I would go with items, because then you can put lots of different pledges on the one sales order.

You could use recurring invoices/sales orders for perpetual recurring pledges/donations
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

sbleitner
User
User
Posts: 4
Joined: 07 Jul 2013, 18:54
Sage Version: v2013 19 UK/Europe/Africa

Re: Sage Statements with Outstanding Sales Orders

Post by sbleitner » 09 Jul 2013, 16:09

brucedenney wrote:I think the problem was that you had not really though through the method of what you were doing, it appears that in answering my probing you are now getting more of an idea of how you want to do this.
When we started 5 years ago that worked best for us, as we have grown we need to make the changes :)
brucedenney wrote: My thoughts.

You do NOT want to be running custom reports for receipts.
Agree
brucedenney wrote:You do NOT want to be using recurring bank transactions for your standing orders.
Why not, I think doing it the way you describe below would really mess up bank reconciliation.
brucedenney wrote:I would use sales orders for pledges.
I would raise invoices (from sales orders for pledged) donations.
Agree
brucedenney wrote:To produce a list of "outstanding" pledges you can use the outstanding sales orders reports.
My issue that I'm trying to overcome is how to send statements to donors that includes outstanding pledges as well as o/s balance less any payment arrangements they have already setup.
brucedenney wrote:If someone makes a pledge to pay x amount on date y and amount a on date b then you can make a sales order with an item for amount x and set the "due date" for that item of y and another item for amount a with a due date of b
If they make several pledges they can all be on the one sales order all with different due dates.
If pledges / donations are for specific "things" then product codes could be used to set the eventual nominal code.
If you want a list of past due pledges, then you could tweak the outstanding sales order report to filter items it by "Due Date" to give you a list of overdue orders.

Note: There are 2 due dates on a sales order, one on the order and one one each of the items on an order. You need to be sure which one you are using and you need to be consistent in your use of it. I would go with items, because then you can put lots of different pledges on the one sales order.

You could use recurring invoices/sales orders for perpetual recurring pledges/donations
Picture this, 150 standing orders, some weekly, some monthly, different dates and different amounts, some involving as little as 3 payments and some as many as 50 payments or more, I would need to employ someone just for entering all the pledges.... I'm not sure why recurring bank payments is not the better option, besides I will loose the capability of forecasting for these standing order arrangements.

This is how I think the workflow should be.
Pledge = Sales order
Pledge with payment arrangement = Recurring Invoice + Recurring Bank Payment
Donation = Invoice
Statement = Still need to figure this one out....

Should this be a sales order report with a custom expression calculating the balance and recurring payments setup or a statement with a custom expression calculating the o/s sales orders. I need to run this for about 200 donors every month so need the fastest option to calculate these expressions.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Sage Statements with Outstanding Sales Orders

Post by brucedenney » 09 Jul 2013, 16:40

Why not use recurring bank receipts? ... because there is no invoice, no paperwork, no receipt nothing, the invoicing module no longer reflect what was received and is incomplete, if complete it could be a useful tool for analysis. I think you can put the payment on the invoice (payment tab) and post it all through in one even on the older versions. So you only need to memorise the invoice with the payment attached to it.

There should be no balances on any of the accounts, only paid things get posted through with the invoices so the accounts should always be zero.
(during a change over they might not, you might want to reversed out and clean up any long term ones and set them up the new way.)

The statement is just the sales orders outstanding you don't need to take into account any balance on the account because there will not be any!
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

sbleitner
User
User
Posts: 4
Joined: 07 Jul 2013, 18:54
Sage Version: v2013 19 UK/Europe/Africa

Re: Sage Statements with Outstanding Sales Orders

Post by sbleitner » 15 Jul 2013, 19:54

Thanks for all your input. I will give all this some more thought before I decide what to do.

BTW when I talk about recurring bank transactions I'm referring to customer payment on account, the good thing about this is that it can be included in the cash flow forecast which is very important to us to. I don't think recurring invoices can be included the same way.

Post Reply

Who is online

Users browsing this forum: No registered users and 11 guests