In This Article

    Share

    Dynamics 365 for Finance and Operations Integration with Excel: How to More Efficiently Process Cycle Counts

    I had a client come up to me the other day and ask how Dynamics 365 for Finance and Operations can handle cycle counts. In their current system, the process was:

    1. The Inventory Control Accountant would run a report on their on-hand inventory and print it out.
    2. The paperwork would be distributed to multiple people in the warehouse who would provide handwritten changes.
    3. The returned documents would then be used to create inventory adjustments for every line item with a discrepancy.

    Sounds pretty tedious doesn’t it? That’s exactly why they were excited to hear about how Dynamics 365 for Finance and Operations would be able to handle this process, reduce unnecessary paperwork, and more importantly, prevent users from hand-keying adjustments.

    The key to this process involves Dynamics 365 for Finance and Operations integration with Excel. Throughout many of the forms in Dynamics 365, there is the capability to open the form in Excel to be revised and published right back. It is this feature that will be so helpful to the client because it will allow the cycle counts to be distributed and posted after a simple import.

    Exporting Data from Dynamics 365 for Finance and Operations to Excel

    In order to show how this could work, let’s start by creating a cycle count journal. As seen below, there are three ways in which cycle count lines can be generated manually:

    • On-hand – a journal line is added only if the item currently has inventory
    • Items – a journal line is created for all items
    • Expired batches – a journal line is created only for those items that have an expired batch

    For this scenario I am going to create lines based on the “items” option.

    When the Create item counting journal form opens more criteria can be specified in order to control the number of lines that are generated. For example, I have selected that I am creating the journal for warehouse 13 and counting group 20. This particular counting group has been linked to each item that I want to perform a monthly cycle count for.

    Below, my lines have been created for each item and configured for that item. Notice that there are several lines with no inventory mentioned in the On-Hand field. This is because of our selection earlier to create a journal line even if there is no inventory.

    Now that my journal has been created, I can open it in Excel. For this particular form, you’ll notice that there are selections to Open in Excel or Export to Excel. The former allows a user to publish the data back into Dynamics 365, whereas the latter is just a one-way export.

    Filling out Data in Excel

    Now that we’ve opened the same form in Excel, we’ll have an easier time completing it. As seen below, the Dynamics 365 side panel will assist in filling out the fields. For example, you wouldn’t expect someone to have all the personnel numbers memorized, and flipping back and forth between Dynamics 365 and Excel would be counterproductive.

    When it comes to populating the rest of the data there are several ways in which this can be managed. Here are just two of those options:

    1. Multiple/smaller cycle count journals can be created to be distributed to each worker on the floor. With the use of a tablet, they can complete their cycle count and publish the data back into Dynamics 365. Once it has been reviewed, the user can post the journal to make appropriate inventory adjustments.
    2. A single cycle count journal can be created. Before distribution, a user can populate the appropriate personnel number who is responsible for that item. Once that has been published, other users can filter on their own personnel number and only update their assigned item numbers. With the use of a tablet, they can complete their cycle count and publish the data back into Dynamics 365. Once it has been reviewed, the user can post the journal to make appropriate inventory adjustments.

    The above assumes that the client wishes to a) give users visibility into how much inventory to expect, and b) publish their changes. One more option to avoid both is to open the data in Excel and copy/paste it into another Excel – without on-hand details. This Excel document can then be distributed to the warehouse users and once completed, copied and pasted back into the Excel that will be used to publish directly into Dynamics 365.

    Assuming there are hundreds of lines (and the thought of reviewing each line would exhaust anyone), the process of publishing the data back into Dynamics 365 comes with its own checkpoint. Any line that has an error will immediately throw an error, and all other lines will have no problem passing through.

    Once everything has been completed correctly, the journal is ready to be posted.

    As I mentioned earlier, the capability to export and re-import data via Excel can be seen throughout many of the forms in Dynamics 365. Typically this functionality is a fundamental need for financial processes, and people often forget how impactful it can be for the rest of us! Dynamics 365 for Finance and Operations integration with Excel delivers a crucial tool for team members who need to process cycle counts efficiently and effectively.

    For questions on Dynamics 365 for Finance and Operations and Excel integration, please contact Hitachi Solutions today.