Insights & Information regarding enterprise resource planning software from a leading industry expert.

My AP Does Not Balance to the GL in Sage ERP Accpac!

Sooner or later, it happens.  You run your AP Aged Payables Report, and compare the total to the balance in your GL, (AP trade account), and they are not equal.  What now?

A reconciliation will need to be done, there’s no getting away from that.  You will need to do a comparison between all the transactions that have gone through AP and cross them off against all the entries in the GL Listing to find out which one(s) are different and/or missing.  This can be a tedious process.  However, there are a couple of tips and tricks that might help short-circuit the task.

1. Ensure all your AP batches have been sent to the GL.  (AP Periodic Processing / Create GL Batches).  Then, POST all the AP batches in the GL.  Re-run your reports to see if we are now in balance.
2. Run your reports as of a future period, such as the end of the current fiscal year.  Sometimes AP transactions get posted to prior periods.  After they get sent to the GL, the period may be adjusted to a current period before posting.  (So as not to affect an already published financial statement, for example, or because that period has already been closed).  Therefore, the imbalance may only be due to a fiscal period discrepancy, and will even out at a later period.  This is not normally a concern unless it crosses over a year end. 
3. Under GL Transaction History, select the AP trade account.  Change the source code to different modules besides AP and check if there are any entries which originated from somewhere besides AP.  These entries may be the cause of the variance.

If these 3 tricks don’t reveal the difference between your subledger and your GL, a full reconciliation will be required.  Here’s how to make that process simpler:

1. Locate the last fiscal period where the AP and the GL were in balance.  Use that as a starting point to begin your reconciliation.
2. Identify the variance amounts for each subsequent period since AP & GL were in balance. 
   1. If several subsequent periods are out of balance by the same amount, locating and adjusting the first of those periods will bring the following ones into balance.
   2. If the periods are out of balance by different amounts, then there are variances in each period that need to be identified.

Period	AP	GL	Variance
04	120,000	120,000	0
05	114,500	118,000	-3,500
06	95,000	98,500	-3,500
07	108,000	106,500	1,500
08	87,800	98,700	-10,900

In this example, period 4 is balanced, and there are discrepancies in periods 5, 7 and 8.  The reconciliation process needs to begin at period 5.  Depending on the adjustments required to bring period 5 into balance, (the AP may need to be increased, or the GL amount decreased, or some combination of these), the variance amounts of periods 7 and 8 may be changed, therefore it will be imperative to start the reconciliation at the earliest period that is out of balance.

1. Print the GL Transaction Listing for the AP trade account for period 5.  Then print either of the AP Transaction reports – Vendor Transactions, or GL Transactions – depending on which one works best for your particular set of data. 
2. Compare each of the entries in the GL to each transaction on the AP report.  Once each line item has been compared and crossed off with its match, you should be left with the ‘orphan’ entries – your variance.  You could also export each of these reports into excel and use some sorting and comparison formulas to assist with this task.
3. Now that you have the source of your discrepancy, you will be able to correct the imbalance by making the appropriate adjustments. 

This process is identical for the AR subledger as well. 

There are some settings which can be optimized for reconciliation purposes. 

1. Ensure your AP trade account is set to be a ‘control account’ which allows only AP transactions to be posted into it.  (GL Accounts / Accounts – select ‘control account’ and then add ‘AP’ to the ‘subledger’ tab)
2. Check your AP Setup / GL Integration / Transactions.  Set your Detail Transaction Types (invoice, credit note, payment, etc) to have easily recognizable fields sent to the GL Detail Reference and GL Detail Description columns (vendor number and document number for example).  This will make identifying specific AP transactions easier in the GL Transactions Listing report. 

 Give us a call if you need assistance with this! 

Sage Accpac ERP Bank Reconciliation Revamped!

by Leon Colton, Consultant

Accpac users probably won't be overly excited if asked about their experience with Bank Reconciliation.

Remember those children games when you need to match one thing with another? And the sense of delight when you find that fragment of the puzzle and plug it in its proper place to complete the picture? That is the sense Accpac users deserve on first-Friday-of-month afternoons, when reconciling last month's Bank statement. But instead of "arts & crafts of bookkeeping" they probably toiled for hours to crunch their numbers on uncomfortable and cumbersome reconciliation forms, till the bottom line finally turns to ZERO! First withdrawals, then deposits, finally Bank entries.  And what about more complicated transactions, or NSF cheques handling, or those interest charges included in a customer's cheque, or searching for that bank entry posted a few months ago. Does all of that sound familiar?

Well, here is the good news - Bank Reconciliation has changed for good, it was overhauled and greatly improved in the new Accpac 5.6 version! This time it seems Sage really listened to end-users and designed new functionalities and rebuffed existing ones. Simple to use and readable, designed with users' convenience in mind, a monthly reconciliation can be a breeze. Take a look at some of the changes made:

Streamlined & Simplified Reconciliation Form:

Four tabs (Withdrawals, Deposits, Returns & Bank Entries) united in one Reconciliation tab, and with sort option selected by date you can view transactions as they appear on the bank statement. For those who prefer "out of sight - out of mind" methods - new filters by reconciliation status and transaction type are just what you need! Filter those already reconciled transactions from the screen, view only outstanding cheques or deposits. And feel comfortable sorting by date or document number for easy matching.

Too many dates in the summary screen? No worries! Now it's only Statement date and Reconciliation date. You want to speed up the process - no problem, just double click on the line to change status from ‘Outstanding'  to ‘Clear'. Now that's real fun!

New Bank Entry Button

Did you ever say - "hey, why can't we use Distribution Codes and Distribution Sets when entering bank transactions? Now you can, pretty much like in Receivables and Payables modules.

The Bank Entry button on the Reconciliation tab opens a new Bank Entry form, where you can enter any kind of reconciling transaction- from simple service charges to compound entries that use Distribution Codes and Distribution Sets or just G/L Accounts:

NOTE: When the bank entry gets posted it appears on the Reconciliation Tab, just make sure ‘All' transactions are selected in Display field.

Handling more Complex Transactions

Since the new Bank Entry form can handle any number of G/L Accounts, there's no need to scramble everything into one amount, or use A/P and A/R for interest charges or taxes.

More Efficient Transaction Reversal

Instead of Returned cheques and Reversed Payments, you can now do all transaction reversal on one screen.

It is also possible to select the Reversed reconciliation status right in the Reconciliation tab to reverse a payment or other withdrawal, including Bank Entry. However, you must use the Reverse Transactions form to reverse a receipt (deposit). Reversed deposits will appear as outstanding withdrawals on your current Bank Reconciliation when the reversal is posted.

It is now possible to quickly reverse a whole range of transactions in one shot, using the ‘Quick Clearing' button. And restore it back just as quickly, if it was done accidentally!

Zoom to Actual Book Balance

This one is my favourite! I remember the feeling of disbelief when first coming across the discrepancy between the G/L Bank Account balance and Book balance stated in the Bank Reconciliation- it was like my ground was shaking and I thought the entire company data got corrupted?! Little did I know about Bank Services and GL being out-of-sync, when some Bank related transactions are posted straight in G/L, bypassing subledgers. To be honest, I always considered that being a ‘bug' of the system. That is now fixed - these two amounts are separately stated in the zoom-in window, with the book balance breakdown.

You'll also notice the Go button beside "Bank Entries Not Posted" amount, which allows you to post entries on the fly.

Unification of Terminology

‘In Transit' status for deposits is now called ‘Outstanding', just as withdrawals.

All funds moving into Bank Account are now called ‘Deposit', such as Withdrawal Bank errors cleared with lesser amount.

All funds moving out of Bank Account are now called ‘Withdrawal':

• Withdrawals.
• Payments.
• Reversed receipts.
• Deposit bank errors where the Cleared Amount is greater than the deposit amount.

Other Improvements;

1. Drilldown to original transaction, such as payment to Vendor.
2. New Totals tab with breakdown of reconciled transaction by status (Cleared with write-off, Cleared with bank error etc.)
3. Improved Bank G/L Integration, to include Bank Entry Detail and more fields to flow through to G/L batch, such as: Bank code, Bank Entry Type, Distribution Code, Entry Number and Entry Description
4. New Reports: Bank entries Posting Journal, sorted by posting sequence
5. Transaction History Inquiry with ability to make selection by Source Application (A/R, A/P, Payroll, Bank Services, Other) or by Reconciliation Status, or even to search for specific payment by amount.

In conclusion the new Bank Reconciliation is definitely worth trying!

Sage Accpac Spring Cleaning

It is that time of year again, when we all have to get out our gardening duds, break out the swiffer and dust the cobwebs off.

While you are doing this, why don't you include maintenance of your Sage Accpac ERP system?

Your Sage Accpac system would love it if you gave it a little care and attention, you may consider reviewing things such as:

• Reviewing your backup plan including ensuring you actually have DATA on that backup.
• Reviewing your Data Integrity plan, are you regularly performing a data integrity check?
• What is the size of the available disk space on your server?
• Is your server performing at capacity?
• Have you checked in with your users lately? Perhaps they are having issues that they have developed "work arounds" for that is costing your organization time and effort needlessly.
• When did you last inventory your hardware?
• Are your Product Updates "up-to-date"?

If these tasks seem too time consuming or technical, call us, we can help!

Clients of Plus Computer Solutions who are current on their Gold and Silver Telephone Support contracts will be hearing from me soon to ensure that we work with you to include your Sage Accpac ERP review in your Spring cleaning plans. 

Happy Spring!

HST - How to Prepare for Implementation Sage Accpac Part 2

As mentioned in the previous post, below is some information provided by the CGA that every business should consider so that they are best prepared for the HST.

To begin the preparation process, consider all facets of an organization that are affected by sales taxes (for example, accounts receivable, accounts payable, payroll, purchasing, forecasting and budgeting); this can be a very time consuming task. The benefit of spending time preparing for implementation is a reduction in costly errors, surprises for overlooked issues, and future audit exposures.

Below are a number of issues businesses need to consider for the sales tax implementation.

Collection of Sales Taxes

• Amend Software and/or tax table used to generate invoices, debit and credit notes to accommodate additional tax rates/codes.
• Amend automated system-generated entries (e.g. monthly inter-company charges, monthly rent charges or management fees to reflect the sales tax codes/rates).
• Consider the impact of harmonization on real property contracts during the transitional period and after implementation.
• Determine if prepayments have been made before the implementation date. The transitional rules may be used to determine the tax rate applicable in these situations.
• Develop special codes for point-of-sale rebates for the provincial component of BC HST and OVAT if applicable.
• Do a product and service sales tax analysis. Many goods, services, intangible personal property and real property non-taxable under the old Ontario sale tax legislation will now become taxable as a result of harmonization
• However, unless a supply is specifically excluded from BC HST or OVAT (e.g. books, children's clothing), or under a specific Ontario or BC levy post-harmonization (e.g. in Ontario certain insurance premiums), the sales tax status will be consistent with current GST rules, which will simplify tax administration for businesses.
• Do not delete or deactivate old tax rate codes since it may be necessary to use them after implementation during the transitional period.
• Follow transitional rules for volume rebates, promotional allowances, price adjustments, goods returned or exchanges after the July 1, 2010 implementation date and guidance for goods in transit on July 1, 2010.
• Modify tax tables for Internet web sites. All GST registrants will automatically become registered to collect BC HST and OVAT.
• Review ongoing or long-term contracts that straddle the harmonization date to ensure the correct sales rate is applied (e.g. service agreements, licenses, memberships and leases).
• Update point-of-sale terminal or cash-register software.

Recovery of Value-Added Taxes Paid (Input Tax Credits and Rebates)

• A business defined as a "large business" (over $10M taxable sales) or a financial institution cannot claim input tax credits for the provincial component on specified restricted expenses. Restrictions will last up five years with a three-year phase out.
• Develop updated or new tax tables/codes for accounts payable systems that automatically record input tax credits or rebates based on embedded taxes. Prepare to code payables for restricted versus non-restricted expenses.
• Do not delete/deactivate old tax rates as they may be required in some situations. If your system can only accommodate one or a limited number of tax rates, develop a manual system to record sales taxes correctly during the transitional period.
• During the transitional period, implement procedures to ensure only the value-added tax paid is recovered. Develop an override procedure to use in instances where old tax rates are charged in error.
• Develop a system to track the federal and provincial components of HST if you are part of the MUSH sector (municipalities, universities, schools, and hospitals) or qualifying non -profit organization.
• Evaluate and update periodic system-generated payments to apply the new taxes
• Make adjustments to the remittance percentages if you are a small businesses or public service body using the simplified remittance methods
• Modify employee expenses reimbursement and allowance software or pre-printed forms for not only the new rates but also the restricted expenses and possibly the use of factors. In addition, watch for special transitional rules for expenses reports that straddle the implementation date.
• Selected listed financial institutions must adjust formulae included in the Special Attribution Method calculation if applicable.

Other Considerations;

• Revise Cash flow projections.
• Update budgets and forecasts.
• Modify purchase orders with pre-printed sales tax information or system generated purchase orders to accommodate the new taxes, e.g. the goods for resale exemption will no longer apply in Ontario or British Columbia
• Modify pre-printed price lists or Internet websites containing sales tax information.
• Develop a system to track bad debt adjustment, tracking the tax from the original transaction
• Develop a process to change taxable benefit remittance rates for the 2010 and 2011 taxation years, if applicable.
• Modify documented procedures for how internal tasks are performed whether automatically or manually.
• Additional calculation may be required for the embedded tax content subject to the change-in-use provisions.

Plan to do a test run of sample data for all modified systems as a result of the numerous changes and revisions. This will reduce the potential for error and surprises when the systems are activated on July 1, 2010.

For more information & to find out how to update your Sage Accpac ERP System join us at our our HST User Group Meeting on April 08, 2010 from 8:30am-11:00am. To register & more information visit; http://erp.plus.ca/HSTUserGroupMeeting

Accpac Can’t Do That, YES it can!

It's been said many times, and unfortunately often by the Accpac experts, that Accpac can't do "that".  I'm sure there are cases of that being true, but often it is not.

Accpac has an excellent API (application programming interface) that allows you to interact safely with Accpac data.  You can add, remove, and modify most data in Accpac and be completely protected by the methodologies that Accpac uses.  You can achieve the same results by skipping the API and accessing the database directly, but you stand the chance of making a change that is unacceptable and causing corruption in your data. 

A simple example of this is creating an AR invoice.  Using the API, if you try to create an invoice for a customer that does not exist, you'll get a warning.  If you delete an invoice, all the line items that exist in other tables will get properly deleted, as well as any other processing that Accpac must do such as statistics.  If you did the same thing directly to the database, you stand the chance of missing or mishandling records.

So what can be done?  It's really limited by your imagination, but here are a few quintessential examples of things I've already created.

Data Import

I have a customer who has franchise retail locations.  These locations have proprietary POS software that exports a monthly report of sales in Excel format.  They would then take this output file and use it to key the data into Accpac by hand.  This was time consuming and created opportunities for errors. 

To resolve this, I created a program that would read the Excel file and input the values directly into AR automatically.  Each file would have a batch created and each day or week (depending on the respective file being imported) would be an entry.  This saved hours of work and eliminated all human error.

Data Export

I have a customer who wanted a very complicated report, with data pulled from and compared against data from many different sources, but with Accpac's user level security applied. 

Using the API from within Excel, the user is prompted to sign into Accpac and then the data is pulled and inserted into the Excel document, all without compromising Accpac security or asking for complicated database information.

Data Entry

For some entries in Accpac, like an AR Invoice, for example, you may want entries to be created with a specific format and be the same every time. 

One solution is to train your employees to enter the data in that manner, but that's not always possible, especially if they are being rushed through the process, as is often the case. 

I have a customer who rents storage lockers.  These are charged by square meters.  The customer wanted every invoice for a locker formatted the same;

Locker Number: 123 (12m x 5m x 1 Year)

Because this one item deals with 3 multipliers, the QTY had to be calculated.  This process was prone to many mistakes, such as formatting errors or math errors.  To resolve this, I modified the AR Invoice entry screen to recognize the type of entry, make the description and QTY column read-only, and when those columns are attempted to be modified, a custom interface pops up with fields for length, width, duration, and locker number. 

Using this modification, the customer's employees had no choice but to enter the data correctly.  At the same time, line entries that are not of this type are unaffected.

These are just some examples of what it possible.  Next time you want Accpac to do something different, give Plus a call & work with us to come up with a solution!

Plus Customer Amix Salvage & Sales Wins 2009 Sage Customer Award

Plus is happy to announce that our customer, Amix Salvage & Sales, received the Lifetime Achievement Customer Award from Sage on November 09 at Sage Summit in Atlanta, Georgia.

Amix Salvage & Sales has been using Sage solutions for over 17 years & during this time have experienced a variety of unique market challenges including massive volatility in commodity prices, world wide economic troubles & the growing demand for recycled metals in new market overseas.

Plus would like to extend a HUGE Congratulations to Amix Salvage & Sales for this accomplishment!

To learn more about Amix, visit their website at www.amix.ca

Quality vs Price - Turn it Around

When dealing with potential clients, many of us have heard the following:

"I think your price is a little high."

Maybe you have some time to spend with these prospects but maybe you don't.  Hilary Hinton "Zig" Ziglar, author, salesperson and motivational speaker, suggests you look this prospect in the eye and simply say:

"You know, Mr. Prospect, many years ago our company made a very basic decision. We decided it would be easier to explain price one time, than it would be to apologize for quality, forever. Now, I bet you're glad we made that decision, aren't you? "

Can you close the sale with this phrase?   It's honest, straight-forward and firm.  Don't sell quality short.  Mr. Ziglar believes in this philosophy and so do I.

Reference - "Turn It Around In a Moment", Zig Ziglar 

How Do I Backup Accpac?

A question that I wish more people asked me is, How do I Backup Accpac?  That question can be broken into three sections;

• Accpac Shared Data
• SQL Data (MSSQL, Pervasive, Oracle, DB2)
• Customizations

Each item has its own challenges, but once a routine is established, disaster recovery can be a pleasant experience as it relates to Accpac.

The first item is Accpac's Shared Data directory. The location of the shared data directory can be located by starting Accpac, clicking on the Help menu item and selecting System Information. This can contain many things, but most critically are Accpac's licences files, as well as the SITE, COMPANY, and USER directories.   These three directories contain things like database configuration and user settings.  Simply copy or use a file system backup utility to transfer these items to your backup location.  The licence files are all easily identified by their extensions, which are either LIC or SBE depending on the edition of Accpac you own.  Ensure that you include all the licences files, as well as all the subdirectories of the SITE, COMPANY and USER directories. 

One thing to watch out for while performing a backup of these items is locked files.  Many utilities will be unable to copy files in this state, however some files can be ignored.  One example of this is the semaphor.bin file.  Don't panic if you don't get this particular file, you can live without it.  One reason files may be locked is if Accpac is running.  If you run into locked files, be sure to investigate why they were locked and try to get a full backup as soon as possible. 

The next item is the databases.  There are two approaches to this, the first and easiest is performing a database dump.  This is an Accpac function that is independent of the database engine itself and can be performed by an end-user with appropriate Accpac security rights.  The database dump tool is located in the Windows Start menu or under Administrative Tools in Accpac. 

A database dump can be scheduled, but this requires an add-on product.  The result of a database dump is a proprietary format backup of the Accpac data.  One thing to keep in mind about a database dump is that it will only include the data Accpac knows about.  If an add-on product adds data to the database without properly registering it with Accpac, then it will not be included.

The second approach to performing a back up of the database is to interact with the database engine itself.  In the case of Pervasive SQL, the files that make up the database can be copied to your backup location at the file system level.  Something to watch out for is again, locked files.  Pervasive keeps each table in an individual file and only locks files where their respective tables are in use.  If someone is logged into Accpac, there is a strong possibility they have locked files.  Most backup utilities will skip over locked files, which will create an incomplete backup.  If you restore this incomplete backup, you're most assuredly going to have data corruption, so keep an eye on your logs.

Where Pervasive SQL has many individual files that create the database, Microsoft SQL has one.  This almost completely eliminates the possibility of doing a backup by copying the database files.  You must shutdown the SQL engine, because while it's running, all the database files are locked.  Shutting down the SQL engine, even at night isn't always practical, because some applications use the wee hours of the morning to perform maintenance. 

The proper way to make a backup is to issue a command to the SQL server to produce one.  This can be done a couple ways.  For the sake of a daily backup, you can use the Agent.  The agent can be used to execute maintenance plans that among other things include backup tasks.  Unfortunately with SQL Express, however, there is no agent, as this is one of the limitations of this particular version.  To get around this, I've developed a script that can be scheduled by the Windows Task Scheduler that will create a set of backups that can be accessed without conflict by a backup utility.  This can also be done manually from the SQL Management Studio.

The last thing to consider when backing up Accpac is the customizations. There are many things that can be considered a customization, one very common example is custom Crystal reports.  In Accpac there is a UI called Customization Directories and all custom Crystal Reports should be contained with the paths outlined there.  If all the custom Crystal Reports are properly organized in these locations, then simply configure your backup software to backup those locations.  Other customizations, such as macros or 3^rd party data could be stored almost anywhere, so you would have to consult the manufacturer for a proper backup procedure.

October Events!

Wow, it is hard to believe that October has finally arrived.

Like most people, October is a busy month for Plus and we hope that you will be able to join at us at either one or BOTH of these exciting events happening in the next couple of weeks;

October 07 - Financial Technology Show at Canada Place come & join us in Booth 209 and attend the only forum in Canada that provides a one-stop-shop for accountants. For information or to register visit; http://www.thefinancialtechnologyshow.com/

October 20 - Plus' 2nd Annual Customer Conference at the Executive Hotel & Conference Centre in Burnaby BC. If you haven't heard, this is an event you do not want to miss, so don't, register today! Have your choice of breakout sessions, be entered to win prizes, meet with Sage Exec's & Plus Staff. For more information visit: http://www.plus.ca/section.asp?pageid=9300 or to register click here: http://erp.plus.ca/customer-conference-registration

We hope that you will be able to join us at these events, questions, concerns...let us know or if you are looking for further detailed information contact us directly. 

 

Prescription Before Diagnosis is Malpractice!

Prescription Before Diagnosis is Malpractice in Medicine & ERP Implementations. Recently, and for the past several months, I have been seeing numerous doctors to resolve some medical issues.

I am currently about to see my fourth doctor and expect that my journey to diagnosis will come to an end.  During this process it has occurred to me that what Ed Kless, senior director, partner development and strategy at Sage has been telling us for years is as true, "Prescription before diagnosis is malpractice."

The humorous part of all this to me is that it is very difficult to stay logical and go through the process; it is very tempting to have the Doctor just fix me.  I would have been very willing to simply have accepted whatever possible preliminary diagnosis which had been offered, and allow myself to be treated for the potential problem without questioning it extensively.  I have had to learn to be patient and allow the process to continue in order to be properly diagnosed.

My point, and I do have one as Ellen DeGeneres would say, is that the same theory holds true in our world of ERP implementation.  Many times companies come to us seeking the cure to their ailments, it sometimes takes us a good deal of time to convince them to take the time to peel the layers of the onion to get to the real problem rather than rushing in with software to cure the disease.

To illustrate, a client once had corruption in their data continually, we performed data repair after data repair as it got the point where it was a daily occurrence, of course we and the client were desperate to get to the bottom of things and tensions were running high, the software was in question, and alternative solutions were being contemplated.  One afternoon, while babysitting the system, we were looking out the window and noticed what had always been there, the company was located next to a very busy trolley bus transfer station, and wouldn't you know that when activity was high at the bus station, there were problems with the data.   Hydro was called, repairs to the power were made to prevent the fluctuations in the power to the building and voila, problem solved.

This is a one example, there are many others, of where Plus has proven that we can improve, that is,  "diagnose" issues within organizations with and without software as the cure simply by taking the time to review the processes and properly diagnose and prescribe treatment for the organizational illness. 

It is rewarding to all, and there is no other way.