Detailed Process for Migration to Weighted-Average Cost

Body

Overview

Note: To print a copy of this article, right-click and select Print. In the Destination drop-down list, select a specific printer name or select Save as PDF. Then, click Save.

Note: Click the IQA IM Calculation Template file link to download and open the spreadsheet. 

The high-level process and responsibilities for this migration is as follows:

  1. Ensure Inventory is reconciled and balanced (Customer)
  2. Ensure processes are ready to migrate (Customer)
  3. Capture existing data (Customer)
  4. Create upload files. (Customer) 
    1. Upload files to be created  from Template include:
      1. Outbound Inventory Movements (OIM) using templates to remove all existing inventories prior to switching over to weighted-average
      2. Inbound Inventory Movements (IIM) using templates to re-adding the inventories after switching over to weighted-average 
      3. OIM & IIM required to balance IQA
      4. Update IQA records to be explicitly set to the default Ledger.
    2. Additional Files that may be needed:
      1. Update Products that are Type = “Raw Material” to “Purchased”
      2. New IQA records required for non-default ledgers
      3. Purchase Prices.

  5. Remove inventories by importing OIM transactions to the OIM Object. (Customer or Accounting Seed (AS) Support)
  6. Turn on Weighted-Average Cost methodology. Support will have to turn this on and once turned on, cannot be changed back to Standard Cost.   (AS Support only)
  7. Make updates required prior to re-adding inventories into the IIM Object. (Customer or AS Support)
  8. Add inventories by importing the IIM transactions to the IIM Object. (Customer or AS Support)
  9. Run reports to verify that the new inventory balances to the Pre-migration Inventory. (Customer)
  10. Load purchase prices to Products. (Customer or AS Support)
  11. Create a new Inventory Cost Tab. (Customer)
  12. Create a new Purchase Price Tab. (Customer)
  13. Update Page Layouts for new Weighted-Average Cost methodology. (Customer)
  14. Provide AS Support Access to your Org ID. (Customer)

Important: AS Support must perform step 6. AS Support can perform the data imports (Steps 5,7,8 & 10) if requested. Open in a Case specifying which steps you would like AS Support to complete. Identify the Org ID, and give AS Support system admin access to the Org ID (instructions are at the end of this article). If you are requesting AS Support to perform any of the data import steps, attach the files to the case, and then, confirm the steps that you have completed.

Return to top

Contents

  1. Reconcile the Physical Inventory to your Inventory Valuation on the General Ledger

  2. Verify that Purchase Orders, Sales Orders and if using Orders & Inventory Manufacturing/Builds are all Either Fully Closed or Completely Open

  3. Set up and Run Reports to Ensure all Existing Quantities and Details are Documented Prior to Switching over from Standard Cost (These can also be used to load the OIM and IIM templates)

  4. Create Outbound Inventory Movements (OIM) and Inbound Inventory Movements (IIM) using the IQA IM Calculation Template to remove all existing inventories prior to switching over to weighted-average, to Balance the inventory movements for each IQA prior to switching over to weighted-average, and after switching over to weighted-average to update existing IQA with the default Ledger and re-add the inventories

  5. Remove Inventories by Importing OIM Transactions to the OIM Object

  6. Turn on Weighted-Average Cost Methodology

  7. Make Updates Required prior to Re-adding Inventories into the IIM Object

  8. Re-add Inventories by Importing the Step 12 IIM Transactions to the IIM Object

  9. Run Reports to Verify that the New Inventory Balances to the Pre-migration Inventory

  10. Load Purchase Prices to Products

  11. Create a New Inventory Cost Tab

  12. Create a New Purchase Price Tab

  13. Update Page Layouts for the New Weighted-Average Cost Methodology

  14. Grant AS Support Temporary System Administrator Access to your Salesforce Org

Detailed Process for Weighted-Average Cost Migration

1. Reconcile the Physical Inventory to your Inventory Valuation on the General Ledger

Perform this process to determine your correct inventory quantity and valuation. That way, you will have an expected result when completed. The end result is to have the total physical inventory numbers match precisely with the inventory valuation on the General Ledger.

As a part of this process, complete the following:

  1. Perform cycle counts by:
      1. Product
      2. Warehouse
      3. Location
      4. Ledger.

    Reconcile the Inventory Balance. For more information, refer to the Report on and Reconcile Inventory article.
  2. Determine the average cost for each product.
    Note: Since the inventory cost variance has already been accounted for, the Weighted-Average Cost for each product should equal the Standard Cost for each product.
  3. If Multi-ledger is currently enabled, all inventory is on one (1) ledger. Hibiscus adds Ledger to the Inventory dimension so you can now assign inventory to a specific ledger when it is reloaded. Keep this in mind as you go through the reconciliation process.

2. Verify that Purchase Orders, Sales Orders and if using Orders & Inventory Manufacturing/Builds are all Either Fully Closed or Completely Open

  1. Purchase Orders must be either:
    1. Fully received and have the payable generated and posted
      -OR-
    2. Completely unreceived, with no payable generated.
      Note: A simple check is that Voucher Payable should be $0 when completed.
  1. Sales Orders must be either:
    1. Fully allocated and have the billing generated and posted
      -OR-
    2. Be completely unallocated, with no related billings.
      Note: A simple check is that Voucher Payable should be $0 when completed.
  1. If you use Accounting Seed’s Manufacturing Build process, everything must be fully allocated and built.
    Important: There can be NO partially built orders.

The Manufacturing Build will be transitioning to a new process. For more information, refer to the Manufactured Product Setup and the Build Manufactured Product article.

Return to top

3. Set up and Run Reports to Ensure all Existing Quantities and Details are Documented Prior to Switching over from Standard Cost (These can also be used to load the OIM and IIM templates)

  1. The following reports are recommended so that all of the data elements are captured and can be exported for use in the templates
    1. An Inventory Quantity Available (IQA), Cost & Total Value
      1. An IQA Detail Report
      2. An IQA by Warehouse, Location & Product
      3. If you have any serialized inventory products, a Serial Number report
      4. Trial Balance (make sure the “Suppress Zero Amount Rows” box is not checked. The Trial Balance should be run before the OIM, after the OIM is complete and after the IIM is complete to ensure the GL Accounts are correct.
  1. The following are examples of the above recommended reports. Your reports can be customized for your specific situation.
    1. An Inventory Quantity Available (IQA), Cost & Total Value
        1. Report Setup

          WAC01.png
          Note: Click here to view the above image in full screen. 

        2. Report Output

          WAC02.png

    2. An IQA Detail Report
        1. Report Setup

          WAC03.png
          Note: Click here to view the above image in full screen. 

        2. Report Output

          WAC04.png
          Note: Click here to view the above image in full screen. 

    3. An IQA by Warehouse, Location & Product
        1. Report Setup

          WAC05.png
          Note: Click here to view the above image in full screen. 

        2. Report Output

          WAC06.png
          Note: Click here to view the above image in full screen. 

    4. If you have any serialized inventory products, a Serial Number report
        1. Report Setup

          WAC07.png
          Note: Click here to view the above image in full screen. 

        2. Report Output

          WAC08.png
          Note: Click here to view the above image in full screen. 

Return to top

  1. Purchase Price is a new way to store default prices for a product and will default to the Purchase Order Line as Unit Price. It is recommended that you upload Purchase Prices for all of your Products. In addition to the Product and Unit Price, you can also include these by Vendor and Currency.
      1. Set up and run a report with the Product w Unit Cost & Default Vendor for the Purchase Price default Products.
      2. You could also set up a report to pull actual purchases and the unit cost by vendor to get default Purchase Prices.
      3. The following are examples of the above recommended reports. Your reports can be customized for your specific situation.
          1. Product w Unit Cost & Default Vendor -- Report Setup

            WAC09.png

          2. Report Output

            WAC010.png

          3. Actual Purchases and Cost by Vendor -- Report Setup

            WAC011.png

          4. Report Output

            WAC012.png

Return to top

4. Create Outbound Inventory Movements (OIM) and Inbound Inventory Movements (IIM) using the IQA IM Calculation Template to remove all existing inventories prior to switching over to weighted-average, to Balance the inventory movements for each IQA prior to switching over to weighted-average, and after switching over to weighted-average to update existing IQA with the default Ledger and re-add the inventories

  1. Create a Migration Clearing GL Account to keep the GL transactions isolated. For example, 1599-Migration Inventory.
    Note: The Movement Date and Serial Number need to be TEXT format. The Movement date should be MM/DD/YYYY.
    Note: The SF ID should be used for the IQA (Inventory Quantity Available), the Debit & Credit GL Accounts and the Ledger
  2. Export IQAs using Dataloader/
      1. Make Inbound and Outbound Accounting Inventory Movements visible.
          1. Click Setup and access the Inventory Quantity Available object.

            WAC013.png

          2. Locate and select Inbound Accounting Quantity.

            WAC014.png

          3. Click Set Field-Level Security. 

            WAC015.png

          4. Click the Visible checkbox to make all fields visible. 

            WAC016.png

          5. Click Save. 
          6. Locate the Outbound Accounting Quantity and repeat the steps above to make it visible. 
      2. Using Salesforce Dataloader, export the Inventory Quantity Available, selecting all fields.
          1. Open Salesforce Dataloader and select export.

            WAC017.png

          2. Log in and then, click Next.

            WAC018.png

          3. Select Inventory Quantity Available object from list. Select the filename and location. Then, click Next.

            WAC019.png

          4. Select all fields and click Finish. Then, click YES when prompted. 

            WAC020.png

      3. Export the following objects: Products, GL Accounts, Warehouse, and Location.

Return to top

  1. Update the IQA IM Calculation Template with the IQA Export.
      1. Use a spreadsheet application, such as Microsoft Excel to open the exported file. 
      2. Use the provided Excel template, Update the Calculation Template with the data from the IQA Export file. (Cut & Paste)

        WAC021.png
        Note: Click here to view the above image in full screen. 

      3. The the number of IQAs extends beyond the last row of the template (row 501), you will need to copy and paste the formulas in the steps columns (Q-AC) down to the last IQA.
      4. The unit cost on the Calculations template should be the unit cost found on the product.
      5. On the Constants tab, put in the Name and SF ID for the Default ledger, Inventory GL Account, the Migration GL Account and the Movement Date.
      6. Explanation: The point of this template is to calculate and create the inventory movements needed to ensure that the Weighted-Average Cost will be calculated correctly. (With Weighted-Average Cost, the underlying quantity available formula was changed, removing non-accounting IMs and the manufacturing IMs that are not used once Weighted-Average Cost is implemented.)
          1. Step 1 will be used to create the OIM to migrate out the current inventory Quantity.
          2. Steps 2, 4, 8, 9 & 10 are used to create the IIMs & OIMs needed to balance the IQAs.
          3. Steps 11 & 12 are used to create the IQA Ledger update and the OIMs needed to reload the inventory onto the IQAs and create the initial weighted average cost records.
      7. If the IQAs in iii) was > 500, copy the rows in tabs Step 1 through Step 12 down so that all IQA are included.
      8. Save the file.
      9. Copy the tabs Step 1 through Step 12 to a new file and save it as a CSV file.
          1.  On each tab, Filter the quantity column so that Quantity=0 is excluded (on the filter drop-down, uncheck the box for 0).
          2. Select all visible rows (1 through last visible).
          3. Copy selection.
          4. Create new Excel file.
          5. Perform a PASTE VALUES & SOURCE FORMATTING. (This is the same as doing a PASTE VALUES and the a PASTE FORMATTING) into new Excel file.
          6. Perform SAVE AS on the new file.
            Note: We recommend using the Step # for the filename.
            Note: File format should be CSV UTF-8 (.csv) or Comma delimited file (.csv).

  2. Other possible updates & Templates.
      1. With Weighted-Average Cost, the Product type of “Raw Material” has been disabled. All Products that are type = “Raw Material” must be changed to “Purchased” AFTER Weighted-Average Cost is turned on and BEFORE any IIM are imported to reload their IQA. This can be done manually or with an import file.
      2. As noted above, if you are using Multi-ledger you must put the Ledger on the Inventory Movement. Currently, the IQA records do not have a Ledger explicitly assigned to them. All of them though will be implicitly set to the default ledger when Weighted-Average Cost is activated. These cannot be changed to another ledger though. Step 11 will explicitly set all of the current, existing IQA records be to the default Ledger.
        Note: If any Products were changed from Type = “Raw Material” to “Purchased”, it is recommended that the IQA records be relinked to the product records to avoid possible cross-reference errors. This can be easily accomplished by adding the Product onto the the same IQA upload file that you will be using to add the Ledger to the IQA record.
          1. Below is an example of a combined Ledger – Product IQA upload.

            WAC022.png

      3.  If you are using Multi-ledger and have Inventory on any Ledger other than the default Ledger, NEW IQA records MUST be created after Weighted-Average Cost is turned on, but BEFORE any IIM are processed to the other Ledgers. The new IQAs can be uploaded, but must be assigned explicitly to the other ledgers (do not leave the ledger fields blank). Again, it is highly recommended to use the SF IDs to avoid errors.
          1. Below is an example of a New IQA upload.

            WAC023.png
            Note: Click here to view the above image in full screen. 

      4. It is STRONGLY recommended that a new case be opened requesting that the completed IQA IM Calculation Template be reviewed prior to importing the files. Attach the IQA export and the completed IQA IM Calculation Template.

Return to top

5. Remove Inventories by Importing OIM Transactions to the OIM Object

Once reviewed, steps 1 through 10 can be imported in order

  1. IIMs are Inbound Inventory Movements
  2. OIMs are Outbound Inventory Movements
      1. Below is an example of importing OIM transactions to the OIM Object.
        Note: If using Salesforce Data Loader, the import would be an Insert, and the mapping should look like the example screen below. 

        WAC024.png

        Note: If using the Import Data Wizard, then it should look like the example screen below.

        WAC025.png
        Note: Click here to view the above image in full screen. 

        WAC026.png
        Note: Click here to view the above image in full screen. 

        Note: If you have serialized products and plan to use the Salesforce DataLoader, make sure that the serial number is formatted as TEXT.

  3. After Steps 1 - 10 have been imported successfully, re-export the IQAs. Attach the new IQA to the Case to confirm that the inventory is ready to convert.

    Important: Weighted-average will NOT be turned on with this final IQA export being provided.

    WAC027.png

Return to top

6. Turn on Weighted-Average Cost Methodology 

Important: Remember that once turned on, Weighted-Average Cost cannot be changed back to Standard Cost.

WAC028.png

7. Make Updates Required prior to Re-adding Inventories into the IIM Object

  1. Update all Products that are Type = “Raw Material” to “Purchased.”
  2. Update all IQA records to be explicitly set to the default Ledger (and relink Products if needed) via Step 11 import.
  3. Load all new IQA records required for non-default ledgers.

8. Re-add Inventories by Importing the Step 12 IIM Transactions to the IIM Object

  1. If using Salesforce DataLoader, the IIM mapping should look like the example screen below.

    WAC029.png

  2. If using the Import Data Wizard, it should look like the example screen below. 

    WAC046.png
    Note: Click here to view the above image in full screen. 

    WAC047.png
    Note: Click here to view the above image in full screen. 

Notes: 

  • If you are using Multi-ledger you must put the Ledger on the inventory record to indicate which Ledger the Inventory Costs are on.
  • The Movement Date and Serial Number need to be TEXT format. The Movement date should be MM/DD/YYYY.
  • The SF ID should be used for the IQA (Inventory Quantity Available), the Debit & Credit GL Accounts, the Ledger, and Product.

Return to top

9. Run Reports to Verify that the New Inventory Balances to the Pre-migration Inventory

Important: These must balance before proceeding.  Open a Case, if AS Support is needed to help with this investigation.

  1. Run Trial Balance to verify that the Inventory amounts are correct.
  2. Create and run an Inventory Costs report.
      1. The Weighted-Average Cost is captured on the Inventory Cost Record. The best way to see all of the Inventory Cost Records is to set up a report.
      2. Below is an example of an Inventory Cost report -- Report Setup

        WAC032.png
        Note: Click here to view the above image in full screen. 

      3. Report  

        WAC033.png

  3. Create and run a Product IQA, Inventory Costs report.
    1. Below is an example of Product IQA, Inventory Costs report -- Report Setup

      WAC034.png
      Note: Click here to view the above image in full screen. 

    2. Report

      WAC035.png
      Note: Click here to view the above image in full screen. 

10. Load Purchase Prices to Products

  1. As noted above, Purchase Price is a new way to store default prices for a product and will default to the Purchase Order Line as Unit Price. It is recommended that you upload Purchase Prices for all of your Products. In addition to the Product and Unit Price, you can also include these by Vendor and Currency.
      1. The Product w Unit Cost & Default Vendor report can be used to upload default Purchase Prices for the Products.
        Note: Kits and Manufactured Products should not have Purchase Prices.
      2. Below is an example of a default Purchase Prices upload file.

        WAC036.png

Return to top

11. Create a New Inventory Cost Tab

  1. An easy way to always have access to view the Inventory Cost is to create a new Tab. Following are the steps.
      1. Click Setup and enter Tabs in the Quick Find search textbox. Click Tabs.

        WAC037.png

      2. Click New to create the new Inventory Cost tab.

        WAC038.png

      3. Select the Inventory Cost Object and select a Tab Style.

        WAC039.png

      4. Select the user profiles to make visible to the New Inventory Cost tab. The example below defaults to all profiles. Click Next at the bottom of the screen.

        WAC040.png

      5. Select the custom apps for making the new tab available. The sample screen below selects all of the apps. Click Save.

        WAC041.png

      6. The new tab is now available. Click the App Launcher icon and select Inventory Costs.

        WAC042.png

      7. The Inventory List View displays. Change the pinned view to All. To add visible fields, click Setup (the Gear icon in the top-right menu) and select Select Fields to Display from the drop-down list.

        WAC043.png

      8. Select all of the fields that you want to see. Then, click Save.
        Note: We recommend Product and Unit Cost. (Unit Cost is the current Weighted-Average Cost.)
        Note: If you are using multi-ledger and/or multi-currency, include the Ledger and Currency fields.

        WAC044.png

      9. All of the fields selected are now visible on the list view.

Return to top

12. Create a New Purchase Price Tab

Repeat the steps above to create a Purchase Price tab.

13. Update Page Layouts for the New Weighted-Average Cost Methodology

  1. After migrating to Weighted-Average Cost, the page layouts for a number of objects will need to be updated. For more information, refer to the Edit Page Layout articles in the Installation section of the Knowledge Base. 
  2. Product Object
      1. Remove field - Manufacturing Order Template
      2. Remove field – Unit Cost
      3. Remove button – Create Manufacturing Order
      4. Remove Related List – Materials
      5. ADD Related List – Inventory Costs
          1. Configure the Related List by clicking the Wrench icon. Add the desired fields and sort order. Click OK to save. Below is an example.

            WAC045.png

      6. ADD Related List – Inventory Quantity Available
          1. Configure the Related List by clicking the Wrench icon. Add the desired fields and sort order. Click OK to save.
      7. ADD Related List – Purchase Prices
          1. Configure the Related List by clicking the Wrench icon. Add the desired fields and sort order. Click OK to save.
      8. Click Save.
  3. Sales Order Object
      1. Go to Search Layouts and click the drop-down list (far right), then click Edit.
      2. In the Custom button section:
          1. Remove Create Manufacturing Order from Selected buttons
          2. ADD Create Purchase Orders to Selected buttons
      3. Click Save. 
  4. Purchase Order Object
      1. Add field – Initial Receive Date
      2. Add field – Ledger
      3. Click Save.
  5. Project Object
      1. Check which page layout is assigned. Change assignment to Project Layout. If there has been extensive customization to the Manufacturing Order Layout, it can remain assigned, but the following changes should be made.
          1. Remove Allocate & Build Custom buttons
          2. Remove field – Manufactured product
          3. Remove Related List – Materials
      2. Click Save.
  6. Inventory Quantity Available Object
      1. Add field – Ledger
      2. Manufacturing Quantity field is no longer used and can be removed.
      3. Manufacturing Inventory Movements is no longer used and the related list can be removed.
      4. Click Save.
  7. All Inventory Movement Objects (Inbound IM, Outbound IM, Purchase Order IM & Sales order IM)
      1. Add field – Ledger
      2. Add fields – Warehouse & Locations if not already on the layout
      3. ADD Related List – Transactions
        Note: This should already be configured, but it can be edited like the ones above.
      4. Click Save.

14. Grant AS Support Temporary System Administrator Access to your Salesforce Org

To grant AS Support temporary access at the System Administrator level to your Salesforce Organization (Org), perform the following:

  1. Grant Access to Accounting Seed Support
  2. Locate Your Org ID
  3. Salesforce Classic UI
      1. From your name on the top navigation bar:
          1. Select My Settings
          2. Expand the Personal menu item
          3. Select Grant Account Login Access
          4. Populate the Accounting Seed Support Access Duration picklist with the appropriate duration.
            Note: We recommend one week.
  4.  Salesforce Lightning UI:
      1. Click the View Profile icon
      2. Click Settings
      3. Click Grant Account Login Access under Personal Information
      4. Populate the Accounting Seed Support Access Duration picklist with the appropriate duration.
        Note: We recommend one week.

Return to top

Title
Detailed Process for Migration to Weighted-Average Cost
URL Name
1111722934
Orders & Inventory (Average Cost)Inventory Setup (Average Cost)
Skip Feed

Nothing here yet?

Log in to post to this feed.

End of Feed
Nothing here yet?Log in to post to this feed.