← Getting your data in

Excel → Practical Stock Planner: File 6 – SKU_Supplier_Details (how each product is bought)

Practical Stock Planner – Time-phased stock planning. One short guide for one file. No IT skills needed – just Excel.

What this file is: the buying terms for each SKU – who you buy it from, lead time, pack size, minimum order and price. Shopify, WooCommerce, Xero and QuickBooks Online don't hold most of these terms, so you build this file once in Excel and update it when buying terms change.


Before you start

  1. Download the SKU_Supplier_Details template from Practical Stock Planner. Open Load Data, then open Templates in the sidebar. Practical Stock Planner accepts CSV files only – one CSV file per input file; this one starts from template_SKU_Supplier_Details.csv. Keep the template's header row exactly as it is. After filling it in Excel, save it with File › Save As › CSV UTF-8 (Comma delimited).
  2. Finish SKU_Details and Supplier_Details first. Every SKU here must be in SKU_Details, and every Supplier Code must match Supplier_Details exactly.
  3. One supplier per SKU. Practical Stock Planner uses only the first row for each SKU. If you list alternates, put the preferred supplier first.
  4. SKUs with no row still get planned, with defaults: no supplier, the default lead time and MOQ 1.

Steps

  1. Copy the SKU column from your SKU_Details file into the template.
  2. Fill in Supplier Code for each SKU (see Where to find it below).
  3. Type the buying terms. Leave a cell blank to use the default shown in the table.
  4. Check for duplicates: the same SKU + Supplier Code twice, or the same Supplier Code + Supplier Part twice.

Column mapping

Practical Stock Planner columnWhat to enterBlank means
SKUYour product code, exactly as in SKU_Details– (required)
Supplier CodeThe supplier you buy this SKU from. Must match Supplier Code in Supplier_Details.Supplier shows as "Supplier TBC"
Supplier PartThe supplier's own item codeDescription, then SKU. ⚠ Two blank Supplier Parts for the same supplier may be flagged as duplicates – safest to put your own SKU here.
Supplier Part DescriptionThe supplier's descriptionYour product description
Lead Time WeeksOrder-to-receipt time, in whole weeksThe default lead time setting
Supplier UoMThe unit you buy in from your supplier, e.g. CTN for a carton"BASE EA" (you buy single items)
Supplier UoM ConversionUnits per supplier pack (carton of 12 → 12). Never 0.1
MOQ Supplier UoMMinimum order, in supplier packs1
Lot Size Supplier UoMOrder multiple above the MOQ, in supplier packs1
Price per Supplier UoMPurchase price per supplier pack, excluding sales tax = unit cost × conversion0

Where to find it in your system

SystemSupplier CodeSupplier Part / DescriptionPrice per Supplier UoM
ShopifyProduct CSV Vendor – one vendor per product; fill down on variant rows. Use the same text as the Supplier Code.Supplier SKU is on purchase-order lines and the PO PDF, with no CSV export (Creating purchase orders)Product CSV Cost per item × conversion
WooCommerceNot held – the product export has no supplier column. Type it.Not held – type itProduct CSV Cost of goods × conversion, or your supplier price list
XeroNot on the item. Inventory Item Details report: rows with Source = Payable Invoice show the supplier in the Contact column. Or check recent POs.PurchasesDescription in the Products and services CSVPurchasesUnitPrice × conversion – use the price excluding sales tax (e.g. GST or VAT)
QuickBooks OnlinePreferred supplier name on the Product/Service List reportPurchase descriptionPurchase price × conversion

None of these systems holds lead time, MOQ, lot size or supplier pack size. Type them. Xero tip: compare PO Date raised with bill dates for a rough lead time.

Shopify tip – keep buying data in Shopify: add product metafields (e.g. "Lead time weeks", "MOQ", "Carton qty") under Settings → Metafields and metaobjects → Products → Add definition. Product metafields come out in the product CSV export as extra columns named like Lead time weeks (product.metafields.custom.lead_time_weeks). ⚠ Variant metafields are not in the product CSV Using CSV files for products, so this works best when buying terms are the same for all variants of a product.

Keep leading zeros in SKUs. Format the SKU column as Text before you type or paste SKUs, then save with File › Save As › CSV UTF-8 (Comma delimited). If you open a CSV directly in Excel (e.g. by double-clicking it), Excel can strip leading zeros (00123 becomes 123) – import it via Data › From Text/CSV instead and set the SKU column to Text. In Google Sheets: select the SKU column and choose Format › Number › Plain text before you type or paste, then save with File › Download › Comma-separated values (.csv).

Checklist

  • ☐ Template header row unchanged, saved as a CSV file (File › Save As › CSV UTF-8 (Comma delimited)).
  • ☐ Every SKU is in SKU_Details; every Supplier Code is in Supplier_Details (exact match).
  • ☐ Preferred supplier first where a SKU has more than one row.
  • ☐ Supplier UoM Conversion never 0; MOQ, lot size and price in supplier packs.
  • ☐ Prices excluding sales tax.

If the upload is refused: a workbook or other non-CSV file gets “Couldn't use” followed by the file name, then “Please upload a CSV file. Practical Stock Planner imports CSV only.” Save it with File › Save As › CSV UTF-8 (Comma delimited) and upload the .csv. A file that is not UTF-8 gets “Couldn't read” followed by the file name, then “Save it as UTF-8 or UTF-8 with BOM and try again. In your spreadsheet, choose Save As > CSV UTF-8.” Save it again as CSV UTF-8 and upload the .csv.

Using Google Sheets instead of Excel? Save each file with File › Download › Comma-separated values (.csv). To keep leading zeros, select the SKU column and choose Format › Number › Plain text before you type or paste SKUs.

Questions? Contact [email protected].

Download PDF (Excel_SKU_Supplier_Details.pdf)