Managing prices for 2000+ Items based on frequent price data imports

ERPNext
, , , ,
1

Hi all, I’d like your insights on the following.

We are buying, selling and using (for projects) the following generic “raw” materials.

Plywood 6mm
Plywood 9mm
Plywood 12mm
Plywood 19mm
Plywood 25mm
Plywood 6mm - Moisture resistant
Plywood 9mm - Moisture resistant
Plywood 12mm - Moisture resistant
Plywood 19mm - Moisture resistant
Plywood 25mm - Moisture resistant
Plywood 6mm - Fire resistant
Plywood 9mm - Fire resistant
Plywood 12mm - Fire resistant
Plywood 19mm - Fire resistant
Plywood 25mm - Fire resistant
Plywood 6mm - Fire resistant - Single sided primer
Plywood 9mm - Fire resistant - Single sided primer
Plywood 12mm - Fire resistant - Single sided primer
Plywood 6mm - Fire resistant - Double sided primer
Plywood 9mm - Fire resistant - Double sided primer
Plywood 12mm - Fire resistant - Double sided primer
Plywood 6mm - Perforated
Plywood 9mm - Perforated
Plywood 12mm - Perforated
Plywood 19mm - Perforated
Plywood 25mm - Perforated
OSB [sort of the same layout as plywood]
Etc… 750 main and sub-types more.

  • All of them are coming form 3 main resellers offering multiple brands and types.
  • We just keep small stock for a couple of them. Let’s say 75 types. The rest we are buying for a specific project.
  • I’m getting clean CSV/EXEL sheets with price updates of every Item from the 3 suppliers. It’s a list of 5000 records containing: Reseller, MPN (or other unique reseller nummer), the price per square meter and the price per full sheet.
  • We only buy full sheets and sheet size for every brand and type is different. Let’s say there are 100 different sizes of sheets.
  • General profit: 150% for full sheets. 200% for cut sheets.
  • Selling price is basically the above calculation plus our own input per item: we don’t want it to drop to much

Questions / goals:
How do we get the info into ERPNext consedering:

  • keeping track of suppliers price history. And company price history.
  • quickly finding (filtering) raw material prices from all manufacturers based on multiple relevant propperties (stated above like thickness, fire resistance etc). This would be used for quick calculations for customer re quests.
  • Having the same pricing info at hand for the items we sell. Allso these same propperties should the the filters to get to the right item (price) fast. And add it to a quote or manufacturing order.
  • Having our “items for sale” (at least the ones we are keeping on stock) linked to the right Supplier, MPN and latest price.

We’re OK with using the import data feature to mass-update the prices every few months.

How should we fit these items, prices and links into ERPNext? Your insights are verrywell appreciated.

2

would you mind to provide some sample data(mock data of course)?

3

Item price has a valid_from and valid_to date to keep track of historical pricing (for any price list).

The import tool is useful here but it would be a good idea to use several smaller imports vs 5,000 rows in a single file.

If prices change often you might consider “moving” no longer valid prices to some other price list like hist_selling or archived_buying, to keep pricing queries performing optimally.