60 Inventory Orgs 6 Ledgers No Worries Reconcile

60 Inventory Orgs? 6 Ledgers? No Worries, Reconcile Your Inventory With Ease! OAUG Nor. Cal Training Day January 13, 2011 Douglas Volz Consulting doug@volzconsulting. com

Reconciliation Agenda p Background p General Reconciliation Principles p Receiving Valuation Accounts p Inventory Valuation Accounts p Work In Process Valuation Accounts p Common Reconciliation Challenges p Custom Solutions for Multiple Organizations Slide 2

Summary Background p Inventory to G/L reconciliation can be difficult p This presentation includes the standard Oracle techniques p Plus additional ideas and solutions that exceed standard tools p Doug has helped Oracle clients with Cost Accounting since 1990 p Doug also leads the OAUG Cost Management SIG Slide 3

Sample Financial Structure to Reconcile Slide 4

There Has to be an Easier Way !! Slide 5

General Reconciliation Principles p Use the following formula: Prior cum. balance + monthly accounting activity = month-end cumulative G/L balance p Also compare above formula to cumulative perpetual report balances p If you do not balance, check for manual G/L journal entries and entries from other subledgers Slide 6

Sample Reconciliation Worksheet Slide 7

More Reconciliation Principles p Ignore minor rounding differences: § When the unit cost precision is higher than the currency decimal precision, you may have minor rounding differences between the valuation reports and cumulative accounting totals p Release 11 i: Watch out for transaction & reporting cutoffs issues p Release 12: All Inventory and Receiving Value Reports have an “As of Date” Slide 8

How to Balance the Receiving Valuation Accounts p Use the Receiving Valuation Reports to get the monthend balances p Use the Receiving Account Distribution Report for monthly increases to the Receiving Valuation Account p Use the Material Distribution Summary Report for monthly decreases to the Receiving Valuation Account Slide 9

Receiving Value Report Slide 10

Receiving Account Distribution Report Slide 11

How to Balance the Inventory Valuation Accounts p Use the Inventory Valuation Reports to get the month-end balances p Or, use the Period Close Reconciliation Report p You can also use the Transaction Historical Summary Report § Transaction Historical Summary Report – Standard Costing § Transaction Historical Summary Report – Average Costing p Also use the Period Close Value Summary Report p And for monthly totals use the Material Distribution Summary Report Slide 12

Material Account Distribution Summary Slide 13

All Inventories Value Report Slide 14

Inventory Value Report Slide 15

If the Inventory Accounts Do Not Balance p Check for miscellaneous inventory transactions, where the offset account is the same as the subinventory or organization valuation account Alias Issue Example Inventory 10 DR Inventory Account (from the Account Alias) Account 10 CR Inventory Account Acct Line Type Inv valuation p Use the Material Distribution Inquiry to find these, query for entries that hit your subinventory valuation accounts (Std) or organization valuation accounts (Avg), where the Accounting Line Type is “Account” Slide 16

If the Inventory Accounts Do Not Balance Slide 17

How to Balance the Intransit Valuation Accounts p Use the Intransit Valuation Reports to get the month-end balances § Choose Yes for the option “Only Display Inventory You Own” p Use the Material Distribution Summary Report for monthly totals Slide 18

Intransit Value Report Slide 19

How to Balance the WIP Valuation Accounts p Use the WIP Valuation Report to get the month-end balances (No cut-off issues) § Choose the option to include closed jobs p Use the WIP Account Summary and Material Distribution Summary Reports for monthly totals Slide 20

WIP Value Report Slide 21

WIP Account Summary Report Slide 22

R 12: Journal Entries Report – Cost Management Process Category: § Inventory § Receiving § Work in Process § Manual / Third Party Merge Slide 23

Journal Entries Report – Cost Management Slide 24

Journal Entries Report – Cost Management Slide 25

Journal Entries Report – Cost Management Slide 26

Drill-Down From the General Ledger p Release 11 i: The Inventory/WIP GL batch numbers are visible in the G/L, as part of the journal entry batch name p Release 11 i: You can run the Material and WIP Distribution Reports by this G/L batch number and compare to the General Ledger p Release 12: You cannot see the organization code or G/L batch number on the G/L journal entry batch name unless you customize your SLA rules p Release 12: You can drill down from your G/L to the Subledger Accounting Entries to the material, receiving and WIP transactions p Release 12: You can run the Subledger Journal Entries, Account Balances or Account Analysis Reports (with setup required) Slide 27

Oracle Release 10 to 11 i Inventory/WIP Integration to the GL COLUMN NAME -----REFERENCE 1 DATA TO INSERT ---------------GL_BATCH_ID, ORGANIZATION_CODE DESTINATION WHERE THE DATA SHOWS UP -----------------------------JE_BATCH NAME Post Journals Form REFERENCE 2 Insert GL_BATCH_DATE, ORGANIZATION_CODE, ORG_GL_BATCHES. DESCRIPTION, JE_BATCH DESCRIPTION REFERENCE 3 DO NOT POPULATE Use unknown REFERENCE 4 DO NOT POPULATE Post Journals Form JE_HEADER NAME Note: Account Analysis Report, Account Inquiry Journal Import will append JE_CATEGORY_NAME, CURRENCY_CODE REFERENCE 5 Same as REFERENCE 2 JE_HEADER DESCRIPTION REFERENCE 6 DO NOT POPULATE JE_HEADER external_reference REFERENCE 7 DO NOT POPULATE Used for JE_HEADER ACCRUAL_REV_FLAG REFERENCE 8 DO NOT POPULATE Used for JE_HEADER ACCRUAL_REV_PERIOD_NAME REFERENCE 9 DO NOT POPULATE Used for JE_HEADER ACCRUAL_REV_CHANGE_SIGN_FLAG REFERENCE 10 Same as REFERENCE 2 JE_LINES DESCRIPTION General Ledger Report, Journal Entry Inquiry REFERENCE 21 Insert GL_Batch_ID GL_JE_LINES. REFERENCE 1 Not Displayed REFERENCE 22 Insert ORGANIZATION_CODE GL_JE_LINES. REFERENCE 2 Not Displayed REFERENCE 23 Insert TRANSACTION_ID GL_JE_LINES. REFERENCE 3 Not Displayed Slide 28 Account Inquiry

List of Month-End Close Reports p Material Distribution Detail/Summary Reports p WIP Account Detail/Summary Reports p Receiving Account Distribution Report p Purchase Price Variance Report p Invoice Price Variance Report p Discrete Job Value p Repetitive Value Report p Expense Job Value Report p Margin Analysis Report Slide 29

Month-End Close Reports (Cont’d) p Inventory Value Reports p Elemental Inventory Value Reports p Intransit Value Report p Subinventory Account Value Report (disabled in Release 12) p All Inventories Value Reports p Receiving Value Report p Receiving Value by Destination Report (disabled in Release 12 – unable to view the perpetual expense balances) p WIP Value Report p Period Close Value Summary Report p Period Close Reconciliation Report (11. 5. 10 and 12) Slide 30

Common Reconciliation Challenges p Release 11 i only: report cut-off issues at month-end or running multiple periods at a time p Account alias or miscellaneous inventory transactions using same accounts p Disabling a code combination during the month p Subledger miscodings p Manual G/L journal entries p Back-dated transactions from external system feeds p Miscoded purchase orders p Delivering goods to a closed work order Slide 31

Common Reconciliation Challenges (Cont’d) p Sharing System Accounts: § § § § § Expense and Inventory Accrual Accounts Receiving Valuation Accounts Organization Level Accounts (Average, FIFO, LIFO Costing) Subinventory Accounts (Standard Costing) Cost Group Accounts (Project Mfg, WMS) WIP Accounting Classes Inventory Account Aliases Order Types and COGS Release 12 Subledger Accounting Rules causing you to share accounts p Release 11 i: wrong initial subinventory summary balances p Initial legacy GL balances to do not match Oracle perpetual Slide 32

R 12 Improvements Period Close Diagnostics p Forms Drilldown • Using the same Inventory Accounting Periods form you can drilldown to associated forms to view the problem transactions p New workflow to notify of failing transactions p Period Close Pending Transactions Report • Report the transactions holding up the inventory accounting period • Only by inventory organization and only by transaction error Slide 33

Reconciliation Improvements in R 12 p The following reports can now be run with an As-of Date: § § § § Inventory Value Report – By Subinventory Inventory Value Report – By Cost Group Elemental Inventory Value Report – By Subinventory Elemental Inventory Value Report – By Cost Group All Inventories Value Report – By Cost Group Intransit Value Report Receiving Value Report Many of these reports are also available in R 12 as XML reports Slide 34

R 12 Challenges – Inventory Reconciliation p Inventory Reconciliation § May have more inventory reconciliation issues in R 12 p Issues Common to Release 12 and 11 i § The differences between the perpetual and accounted-for inventory balances are trapped in a table the users don’t see § Limited use for the Period Close Reconciliation Report • Most users and analysts don’t know about it • Limited to a single warehouse • Shows zero quantity items – which is not efficient § Need a Multi-Org Perpetual vs. Accounted-For Report Slide 35

R 12 Challenges – Inventory Reconciliation p Sample Period Close Variances Report § From the CST_PERIOD_CLOSE_SUMMARY table You can also use this information to get your month-end perpetual quantities and values for intransit and subinventories Slide 36

New Reconciliation Challenges in R 12 Subledger Accounting and Inventory Reconciliation § Which accounts represent inventory and WIP valuation? § Harder to reconcile manually with no valuation accounts on the Inventory Value or Receiving Value Reports § Receiving Value by Destination Account Report is disabled Release 12 dropped the valuation accounts on most inventory reports Slide 37

R 12 Material and WIP Distribution Inquiries and Reports § Release 12 mat’l and WIP distribution inquiries & reports do not reflect the SLA results § SLA Journal Reports do not show operational information § Release 12 records the material accounting and WIP accounting entries by cost element – rendering these inquiries and reports more difficult to use, especially for Standard Costing. Release 12 forces Standard Costing to record WIP & Material entries by Cost Element / you can summarize in SLA but you don’t have the same detail reporting in SLA Slide 38

Basic Table Structure for SLA Enter Transaction(s) Accounting Processor Module Specific Transaction Accounting Tables One Common Accounting Subledger Table Receiving Material WIP Create Accounting SLA Accounting Tables G/L Tables XLA_EVENTS Transaction Accounting Tables GL_LEDGERS XLA_AE_HEADERS GL_ INTERFACE GL_JE_BATCHES XLA_AE_LINES GL_JE_HEADERS XLA_DISTRIBUTION _LINKS GL_JE_LINES Slide 39

Custom Solutions for Multiple Organizations p Common Custom Reports and Desired Enhancements § § § § § Multi-Org Material Account Summary Report Multi-Org Receiving Account Summary Report Multi-Org WIP Account Summary Report Multi-Org All Inventories Value Report Multi-Org Inventory Value Report Multi-Org Receiving Value Report Multi-Org Expense Value Report Multi-Org WIP Value Report Multi-Org G/L to Inventory Reconciliation Report Slide 40

Desired Enhancements (Cont’d) p Eliminate the two layers of accounting entries in R 12 p Put the Inventory and Receiving Valuation Accounts back on the valuation reports p Add a standard report or alert to find the miscellaneous inventory transactions that are causing an out-of-balance p Simplify, make more robust, or eliminate the interfaces needed in Oracle (especially the Receiving Interface) Slide 41

Desired Enhancement: G/L to Inventory Reconciliation Script Just think how much time this would save … Slide 42

Sample G/L to Inventory Reconciliation Script p Selects and compares: § Selects the cumulative G/L balances by journal source § Selects the cumulative Inventory (stores) and Intransit perpetual balances from the month-end snapshot § Selects the cumulative WIP balances § Compares the three above balances and reports differences § Runs in under 2 minutes for 6 ledgers with 60 inventory orgs § R 11 i, change GL_LEDGERS to GL_SETS_OF_BOOKS Slide 43

Sample Reconciliation Script (Cont’d) p Automatically Compares: § G/L to Inventory Perpetual § G/L to Intransit Perpetual § G/L to WIP Period-End Balances p Caveats: § Tested for Standard Costing (should work for Avg, FIFO, LIFO) § Assumes using product line or product group segment from your item master COGS account § Does not consider Cost Groups § Assumes using one valuation account per subinventory or org § Your Release 12 Subledger Accounting Rules may be different Slide 44

G/L to Inventory Reconciliation Data Flow LEGEND Costing & WIP tables General Ledger tables Period End Cost Snapshot Tables CST_PERIOD_CLOSE_SUMMARY WIP_PERIOD_BALANCES Compare Balances by Full Account General Ledger Tables GL_LEDGERS (R 11 i: GL_SETS_OF_BOOKS) GL_BALANCES GL_JE_HEADERS GL_JE_LINES GL_CODE_COMBINATIONS Slide 45 Report Output

G/L to Inventory Reconciliation Lookup p Uses a lookup type to identify the inventory reconciliation accounts Slide 46

G/L to Inventory Reconciliation Report These concepts work for AVG and STD Costing, for both Release 11 i and 12 Slide 47

Summary § Release 12 solves many of the report cut-off issues § Still have multi-org reporting issues with account distribution and valuation reports § Release 12 Subledger Accounting can complicate reconciliation § Investments in custom reporting can save significant amounts of time at month-end Slide 48

Send Email Address to Get the Following: p Mention which release, costing method, and if using separate accounts by cost element § Sample reconciliation excel spreadsheet § Comparison Script between the G/L and Inventory Perpetual Values § doug@volzconsulting. com p Informal workshops can be arranged Slide 49

Acknowledgements § Oracle Cost Development § Partners in crime at my many clients Slide 50

Douglas Volz (doug@volzconsulting. com) Professional Background Doug Volz is a Senior Architect and Advisor for Oracle Application projects, with a particular interest in Cost and Project Management. He has 30 years accumulated experience, including 5 years in Oracle Development (co-designing Oracle Cost Management) and 12 years in industry in Cost and Accounting Management positions. His Manufacturing and Cost systems experience covers project management, software design/development, delivery and consulting services, for both Oracle Corporation, and multiple international consulting firms. Prior to his systems career, Mr. Volz also held numerous management accounting positions for telecommunications, defense, and electronics companies. In his consulting roles Doug has served over 100 clients. Many of these were multi-org, multi-currency with global footprints. Countries include US, Mexico, UK, Netherlands, Belgium, Taiwan, P. R. O. C. , Norway, Japan, Italy, Switzerland Germany. Doug leads the OAUG Cost Management Special Interest Group. He also advises and participates on the Oracle Customer Advisory Board for Fusion Costing. Core Expertise Experience p Multi-organization, Multi-currency ERP Implementations p Project Management and Senior Project Advisor p Core manufacturing processes 4 Cost Management 4 Inventory 4 Bills of Material 4 WIP p Systems Integration and Data Conversions p Sample of clients served: Ø Beckman Coulter (US) Ø Matsushita (UK, Mexico) Ø NTL (UK, now Virgin Media) Ø Logitech (US, Taiwan, P. R. C. ) Ø Celgene Corporation (US, Switzerland) Ø NTL (UK, now Virgin Media) Ø TCI International (US) Ø Onninen AS (Norway) Slide 51

Thank You for Your Attendance and Participation Slide 52