1 / 35

AV-12 Customer Case Study – Creating Period-end Reports (IC and AP)

AV-12 Customer Case Study – Creating Period-end Reports (IC and AP). Dave Wiedenfeld Group Project Leader , IT CF Industries, Inc. Real-time System!. Period End. Inventory Transaction. Inventory Transaction. Inventory Transaction. Inventory Transaction. Inventory Transaction.

aiko-hull
Download Presentation

AV-12 Customer Case Study – Creating Period-end Reports (IC and AP)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. AV-12Customer Case Study – Creating Period-end Reports (IC and AP) Dave Wiedenfeld Group Project Leader, IT CF Industries, Inc.

  2. Real-time System! Period End Inventory Transaction Inventory Transaction Inventory Transaction Inventory Transaction Inventory Transaction Inventory Transaction While having a real-time system is great from an operational perspective, it can be challenging to generate period-end reports suitable for accounting.

  3. The Concept Period End To get a period-end report, transactions used by the report need to be ‘rolled back’ to the end of the period.

  4. Inventory – What to roll back To back out transactions not wanted, it must first be understood how transactions are created and dated

  5. An Inventory Transaction’s Journey to the G/L

  6. Inventory Transaction Types

  7. G/L Transactions Created for:Issues, Inventory Adjustments, and Counts

  8. Offset Account Specifications

  9. G/L Transactions Created for:Inventory Re-class (In Out)

  10. G/L Transactions Created for:Storeroom Transfers (To / From)

  11. G/L Transactions Created for:Receipts

  12. Inventory Transactions Created when an Invoice is Posted

  13. G/L Transactions Created for:Invoice Expense

  14. G/L Transactions Created for:Receipt Reversal

  15. G/L Transactions Created for:Invoice Adjustment

  16. Process for determining Historical Inventory Value

  17. Valuation “Gotcha” Any Script will need to accommodate for the fact that the MTP may NOT have generated the corresponding G/L entries yet

  18. Universal Time When Specifying a Period End Date/Time, it must be converted to Greenwich Mean Time.

  19. Step 1: Determine Current Value Total Value for all Inventory Classifications by Item, Storeroom, and Current G/L Account for that Storeroom and Timestamp effective Date/Time of valuation with current universal date/time.

  20. Step 2: Determine Ratio of which storeroom to credit with Receipt Reversals from Invoices Invoice Expense Transactions do not include storeroom designations, but storerooms can be derived from the receipt reversal side of the transaction.

  21. Step 3: Establish Effective Date Desired and create temp table to store transactions to “back out” The Effective Date/Time should include a Time at the end of the Day.

  22. Step 4: Add Invoice Expenses ‘In Range’ to back out transaction table • The Effective Date is Adjusted for Universal Time • Expense is prorated to Storerooms from previous step • If G/L Transactions are not created yet, the Item Storeroom G/L Account is used • Date of transaction is based on when the invoice was posted • Only Posted transactions for Inventory Items are included

  23. Step 5: Add Receipt Reversals ‘In Range’ to back out transaction table • The Effective Date is Adjusted for Universal Time • If G/L Transactions are not created yet, the Item Storeroom G/L Account is used • Date of transaction is based on when the invoice was posted • Only Posted transactions for Inventory Items are included

  24. Step 6: Add Invoice Adjustments ‘In Range’ to back out transaction table • The Effective Date is Adjusted for Universal Time • If G/L Transactions are not created yet, the Item Storeroom G/L Account is used • Date of transaction is based on when the invoice was posted • Only Posted transactions for Inventory Items are included • If two Inv. transactions are created for each adjustment, only the first is used

  25. Step 7: Add Receipts ‘In Range’ to back out transaction table • The Effective Date is Adjusted for Universal Time • If G/L Transactions are not created yet, the Item Storeroom G/L Account is used • Date of transaction is based on when the receipt transpired • Only Posted transactions for Inventory Items are included

  26. Step 8: Add Issues, Adjustments, Counts and Transfers ‘In Range’ to back out trans table

  27. Step 9: Summarize Back out Transactions into the Net Change to Qty and Value by Item, Storeroom, and Account • Values of the back out transactions are reversed and added to current value • Result is summarized by column desired to get prior period value • This process will show previous value for Accounts, Storerooms, and Items

  28. RNI – Same Type of Back out Process Only Three Transaction Types affect RNI

  29. Process for determining Historical Receipts Payable

  30. Step 1: Establish Effective Date Desired The Effective Date/Time should include a Time at the end of the Day

  31. Step 2: Total RNI Value by PO Delivery • Only include receipts/reversals that were posted prior to AS OF date • If Invoice not joined, Value is totaled in Received Column • If Invoice is joined, Value is totaled in Invoiced Column • Open Amt is total of all RNI activity for PO delivery (prior to AS OF)

  32. Step 3: Determine PO & Line combos that net to zero and remove them from results

  33. Step 4: Return Results

  34. Questions?

More Related