We are working on building a customized version of the "Inventory" Power BI template. We've been able to use the documentation and understand most things, but one thing that is puzzling us is understanding how QQube reconciles open sales orders' forecasted value when they are fulfilled of intermediate items. To be very clear - the product item sales order appears to be working as expected, but its dependent intermediate items are what I lack clarity on.
As I understand it, if there is an open sales order for a product in QuickBooks, then QQube generates a corresponding open sales order line for each of the items in that product's BOM, and the stock "Selection Available Quantity" then correctly factors in the on-hand quantity minus the forecasted quantities of all intermediate goods. However, I noticed that every single value in the "Line Forecast Quantity" column is either zero or negative, and all values before a certain date (which looks suspiciously like the oldest open sales order) are set to zero.
So my question is: How does QQube reconcile the forecasted quantity of an intermediate item? Does it update the record and set it to zero? Does it simply delete (or not sync) the generated sales order line? Or is there another place I should be looking?
Thank you for your help.
The key here is understanding what QuickBooks doesn't do - keep historical snapshots of Sales Orders in time. Sales Orders in QuickBooks only have a current state e.g. what is currently open.
So we have no choice but to acquiesce.
Theoretically the current state should work fine, because the difference between what was open today, and the day before, should be another invoice (attached to that sales order), which reduces the on hand inventory. It is only in cases where people don't follow a set of steps, e.g. where sales orders are not manually closed if the sales order is not truly open.
Similarly we calculate fulfillment rate based upon the earliest invoice linked to the Sales Order. Is it 100% accurate? It can't be, because people change things in QuickBooks - for which we have no control over. Overall, however, it should be close.
Line Forecast Quantity is a special QQube field that combines Current Open Sales Orders (reducing future on hand), and Current Open Purchase Orders (adding to future on hand) to come up with a singular value. (You could just have two columns, open so and open po - this makes it easier in some cases.)
We don't calculate anything regarding a date, as the date buckets might change with certain customers; you might use a calculated column to extrapolate. OR some people just use the week ending date from the calendar dimension to approximate what should be on hand at the end of each week into the future.
QuickBooks, is an easy to use off the shelf program, costing tens of thousands less than higher end programs, but it also can't do all of the things those programs can do.
Choose a location