Skip to main content

Notifications

Announcements

Community blog subscriptions are now enabled!
News and Announcements icon
Dynamics 365 Community / Forums / Business Central forum / Inventory valuation by...
Business Central forum
Suggested answer

Inventory valuation by dimension

(1) ShareShare
ReportReport
Posted on by jasontl 271
Hi all,
 
I have not really implemented for client to generate inventory related reports by financial dimensions.
What we typically do is to generate inventory valuation report by:
  • location
  • item
  • any field property on item table
However, let's assume in this context is not related to dimensions that are appended to item but rather location or transactional dimensions.
If business situation is not practical for users to split tracking by location and is planning to use transactional dimensions (eg, sales transaction 123 tagged to dimension A, sales transaction 124 tagged to dimension C and purchase transaction 111 tagged to dimension A).
 
Assuming costing method is average by item & location & variant, whatever adjustments posted to value entry and G/L entry table may not be accurate?
I also don't see any standard reports to query inventory by dimension (remember this is not dimension at item table but at transactional).
 
Item A - sales dimension A - purchase dimension B
Item B - sales dimension B - purchase dimension C
 
Can anyone who have implemented before share their experience please?
 
Thanks in advance
Categories:
Inventory and warehouse management
Financial Management
  • jasontl Profile Picture
    jasontl 271 on at
    Inventory valuation by dimension
    Hi Valetin Castravet,
     
    Thanks for the second opinion.
     
    This client is not in a typical inventory trading industry hence transfer order is not really practical to be used here. We had some enhancement done to populate into an item reclass journal instead.
    But the adjustment I was mentioning here are positive and negative item journal, not reclass journal fyi.
  • Suggested answer
    Valentin Castravet Profile Picture
    Valentin Castravet 6,225 Super User on at
    Inventory valuation by dimension
    There are several things you can do in this case to help the operations team but using a dimension in lieu of a location code is not one of them. You are going to run into many problems, one of them being the one below in your analogy. 
     
    For instance, why doesnt the operations team use transfer orders rather than the item journal? In a transfer order they dont have to input the unit cost. 
     
  • jasontl Profile Picture
    jasontl 271 on at
    Inventory valuation by dimension
    Hi Valentin cast,
    To be honest, it doesn't pass my opinion hence I made a post here to try get more opinion.
    Currently, they are using separate location. Their operation team is "struggling" to perform inventory transfer between locations on timely basis thus resulting in month end item journal where users again have done positive adjustment but incorrectly at 0 cost resulting in abnormal average cost.
    To me, I know these are user mistakes and process but I have been repeating and staying firm to what would be best practice.
    Unfortunately, the management hopes to find a light in more balanced solution that can reduce item journal adjustment, relief operation team's data entry on adjustment but at the same time still obtain financial inventory value by "location" (without usage of actual location but dimension).
     
    Hi Jun Wang,
    Thanks and I have thought deeper than that. Please see my analogy in below after reply to Tech and see whether this makes sense to you too.
     
    Hi Tech BC Gokul,
    Thanks for your input. Besides global dimension, I know of many methods to build a report base from value entry table using dimension but that is not my point. My point is as per below.
     
    >>Analogy<<
    1. If item has a default dimension (eg, item attribute, category dimension, product group) and it is a one-to-one relationship, I have no problem with analyzing using dimension but the question is about the logical process flow and whether that makes sense in this context.
    2. If location dimension is to be introduced, then one item will have many-to-one relationship in value entry table. I give an example below
    <location dimension A> <item A> <+5 qty> <purchase> <location generic>
    <location dimension B> <item A> <+3 qty> <purchase> <location generic>
    so combined qty in location generic for item A is now +8 qty
    if the physical flow is -5 qty from <location dimension B> and user creates a Sales Order or Transfer Order
    system does not block (which ease the operation) and user can conveniently reduce qty from 8 to remaining 3.
    question is you resulted in below entry
    <location dimension B> <item A> <+5 qty> <sales/transfer> <location generic>
    when you run ACIE batch job, how will inventory value the average costing properly by "location" in this context?
    you probably end up in a -2 qty for <location dimension B> with a negative cost?
  • Tech_BC_Gokul Profile Picture
    Tech_BC_Gokul 247 on at
    Inventory valuation by dimension
    Hi 
     
    Adding filter for global dimensions will be available in standard system in item card you can select the flow filters 
     
    but if you want to analysis on other dimensions create a new inventory valuation report and pass the flow filters for other dimensions into the value entry variable in the report to handle the flow filter values
     
    this will give you expected result but the solution is customisable 
     
    thanks 
    gokulnath
  • Suggested answer
    Jun Wang Profile Picture
    Jun Wang 5,231 Super User on at
    Inventory valuation by dimension
    use dimension to represent location doesn't sound like a good idea. BC has many features for locations and at lease one default location needs to set up. 
    Jun Wang
  • Suggested answer
    Valentin Castravet Profile Picture
    Valentin Castravet 6,225 Super User on at
    Inventory valuation by dimension
    Question is client wants to replace usage of location code by dimension 1 (which happens to be "location" in a way). 
    Is there a reason they want to do this? Why not just keep using the location code? 
     
  • jasontl Profile Picture
    jasontl 271 on at
    Inventory valuation by dimension
    Thanks Tarik for your sharing.
     
    Perhaps let me re-phrase slightly to my question.
    I understand that inventory valuation can be filtered using global dim 1 and 2 or even we can query the value entry table filtered by dimensions.
     
    Question is client wants to replace usage of location code by dimension 1 (which happens to be "location" in a way). 
    1.  How practical is this solution going to be? as I have never tried doing inventory valuation by dimension (location dimension)
    2.  There is going to be alot of item reclass and/or transfer order taking place - how will "location dimension" actually affect the standard Adjust Cost - Item Entries routine?
    3.  Client is using average costing and average by item & location & variant in inventory setup at the moment
  • Suggested answer
    Tarik Jerkovic Profile Picture
    Tarik Jerkovic 48 on at
    Inventory valuation by dimension
    Hi Jason,
     
    If I understand well here is native solution - native reports.
     
    First you can use filter for global dimensions 1 and 2 
     
    Report parameters Filter total by resolve your question regarding dimensions - its important to block any purchase and sales if user didnt put vaule of dimensions
     
     
    I think all reports regarding inventopry have dimension by default 
     
     

    Native Reports and Dimensions in Dynamics 365 BC

    1. Global Dimensions 1 and 2:

      • Dynamics 365 BC allows you to set up two global dimensions that can be used across the system for filtering and reporting. These dimensions are commonly used for high-level categories like department or project.
      • When running reports, you can set filters based on these global dimensions to get the specific data you need.

    2. Report Parameters and Filtering:

      • In the report parameters, you can filter totals by dimensions. This feature is crucial for ensuring that users input values for dimensions when required.
      • This helps maintain the integrity of your data, ensuring that all transactions are properly categorized according to the defined dimensions.

    3. Blocking Transactions Without Dimension Values:

      • It's important to set up validation rules to block any purchase or sales transactions if users do not input values for the necessary dimensions.
      • This ensures that all financial and operational data are accurately categorized, which is vital for accurate reporting and analysis.

    4. Inventory Reports:

      • Most inventory-related reports in Dynamics 365 BC come with dimension filtering capabilities by default.
      • This means you can easily analyze inventory data based on different dimensions, providing deeper insights into inventory management and performance.

    Implementation Tips:

    • Configuration:

      • Ensure that global dimensions are properly configured in the General Ledger Setup.
      • Define any additional shortcut dimensions if more than two dimensions are needed for detailed analysis.

    • User Training:

      • Train users on the importance of entering dimension values during data entry.
      • Demonstrate how to use filters in reports to view data segmented by dimensions.

    • Validation Rules:

      • Implement validation rules to enforce the entry of dimension values for key transactions like purchases and sales.

    By leveraging these native capabilities, you can enhance the accuracy and depth of your financial and operational reporting in Dynamics 365 Business Central.

Helpful resources

News and Announcements

Community blog subscriptions are now enabled!

Quick Links

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Blog subscriptions now enabled!

Follow your favorite blogs

TechTalk: How Dataverse and Microsoft Fabric powers ...

Explore the latest advancements in data export and integration within ...

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 284,753 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,302 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,146

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans