Home accounting software billing dynamics gp financial management

Mastering Dynamics GP: Combining Misc Charges with Periodic Items for Enhanced Billing

Updated: • 0 • min read
Table of Contents

Mastering Dynamics GP

Microsoft Dynamics GP is a powerful enterprise resource planning (ERP) system that allows businesses to manage their financial, operational, and manufacturing processes. A common challenge in manufacturing environments is accurately costing and billing for miscellaneous charges associated with periodic or standard cost items within a Bill of Materials (BOM). These “miscellaneous charges” can represent a variety of additional costs, such as special handling fees, unique service charges, or specific flat fees that need to be incorporated into the final product cost and reflected in billing.

This guide provides a comprehensive, step-by-step approach to effectively integrate miscellaneous charges with periodic items in Microsoft Dynamics GP Manufacturing. By following these detailed instructions, you can ensure that these charges are correctly processed and accounted for, leading to more accurate product costing, streamlined billing, and improved financial reporting. This methodology is particularly vital for maintaining data integrity and ensuring compliance with your internal costing standards.

Understanding the Challenge: Miscellaneous Charges and Standard Cost BOMs

In Dynamics GP, standard cost items are typically part of a “Periodic” valuation method, meaning their cost is fixed for a period and only updated through a formal revaluation process. The system is designed to handle components with established valuation methods within a Bill of Materials. However, directly adding a generic “miscellaneous charge” item, which often doesn’t track inventory costs in the same way, to a standard cost Bill of Materials can present a conflict.

The core issue stems from the differing ways these item types are managed by Dynamics GP’s inventory and costing modules. Miscellaneous charges are often transient or service-based, lacking a persistent inventory valuation. Standard cost BOMs, conversely, require all components to have a defined valuation method to ensure accurate cost rollup and revaluation. This guide addresses this discrepancy, enabling you to bridge the gap and fully integrate these charges.

Step 1: Updating the Valuation Method for Miscellaneous Charges Items via SQL

The initial hurdle to integrating a miscellaneous charges item into a periodic (standard cost) Bill of Materials is its inherent lack of a recognized valuation method that aligns with standard costing. By default, miscellaneous charge items may not track costs, which prevents their inclusion in manufacturing BOMs that rely on precise cost valuation. To overcome this, you must explicitly assign a compatible valuation method using a direct SQL database update. This process requires careful execution and an understanding of SQL queries.

Important Disclaimer: Directly modifying database tables via SQL carries inherent risks. Incorrect changes can lead to data corruption or system instability. Always ensure you have a comprehensive and functional backup of your Microsoft Dynamics GP database before proceeding with any SQL updates. If you are not comfortable with SQL operations, please consult with a Dynamics GP technical expert or your database administrator.

Understanding Valuation Method Values

The VCTNMTHD column in the IV00101 table (Item Master table) stores the valuation method for each item. The numerical values represent different inventory costing methodologies:

  • Null (or 0): Item does not track costs (typical for many service or miscellaneous charge items).
  • 1: FIFO Perpetual
  • 2: LIFO Perpetual
  • 3: Average Perpetual
  • 4: FIFO Periodic
  • 5: LIFO Periodic

For our purpose, to integrate the miscellaneous charges item with a periodic (standard cost) Bill of Materials, we must update its VCTNMTHD to ‘4’ (FIFO Periodic). While the name is “FIFO Periodic,” this method allows the item to be treated as a standard cost item within the manufacturing module for rollup and revaluation purposes. This is a critical step that tells Dynamics GP to treat the misc charge as a costed component within the manufacturing context.

Executing the SQL Update

The process involves using a SQL management tool to connect to your Dynamics GP database and execute specific update statements. The choice of tool depends on your SQL Server version.

Using SQL Server Management Studio (for SQL Server 2005 and later)

  1. Launch SQL Server Management Studio: From the Start menu, navigate to Programs, then Microsoft SQL Server [Your Version], and select SQL Server Management Studio.
  2. Connect to your SQL Server Instance: Enter your server details and credentials to connect.
  3. Navigate to Databases: In the Object Explorer pane on the left, expand Databases.
  4. Select Your Dynamics GP Database: Choose the specific Dynamics GP database (commonly named ‘DYNAMICS’ or your company database name).
  5. Open a New Query Window: Right-click on your Dynamics GP database and select New Query, or click the New Query button in the toolbar.

  6. Type and Execute the Select Statement: In the query pane, type the following SQL statement. Replace 'MISC CHARGES ITEM' with the actual item number of your miscellaneous charge item. This statement will help you verify the item and retrieve its DEX_ROW_ID.

    SELECT * FROM IV00101 WHERE ITEMNMBR='MISC CHARGES ITEM';

    After typing, click the Execute button (or press F5). Review the results to confirm you have the correct item and note its DEX_ROW_ID. The DEX_ROW_ID is a unique internal identifier for the row in the table, crucial for a precise update.

  7. Type and Execute the Update Statement: Once you have the DEX_ROW_ID from the previous step, type the following update statement. Replace '<XXX>' with the actual DEX_ROW_ID you noted.

    UPDATE IV00101 SET VCTNMTHD = '4' WHERE DEX_ROW_ID = '<XXX>';

    Execute this statement. This action will change the valuation method of your specified miscellaneous charge item to FIFO Periodic, making it compatible with standard cost BOMs.

Using SQL Query Analyzer (for SQL Server 2000 or 7.0)

  1. Launch SQL Query Analyzer: From the Start menu, navigate to Programs, then Microsoft SQL Server, and select Query Analyzer.
  2. Connect to your SQL Server Instance: Provide your server and login details.
  3. Select Your Dynamics GP Database: In the database dropdown menu, select your Dynamics GP database.

  4. Type and Execute the Statements: Enter the SELECT and UPDATE statements as described above into the Query pane. Execute the SELECT statement first to get the DEX_ROW_ID, then modify the UPDATE statement with the correct ID and execute it.

    -- First, execute this to find the DEX_ROW_ID for your item
SELECT * FROM IV00101 WHERE ITEMNMBR='MISC CHARGES ITEM';

-- Then, use the DEX_ROW_ID found above in this update statement
UPDATE IV00101 SET VCTNMTHD = '4' WHERE DEX_ROW_ID = '<XXX>';

Impact of the SQL Update

Upon successfully updating the VCTNMTHD to ‘4’, Dynamics GP will automatically create records in two important tables: ICIV0323 and CT00003. These tables are specifically designed to store data related to standard cost items, enabling the system to properly track, roll up, and revalue their costs. If you are importing items, it is crucial to verify that these standard cost tables are correctly populated to avoid costing discrepancies. The presence of these records signifies that the system now recognizes your miscellaneous charge item as a standard cost component.

mermaid graph TD A[Start SQL Management Tool] --> B{Select GP Database}; B --> C[Execute SELECT Statement for Misc Charge Item]; C --> D{Note DEX_ROW_ID}; D --> E[Execute UPDATE Statement with DEX_ROW_ID and VCTNMTHD = '4']; E --> F[Verification: Records created in ICIV0323 & CT00003]; F --> G[SQL Update Complete];

Step 2: Adding Cost to Your Miscellaneous Charges Item

Once the valuation method is correctly set, the next crucial step is to assign a cost to your miscellaneous charges item. It’s imperative that this cost is entered only into the Material cost bucket. Dynamics GP manufacturing costing differentiates between various cost components (Material, Labor, Overhead, Freight, etc.). For a miscellaneous charge acting as an additional expense, it should be categorized as a material cost within the manufacturing context to ensure it rolls up correctly into the final product’s standard cost.

There are two primary methods within Dynamics GP to add and manage standard costs for items. Both methods ultimately achieve the same goal, but offer different user interfaces and workflows.

Method 1: Using the Roll Up and Revalue Inventory Dialog Box

This method is comprehensive, allowing you to not only set costs but also to process the rollup and revaluation of inventory. This is particularly useful when dealing with multiple standard cost items or when a full costing cycle is required.

Sub-Step 2.1: Entering Revalued Costs

You have two primary options for inputting the revalued costs, depending on how your item’s replenishment method is set up:

Option A: Using the Standard Item Material Costs Dialog Box

This option is ideal for items where the Replenishment Method field is set to Buy, indicating that the item is acquired externally rather than manufactured internally.

  1. Navigate to Standard Item Material Costs: In Dynamics GP, select Cards, point to Manufacturing, then point to Inventory, and finally select Std Item Mat Costs.
  2. Enter Pending Costs: In the Standard Item Material Costs dialog box, locate your miscellaneous charges item. In the Pending area, enter the desired cost for the item. This area acts as a staging ground for proposed cost changes before they are formally applied.
  3. Save Changes: Select Save to temporarily store these pending costs.
Option B: Using the Standard Cost Maintenance Dialog Box

This dialog box provides more control and is suitable for items with a Replenishment Method set to Buy or Make. It offers an override feature for direct cost input.

  1. Navigate to Standard Cost Maintenance: Select Cards, point to Manufacturing, point to Inventory, and then select Std Cost Maintenance.
  2. Select Your Item: Choose your miscellaneous charges item from the lookup.
  3. Enable Override: Select the Override check box. This action allows you to manually input or adjust the standard cost values directly.
  4. Enter Revalued Costs: In the Override Standard area, enter the new cost for the item. Again, ensure this cost is allocated to the Material bucket.
  5. Save Changes: Select Save to record your cost input.

Sub-Step 2.2: Processing Rollup and Revaluation

After entering the pending or overridden costs, the next critical step is to execute the “Rollup and Revalue” process. This action updates the actual standard costs in the system and reflects these changes across your inventory.

  1. Access Rollup and Revalue: Go to Tools, point to Routines, point to Manufacturing, and then select Rollup and Revalue.
  2. Set Roll Up Date: In the Rollup and Revalue Inventory dialog box, you can either accept the default current date or type a specific date in the Roll up Date field. This date determines when the cost rollup takes effect.
  3. Perform Rollup: Select Roll up, and then select Process. This step calculates the new standard costs for all items based on their components (including your newly costed miscellaneous charge item) and the pending cost changes.
  4. Confirm Rollup Completion: You will receive a message stating “Rollup Complete.” Select OK to acknowledge.
  5. Review Item Cost Revaluation Report: When prompted to print the Item Cost Revaluation Report, select your preferred print destination (e.g., screen, printer, file) and select OK. It is critical to thoroughly review this report to ensure that the costs for your miscellaneous charge item, and any affected assemblies, are accurately reflected. Discrepancies at this stage can lead to incorrect inventory valuation.
  6. Perform Revaluation: After reviewing the report, select Revalue, and then select Process. This step posts the pending standard cost changes as actual current costs in your inventory records, impacting your general ledger and costing reports.
  7. Confirm Revaluation Batch Creation: A message will appear: “A standard cost revaluation batch was created. Standard unit cost changes will take effect.” Select OK. This confirms that the new standard costs have been applied.

mermaid graph TD A[Start: Add Cost to Misc Charge Item] --> B{Method 1: Roll Up & Revalue?}; B -- Yes --> C{Enter Costs: Std Item Mat Costs OR Std Cost Maintenance}; C --> D[Save Cost Entries]; D --> E[Navigate to Rollup & Revalue]; E --> F[Set Roll Up Date]; F --> G[Select Roll up & Process]; G --> H[Confirm Rollup Complete]; H --> I[Review Item Cost Revaluation Report]; I --> J[Select Revalue & Process]; J --> K[Confirm Revaluation Batch Created]; K --> L[Method 1 Complete];

Method 2: Using the Standard Cost Changes Dialog Box

This method provides a more direct and streamlined approach for managing standard cost changes, particularly useful for individual item adjustments or quick updates.

  1. Navigate to Standard Cost Changes: Select Cards, point to Manufacturing, point to Inventory, and then select Standard Cost Changes.
  2. Enter Proposed Costs: In the Standard Cost Changes dialog box, locate your miscellaneous charges item. In the Proposed Standard Cost column, directly enter the desired cost for the item. Remember to allocate this cost to the Material bucket.
  3. Save Changes: Select Save to commit the proposed cost changes within this window.
  4. Initiate Roll Up: Select Roll Up from within the Standard Cost Changes dialog box. This action processes the cost changes you’ve entered.
  5. Confirm Save Changes: When prompted to save changes, select Save.
  6. Confirm Rollup Complete: You will receive the “Rollup Complete” message. Select OK.
  7. Review Item Cost Revaluation Report: Similar to Method 1, you will be prompted to print the Item Cost Revaluation Report. Select your print destination and select OK. Verify that the costs are accurate on the report.

Both methods achieve the same outcome: successfully assigning a standard cost to your miscellaneous charges item within the material cost bucket. The choice between Method 1 and Method 2 often comes down to personal preference or the scale of cost changes being managed.

Final Verification and Ongoing Maintenance

After completing either Method 1 or Method 2 for adding cost, a critical final verification step is necessary to ensure the changes have been applied correctly.

Verifying Current Cost

  1. Access Item Maintenance: Navigate to the Item Maintenance window in Dynamics GP.
  2. Locate Your Item: Select your miscellaneous charges item.
  3. Check Current Cost: Observe the Current Cost field for this item. It is paramount that this Current Cost value now matches the new Standard Cost you just applied. If there’s a discrepancy, it indicates that the revaluation process may not have completed successfully or that further investigation is needed.

Ongoing Cost Management

The process outlined above establishes the initial standard cost for your miscellaneous charges item. However, in a dynamic business environment, costs can change over time.

  • Future Cost Changes: In the future, if you need to adjust the standard cost of this miscellaneous charge item, you must repeat the steps outlined in “Step 2: Adding Cost to Your Miscellaneous Charges Item.”
  • Synchronization: It is crucial to remember that whenever the Standard Cost is changed and revalued, the Current Cost in the Item Maintenance window must be updated to equal the new Standard Cost. This ensures that all inventory transactions, costing reports, and financial statements accurately reflect the most current valuation. Failure to synchronize these costs can lead to significant discrepancies in your inventory and cost of goods sold.

Maintaining accurate standard costs, especially for items like miscellaneous charges that can significantly impact total product cost, is vital for precise financial reporting, effective pricing strategies, and informed decision-making within your manufacturing operations.

We hope this comprehensive guide assists you in effectively managing miscellaneous charges within your Microsoft Dynamics GP Manufacturing environment. Accurate costing is the backbone of sound financial management.

Do you have any experiences integrating unique charges into your manufacturing costs? What challenges have you encountered, and how did you overcome them? Share your insights and questions in the comments below!

Post a Comment