TrueBearing Help

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.

What it is: the Vendor_review spreadsheet, built into TrueBearing. Same P21 data, same numbers, but nothing to refresh by hand, no saved database login in a file, and it works on any device. It only reads from P21; nothing you do here changes 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

FigureHow it's worked out
COGS, last 365 daysP21'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 valueFrom 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 periodThe average age of the inventory, in days: (inventory value ÷ COGS) × 365. Lower means stock turns faster.
Inventory turnsHow 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 valueItems 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 valueEvery 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 valueThe same, for direct-ship POs.
Open special-order PO valueThe same, for POs bought for a particular customer's order. These are also included in Open PO value.
Material purchases, last week / this weekStock 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 incomingStock 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 valueStock 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.
COGS includes drop ships. P21 records cost of goods sold on direct-ship sales too, even though they never came out of a branch's stock. That makes COGS a little higher, and the conversion period a little shorter, than stock alone would.

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

  1. Type a supplier number and press Enter, the same as typing it into the workbook's Dashboard, or
  2. 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:

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

ColumnMeaning
QOH / QAL / QA / QBOn hand, allocated, available (on hand less allocated), backordered.
OPOQuantity on open purchase orders.
CostThe 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 columnsUsage 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.
AVGAverage 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, DiscThe branch's settings in P21.
AVG ordersAverage 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:

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:

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

WorkbookVendor Review
Type the supplier in Dashboard A2, Refresh AllPick the supplier; ↻ Refresh for up-to-the-minute data
DashboardThe supplier card
INV_DATA, LOC_10, LOC_11, LOC_12Items, with Location set to All or a branch
All_LOC and its slicersBy item, with the filters
Sales_Numbers and its year/period slicersSales, tapping a year for months
NotesItem notes
DiscontinuedDiscontinued, and the Discontinued on hand tile
The numbers match. Before release, Vendor Review was checked against the workbook's own queries for several suppliers, row by row and month by month.

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.

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.

ColumnWhat it is
SalesLine revenue (extended price), product lines only: no freight, tax or other charges. Counted once per line, at the location it was sold from.
COGSCost of goods sold, P21's COGS amount on each invoice line.
Other costQuantity 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.
TermsThe vendor's default terms in P21, as on the Vendor Review card.
Freight targetThe supplier's freight target value: the order size that ships prepaid.
Order targetThe supplier's target value: the order size to buy to.
Buyer ID, BuyerThe buyer assigned to the supplier in P21.
Shipping instructionsThe 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.

ColumnMeaning
Own usage /moThe branch's usage in P21 (its own sales), per month.
Transfers out /moWhat it shipped to other branches, per month. This is the demand P21 doesn't count.
Sent toWhere it went: quantity, and how many transfers in brackets.
Share by transferTransfers 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 receiveThe supplier's lead time at that branch: see Average days to receive below.
Suggested minOwn 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.

Example: BAT L3.0V-1.3AH-PANA at Roseville. Over six months it used about 753 a month itself and shipped another 144 a month to Amarillo and Billings: about 29.5 a day in all. 45 days of that is 1,327, so the suggestion is 1,327 against today's min of 500, which covers about 17 days.

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.

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.

One thing to know about P21's usage figures. Usage in P21 runs lower than the quantity shipped in sales history, at every branch (roughly 60 to 75%). That's a P21 setting about what counts as usage, not something this screen does. Mins are set against P21's usage, so the screen uses it too.
↑ Top