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. 

Dynamic print range(s) in Excel

I have a spreadsheet where I always want to print 2 columns of interest on the same page. The thing is those columns are different columns for nearly every print run. I can write a formula which calculates which columns they'll be. So, I set about writing a formula for the Sheet1!Print_Area.

Sheet1!Print_Area =offset($A$1,0,MonthNo+2,counta($A:$A),2) 

If MonthNo is 1 Columns C and D will print for as many rows as there are entries in column A.

If MonthNo is 2 Columns D and E will print for as many rows as there are entries in column A. 

And so on...

Using calculations for Print_Area provides the basis for some interesting possibilities:

If I identify a need to print everything on one page in certain circumstances, but on two pages (that need to be carefully controlled) in other circumstances, I can set up a number of calculated names and "cascade" these to create a sophisticated multi-range print range.

e.g.
Sheet1!OnePage =offset($A$1,0,3,counta($A:$A),MonthNo)

Sheet1!Pg1 =offset($A$1,0,3,counta($A:$A),6)

Sheet1!Pg2 =offset($A$1,0,6+3,counta($A:$A)+1,MonthNo)

Sheet1!TwoPages =Sheet1!Pg1,Sheet1!Pg2

Sheet1!Print_Area =if(MonthNo>6,Sheet1!TwoPages,Sheet1!OnePage)

A word of warning: if the areas that make up TwoPages fit within a simple rectangular area, with no cells within that rectangle unselected, Excel will replace the calculation for Print_Area with the absolute cell references of the upper left and lower right cells when MonthNo > 6. I've even seen Excel crash in these circumstances. However, to avoid this possibility, I find it convenient to include an extra row in one of the ranges. (That is why the calculated number of rows for Pg2 is counta($A:$A)+1, one more than the counta($A:$A) for Pg1) 

Sage Line 50 / Sage Instant opening bank balances

There seem to be a lot of questions about recording opening TBs in Sage on various forums. I'm not sure if I've ever seen any comment on the most convenient way to record the opening bank balance (say account 1200), so this short post describes a good way to approach it. 

Assume the closing TB at 31 March 2014 shows a debit of £95 in account 1200 and assume the closing bank reconciliation as at 31 March 2014 looks as follows:

Bank Statement balance	100
Less: Outstanding payments
Cheque 100001	(10)
Add: Outstanding lodgements
Ldg 999999	5
Cash Book balance	95

The bank statement balance represents items that are already fully reconciled, the outstanding cheque and the outstanding lodgement represent unreconciled items.

I've found the best way to set the bank (1200) account up is to enter the bank statement balance of £100 initially by clicking on the OB icon to the right of the Current Balance field in the Balance section of the Account Details tab of the Bank Record for the 1200 account. The date should be changed to 31/03/2014 and £100 entered as a receipt (it would be entered as a payment if the bank statement showed an overdrawn position). After saving, this will show account 1200 as Dr 100 and account 9998 (Suspense) as Cr 100.

Next, in bank payments, enter a payment on 31/03/2014 with the following attributes: Ref - 100001; N/C - 9998; Details - Outstanding cheque; Net - 10; T/C - T9. Save the payment. Then, in bank receipts, enter a receipt on 31/03/2014 with the following attributes: Ref - 999999; N/C - 9998; Details - Outstanding lodgement; Net - 5; T/C - T9. Save the receipt. 

This will leave account 1200 with a balance of Dr 95 at 31/03/2014 which agrees with the TB as at that date, and a balance of Cr 95 in account 9998. When the rest of the TB is entered account 9998 will reduce to zero.

The bank reconciliation should be started by entering the last reconciled bank statement balance as 100 on the 31/03/2014. The bank reconciliation report will then reflect the table above.

I hope this helps.

P.S. If you have payments on account that need to show up on Debtor / Creditor accounts you'll need to alter the methodology to incorporate a customer receipt or a supplier payment instead of a bank receipt or a bank payment. These will form part of the take-on of open items to establish the opening Debtors and Creditors ledger control account balances. The reconciled status of each receipt / payment recorded should be accurately reflected and appropriate adjustments made to the opening bank balance methodology recorded above.  Please feel free to ask any questions you may have regarding your own particular circumstances.

Sage Line 50 ODBC driver outer joins

I've just discovered that you can do outer joins with the Sage Line 50 ODBC drivers. Happy days!

If you try and implement an outer join in MS Query it will use a syntax like:

FROM {oj AUDIT_JOURNAL AUDIT_JOURNAL LEFT OUTER JOIN SALES_LEDGER SALES_LEDGER ON AUDIT_JOURNAL.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF}

Unfortunately this behaves like an inner join!  To get proper outer join functionality the easiest way I've found is to create an inner join and then, in the SQL that is created, add '(+)' to the end of the join criteria line as shown below:

FROM AUDIT_JOURNAL AUDIT_JOURNAL, SALES_LEDGER SALES_LEDGER 
WHERE AUDIT_JOURNAL.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF(+) 

I always understood the (+) notation (used with Oracle databases) as "ALL values from 'AUDIT_JOURNAL' and all matching records from 'SALES_LEDGER' where AUDIT_JOURNAL.ACCOUNT_REF=SALES_LEDGER.ACCOUNT_REF PLUS NULL records where there is no match for AUDIT_JOURNAL.ACCOUNT_REF".

I'm learning every day!

P.S. If you use MS Access to link to Sage tables I've found that you can't identify any key fields and to get the above syntax to work the query has to be a pass-through query. If anyone finds differently perhaps you'd care to share via a comment?