Sage Line 50 / Instant month end reporting using Onion Reporting Software's Onion product

One of the issues users frequently ask about is how they can monitor any “back postings” made in Sage since their previous monthly reporting cycle.  For example, a business may have a reporting cycle that produces reports for a month end five working days after that month end.  Using Onion as the reporting option will produce month by month spend figures for each month in the year to date as at working day five of the new month.  If, on working day six of the new month, a late posting is made to the previous month, how is the user of the Onion reporting pack next month to be made aware of the prior month posting?

The [Onion info] sheet of each Onion workbook records the “Last transaction number” at the time of workbook creation.  This identifies the cut-off point for the reporting pack.  The next time an Onion workbook is created, the user can ask to have postings made after that cut-off point identified in the new Onion workbook. The user ticks a check box, provides the last transaction number from the previous reporting pack, and runs the report. On completion the user can set the NEW_POST filter on the [TB Pivot (YTD)] sheet to Y to see what months have been posted to since the last report. Double clicks on the month totals will show the transaction detail of what was posted.

Excel VBA range find method not working

Just beat my head off a brick wall for the best part of a day on this!

The Find method failed to locate the reference of a text value in a row because I had hidden the row. I was using LookIn:=xlValues. When I changed it to LookIn:=xlFormulas, it worked as expected. Who knew!

P.S. Excel 2003 - don't know about other versions.

Adding Debits and Credits in Excel without a helper column

Irritatingly, I keep coming across exports from accounting systems where the figures are always stated as absolute amounts with a separate column showing Dr or Cr to indicate the signage, Dr being + and Cr being -.

It isn't always convenient to add a helper column but a formula in the form of 
=SUMIF($A$1:$A$10,"Dr",$B$1:$B$10) -SUMIF($A$1:$A$10,"Cr",$B$1:$B$10) will subtract the sum of all the Cr values in column B from the sum of the Dr values in column B based on the Dr/Cr designations in column A.

I've noted that the range for SUMIF isn't necessarily a single column array. So, if you had Dr/Cr designators in columns A and C and amounts in columns B and D the formula could have the form =SUMIF($A$1:$C$10,"Dr",$B$1:$D$10) -SUMIF($A$1:$C$10,"Cr",$B$1:$D$10).

Sage Line 50 / Sage Instant e-reconcile - it can work well for you

At first sight Sage's e-reconcile option doesn't seem all that attractive. The main reasons for not adopting (in no particular order) are:

1. Banks normally charge extra for the statement files facility;
2. e-reconcile doesn't allow the matching of offsetting receipts and payments records in the cashbook;
3. e-reconcile only allows one-to-many or many-to-one matching - you can't match many-to-many; and,
4. e-reconcile doesn't allow the matching of bank errors and their correction on the bank statement.

All of the above are easily countered:

1. Banks normally charge extra for the statement files facility: My post at e-reconcile for everyone in Sage Line 50 / Sage Instant explains how any bank's basic statement export facility (e.g. CSV, xml or Excel format) can be used in e-reconcile. No extra cost. 
2. e-reconcile doesn't allow the matching of offsetting receipts and payments records in the cashbook: OK, take the receipts and payments you need to clear against each other (because they'll never appear on a bank statement and you don't want them to be listed as outstanding forever) and make sure the net total is zero (say Receipts = £1,000 and Payments = £1,000. Net = Zero). When matching a single payment include the extra payment transactions totalling £1,000 and, when prompted with "Match though totals differ?", click on Yes. Then match a single receipt and include the extra receipt transactions totalling £1,000 and, when prompted with "Match though totals differ?", click on Yes. The two "matching errors" net to zero and everything will be OK.
3. e-reconcile only allows one-to-many or many-to-one matching - you can't match many-to-many: This scenario requires a response similar to 2 above. Higlight the multiple statement items and multiple cashbook items you want to reconcile and make sure the totals are the same. Then de-select all but one statement item and only one cashbook item (noting their transaction numbers for future reference) and match them clicking on Yes when prompted with "Match though totals differ?". Then select the bank statement and cashbook items whose transaction numbers you noted earlier and match them in the same way. The two "matching errors" net to zero and everything will be OK.
4. e-reconcile doesn't allow the matching of bank errors and their correction on the bank statement: To deal with errors on the bank statement (and their corrections) that never appear in the cashbook - post them to the cashbook as soon as the initial error appears on the bank statement. Say erroneous charges of £10 were applied to your account. Enter a journal for £10 Dr (erroneous charge by bank) and £10 Cr (erroneous charge correction awaited from bank) and code both sides to the bank account code. Then reconcile the erroneous charge entry with the bank statement leaving you with an outstanding receipt for £10 (the awaited correction on the bank statement) to reconcile when the correction is actioned by the bank.

e-reconcile has so many advantages, not least the auto-reconcile features and being able to sort both sets of transactions by amount to facilitate the easy identification of matches. If you haven't tried it before why not put a month's bank transactions into the practice company and activate e-reconcile, import a bank statement (remember 1 above) and give it a try. Most people don't go back to manual if they try it.

Sage Aged Debtor and Aged Creditor reporting in Excel

See the YouTube video showing Onion Reporting Software's comprehensive Aged Debtor and Aged Creditor reports produced from Sage Instant or Sage Line 50 accounting data by their Onion software.

OpenCart order notification doesn't identify the customer - Part 1

I've been frustrated that the email I get from my OpenCart site when a customer places an order doesn't identify who the customer is.  Even if it is only to satisfy curiosity, I find myself often going to the site to look up the customer who placed the order.

This is how the default email I receive begins:

You have received an order.

Order ID: 14862
Date Added: 04/05/2014

Order Status: Complete

Not very helpful.

I decided I'd find out where this comes from and see if I could do anything about it.

The text on the first line comes from the file ./catalog/language/english/mail/order.php. The relevant line in the file is $_['text_new_received'] = 'You have received an order.';

I changed 'You have received an order.' to 'You have received an order from '

A search of other files for 'text_new_received' brings up file ./catalog/model/checkout/order.php which contains the line $text = $language->get('text_new_received') . "\n\n";

Looking through the rest of the file I identified that the item $order_info['email'] contained the email address of the customer. I changed the line in ./catalog/model/checkout/order.php to:

$text = $language->get('text_new_received') . $order_info['email'] . "\n\n";

After upload to the site the email I receive now starts with:

You have received an order from customer@domain.com

Order ID: 14862
Date Added: 04/05/2014

Order Status: Complete

Job done!

P.S. see the Part 2 posting for a much better way to implement OpenCart changes using vQmod

Sage Departmental reporting in Excel

See the YouTube video showing Onion Reporting Software's free complementary template and how it interacts with the Onion workbook produced from Sage Instant or Sage Line 50 accounting data by their Onion software.