Archive

Archive for February, 2012

Syteline Financial Excel Addon

February 22nd, 2012 No comments

I have found the Syteline Financial Excel Addon a handy tool for building financial statement, much better than the financial statement writer inside Syteline.  Many long time Syteline user still have hard time understand the old financial statement writer, ever after using it for years.  But the Excel add on is really straight forward, especially for accounting people.

But before it’s latest update, the Addon has two major problems.  One is missing the must need Net Income function; another one is a bug in calculating year-end figures.  The latest update get them both fixed. 

Now the only problem for the Addon is performance.  Since each function, like SLGLBAL() is actually a query to DB, if you are constructing a multiple years detail balance sheet comparison, the report can easily has hundreds of function calls, and it will take time to run.  And if you don’t have direct DB connection, say you are Infor SaaS hosting customer,  you need to rely on web service connection, the performance would ever worst. 

But anyway, for most of user that can run report within LAN, the performance is pretty acceptable.

Just for reference, the following tables are used by the Excel based financial tool:

Ledger
Pertot
chart_bp
periods

parms
chart
site

Product Version

In SyteLine < 8.02.00

unitcd{1..4}

In SyteLine > 8.02.00

chart_unitcd{1}

Account Distribution under actual costing, average cost method

February 18th, 2012 No comments

PO Receive (INV PRCV)

Item ABC current on-hand qty = 100, cost = $1.4

PO purchase price $1.17, with 17% VAT tax, material cost = $1. Receive qty = 100

After received, qty = 200, average cost = $1.2

 

  Debit Credit Comment
Inventory 120   100 pcs on after received unit cost of $1.2
Inventory   20 If current item unit cost is differ than PO receiving cost, adjustment entry created
Voucher Payable   100  

AP Voucher Generation (APV)

  Debit Credit Comment
Account Payable   117  
Voucher Payable 100    
Tax Liability 17    

AP Payment (APP)

  Debit Credit Comment
Account Payable 117    
Cash   115  
Exch Rate G/L 2   For foreign currency, if the exchange rate is differ from the time AP generated, G/L will be recognized.

 

Misc Receive (MRCP)

Current onhand qty = 100, unit cost = $1.4

Misc receive qty = 100, unit cost = $1

After received, qty = 200, unit cost = $1.2

  Debit Credit Comment
Inventory 120   100 pcs on $1.2 unit cost
Inventory   20 If current item unit cost is differ than misc receiving cost, adjustment entry created
Inventory Adj   100  

 

Misc Issue (MRCP)

Current unit cost $1.2, misc issue out 100 pcs

  Debit Credit Comment
Inventory   120 100 pcs on $1.2 unit cost
Expense, Cost Account 120    

 

Job Material Issue (INV JOI)

Current unit cost $1.2, issue qty 100 pcs

  Debit Credit Comment
Inventory   120 100 pcs on $1.2 unit cost
WIP Material 120    

 

Job Labor Posting (INV JOL)

  Debit Credit Comment
COGS Labor   40  
WIP Labor 40    

 

Job Completion (INV JFIN)

  Debit Credit Comment
FG Inventory(material) 120    
WIP Material   120  
FG Inventory (labor) 40    
WIP Labor   40  

 

Order Shipment (INV CSH)

  Debit Credit Comment
FG Inventory(material)   120 uses accounts on stockroom location where item was shipped from
COGS Material 120   uses accounts from End User type if one exists, else from Distribution Accounts for Product Code
FG Inventory (labor)   40  
WIP Labor 40    

 

Order Invoicing (INV JOL)

  Debit Credit Comment
Accounts Receivable 100   uses accounts from End User type if one exists, else from Accounts Receivable Parameter
Sales   100 uses accounts from End User type if one exists, else from Distribution Accounts for Product Code

 

AR Payment (ARP)

  Debit Credit Comment
Accounts Receivable   100 uses the A/R account specified on the invoice in A/R Posted Transaction Detail
Cash 98   uses cash account on bank code
Exch Rate G/L 2   For foreign currency, if the exchange rate is differ from the time invoice generated, G/L will be recognized.

Date Time for Multi-Site

February 9th, 2012 No comments

Something to remember, in programming in multiple site environment, if you have site that has differ time zone than the server time zone, remember to use GetSiteDate function, instead of directly GETDATE().  Below is code sample.

   SET @PrintDate = dbo.GetSiteDate(GETDATE())

What do you need to know for putting Syteline in your China branch company

February 5th, 2012 No comments

Today, many, if not most, US manufacturers have operation in China.  They either build out manufacturing facilities to take advantage of global supply chain, or setup sales/marketing operation to catch China market potential, and may be both.  With US headquarter and operation running in Syteline, it is nature to put Syteline into China branch as well.

But what do you need to know in order to have a successful Syteline implementation in China?

From 1996 to 2004, I was the country manager for Symix/Frontstep/Mapics in China.  I had involved in over 130 Syteline implementations in China, many of those, especially the early ones, are US manufacturing companies.  I was also leading the major Chinese localization project in Syteline, like Chinese Financial Module, which is still the central part of Syteline Chinese country pack today.   After coming back to US in 2006, my Syteline consulting practice still crosses Pacific ocean often.  Just last year, I completed three Syteline 8 implementation projects for US companies in China.  With all that said, I think I am in a good position to talk about what you need to know for a successful Syteline implementation in China.

Let’s run down the list.

1) What additional software components you will need?

If you need the Chinese user interface, and/or if you want to keep your financial book in Syteline, you will need the China Country Pack, which is an extra software component you need to purchase from Infor.  The Country Pack includes the Chinese UI and a Chinese Financial Module to handle the special regulation requirement for accounting system in China.

2) Where should you put your servers?

You have options of centralizing servers in your US headquarter, or putting server locally in your China branch.  They both have pros and cons.
With centralized servers, you can utilize knowledge and resource in US HQ for system administration, so you don’t need a dedicated IT person in your China branch.  You may also leverage your hardware/software investment that you already made in US HQ.
The biggest drawback for centralizing server is performance.  In general, the remote access from China to your US HQ will be slow.  Increase the internet bandwidth in both of your HQ and China branch would help, but may not fully resolve the problem, due to the bottleneck may be in the internet connection between the two countries.  Also, the so called “Great Wall” firewall setup/controlled by Chinese government may sometime block your connection without reason.  Recently, one of my client even experience connection problem with Infor Saas hosting server.  By the way, proxy server is not a solution to get around the Great Firewall, since Syteline would not work at all with Proxy.

So if you do want to centralize your servers in US HQ, do set it up and have your users in China try it out first, to see if they are OK with the connection and performance.

3) How should you setup your multi-sites environment?

The multi-sites structure would of course be based on your company situation.  Normally, if you need to consolidate financial statement, you will need to setup a site DB and an entity DB, both of them are in CNY currency, for your China branch.

When creating DB for your China site, just remember to use CNY as domestic currency and select proper China time zone for it.  By doing that, your China user will see the correct local time zone, even though your server may located in US.

If you are going to do data replication between your US site and China site, ITAR compliance may need to be taking into consideration.  You may need to modify your replication rule.

4) Multi-currency is a must.

You would most likely need to setup multi-currency for your China branch DB, with CNY as domestic currency and USD/others as foreign currency.  When setting up USD currency, make sure to check the “Rate is Divisor” box.  This will reduce the transaction rounding error.

5) What do you need to know about the China VAT tax system?

In some extent, China VAT tax system may be considered simpler than US sales tax system, since there are not that many differ tax jurisdictions as US does, and tax rates do not change as often as US.

But you do need to setup VAT tax system in both sales and buy side.  Tax paid with your purchasing will be credit against to your tax liability in sales side.  Please check out my another post for detail in setting up China VAT tax system.

In that nature of China VAT tax system, a VAT tax invoice you collected from your supplier actually has cash value, just like bank notes.  And that is why Chinese government has tie control on VAT Invoice.  VAT invoice can only printed out from a dedicated tax control machine that issued from government, and the invoice# is pre-assigned and controlled by government.  Syteline invoice# can only used for internal control, not really the one that you can present to your customer.

So, to help accounting people reconcile Syteline invoice# and official VAT invoice#, a modification would be required to put VAT Invoice# into various AR forms, like “Invoice, Debit/Credit Memo”, “AR posted transaction”, and “AR payment quick application”, and may also need in AR reports like “AR Aging”.

6) What is the Chinese Financial Module, and what is that for?

Chinese accounting regulation requires an unique voucher number for each accounting transaction (combined multiple debit/credit entries), something similar to control number today we have in Syteline 7 & 8.  But the early version of Syteline (before and include SL6) don’t have that function, and that was the major driving force for developing Chinese Financial Module back in 2001.

Today, Chinese Financial Module is still crucial for any Syteline implementation in China.  It is pretty much a GL system that allow user to enter/maintain voucher, to print voucher form, and to print Chinese accounting book.  Those are generally required for compliance with Chinese accounting regulation.

In terms of Financial Statement, local Chinese government do have some standard format requirement.  But I have found that with Syteline Excel Addon, it is quite easy to fulfill whatever format requirement is.

7) Other special consideration during implementation.

There are some other special considerations that need to be taken into account during your Chinese implementation.  Here I list a few.  The solution to each of them may vary, depend on company situation.

  • Chinese accounting regulation has some standard on top level chart of account setup.  In most case, you company will need to follow that standard.  If you are going to consolidate financial statement to US headquarter, some sort of account mapping will need to setup properly before hand.
  • Duty free imported material keep in bound location.  Custom regulation requires that those duty free imported material to be kept in special bound location, and has full tractability to prove that they are only used on products that will export out later.
  • Company may be required to declare their earning and pay income tax in monthly base, instead of yearly based in US.  That means you may need to do GL adjustment to move P/L to retain earning in monthly base.
  • It is normal accounting practice in China to fully allocate your actual overhead cost into COGS in monthly based.

8) Implementation, local support consideration.

Needless to say, a suitable consulting team is crucial to your implementation project success.  Capabilities you should look for are,

  • Understand both western and Chinese business culture and best practice.
  • Deliver training and consulting service in both English and Chinese.
  • Able to facilitate better communication between US headquarter and China local team
  • Provide long term local support.