Financials

How to effectively reconcile your inventory in VISUAL ERP

In my experience, I found that there is a misconception related to inventory and WIP reconciliation process. This is why I want to provide more information on how Visual works in this area. I believe it will give you the knowledge on how to take the right approach in order to validate your published numbers.

The core of your Visual inventory resides in the accumulation of transactions. This happens to be in the Manufacturing side of the system. Typically, raw material is purchased and it is then used in the manufacturing process before being received into inventory and finally sold or shipped to consumers. The sequence of entering these transactions is critical for the guarantee of a proper cost allocation. When the sequence of this order is broken, discrepancies between the different Visual reports appear. This is when the confusion begins and you are left wandering which report has the correct numbers.

Let’s review the proper steps to take in order to validate those values:

How to effectively reconcile your inventory in VISUAL ERPThe Inventory Valuation Report will contain the most accurate picture at month-end. You may, however, encounter errors if there is an incorrect FIFO layer or if a part has a negative quantity on hand at month-end. These issues must be fixed because it is crucial to have a trustworthy Inventory Valuation report at month-end in order to progress to the next step.
Compare Inventory Valuation with Inventory BalancesThe Inventory Balances Report is a secondary table that is updated when Costing Utilities is run and MFG Journals are prepared. I often see a tendency for users to adjust the GL to the numbers shown in this report and this is incorrect. You should keep in mind that, according to the first step, the correct values are already in the Inventory Valuation report. Once you have verified that the Inventory Valuation report has no issues, you can compare it to the Inventory Balances Report. This allows you to identify any parts that are not displayed correctly in the Financial Subledger. Any differences must be fixed before progressing to the next step.
Compare Inventory Balances to GLThe GL (Inventory Control Account Balances) should reflect what is happening on the Manufacturing side. The GL almost always contains the correct values and should not be adjusted to the Inventory Balances too quickly, unless you have proven that the Inventory Valuation and Inventory Balances Subledgers are in sync. There might be times where the GL reports incorrect postings. These may include when a GJ has been posted to control accounts, if the inventory control account has been used in the wrong journal or if a change in product codes at Part ID level occurs without following the proper process. These issues are, however, a topic for a future blog.

Stay tuned, as I continue onto the next topic. Month-end quality: How fast can you close the month?

 


Valeria Vicco, CDDP - Financial Consultant, is our special guest author at visualnuggets.com and is an active participant in the VISUAL community.  Valeria is part of BMS Business Monitoring Systems Inc (BMS 365) - who focus on Innovative solutions that move Visual ERP users forward.

Connect with Valeria online at LinkedIn and BMS365.com

 

How to easily handle vendor deposits in VISUAL ERP

You can easily process vendor deposits in VISUAL - it's quite simple. 

But before you can do anything - you first need to configure VISUAL to allow for it.  To do this, add the following entry in Preference Maintenance

Vendor Deposits
Set EnableVendorDeposits =Y in the Visual Financials section

  OR, you can add the following to your VISUAL.ini file:

[Visual Financials]
EnableVendorDeposits=Y

Once done - you are good to go with the following steps to process a deposit.

Let's say your vendor requires a deposit of $6250 (half the value of the purchase) 

  1. First create the purchase order for the goods/service.

    VISUAL ERP Purchase Order

  2. Next, to record and process the deposit - create an AP voucher in the Payables Invoice Entry window. 

    Enter the respective vendor information in the header area.  Then insert a line item for the deposit as follows:
     
    ♦  Enter a description for the line in the Reference column (if needed)

    ♦  Enter the purchase order number in the Order ID column.
         A must for this to work!

    ♦  Enter a GL account to use in the G/L Account ID column - it should be a GL account where you want to manage deposits and prepayments. - Please don't use the default PO accrual GL account

    ♦  Enter the deposit amount in the Amount column.

    Vendor Invoice for Deposit

    Note - If you DO use the PO accrual GL account you will likely mess up your accruals when reconciling.

    Visual Accounting

  3. Save & Pay the deposit invoice as required
  4. When the goods/services have been received - do the PO receipt.

    Purchase Order Receipt

  5. Now when you receive the vendor invoice, proceed to voucher it in the Payables Invoice Entry window.  Use the F6 key to call up the receiver(s) associated with the purchase.  Select the appropriate receiver(s) and click OK.

    Purchase Order Receipts to Voucher

  6. VISUAL will populate the grid with the lines associated with the receipts - AS WELL AS add an additional line item for the deposit (or prepayment) as a deduction to the total amount owed.

    VoucherAppliedPrepayment


As I said - simple.

 

Should you CLOSE or LOCK an accounting period?

Infor VISUAL ERP - Financial Calendar MaintenanceWhen you maintain your fiscal calendar in the Financial Application Global Maintenance window,  you can set a period to have either an Active, Closed or Locked status. 

The ACTIVE status is pretty straight forward -- It will allow transactions to occur in the period. 

Setting the period to CLOSED or LOCKED status will prevent new transactions from being entered into the period. 

But why have 2 settings to prevent transactions?  What is the difference?

Well it all comes down to security.

If you CLOSE a period, anyone who has access to the Accounting Window (under the LEDGER menu) can easily toggle the status back to ACTIVE using the "Close/Reopen Period" function (as shown below) - and allow new transactions to occur in the period.
 

Infor VISUAL ERP - Accounting Window
 

If you LOCK a period, then this "Close/Reopen Period..." feature will be disabled.  The only way the period can be "Reopened" is through the Financial Application Global Maintenance which should be locked down (with limited access) from users. 

This setup allows for tighter controls in your organization.


Update!!

OK.  So no sooner did I just post this piece I get pinged from a colleague letting me know that I may be promoting bad “Visual” accounting practice. 

Infor ERP VISUAL - Accounting WindowSo let me put on my accounting hat on…

By no means did I intend to say that you should be opening and closing your periods from the Financial Application Global Maintenance window.  You should be doing this from the Accounting Window using the “Close/Reopen Period” feature.  The reason being is that the Accounting Window will ensure that there are no unposted transactions in the period you’re closing, whereas the Financial Application Global Maintenance window will allow you to CLOSE or LOCK a period with unposted transaction (not good). 

The Locked status is only available from the Financial Application Global Maintenance window and will prevent users from reopening the period from the Accounting Window - reinforcing the fact that the reporting of the period is final

This post was intended to explain the difference between the CLOSED and LOCKED status when maintaining your fiscal calendar.

Accounting hat off

What is the Inventory Balance Report used for?

Infor VISUAL ERP - Inventory Balance ReportThe purpose of the Inventory Balance Report (IBR) is to show the value of inventory on hand at each month end period. You can print it in summary or by FIFO layer.

The report is driven solely by the INVENTORY_BALANCE table which is updated by the cost utilities.

If you're in a STANDARD COSTING environment then the IBR really does not serve a purpose.  Basically, the inventory balance for any given month is the quantity on hand  x  the total unit standard cost in effect at that time.

Unfortunately the INVENTORY_BALANCE table in a FIFO database eventually closes FIFO layers off and they are removed from the table for the next period.  In a standard cost database this never occurs and the table continues to grow.  At one of our client more than 50GB (yes GB with a G!) of the database was in the inventory balance table and it was providing no benefit what so ever. The table was truncated and guess what…costing performance improved dramatically, especially for the first run of the month when this table is updated for the new period.

If you are standard cost consider truncating the data from the INVENTORY_BALANCE table.

Allow multiple AP invoices per receiver line

Infor VISUAL ERP - Application Global MaintenanceThis option enables the concept of landed cost in your database.

The main reason you would want to do this is to allow you to track costs in addition to the direct purchased cost of material through inventory rather than using a burden.

For example say I buy a qty of 3 of PART ID ABC for $1000 each = 3 x $1000 = $3000 USD.\

Now I have to pay for FEDEX to deliver the parts from the supplier in Texas to our plant….courier cost $200 USD

Now I have to pay for customs broker to clear the part across the border….customs broker cost $50 CAD


If you leave this option box unchecked

You can only voucher AP against the original purchased material receipt. You voucher the receiver for $3000 USD. Of course this values the inventory at the USD amount converted to CAD…let’s say that is $2700 CAD (or 900 per each unit)

The freight cost and the brokerage cost are then charged to an overhead expense account or possibly directly to the project or work order if the materials are bought to a job. In our case the materials are bought to inventory but we really want to track the full cost of getting the parts to the plant as our inventory. The only way we could do this is to apply a general burden rate. Since we want to track the actual cost of those parts being purchased this just isn’t good enough.

So we check the box

Now when the parts are received for the part we do a normal PO receipt, receive the invoice from the supplier and voucher the PO receipt just as we normally do.

When the invoice comes from FEDEX, we charge the $200 to the PO Receiver ID associated with the receipt of the goods. Now the inventory value of the part will increase to $3200 USD (or 1,066.67 each USD). Of course there may be different exchange rates applied to these two invoices depending on timing of the transactions but I won’t complicate this further.

When the invoice for the customs broker comes in we can again charge the original material PO Receiver ID with additional costs and the inventory value of the parts will increase again.

As each of these changes occur, in an actual cost database, the FIFO layers are updated and the costs are traced through any subsequent inventory transactions.

So what is the downside?

Costing can be more complex to reconcile.

You must determine a reasonable way to apportion the cost of these additional charges to the various parts being received so this can increase the complexity of AP invoice entry.

You must be able to determine the original PO receipt ID that relates to the additional landed cost charges in some way and setup a system to manage this.

Some additional complexity can be expected for any kind return or credit.

The upside

More accurate actual cost tracking. This can be especially helpful in a project or defense contractor or cost plus pricing scenario.

Unable to add notations to an AR invoice?!?

Are you trying to figure out why you can't add notations to the AR invoice?  Are all the icons disabled in the toolbar of the AR notation dialog box?

Infor VISUAL ERP - Accounts Receivable Invoice Notation
 
Infor VISUAL ERP - Accounts Receivable Invoice Entry - NotationChances are you have the PRINTED checkbox checked for the invoice  (in the header area).  You'll need to uncheck this in order to add your notes to the invoice. 

By the way, you don't have to save the invoice with the printed unchecked.  VISUAL will allow you to add your notes simply by unchecking it.

Go ahead and have a look at that invoice you can't add notes to.  Was the checkbox checked?  Yes?

Thought so.

 

 

 

 

 

 

Including purchase burden in your cost estimates

Are your purchase burdens being applied when calculating your cost estimates on quotes masters, engineering masters and work orders?

Not sure?

First things first...my accounting hat please.

Infor ERP VISUAL - Issue Burden
In PART MAINTENANCE under the "Costing" tab, you'll find the option to apply a Purchase Burden for a part.  This particular burden allows you to account for outside costs relating to handling the part.  It can be entered as a percentage of the material cost OR as dollar amount per unit.

 

 Infor ERP VISUAL - Part Maintenance


If you're not using purchase burdens, then you can move on to more pressing things http://t.co/yMigkLTW

But if you are using purchase burden, then you may want to verify whether it should be used when calculating your cost estimates in your quote masters, eng masters and work orders. 

You can have VISUAL use this burden (or not) by flicking the switch in the PREFERENCES under the OPTIONS menu of the MATERIAL CARD.

Infor ERP VISUAL - work order material card


This setting is USER SPECIFIC, so if you decide as a company that purchase burden should be applied (or vice versa) - you need to ensure that every user has the proper setting in place.

In the VISUAL.ini file:

[Visual Mfg]
IncludePurcBur={Y or N}

Or, if using the Preferences Maintenance module:

Infor ERP VISUAL - Preferences Maintenance


If you do decide to change this setting, be sure to "Reset Material Requirement Costs..." (Part Maintenance) to update your existing Quote Masters, Engineering Master, and Work Orders (where needed) so that they reflect this new cost setting.

Infor ERP VISUAL - Reset Material Requirements Cost



The elusive Gross Profit Report

Infor ERP Express Visual - Gross Profit ReportEvery few months this report becomes an active discussion in the VISUAL forums.  It usually starts off with a user requesting some guidance as they attempt to recreate the VISUAL gross profit report found under the Post Manufacturing Journals. 

They soon come to realize that this is no easy feat as it involves numerous tables and complex queries.

Why are users wanting to recreate this report when they could just use the one in VISUAL?  Well I'm sure they have good reasons....

  • better access and distribution of report;
  • format/layout of report; 
  • additional fields (data) to include;
  • facilitate export of data to excel;
  • they have nothing else better to do!  :-)

In any case, they've taken on the task. 

So what makes this report so challenging?  It's largely because it ties to the financials for the period.  For instance, you cannot just take the costs from the INVENORY_TRANS table as it only contains the current costs (regardless if they are posted or not).  You actually need to pull the costs from the distribution tables which may contain multiple entries for the one shipment (that account for cost changes through the periods).

So if you are wanting to recreate this report, you'll need to look at the following tables:

  • RECEIVABLE_LINE
  • SHIPMENT_DETAIL
  • SHIPMENT_DIST
  • SHIPPER
  • SHIPPER_LINE 
  • INVENTORY_TRANS

Information_greenWhat's that?  You have better things to do with your valuable time than recreate this report?  Contact me.

How to handle that down payment in VISUAL

There's a number of ways that you can tackle this in VISUAL.  And one way is through Progress Billings.  

Progress billing allows you to invoice a customer in one or more increments for good/services before you actually make good on them.  It was designed to meet almost any billing scheme --- such as down payments!

 Let's say we have a sales order for $10K and you require a down payment of 25%.

Infor VISUAL ERP - Sales Order
 

  1. Select Progress Billing Entry from the SALES menu.  Then enter the Order ID of the sales order you want to invoice for the down payment.  You'll notice that this window is similar to the Customer Order Entry window.

    Infor ERP VISUAL - Progress Billing 
  2. Highlight the line item and click on the Edit Billing Events icon from the toolbar (as shown).  You could also access this area under the EDIT menu

    Infor ERP VISUAL - Progress Billings
     
  3. In the Edit Billing Events window, click on the INSERT button to enter the following:

    Seq #  =  Keep the default (should be 10)

    Description  =  Enter description of down payment

    Billing  =  Enter $ value of down payment (you could indicate a % if you prefer - be sure to select appropriate option to the right)

    Event Date = Today's date (or a date you would like the invoice for down payment to be issued) 

    Infor ERP VISUAL - Progress Billings 
  4. Save and Close the window.
     
  5. Now we need the system to generate the invoice for the down payment.  To do this, select Print Invoices from the FILE menu of the Progress Billing Entry window.

    Infor ERP VISUAL - Progress Billings 
  6. You should accept the defaults and PRINT.  

    Infor ERP VISUAL - Progress Billings
     
  7. An invoice for the down payment will be generated and will look similar to the following:

    Infor ERP VISUAL - Progress Billings Down payment  
    This down payment invoice has now been processed in VISUAL and can be accessed from the AR Invoice Entry window.  I should mention that since I kept the defaults in Progress Billings - the down payment amount will be posted as Deferred Revenue.
     
  8. So time goes by and you eventually get around to shipping this order.  There is no change in how you process this shipment in VISUAL.  So go ahead and enter the shipment and generate the packlist.  The invoice when generated through INVOICE FORM should look similar to the following:

    Infor ERP VISUAL - Progress Billings Down payment 
    Notice the DOWN PAYMENT line just after the SUB TOTAL?  The down payment has been applied to the invoice and only the balance is remaining to be paid. 

Not bad. 

Macro Mondays: Retrieving the batch id of a posted transaction

Macro Mondays Series Ever scour through pages (and pages) of posted transactions to find the specific batch id that a record was posted in?  I have on many occasions and it drives me bonkers.

The details of posted transactions (including the batch ids) are kept in the database tables with names ending with "_DIST" - with the exception of INV_TRANS_DIST (which maintains the FIFO layers).

I've created macros (for select VISUAL modules) that allow the user to lookup the batch id used for the posted record/document they're in.

The following example will lookup the PAYABLE_DIST table to retrieve the Batch ID of an AP Voucher that was entered and posted. 


'Insert this macro in Payables Invoice Entry Module
.
'****** RETRIEVE BATCH ID OF POSTED AP VOUCHER
'****** VFAPIENT
'****** Mind the line wrap!!!

Dim x
Dim strCnn
Dim SQL
Dim rs

Dim strMsg

Dim strServerName
Dim strDatabase
Dim strUserName
Dim strPassword

strServerName="E N T E R    N A M E    O F    D A T A B A S E    S E R V E R"
strDatabase="E N T E R    N A M E    O F    D A T A B A S E"
strUserName="E N T E R    U S E R N A M E"
strPassword="E N T E R    P A S S W O R D"

strcnn="Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUsername & ";Pwd=" & strPassword & ";"

set rs = CreateObject("ADODB.Recordset")

SQL="SELECT BATCH_ID FROM PAYABLE_DIST WHERE VOUCHER_ID='" & VOUCHER_ID & "' AND POSTING_STATUS='P' GROUP BY BATCH_ID ORDER BY BATCH_ID"

rs.open SQL, strcnn

if not rs.eof then
    rs.movefirst
    
    do until rs.eof
        if len(strMsg & "")>0 then
            strMsg=strMsg & chr(13)
        end if
        strMsg=strMsg & rs("BATCH_ID") 
        rs.movenext
    loop

end if

rs.close
set rs=nothing

if strMsg<>"" then
    Msgbox "Voucher ID " & VOUCHER_ID & " was posted in the following batch(es):" & vbcrlf & vbcrlf & strMsg
else
    Msgbox "This AP Voucher has not been posted."
end if

'End of MACRO


Macro Mondays is a new series where I plan on posting macros that I use to validate, automate and enforce business processes within the VISUAL application.  If you have any macros that you wish to share, please feel free to send them to me at richard.ferlatte@gmail.com.  I'll be sure to post them on your behalf. 

 

WHO IS VISUALNUGGETS?

Rich Ferlatte
Results Driven VISUAL Specialist

CONSULTING SERVICES

Of course, I am available for hire!

ERP Implementations & Upgrades
Analytics & Reporting
Integrations & Macros
General VISUAL Support

ONLINE VISUAL WORKSHOPS

Taking it to the next level and designing a program around "Hands On VISUAL Training".

Check them out - there's sure to be one to interest you!

Operational Workshops
Reporting Workshops
Financial Workshops
Inventory Workshops
Quality Workshops

VISUAL EXTENSIONS

BOM Xploder
Drastically improve your BOM visibility and discover shortages at ANY level of your bill of material no matter how deep.
SmartViews
See, analyze, and track information that helps you better support individual, departmental, and your company’s performance metrics and goals.
eBoard - Financial Power App
Developed for accounting professionals looking for powerful and easy-to-use GL reporting software. Access live financial data, drill down to support details and export financial statements to Excel in one click.
Inventory App
This Inventory Reconciliation App provides a systematic approach to control and manage your Visual inventory effectively and efficiently
Credit Card Integration
Easily accept and process credit card payments right from within VISUAL.
ACH Document Processing
Leveraging VISUAL's payment scheduling and batch payment functions, with the added convenience, performance, and security of Electronic Funds Transfer payment processing.
Latitude Manifest & Shipping System
A seamless multi-carrier shipping solution that integrates to Infor VISUAL ERP. See how it can simplify your small parcel and LTL shipping and package tracking.
Sales Tax Automation with AvaTax
Easily automate sales and use tax in VISUAL with AvaTax. No more manually entering data or uploading rate tables and tax information.
Workforce Management with Kebrite Solutions
Streamlines the collection of Job, Labor, Payroll, Time & Attendance, and a vast array of HR related data-- with live VISUAL integration.