60 Inventory Orgs? 6 Ledgers? No Worries, Reconcile Your Inventory With Ease! OAUG NorCal Training Day January 13, 2011 - PowerPoint PPT Presentation

Loading...

PPT – 60 Inventory Orgs? 6 Ledgers? No Worries, Reconcile Your Inventory With Ease! OAUG NorCal Training Day January 13, 2011 PowerPoint presentation | free to download - id: 3adbf9-NTgwY



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

60 Inventory Orgs? 6 Ledgers? No Worries, Reconcile Your Inventory With Ease! OAUG NorCal Training Day January 13, 2011

Description:

60 Inventory Orgs? 6 Ledgers? No Worries, Reconcile Your Inventory With Ease! OAUG NorCal Training Day January 13, 2011 Douglas Volz Douglas Volz Consulting – PowerPoint PPT presentation

Number of Views:140
Avg rating:3.0/5.0
Slides: 53
Provided by: douglas161
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 60 Inventory Orgs? 6 Ledgers? No Worries, Reconcile Your Inventory With Ease! OAUG NorCal Training Day January 13, 2011


1
60 Inventory Orgs? 6 Ledgers? No Worries,
Reconcile Your Inventory With Ease! OAUG NorCal
Training Day January 13, 2011
Douglas Volz Douglas Volz Consulting doug_at_volzcons
ulting.com
2
Reconciliation Agenda
  • Background
  • General Reconciliation Principles
  • Receiving Valuation Accounts
  • Inventory Valuation Accounts
  • Work In Process Valuation Accounts
  • Common Reconciliation Challenges
  • Custom Solutions for Multiple Organizations

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

Slide 3
4
Sample Financial Structure to Reconcile
Slide 4
5
There Has to be an Easier Way !!
Slide 5
6
General Reconciliation Principles
  • Use the following formula Prior cum. balance
    monthly accounting activity month-end
    cumulative G/L balance
  • Also compare above formula to cumulative
    perpetual report balances
  • If you do not balance, check for manual G/L
    journal entries and entries from other subledgers

Slide 6
7
Sample Reconciliation Worksheet
Slide 7
8
More Reconciliation Principles
  • 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
  • Release 11i Watch out for transaction
    reporting cut-offs issues
  • Release 12 All Inventory and Receiving Value
    Reports have an As of Date

Slide 8
9
How to Balance the Receiving Valuation Accounts
  • Use the Receiving Valuation Reports to get the
    month-end balances
  • Use the Receiving Account Distribution Report for
    monthly increases to the Receiving Valuation
    Account
  • Use the Material Distribution Summary Report for
    monthly decreases to the Receiving Valuation
    Account

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

Slide 12
13
Material Account Distribution Summary
Slide 13
14
All Inventories Value Report
Slide 14
15
Inventory Value Report
Slide 15
16
If the Inventory Accounts Do Not Balance
  • Check for miscellaneous inventory transactions,
    where the offset account is the same as the
    subinventory or organization valuation account
  • 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

Account Alias Issue Example
Inventory Acct Line Type
10 Account
DR Inventory Account (from the
Account Alias)
10 Inv valuation
CR Inventory Account
Slide 16
17
If the Inventory Accounts Do Not Balance
Slide 17
18
How to Balance the Intransit Valuation Accounts
  • Use the Intransit Valuation Reports to get the
    month-end balances
  • Choose Yes for the option Only Display Inventory
    You Own
  • Use the Material Distribution Summary Report for
    monthly totals

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

Slide 20
21
WIP Value Report
Slide 21
22
WIP Account Summary Report
Slide 22
23
R12 Journal Entries Report Cost Management
  • Process Category
  • Inventory
  • Receiving
  • Work in Process
  • Manual / Third Party Merge

Slide 23
24
Journal Entries Report Cost Management
Slide 24
25
Journal Entries Report Cost Management
Slide 25
26
Journal Entries Report Cost Management
Slide 26
27
Drill-Down From the General Ledger
  • Release 11i The Inventory/WIP GL batch numbers
    are visible in the G/L, as part of the journal
    entry batch name
  • Release 11i You can run the Material and WIP
    Distribution Reports by this G/L batch number and
    compare to the General Ledger
  • 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
  • Release 12 You can drill down from your G/L to
    the Subledger Accounting Entries to the material,
    receiving and WIP transactions
  • Release 12 You can run the Subledger Journal
    Entries, Account Balances or Account Analysis
    Reports (with setup required)

Slide 27
28
Oracle Release 10 to 11i Inventory/WIP
Integration to the GL
COLUMN NAME DATA TO INSERT
DESTINATION WHERE THE DATA SHOWS
UP ----------- ------------------------------
---------------------------- --------------------
---------- REFERENCE1 GL_BATCH_ID,
ORGANIZATION_CODE JE_BATCH NAME
Post Journals Form REFERENCE2 Insert
GL_BATCH_DATE, ORGANIZATION_CODE,
ORG_GL_BATCHES.DESCRIPTION, JE_BATCH DESCRIPTION
Post Journals Form REFERENCE3 DO
NOT POPULATE Use
unknown REFERENCE4 DO NOT POPULATE
JE_HEADER NAME Account
Analysis Report, Account Inquiry
Note Journal Import will append
JE_CATEGORY_NAME, CURRENCY_CODE REFERENCE5
Same as REFERENCE2 JE_HEADER
DESCRIPTION Account Inquiry REFERENCE6
DO NOT POPULATE JE_HEADER
external_reference REFERENCE7 DO NOT
POPULATE Used for JE_HEADER
ACCRUAL_REV_FLAG REFERENCE8 DO NOT POPULATE
Used for JE_HEADER
ACCRUAL_REV_PERIOD_NAME REFERENCE9 DO NOT
POPULATE Used for JE_HEADER
ACCRUAL_REV_CHANGE_SIGN_FLAG REFERENCE10
Same as REFERENCE2 JE_LINES
DESCRIPTION General Ledger Report,
Journal Entry Inquiry REFERENCE21 Insert
GL_Batch_ID GL_JE_LINES.REFERENCE1 Not
Displayed REFERENCE22 Insert
ORGANIZATION_CODE GL_JE_LINES.REFERENCE2 Not
Displayed REFERENCE23 Insert
TRANSACTION_ID GL_JE_LINES.REFERENCE3 Not
Displayed
Slide 28
29
List of Month-End Close Reports
  • Material Distribution Detail/Summary Reports
  • WIP Account Detail/Summary Reports
  • Receiving Account Distribution Report
  • Purchase Price Variance Report
  • Invoice Price Variance Report
  • Discrete Job Value
  • Repetitive Value Report
  • Expense Job Value Report
  • Margin Analysis Report

Slide 29
30
Month-End Close Reports (Contd)
  • Inventory Value Reports
  • Elemental Inventory Value Reports
  • Intransit Value Report
  • Subinventory Account Value Report (disabled in
    Release 12)
  • All Inventories Value Reports
  • Receiving Value Report
  • Receiving Value by Destination Report (disabled
    in Release 12 unable to view the perpetual
    expense balances)
  • WIP Value Report
  • Period Close Value Summary Report
  • Period Close Reconciliation Report (11.5.10 and
    12)

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

Slide 31
32
Common Reconciliation Challenges (Contd)
  • 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
  • Release 11i wrong initial subinventory summary
    balances
  • Initial legacy GL balances to do not match Oracle
    perpetual

Slide 32
33
R12 Improvements Period Close Diagnostics
  • Forms Drilldown
  • Using the same Inventory Accounting Periods form
    you can drilldown to associated forms to view the
    problem transactions
  • New workflow to notify of failing transactions
  • 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
34
Reconciliation Improvements in R12
  • 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
  • All Inventories Value Report By Cost Group
  • Intransit Value Report
  • Receiving Value Report

Many of these reports are also available in R12
as XML reports
Slide 34
35
R12 Challenges Inventory Reconciliation
  • Inventory Reconciliation
  • May have more inventory reconciliation issues in
    R12
  • Issues Common to Release 12 and 11i
  • The differences between the perpetual and
    accounted-for inventory balances are trapped in a
    table the users dont see
  • Limited use for the Period Close Reconciliation
    Report
  • Most users and analysts dont 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
36
R12 Challenges Inventory Reconciliation
  • 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
37
New Reconciliation Challenges in R12
  • 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
38
R12 Material and WIP Distribution Inquiries and
Reports
  • Release 12 matl 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 dont have the same
detail reporting in SLA
Slide 38
39
Basic Table Structure for SLA
Module Specific Transaction Accounting Tables
Enter Transaction(s)
One Common Accounting Subledger Table
Accounting Processor
SLA Accounting Tables
G/L Tables
Transaction Accounting Tables
GL_LEDGERS
XLA_EVENTS
Create Accounting
GL_JE_BATCHES
XLA_AE_HEADERS
GL_ INTERFACE
Receiving Material WIP
GL_JE_HEADERS
XLA_AE_LINES
GL_JE_LINES
XLA_DISTRIBUTION_LINKS
Slide 39
40
Custom Solutions for Multiple Organizations
  • 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
41
Desired Enhancements (Contd)
  • Eliminate the two layers of accounting entries in
    R12
  • Put the Inventory and Receiving Valuation
    Accounts back on the valuation reports
  • Add a standard report or alert to find the
    miscellaneous inventory transactions that are
    causing an out-of-balance
  • Simplify, make more robust, or eliminate the
    interfaces needed in Oracle (especially the
    Receiving Interface)

Slide 41
42
Desired Enhancement G/L to Inventory
Reconciliation Script
Just think how much time this would save
Slide 42
43
Sample G/L to Inventory Reconciliation Script
  • 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
  • R11i, change GL_LEDGERS to GL_SETS_OF_BOOKS

Slide 43
44
Sample Reconciliation Script (Contd)
  • Automatically Compares
  • G/L to Inventory Perpetual
  • G/L to Intransit Perpetual
  • G/L to WIP Period-End Balances
  • 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
45
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
Report Output
General Ledger Tables GL_LEDGERS (R11i
GL_SETS_OF_BOOKS) GL_BALANCES GL_JE_HEADERS GL_JE_
LINES GL_CODE_COMBINATIONS
Slide 45
46
G/L to Inventory Reconciliation Lookup
  • Uses a lookup type to identify the inventory
    reconciliation accounts

Slide 46
47
G/L to Inventory Reconciliation Report
These concepts work for AVG and STD Costing, for
both Release 11i and 12
Slide 47
48
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
49
Send Email Address to Get the Following
  • 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_at_volzconsulting.com
  • Informal workshops can be arranged

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

51
Douglas Volz (doug_at_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 and 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
  • 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)
  • Multi-organization, Multi-currency ERP
    Implementations
  • Project Management and Senior Project Advisor
  • Core manufacturing processes
  • Cost Management
  • Inventory
  • Bills of Material
  • WIP
  • Systems Integration and Data Conversions

Slide 51
52
  • Thank You for Your Attendance and Participation
About PowerShow.com