Michigan Health Equity Data Project 2018 Update

Douglas Volz Consulting, Inc May 20th, 2008 Make Cost Management Work for You! 20th May 2008 (Revised 13th February 2016) Douglas A. Volz OAUG Discrete MFG Cost Sub-Committee Presentation Make Cost Management Work for You! 1

Douglas Volz Consulting, Inc May 20th, 2008 Agenda Welcome to Our Session! Proposed 2008 Schedule - July to December 2008 Introduce the Cost Enhancement List Costing Tips and Tricks Presentation Make Cost Management Work for You! 2

Douglas Volz Consulting, Inc May 20th, 2008 Proposed 2008 Schedule - July to December 2008 June 17th Summer Break (no meeting in July or August) September 23rd (Oracle OpenWorld is also during this time) October 21st November 18th December -- No Meeting Make Cost Management Work for You! 3

Douglas Volz Consulting, Inc May 20th, 2008 Cost Enhancement List The objectives are: The goal is to compile a list of cost enhancements Help each other by describing how we currently deal with these limitations Pass this information to Oracle Development as a source of ideas for future releases. This list will be discussed during the June 2008 session as a group discussion (everyone gets to describe their own enhancements) Enter your enhancement ideas into the Cost Enhancement List and send to davolz@comcast.net (or use the Cost email list costmgmt@solutionbeacon.net) Make Cost Management Work for You! 4

Douglas Volz Consulting, Inc May 20th, 2008 Make Cost Management Work for You! 20th May 2008 (Revised 13th February 2016) Douglas A. Volz Douglas Volz Consulting, Inc. Helping clients use OracleApplications since 1990 www.volzconsulting.com Make Cost Management Work for You! 5

Douglas Volz Consulting, Inc May 20th, 2008 Tips and Tricks Introduction There Has to be a Better Way! Make Cost Management Work for You! 6

Douglas Volz Consulting, Inc May 20th, 2008 Introduction This presentation is mostly for Release 11i but many topics still apply to Release 12 Differences between 11i and 12 will be discussed The objective is to find out about better ways to control cost and inventory and better ways to report profits Get suggestions on standard features you may not be using Learn about report and program extensions that add significant value Make Cost Management Work for You! 7

Douglas Volz Consulting, Inc May 20th, 2008 Agenda Get suggestions on standard features you may not be using: Currency setup Period open and close by organization hierarchy Material overhead absorption rules Transaction Value Historical Summary Report Sharing costs (when using Standard Costing) Data verification audits Period close snapshots (Release 11.5.10) Make Cost Management Work for You! 8

Douglas Volz Consulting, Inc May 20th, 2008 Agenda (Continued) Simple reporting extensions that add significant value: Summarize G/L account activity by journal source and category Check inventory period close status by operating unit Margin reporting extensions Multiple inventory organization reporting for: Inventory value Material accounting entries Purchase price variance reporting for standard costing Invoice price variance reporting for all costing methods Finding high-value material transaction entries with ease Make Cost Management Work for You! 9

Douglas Volz Consulting, Inc May 20th, 2008 Agenda (Continued) Solve current problems you may have: Excess and Obsolete Reporting (Inventory Provision) Inventory to G/L Reconciliation Incorrect cost inputs and how to fix after the fact Managing period close interfaces Common item master & cost data setup issues for the Cost Rollup Better what-if cost simulation capabilities Cost mass edits Make Cost Management Work for You! 10

Douglas Volz Consulting, Inc May 20th, 2008 Agenda (Continued) Best implementation and data conversion practices for costing Conclusions Appendix High-Level Review for Oracle Cost Management (R11i) Release 12 cost enhancements Make Cost Management Work for You! 11

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features You May Not Be Using Currency setup Period open and close by organization hierarchy Material overhead absorption rules Transaction Value Historical Summary Report Sharing costs (when using Standard Costing) Data verification audits Period close snapshots (Release 11.5.10) Make Cost Management Work for You! 12

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Currency Setup You can change your item cost decimal precision at any time Change the extended precision to your requirements Used by the Cost Processor and all reports showing item costs Make Cost Management Work for You! 13

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Period Open/Close by Org Hierarchy In Release 11i you can open and close multiple inventory periods by organization hierarchy Define your organization hierarchy using an Human Resources Responsibility, or, if HRMS is not installed use an Inventory responsibility Using the Inventory Organization Parameters, Costing tab, make sure your Transfer to GL settings are all the same for all orgs in the same organization hierarchy See the next slides for screen shots Make Cost Management Work for You! 14

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Period Open/Close by Org Hierarchy Define your organization hierarchy: Make Cost Management Work for You! 15

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Period Open/Close by Org Hierarchy Inventory Organization Parameters, Costing tab: Transfer to GL setting has to be the same for all inventory organizations Make Cost Management Work for You! 16

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Period Open/Close by Org Hierarchy Open Period Control request Make Cost Management Work for You! 17

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Period Open/Close by Org Hierarchy Close Period Control request Make Cost Management Work for You! 18

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Mat l Overhead Absorption Rules Explicitly say which ones do and do not earn material overhead Make Cost Management Work for You! 19

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Transaction Value Historical Summary Report Rollback inventory value to any date you choose Choose which columns of information to view You have four columns to select The fifth column on the report is everything else Useful as a roll-forward audit report Useful to verify month-end inventory value report balances Two reports: Transaction Value Historical Summary (for Standard Costing) Transaction Value Historical Summary Average Costing Make Cost Management Work for You! 20

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Transaction Value Historical Summary Make Cost Management Work for You! 21

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Sharing Costs Under limited circumstances this feature can be quite useful Gives you the ability to share standard costs across a network of inventory organizations Useful if you do not track separate freight and duty costs by inventory organization You set the Costing Organization on your Organization Parameters screen, Costing Tab, when you define your inventory organization Note: only works for Standard Costing Make Cost Management Work for You! 22

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Data Verification Audits Found under the MRP/MPS/Planning Responsibilities: Useful for general item setup verification Make Cost Management Work for You! 23

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Data Verification Audits Run the All Audits to get: For bills of materials: Components with zero usage quantity Costed items not assigned to the costing category set For standard costing: Costed items with no frozen costs Items that have incorrect summary costs: Identify items whose summary costs do not agree with the detailed cost information And many more non-costing related data audits Make Cost Management Work for You! 24

Douglas Volz Consulting, Inc May 20th, 2008 Standard Features: Period Close Snapshot Period close snapshots (Release 11.5.10) Prior to Release 11.5.10, the inventory period close only stored summary values by inventory organization and subinventory (blank subinventories are for intransit) In Release 11.5.10, stores period-end inventory values and costs by inventory organization and subinventory for each individual item Release 11.5.10 also automatically runs at period close a detailed Period Close Reconciliation Report that compares the cumulative onhand values against the cumulative accounting values Use the Period Close Reconciliation Report to find discrepancies between your onhand perpetual values and your cumulative accounting values Make Cost Management Work for You! 25

Douglas Volz Consulting, Inc May 20th, 2008 Simple Reporting Extensions Simple reporting extensions that add significant value: Summarize G/L account activity by journal source and category Check inventory period close status by operating unit Margin reporting extensions Multiple inventory organization reporting for: Inventory value Material accounting entries Purchase price variance reporting for standard costing Invoice price variance reporting for all costing methods Finding high-value material transaction entries with ease Make Cost Management Work for You! 26

Douglas Volz Consulting, Inc May 20th, 2008 Reporting Extensions: Summarize G/L Account Activity How to quickly find a summary of monthly activity by G/L journal source? Use one of the standard G/L journal lines reports Usually too much information Ask doug@volzconsulting.com for a sample SQL report for the G/L Summary by Journal Source and Category Report Tailor it for your requirements, you can use it as a SQL Report, or Your IT staff could put it in XML Publisher to make it multi-currency and easier to download into Microsoft Excel Free for use but please acknowledge the author in your documentation Make Cost Management Work for You! 27

Douglas Volz Consulting, Inc May 20th, 2008 Reporting Extensions: Summarize G/L Account Activity Sample Layout Make Cost Management Work for You! 28

Douglas Volz Consulting, Inc May 20th, 2008 Reporting Extensions: Summarize G/L Account Activity Other useful ideas for summarizing G/L activity: You can create Lookup Codes, codes that list your inventory or margin accounts Take the code example from G/L Summary by Journal Source and Category Report and add logic to it to limit the accounts by lookup code (from mfg_lookups in this example) Make Cost Management Work for You! 29

Douglas Volz Consulting, Inc May 20th, 2008 Reporting Extensions: Check Inventory Period Close Status Check inventory period close status by operating unit Useful when you have many inventory orgs to close No longer need to check each inventory org one-by-one Make Cost Management Work for You! 30

Douglas Volz Consulting, Inc May 20th, 2008 Reporting Extensions: Margin Reporting In Release 11i you run the Load Margin Analysis program for each month Puts that month s sales and COGS into a Margin Summary table Your IT staff can build queries for custom reporting The underlying table is: CST_MARGIN_SUMMARY Make Cost Management Work for You! 31

Douglas Volz Consulting, Inc May 20th, 2008 Reporting Extensions: Multiple Inventory Orgs Reports Multiple inventory organization reporting for: Inventory value Material accounting entries Recommend two custom reports: Multi-Org All Inventory Value Report Multi-Org Material Account Distribution Summary Report The Multi-Org All Inventory Value Report uses the existing Oracle*Reports code, and adds an Operating Unit or Organization Hierarchy Parameter(s) to it (or get the White Paper from the Inventory Reconciliation presentation for sample SQL code) The Multi-Org Material Account Distribution Summary Report can be done either as a SQL Report or an XML Publisher Report (or get the White Paper from the Inventory Reconciliation presentation for sample SQL code) Make Cost Management Work for You! 32

Douglas Volz Consulting, Inc May 20th, 2008 Reporting Extensions: Purchase Price Variance Purchase price variance is the difference between your PO unit prices and your Standard Frozen Costs However the standard Oracle report is difficult to use Ask doug@volzconsulting.com for a sample SQL Purchase Price Variance Report from www.volzconsulting.com. (Note: this version is from Release 11i, due to changes in Release 12, this version may not work in Release 12.) Tailor it for your requirements, you can use it as a SQL Report, or Your IT staff could put it in XML Publisher to make it multi-currency and easier to download into Microsoft Excel Free for use but please acknowledge the author in your documentation Make Cost Management Work for You! 33

Douglas Volz Consulting, Inc May 20th, 2008 Reporting Extensions: Invoice Price Variance Invoice price variance is the difference between your PO unit price and your A/P invoice actual cost However the standard Oracle report is difficult to use Ask doug@volzconsulting.com for a sample Invoice Price Variance Report. (Note: this version is from Release 11i, due to changes in Release 12, this version will not work in Release 12.) Tailor it for your requirements, you can use it as a SQL Report, or Your IT staff could put it in XML Publisher to make it multi-currency and easier to download into Microsoft Excel Free for use but please acknowledge the author in your documentation Make Cost Management Work for You! 34

Douglas Volz Consulting, Inc May 20th, 2008 Reporting Extensions: Find High Value Mat l Transactions Finding high-value material transaction entries with ease Standard report does not exist Request the Find High Value Mat l Transactions Report by sending an email to doug@volzconsulting.com for a SQL report version for your use. (Note: this version is from Release 11i, due to changes in Release 12, this version will not report any accounting information from the Release 12 Subledger/SLA Accounts.) You select the minimum absolute transaction value to search for The report brings back the entire material transaction Tailor it for your requirements, you can keep it as a SQL Report, or Your IT staff could put it in XML Publisher to make it multi-currency and easier to download into Microsoft Excel Free for use but please acknowledge the author in your documentation Make Cost Management Work for You! 35

Douglas Volz Consulting, Inc May 20th, 2008 Solve Current Problems You May Have Solve current problems you may have: Excess and Obsolete Reporting Inventory to G/L Reconciliation Incorrect cost inputs and how to fix after the fact Managing period close interfaces Common item master and cost data setup issues for Cost Rollup Better what-if cost simulation capabilities Cost mass edits Make Cost Management Work for You! 36

Douglas Volz Consulting, Inc May 20th, 2008 Current Problems: Excess and Obsolete Reporting Oracle Inventory only has a Slow Moving Inventory Report So unfortunately you should consider a custom solution Parameters and decisions to consider: What is a new item so you exclude it from the analysis Do you want to include your MRP/ASCP planning as a source of future demand? How many months of transaction history should you consider? And don t forget to include your corporate excess and obsolete provision policies (amount to reserve based on forecasted usage) Note: U.S. Sarbanes-Oxley requirements can turn this into a very complex solution! Make Cost Management Work for You! 37

Douglas Volz Consulting, Inc May 20th, 2008 Current Problems: Inventory to G/L Reconciliation Basic reports to use, when to run, timing considerations Sample spreadsheet What to do when you can t reconcile Better ways to reconcile multiple inventory organizations For the latest presentation on inventory reconciliation get the 2014 Inventory Reconciliation presentation from http://www.volzconsulting.com/resources.html. Make Cost Management Work for You! 38

Douglas Volz Consulting, Inc May 20th, 2008 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 report balances If you do not balance, check for manual G/L journal entries and entries from other subledgers In Release 11.5.10, there is a new month-end balances/value table that holds the item quantities and values in detail, thereby eliminating report cut-off issues This information is populated when you close the inventory accounting period But there is limited reporting from this information Make Cost Management Work for You! 39

Douglas Volz Consulting, Inc May 20th, 2008 How to Balance the Receiving Valuation Accounts Use the Receiving Value 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 If using outside processing you would also run the WIP Account Summary Report Note: in R11i, the quantities and values for the Receiving Value Reports are on demand they must be run at the correct time, after the period is closed and before the new accounting period has transactions Make Cost Management Work for You! 40

Douglas Volz Consulting, Inc May 20th, 2008 How to Balance the Inventory (Stores) Valuation Accts. Use the Inventory Value Reports to get the month-end balances Use the Transaction Value Historical Summary Report also called the Rollback Report to get a prior balance for subinventories (only) Use the Period Close Value Summary Report, for a double check Use the Material Distribution Summary Report for monthly totals Note: in R11i, the quantities and values for the Inventory Value Reports are on demand they must be run at the correct time, after the period is closed and before the new accounting period has transactions Make Cost Management Work for You! 41

Douglas Volz Consulting, Inc May 20th, 2008 How to Balance the Intransit Valuation Accounts Use the Intransit Value 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 Note: in R11i, the quantities and values for the Intransit Value Reports are on demand they must be run at the correct time, after the period is closed and before the new accounting period has transactions Make Cost Management Work for You! 42

Douglas Volz Consulting, Inc May 20th, 2008 How to Balance the WIP Valuation Accounts Use the WIP Value 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 Note: the quantities and values for the WIP Value Reports are stored by accounting period, you may run these any time after period close as needed. Make Cost Management Work for You! 43

Douglas Volz Consulting, Inc May 20th, 2008 Sample Reconciliation Spreadsheet and Example Make Cost Management Work for You! 44

Douglas Volz Consulting, Inc May 20th, 2008 What do You do When You Have Several Organizations? Using standard reports may be too time-consuming Four or more reports needed to reconcile each inventory organization Several firms with multiple inventory organizations have created a Multi- Org Inventory Reconciliation Report Using stored month-end balances available in 11.5.10 (or 11.5.9 plus patches) Directly compare the General Ledger account balances to the stored month-end perpetual values Helps to quickly spot problems Get the White Paper from the Inventory Reconciliation presentation for sample SQL code Make Cost Management Work for You! 45

Douglas Volz Consulting, Inc May 20th, 2008 What do You do When You Can t Reconcile? Reasons Report cut-off issues Incorrect misc. inventory transactions Disabling a code combination during the month Subledger miscodings Manual GL journal entries Back-dated transactions from external system feeds Miscoded purchase orders For a detailed presentation on reconciliation, go to http://www.volzconsulting.com/resources.html Make Cost Management Work for You! 46

Douglas Volz Consulting, Inc May 20th, 2008 Current Problems: Incorrect Cost Inputs Two issues: How to prevent incorrect cost inputs And how to fix after the fact How to prevent incorrect cost inputs Consider custom reports or alerts to compare current cost with PO unit prices If using Standard Costing, activate an existing alert for items with no costs (you may have to modify it to ignore old items) Use item status control to prevent transactions before the item is ready to be used Make Cost Management Work for You! 47

Douglas Volz Consulting, Inc May 20th, 2008 Current Problems: Incorrect Cost Inputs And how to fix after the fact Consider reporting/SLA solutions to fix your COGS Request SQL report samples, ask doug@volzconsulting.com for old vs. new comparison reports for COGS and inventory value You enter the desired new item costs in a simulation cost type One report compares your As Booked COGS against the new COGS and gives you a report you can use as a manual journal entry The second report requires the Release 11.5.10 month-end inventory snapshot and compares the simulation cost type against the month-end inventory value More complex solutions have also been done, including: Creating a program that creates sub-ledger COGS entries to fix the existing COGS transactions (yes, even in Release 12 and SLA!) Including the ability to fix transactions as long as the G/L is still open (and inventory is closed) Make Cost Management Work for You! 48

Douglas Volz Consulting, Inc May 20th, 2008 Current Problems: Managing Period Close Interfaces Until you get to Release 12, Oracle does not provide good overview reporting for your period close interfaces Consider writing a custom report that reviews your major inventory, WIP and other financial period close interfaces See How in the Dickens Do I Handle Those Month-End Interfaces? For detailed information on creating a custom report http://www.volzconsulting.com/resources.html Make Cost Management Work for You! 49

Douglas Volz Consulting, Inc May 20th, 2008 Current Problems: Item Master and Cost Setup Issues Common item master and cost data setup issues for the Cost Rollup Make items where the assembly is set to not be rolled up Buy items that are not included in the cost rollup Buy items set to be rolled up (may double up your costs) Bills of materials with no components (will roll up to a zero cost) Differences between the summary and detail cost table settings for: Rollup source type (detailed cost table) Rollup flag (summary cost type) Mistakes happen when you change the item master but not the cost controls for your items Request SQL report samples, ask doug@volzconsulting.com Make Cost Management Work for You! 50