Purchasing Guide
The Purchasing Dashboard: inventory and open purchasing for the company and each branch. Vendor Review: everything about one supplier on one screen. Items by Location: the same for every item a location sells or stocks. Vendor Leaders & Bleeders: every supplier's sales and costs against a year earlier. Transfer Review: the branch minimums that transfers between branches have thrown off. All straight from P21.
Getting in
Sign in to TrueBearing with your Microsoft account as usual. If your account has the Purchasing role you land straight on the Purchasing Dashboard. The Purchasing menu in the bar holds the dashboard, Vendor Review and Transfer Review, with Customers next to it. Other roles can be given access by an admin (Admin → Purchasing Access); for them, the Purchasing menu is an extra item in the bar.
Purchasing Dashboard
The PO and transfer figures match P21's Executive Metrics → Purchase tab (Current Open Values) to the penny, when both are pulled at the same moment.
One table: the company in the first column, then each branch. Central 1 Security only appears if it has stock, sales or open POs. Figures are pulled from P21 when you open it and kept for 15 minutes; ↻ Refresh pulls them again. Hover a figure's name, or read the line under it, for how it's worked out.
What each figure means
| Figure | How it's worked out |
|---|---|
| COGS, last 365 days | P21's cost of goods sold on everything invoiced in the last 365 days, from P21's sales history report. Counted at the branch the stock shipped from, since that's the inventory it used, so a sale written at Amarillo but shipped from Roseville counts toward Roseville. |
| Inventory value | From P21's FIFO layers: every receipt of stock is a layer with its own cost, and as stock is sold the oldest layers are used up first. The value is the quantity left in each open layer × that layer's cost, added up. Stock on hand that has no layer behind it (it happens: see FIFO layer check) is added at moving average cost, as P21 does. |
| Inventory conversion period | The average age of the inventory, in days: (inventory value ÷ COGS) × 365. Lower means stock turns faster. |
| Inventory turns | How many times a year the stock turns over: COGS ÷ current inventory value. Higher is faster. It's the other side of the conversion period: 365 ÷ turns gives the days. |
| Dead stock value | Items whose last sale was 365 or more days ago, with more on hand than allocated, set up at the branch for 365 or more days, and with nothing received there (on a PO or a transfer) in the last 90 days. Valued on the quantity not allocated, at the item's FIFO cost (its layers' value ÷ their quantity), or standard cost if P21 has no open layers for it. Items that have never sold at all aren't counted. |
| Open PO value | Every open PO except drop ships, the way P21 totals it: stock POs (generated and entered by hand) and special orders. The quantity still to come in × unit price, on open lines of open POs. Where an item is priced by the pack (a 10-pack, say), the quantity is counted in packs. The count under it is the number of POs. |
| Open drop-ship PO value | The same, for direct-ship POs. |
| Open special-order PO value | The same, for POs bought for a particular customer's order. These are also included in Open PO value. |
| Material purchases, last week / this week | Stock POs (generated and entered by hand) placed in the week, by order date: quantity ordered × price on every line that isn't cancelled or deleted, with pack-priced items counted in packs. Drop ships and special orders are left out. Weeks run Monday to Sunday; "this week" is Monday to today. The dates show beside each row. |
| Unsold incoming | Stock coming in that no open order is waiting for. For each item at each branch: the quantity still to come in on open stock POs (generated and entered by hand), plus what's coming in on transfers from other branches (open transfers not yet received, and P21's transfer backorders headed there), less what's on open customer orders at that branch (regular and backordered lines; specials and direct ships have their own POs), less what's waiting to be transferred out of that branch (P21's transfer backorders and unshipped transfer lines). What's left, valued at the PO price, or for transfers at moving average cost at the branch shipping it. Where an order covers part of what's coming in, only the rest counts. The count under it is the number of items. |
| Open transfer value | Stock on open transfers that hasn't been received at the other end yet, at moving average cost, counted at the branch that ships it. The count under it is the number of transfers. |
FIFO layer check
A note under the table counts items whose open FIFO layers add up to more or less than the quantity on hand, by branch, with the value of stock that has no layer behind it. That stock is included in inventory value at moving average cost (or standard cost, if the moving average is wildly out). It's worth putting right in P21; once it is, the item drops off the count at the next refresh.
Picking a supplier
- Type a supplier number and press Enter, the same as typing it into the workbook's Dashboard, or
- type part of the supplier's name and pick it from the list.
The last supplier you looked at opens again next time. Data is pulled from P21 when you pick the supplier and kept for five minutes; ↻ Refresh pulls it again now. The time it was pulled shows beside the button.
The supplier card
The top of the screen is the workbook's Dashboard sheet:
- Buyer, phone, address, terms, payment class, freight target, target value and the last review date (with the review cycle, when one is set).
- A box for each branch (Roseville, Amarillo, Billings; Central 1 Security only
when it has stock or sales for this supplier) with three figures:
- Inventory value: on hand × cost.
- COGS, last 12 months: cost of goods sold of this supplier's items over the last 12 full months (this month is left out because it isn't over), by the branch that made the sale. It's the same cost the Sales tab uses. Hover over it to see the exact months.
- Inventory target (45 days): 45 days of that cost, COGS ÷ 365 × 45. Compare it with the inventory value above it: higher than the target means more than 45 days of stock on the shelf.
- Discontinued: the value of discontinued items still on the shelf at the three branches. See Discontinued.
- 👤 Contacts opens the supplier's contacts in a small window with a search box: type part of a name, title, phone number or email to narrow the list. Phone numbers and emails are links. Drag the window by its title to move it out of the way of the item list, or by its corner to resize it; double-click the title to put it back in the middle.
- Vendor notes: tap the line under the Contacts button to open them.
Items
Every item this supplier provides, a row per branch that carries it. This is the workbook's INV_DATA sheet; pick a branch under Location and it becomes that branch's LOC sheet. Discontinued items are included only where they still have stock, as in the workbook, and are greyed. The first 500 rows show; Show more adds the next 500.
What the columns mean
| Column | Meaning |
|---|---|
| QOH / QAL / QA / QB | On hand, allocated, available (on hand less allocated), backordered. |
| OPO | Quantity on open purchase orders. |
| Cost | The unit cost the stock is valued at: P21's FIFO cost (or moving average where P21 has no layers). The cost on the supplier record is available as Supplier cost under Columns, but isn't used for value: it can be the price of a whole pack, and often differs from what the stock cost. |
| INV$ | The value of the stock on hand, worked out the same way as the Purchasing Dashboard's inventory value, so the two agree. |
| Month columns | Usage for that month at that branch, this month first (marked MTD, still in progress) then the six before it. Blank means P21 has no usage record for that month. |
| AVG | Average monthly usage over the six full months (this month is left out). Blank months are skipped and zeros count, the way Excel's AVERAGE worked in the workbook. |
| Min / Max, Replen, Sell, Stk, Buy, Disc | The branch's settings in P21. |
| AVG orders | Average number of orders a month over the same six months. |
Choosing, moving and sizing columns
On Items, By item and Discontinued, and on both Transfer Review tables, you can set the table up the way you like it:
- Show or hide: Columns, beside the search box, lists every column with a tick box. The table changes as you tick, and the list stays open until you click somewhere else. Hidden on Items to start: short code, price 5 and 10, orders by month, last purchase and modified dates, class, price family, product group, discount groups, purchase class and extended description. Item is always shown.
- Move: drag a column heading (grab its name) left or right; a blue line shows where it will land. Or use the ▲ ▼ beside each column in the Columns list, which is the way to do it on a phone or tablet. The list shows your columns top to bottom in the order they run left to right. On By item, Item stays first.
- Resize: drag the right edge of a heading. Text that no longer fits ends in "…". Reset widths in the Columns list lets the table size itself again.
- Start over: Standard puts back the standard columns in the standard order at automatic widths; All adds every column, keeping your order.
Your own default layout
Once a table is set up the way you want it, open Columns and choose Save as my default. That saves which columns show, their order and their widths. From then on the table opens that way every visit. You can still change things while you work; the next visit starts from your default again, and Use my default puts it back straight away. Save again at any time to replace it, or Clear my default to remove it.
Without a saved default, the table comes back the way you last left it. Either way, layouts are saved to your account, separately for each table, so they follow you to any computer or phone you sign in on, and apply to every supplier. Month columns are remembered by position (this month, last month, and so on), so a layout still works next month. Sorting by a column you then hide keeps that order.
Both screens use the full width of the window. Each table sits in its own box sized to the window, and the heading row stays pinned at the top of the box as you scroll through the rows. Scroll the page down to the table and the box stops just under the top bar, so the headings stay on screen. Show more is at the bottom of the box. While the table's own sideways scrollbar is below the bottom of the window, a copy of it sits just above the footer, so you can always scroll left and right.
Item notes at a glance
An item with P21 item notes has a * after its ID, on both Items and By item. Hover over the ID to see its notes, newest first, with the topic, date and who last changed each. On a phone or tablet, tap the ID to open them and tap again to close. Move the pointer into the panel to scroll a long note. The full list is on the Item notes tab.
Filters, search and sorting
Location, Replen, Stock, Buy and Disc work like the workbook's slicers; each only offers the values this supplier actually has. The search box matches item ID, short code, supplier part number or description. Tap any column heading to sort by it; tap again to reverse. The line above the table totals what's showing: rows, units on hand and inventory value.
By item
The workbook's All_LOC view: one block per item, with a line for each branch under it. It opens on short code, cost, on hand, allocated, backordered, on order, average usage, min and max. Columns adds any other column from Items, the same way as on Items (your choice for By item is remembered separately). The same filters and search apply.
The item line sums up its branches:
- Totals for quantities, inventory value, min, max, and monthly usage and orders.
- Average for cost.
- Latest for dates (last sale, last purchase, modified).
- Fields that belong to the item itself (short code, supplier part number, description, prices, class, price family) appear once, on the item line.
- Branch settings (Replen, Sell, Stock, Buy, Disc, product group, discount groups, purchase class) show the value when every branch agrees and mixed when they differ; the branch lines show each one.
Hover a column heading for what its item line shows. Tap a heading to sort the items by that line's figure (tap again to reverse); the branch lines under each item stay in location order.
Sales
Sales, cost, profit and GP% of this supplier's items by year since 2013, newest first, from P21 sales history (the workbook's Sales_Numbers sheet). Tap a year to see its months. Pick a Selling location to see one branch. Sales history loads a moment after the rest of the screen; for a large supplier it can take up to half a minute.
Item notes
P21 item notes on this supplier's items, newest first, with who last changed each and whether the item is discontinued at Roseville. Search by item, topic or note text.
Discontinued
Discontinued items still on hand at Roseville, Amarillo or Billings, largest value first, with a 90-day view of whether they're still moving: average monthly usage, average orders a month, and units per order over the last three full months. Columns works here too.
Downloading to Excel
⭳ CSV on each tab downloads what that tab shows, with your filters, ready to open in Excel. On Items and By item it's always the full item list with every column, whichever columns are showing and not just the 500 rows on screen.
Coming from the Vendor Review workbook
| Workbook | Vendor Review |
|---|---|
| Type the supplier in Dashboard A2, Refresh All | Pick the supplier; ↻ Refresh for up-to-the-minute data |
| Dashboard | The supplier card |
| INV_DATA, LOC_10, LOC_11, LOC_12 | Items, with Location set to All or a branch |
| All_LOC and its slicers | By item, with the filters |
| Sales_Numbers and its year/period slicers | Sales, tapping a year for months |
| Notes | Item notes |
| Discontinued | Discontinued, and the Discontinued on hand tile |
Items by Location
Vendor Review turned around. Open it from Purchasing → Items by Location and pick a location: it lists every item there that's marked sellable or has stock on hand, whoever supplies it, with the same columns as Vendor Review's Items plus Supplier (the item's primary supplier at that location; Supplier ID is under Columns). A main branch has around 42,000 items, so the first load takes a few seconds; after that it's kept for five minutes, and ↻ Refresh pulls it again.
- The card at the top counts what's listed: marked sellable, on hand, on hand but not sellable, and how many primary suppliers. The tiles show the location's inventory value, its cost of goods sold over the last 12 months, the 45-day inventory target, and discontinued stock on hand.
- Show narrows the list: On hand, Used in the last 6 months, or Not on hand or used (sellable items that are just sitting in P21). The Replen, Sell, Stock, Buy and Disc filters, the search box (which also finds a supplier by name), sorting, columns, your own default layout and CSV all work as they do in Vendor Review. Its column layout is saved separately from Vendor Review's.
- Sales is everything sold from the location, by year and month. Item notes and Discontinued cover the listed items at that location. There's no By item tab, as it's one location.
Vendor Leaders & Bleeders
The sales Leaders & Bleeders, for suppliers. Open it from Purchasing → Vendor Leaders & Bleeders. It lists every supplier with sales, graded on how its sales changed with the same bands as the sales screen: leaders A up 30% or more, B 20%, C 10%; bleeders D down 10% or more, E 20%, F 30%. A+ is a supplier with no sales at all in the earlier window. Click a supplier's name to open it in Vendor Review.
- Year to last month compares this year through the end of last month with the same months last year (in January, last year's twelve months with the year before). A finished month is a finished number, so the comparison doesn't move with a half-invoiced current month. Rolling 365 days compares the last 365 days with the 365 before them.
- Location: Company-wide, or one selling location. Buyer and the search box (supplier or buyer, by name or number) narrow the list, and the grade chips, Only graded and Show new work as on the sales screen. The tiles above the table total the suppliers shown.
| Column | What it is |
|---|---|
| Sales | Line revenue (extended price), product lines only: no freight, tax or other charges. Counted once per line, at the location it was sold from. |
| COGS | Cost of goods sold, P21's COGS amount on each invoice line. |
| Other cost | Quantity shipped × the line's other cost: the cost basis TrueBearing's margins use, and the cost on Vendor Review's Sales tab. |
| GP % | (Sales − other cost) ÷ sales. GP % on COGS (under Columns) uses COGS instead. |
| Terms | The vendor's default terms in P21, as on the Vendor Review card. |
| Freight target | The supplier's freight target value: the order size that ships prepaid. |
| Order target | The supplier's target value: the order size to buy to. |
| Buyer ID, Buyer | The buyer assigned to the supplier in P21. |
| Shipping instructions | The supplier's default shipping instructions. |
Columns, order, widths, your own default layout and CSV work as in Vendor Review. Sales change $, Other cost change and the two GP % on COGS columns are under Columns. The figures are pulled from P21 when you open the screen (about half a minute the first time) and kept for ten minutes; ↻ Refresh pulls them again.
Transfer Review
Open it from Purchasing → Transfer Review. It looks across every supplier at once and has two tabs. Pick how much history to use under History (the last 3, 6 or 12 full months; it opens on 6). Every figure on the screen covers the same days, shown under the title. Supplier and Item or description narrow the list; click a supplier's name to open it in Vendor Review. Tap any column heading to sort. Columns lets you show, hide, move and resize columns and save your own default, separately for each tab.
Why transfers throw minimums off
P21 records usage only at the branch that sells an item. When Roseville ships stock to Amarillo or Billings on a transfer, that never counts as usage at Roseville. So Roseville's usage, and any min set from it, covers Roseville's own customers and none of the stock it feeds the other branches. Roseville ships far more out than it receives, so its mins are the ones that come up short.
Shipping branch mins
Every item a branch shipped out on transfers, with a suggested min that accounts for them.
| Column | Meaning |
|---|---|
| Own usage /mo | The branch's usage in P21 (its own sales), per month. |
| Transfers out /mo | What it shipped to other branches, per month. This is the demand P21 doesn't count. |
| Sent to | Where it went: quantity, and how many transfers in brackets. |
| Share by transfer | Transfers out as a share of everything the branch supplied (own usage + transfers out). |
| Min covers (days) | How many days of own usage plus transfers out today's min covers. |
| Avg days to receive | The supplier's lead time at that branch: see Average days to receive below. |
| Suggested min | Own usage plus average transfers out, per day, times the days picked under Stock for (45 to start): the min that holds about 45 days of everything the branch supplies. |
| Change / Change $ | Suggested minus current, and that many units at moving average cost. |
Stock for sets the days the suggested min should hold: 30, 45 (to start), 60 or 90. Show picks Raise min (to start), Lower min or All, since a min can be higher than 45 days needs as well as lower. The line above the table totals the change in stock, at moving average cost, if every item listed were changed. Items with no min set today are marked under the min.
Receiving branches
Items a branch received by transfer again and again: at least the number of times you pick (4 to start). Amarillo and Billings are set up to buy from suppliers themselves, so each transfer in is a top-up because they ran short.
- Every (days): how often, on average, a transfer came in.
- Usage supplied by transfer: quantity transferred in ÷ the branch's own usage. Near or over 100% means the branch is barely buying the item itself; Roseville is.
- Min covers (days): how many days of its own usage the branch's min covers.
- Avg days to receive: how long the branch waits when it buys the item itself (below). Blank usually means the branch hasn't bought it at all in the last year.
A high count with a short min cover points to raising the branch's min. A high count with a decent min cover and high usage supplied by transfer points to the branch not buying the item itself, so either it should, or Roseville's min should carry it (see the other tab).
Average days to receive
On both tabs: the average number of days from placing a purchase order to receiving it at that branch, from the actual receipts in P21 over the last 12 months (whatever the History setting). Each PO line counts once, to its first receipt. Only stock POs count: ones P21's PO generation made and ones entered by hand. Direct ships and POs bought for a particular customer order are left out. Under the number is how many POs it's based on. When the item itself had none at that branch, the supplier's average there is shown instead, marked supplier. It's for reference: the suggested min doesn't use it.