It is a typical KPI request to be able to report on unit account values. Let’s go ahead and say that we’re looking at a profit and loss report, by product and the business wants to see units sold that are included in that margin.
In a perfect world (and using some tools that are not named Management Reporter), if you’re capturing the Product A quantity in the Sales Order Processing (SOP) module in GP, you’d be able to reference the sum of the sales instead of using a unit account. However… We don’t live in a perfect world while using Management Reporter thus Unit Accounts are required.
So in GP… we’d setup a unit account for something like “Units Sold – Product A”
In Management Reporter… we’d then have a row definition that looked something like…
Sales Product A
– Expenses Product A
= Margin
/ Units Sold – Product A
= Margin/Unit
In a typical fashion, in order for this report to be up to date, the accounting team would need to make a routine journal entry to the unit account within GP. This process leaves way for manual errors, timing issues & the potential for management to lose confidence in their reports.
Using the method below, we complete streamline this process by automating the unit account journal entry within GP.
Part 1: Associate a unit account with an inventory item in GP.
Note: For example deliverable, one item may be used at multiple locations (segment 1 of COA). Therefore, we just assign the Natural account to the item & then let the integration define the remaining COA segments)
Approach: We accomplish this using eOne Solution’s Extender product
Part 2: Setup integration that captures posted activity in GP & creates a journal entry in GP.
Note: For example deliverable, we’re pulling the prior day’s activity & creating a journal entry first thing in the morning.
Note: Integration script defines COA segments based on the SITE of the sale on SOP invoice & then the natural account that we associated to the item.
Approach: We accomplish this using eOne Solution’s SmartConnect product
Map/Integration Setup
ODBC Connection to Company Database – Selecting Prior Day Sales
Part 3: Notify accounting team that all they need to do moving forward is post their batches & reconcile once @ month-end!
Integrated transactions in GP would look similar to…
Note: All prior day activities are rolled into 1 line. Option to separate for each sales transaction would be available if desired.
A few other notes to consider in this process…
– Returns must be accounted for
– When deploying, it is recommended to have the accounting team reconcile more frequently. As they gather an understanding of the process and ensure that numbers are tying out, reconcile less frequently.
– Failed integrations should auto-notify an integration PowerUser. PowerUser could re-run integration if/when needed
This solution is built in a manner that’s easy to implement & maintain and quickly accomplishes the goals of time savings, accurate data & providing a more robust GP system.
Thinking about implementing for your business and looking for additional details? Already implemented and have feedback? Let us know in the comments below!
