Stock Management (Franchisor and Flightdeck)

Relates specifically to Yale Security Point

Product information will be maintained by the customer on an xls spreadsheet.

The structure of first sheet of this xls will be as follows:
Row 1 contains headings. Other rows contain data. Columns are:

Category Char(50) Category of product (if any)
Application Char(30) Main application for product (if any)
Manufacturer Char(30) Product manufacturer (if any)
YSP Code Char(16) YSP unique product code
Manufacturer Code Char(20) Manufacturers product code
Description Char(100) Product description
Image Char(20) Filename (not path) of image file (if any)
Lock Size Char(20) Dimensions of product (if any)
Lock Finish Char(10) Code of lock finish (if any)
Cylinder Finish Char(10) Code of cylinder finish (if any)
List Currency List RRP of product
Mins to Fit Integer Average fitting time in mins (as appropriate)
Discount Float YSP discount from manufacturer’s RRP
Buy Currency Nett YSP buy price from manufacturer
Margin Float Margin (% of RRP) retained by YSP
Sell Currency Nett franchisee buy price from YSP
Min Stock Level Integer Minimum stock level per franchisee
Reorder Level Integer Order when stock reaches this level
Minimum order quantity Integer Must order at least this quantity from supplier

Mandatory fields will be Category, Manufacturer, YSP Code, Manufacturer Code, Description, List, Discount, Buy, Margin, Sell.
Products will be flagged as no longer available in existing quotes (and masked when adding new quote items) if not in a product update.

The structure of second sheet of this xls will be as follows:
Column A contains variable names. Column B contains values. Rows are:

Database Version (e.g. “1.01”) – Version of product database
Release Date (e.g. “26/07/10”) – Date released
VAT Rate (e.g. “20”) – VAT rate applicable to products
Min Labour Rate (e.g. “60”) – Min hourly rate a franchisee can charge
Max Labour Rate (e.g. “85”) – Max hourly rate a franchisee can charge
Out of Hours (e.g. 15) – Premium (%) for out of hours fitting
Bank Holiday (e.g. 20) – Premium (%) for public holiday fitting
Unsocial (e.g. 60) – Premium (%) for Xmas & New Year fitting
The release process will involve:
Franchisor creates (updates) this xls in a Windows folder
Franchisor puts Product Database details into cells on sheet2 of xls
Franchisor puts (updates) relevant images (JPG) into folder along with xls
A menu option to import products into Flightdeck will ask the franchisor to navigate to the xls
Flightdeck will produce a CSV file as specified below to import Product Details to Prelude Desktop
Franchisor provides variant of xls to franchisees with Manufacturer, Manufacturer Code, Discount, Buy, Margin, Sell, Min Stock Level and Reorder Level removed.
Franchisees don’t need the remainder in the field (or at all).
Franchisee uses menu option in QuotePoint to import new Product Database.