For many dealers, once you reach a certain size, it makes sense to stock product. In a lot of cases, paper is a natural fit, as the wholesalers’ prices are out of line if you’re trying to compete with the big boxes or Costco. Beyond that, though, you may find it makes sense to bring in product from the big players – Smead, Avery, 3M. Another consideration is your salespeople, though, and keeping them from giving away the store. For that, we have vendor contracts and front-loaded prices.
At a high level, we pick a point somewhere between the real cost we’re paying the manufacturer and what we’re paying the wholesalers. This cost (call it “loaded”, “factored”, or something else) gets loaded in DDMS. Deeper down in the system, we create a vendor contract so POs can be cut with the correct cost.
Without getting deep in the weeds on the DDMS side of things, we shut off updating Average Cost in purchasing and set our loaded cost as Average. This is where DDMS first pulls cost in Order Entry (depending on a couple other factors, as I recall). If we’re pulling from our own stock, this cost gets left alone through the purchasing process.
However, give it a quarter or two, and your costs are out of date. They need to be updated on both sides – the real cost you send to your vendors, along with your own loaded costs so as to allow maintaining your own margins. To that end, we have the Vendor Contract report:
Click for full size
I’m not showing actual dollar figure costs, but that’s not really the point here. What we have here is a report that easily gives us our real costs, factored costs, the markup of factored over actual, wholesale purchase costs, and the delta between that and our factored cost. Where it gets more interesting, as well as useful, is when pull the same report in Excel:
Click for full size
On the far right, we have three new columns – New C.Cost, New F.Cost, and New Delta. When we plug our new direct cost into column L, the New F.Cost column has a formula set to calculate our new factored cost. The formula I use is =IF(L2=0,"",(L2*(1+E2))). The first bit of that is something that will leave the cost in M blank if there is no cost set in column L – easy to do, and it keeps our spreadsheet clean. The work is being done by L2*(1+F2). Since the sheet already has our markup calculated in column F, we use the reverse to get from our new direct cost to our new factored cost. Column N then has a formula that gives us the delta between our new factored cost versus wholesale, done using =IF(L2=0,"",(G2-M2)/M2). Same blank suppression trick, and the widely-known formula for calculating markup.
Our first item in the spreadsheet is AVE00166, the classic glue stick. We have a markup currently of 25%. Say now we’re paying $1/ea for them, and our results look something like this:
Needless to say, if we’re paying a dollar each on these, suddenly Elmer’s will find themselves the new vendor for glue sticks for a lot of people. But the point is, where some dealers are burning a lot of man hours re-figuring costs once a quarter or more, 10 minutes with this sheet and a VLOOKUP to the vendor’s price file will have our new costs figured for us.
As to our interface to this report, we’re doing something a little different this time:
Click for full size
We’ve added a section to our accordion to keep our different functions separated. Looking a little closer at the interface to Vendor Contract itself, notice we now have a button, something we haven’t seen yet in the PSyOPs framework:
Hover the mouse over that button, and we have some options:
Pick Vendor and the box updates to show us what we’ve selected:
Our options are Vendor, which uses the Vendor ID from DDMS; Prefix, which uses the item prefix (which is a bit imperfect for Sanford and Acco, among others); and Contract, which uses the Vendor Contract ID. I find Vendor the most useful, since this will pull all vendor contracts, if you have multiples, and will pull your Dymo, Papermate, and Sanford, if you stock Sanford product and use the standard prefixes.
Notice also in the Excel version, I have the prefix and SKU in separate columns – where normally I put them together, here I prefer them separate for a couple reasons. First is the aforementioned VLOOKUP – the price files you get from vendors obviously don’t include prefixes, and we can easily keep the SKU separate from the prefix to ease the VLOOKUP process. The other reason is FileMapper, used to load direct costs in DDMS as M records in Item. This also uses a single prefix (so don’t forget to move your PAP and DYM items out of this file first). Once we have our new costs figured, we can save this file as tab-separated text and use it directly to load our new costs.
For this one, because it’s a healthy amount more complicated than quite a few things we’ve added to PSyOPs so far, due to the javascript-driven menus, the zip file for this report will include all the necessary files in the proper structure. There is a single css and a single JS file needed for jMenu, and I’ve included an updated version of contractreports.php itself, along with vendor_contract.php and vendor_contract-dl.php, naturally. This package is available from bendustries.co/files/contracts/vendor_contract.zip.
What we have here is, in my opinion, on the more powerful reports the PSyOps framework has to offer thus far. I’ve got designs on extending the functionality we’re starting with here, but crazy works hours and personal projects have been interrupting my progress. Soon, though, we’ll be adding more power yet to this engine. In the meantime, I remain 
Recent Comments