Skip to content

How Pulse Dashboard calculates its numbers

Adapted from the "PULSE Dashboard & Reporting Software Definitions" document, updated October 2020.

Every number on a Pulse Dashboard screen is derived from your Macola® data by a specific rule. This page states those rules — what counts as usage, how fill rate is worked out, which transactions inventory turns ignore — so that when a figure looks surprising you can tell whether the calculation is behaving as designed or your data needs a look.

Questions and comments are welcome: (513) 723-8095 or [email protected].

Customer Service and Shipping

Which customer orders need managing

Three sets of orders decide on-time delivery:

  • orders to be shipped today
  • orders to be shipped from tomorrow through the end of this month
  • orders to be shipped in subsequent months

Pulse Dashboard puts the orders due each day in front of you, which is what lifts the on-time delivery percentage.

The order categories

Category Definition
Backorder One or more order lines have been partially invoiced; the remaining line items take a status of backorder
Shortage When the line was keyed, quantity available to ship was less than quantity ordered
On hold The order is on customer credit hold
Past due The line carries a request date on or before today
Available to ship Every line on the order has quantity available to ship equal to quantity ordered
Shipped / not posted Includes any unposted invoices

The categories are mutually exclusive — there is no overlap between any of them.

Forcing the quantity-to-ship field breaks the available-to-ship figure

It is a common practice to force quantity to ship to equal quantity ordered. When that happens, neither Macola® nor Pulse Dashboard can tell whether the order is genuinely available to ship. Let Macola® populate the quantity-to-ship field itself.

The cut-off date

The customer order line request date is the default cut-off date. You can choose the promise date or the order header ship date instead. The required ship date field on the order line cannot be used as the cut-off date.

Items causing shortages and backorders

The "Items Causing B/O and Shortages" box time-phases four inputs: the on-hand quantity in the item/location file, unshipped customer order demand, purchase order replenishments and POP order replenishments. Drill down to see the chronological sequence of demands and replenishments, when the item goes short, and the final on-hand quantity.

Fill rate

Fill rate is the ratio of what was fully invoiced per day, and can be displayed daily or monthly. Every partial shipment pulls it below 100%.

Pulse Dashboard takes all invoices for the day — using the OEHDRHST invoice date field — and compares what should have shipped for those orders against what actually shipped. Invoice $90 for the day when the outstanding open value on those orders was $100, and the fill rate is 90%.

Inventory

Inventory figures come from the Macola® inventory transaction history file, recalculated at least once a day by a nightly stored procedure. Pulse Dashboard uses the transaction date, not the document date.

How the dates are calculated

Date Built from
Date of last purchase receipt Purchase order receipts
Date of last manufacturing Reported production
Date of last usage Customer sales and returns; production component issues and returns; POP production negative issues; manual inventory issues and receipts
Date of last sale Customer sales and returns
Date of last inventory activity Customer sales and returns; P/O receipts; production component issues and returns; production reporting; POP production negative issues; manual inventory issues and returns. Inventory transfers are not included
Date of last count Taken from the item/location file

Usage and average monthly usage

Usage is the combination of customer sales and returns, production or shop order component issues and returns, POP production negative issues, and manual inventory issues and receipts.

Average monthly usage is calculated over the prior 365 days. The 3-month, 6-month and 12-month usage fields are real time as of yesterday.

Inventory adjustment quantities

Manual quantity adjustments plus physical and cycle count quantity adjustments.

Inventory aging

The aging screen finds dead inventory, based on date of last usage. It is not the same thing as excess inventory.

Aging is misleading for brand-new parts

The aging screen is based only on usage. A part that has just been set up has no usage, so it ages as though it had been sitting there for years.

Months on-hand

On-hand quantity divided by average monthly usage over the past 365 days. An item with an on-hand quantity that has never been used is set to 9,999.

ABC classes

Following the APICS recommendation:

Class Share of prior year's dollar usage Rough share of items
A 80% 20%
B 15% 30%
C 5% the rest

The ABC Class Generator lets you choose different percentages and add a fourth class. We recommend making ABC classes uniform across locations — an A item at your main location should be an A item everywhere.

ABC classes exist so you can put most of your attention on the A items. At one customer with more than 5,000 inventory items, fewer than 110 are in class A.

Inventory turns

IMINVTRX is the only file used. Each night a stored procedure reads it and builds a summary database holding usage and turns for each of the past 24 months, which is why the screens come up instantly.

The formula, per item number per location:

    usage for month 1 + month 2 + … + month 12
  ───────────────────────────────────────────────
  (avg on-hand month 1 + … + avg on-hand month 12) / 12

It is a 12-month moving average; you can choose between 1 and 12 trailing months.

The three steps the graph takes:

  1. Turns are calculated per Item, Item/Location or Item Location Grouping, aggregating on-hand and usage per your selection and applying any filters. Choose per Item while excluding location 001, and Pulse adds up quantities across every location except 001.
  2. Value Bounds are applied. A month falling outside the bounds is nullified and excluded from the next step.
  3. Averages are calculated from the months that were not nullified. If July's turns for three items were 3, 5 and NULL, July shows (3 + 5) / 2 = 4. If August's were 3, 5 and 7, August shows 5. A month with no valid values is not plotted at all — it is not shown as zero.

Defaults: Calculate Turns = Per Item, Lower Bound = 0, Upper Bound = 100.

On-hand quantity comes in two flavours. APICS suggests the average on-hand quantity for each month; some customers prefer end of month, so both are offered and the stored procedure holds both.

  • Document type I: on-hand = old quantity − quantity
  • Document type R: on-hand = old quantity + quantity

Transactions that are not relevant to turns: records with a quantity of zero, purchase order receipts, allocations, beginning balances, on-order records, bin adjustments, cost adjustments, physical counts, adjustments, production reporting and LIFO/FIFO adjustments.

Backdating transactions makes end-of-month quantities wrong

Only the transaction date and time fields are used — the document date has no associated time field, so it cannot be used. Transactions are sorted by date and time to reconstruct usage and end-of-month quantities. Backdating breaks that reconstruction.

The formula is most accurate when there are several transactions in each month to average across.

Why turns matter

An item that turns four times a year carries a much higher holding cost than one turning eight, twelve or fifteen times. Carrying costs typically run 20–30% of the value of on-hand inventory — personnel, rent, utilities, insurance and the rest of keeping extra stock. Raising turns reduces inventory, reduces carrying cost and makes it easier to respond to changes in customer requirements and to replace obsolete items.

Turns that are too high, though, can mean inventory is too low, which shows up as stock shortages.

Look at three and six months, not just twelve

Because turns are tedious to calculate per item, most companies only look at the trailing twelve months. That tells you the level, not the direction. To see whether turns are rising or falling, review the trailing three or six months as well.

The Pulse inventory database

Two tables are updated at least once a day, usually overnight, and are available for your own Crystal reports, Excel queries or SQL queries: InventoryUsage_Weekly (past 52 weeks) and InventoryUsage_Monthly (past 26 months).

Stored for both months and weeks:

Field Column
Item number item_no
Location code loc
ABC class inv_class
Product category prod_cat
Controlled flag controlled_fg
Date of last purchase date_of_last_purchase
Date of last sale date_of_last_sale
Date of last inventory activity date_of_last_IMactivity
Date of last usage date_of_last_usage
Average monthly usage, past 365 days avg_qty_year
Inventory turns, past 365 days turn_year
Usage quantity year to date usg_qty_YTD
Usage quantity, past 365 days usg_qty_12_month
Months of inventory on hand months_on_hand

Stored by month for the past 25 months, where month 00 is the current month:

Field Column
Usage $ usg_amt_00 … usg_amt_24
Usage quantity usg_qty_00 … 24
Beginning on-hand quantity beg_qty_00 … 24
Beginning on-hand $ beg_amt_00 … 24
Ending on-hand $ end_amt_00 … 24
Average on-hand $ avg_amt_00 … 24
Average on-hand quantity avg_qty_00 … 24
Inventory turns turn_00 … 24
Purchased quantity purch_units_00 … 24
Purchased $ purch_amt_00 … 24
Transfer-in quantity transfers_in_units_00 … 24
Transfer-in $ transfers_in_amt_00 … 24
Transfer-out $ transfers_out_amt_00 … 24
Production $ production_amt_00 … 24
Inventory adjustment quantity qty_adj_00 … 24

Stored by week for the past 53 weeks, where week 00 is the current week:

Field Column
Usage $ usg_amt_00 … usg_amt_52
Usage quantity usg_qty_00 … 52
Sold $ sold_amt_00 … 52
Sold quantity sold_units_00 … 52

Production

Customer order shortages versus component shortages

Two screens sound alike and answer different questions.

The Customer Service and Shipping shortages screen tells you which customer shipments cannot go out on time — the customer asked for a date, and the item will not be on hand by then. It looks only at the item number scheduled for shipping, and decides availability from current on-hand quantity plus pending production and purchase orders.

The Production module examines each component in the captured bill of material, and decides availability for each of them the same way.

Both let you set how many days into the future to look. If today is Monday and you want this week and next, set 14 days.

These screens are only as good as four dates and one quantity

Accuracy depends on accurate on-hand quantities, accurate purchase order due dates, accurate production order due dates and accurate customer requested ship dates. See What each date field on an order means.

Support & contact

Our team is glad to help with anything from a quick question to a full implementation.