Introduction
In order to control the behavior of the program and to be able to create correct excel file, for each product a Configuration is assigned. It seems that for each product family one configuration can be given. One configuration can describe more than one product family. Here below we will try to describe all the configurations. Each configuration description contains the properties that needs to be given in the excel file and also the behavior of the program.
Configurations
For each configuration we enumerate the properties that needs to be given in each row of excel beside the concatenated product category and family (PRICELIST sheet column B).
Config 1
Typical product family that belongs to this configuration is Oleostatic.
Properties in Excel
- Nav code pce/sleeve/roll (PRICELIST sheet column A)
- profile (PRICELIST sheet column C)
- description ( contains the length in inch, etc..) (PRICELIST sheet column D)
- unit price (price of a belt in Euro) (PRICELIST sheet column H)
Properties on page
- profile (selection)
- description (selection, data for the chosen profile)
- quantity (number of pieces, "pieces" should be written after)
Result table columns
- The Description contains the product result family, description
- piece price = unit price from excel
- quantity
- line amount is piece price multiplied with quantity
- On stock (we find the number of pieces on stock by using Nav code from Column A for the chosen product). The unit of measure should be pce.
On stock availability
We compare the values in "quantity" and "on stock column" in the result table.
Config 2
Typical product family that belongs to this configuration is Pluriband.
Properties in Excel
- Nav code pce/sleeve/roll (PRICELIST sheet column A)
- NAVcode standard partial sleeve (PRICELIST sheet column L)
- profile (PRICELIST sheet column C)
- description (PRICELIST sheet column D)
- unit price (price of a rib in Euro) (PRICELIST sheet column H)
- sleeve size (PRICELIST sheet column K)
Properties on page
- profile (selection)
- description (selection, data for the chosen profile)
- width (number of ribs, edit box, "ribs" after the edit box)
- quantity (number of pieces, edit box)
Result table columns
- The Description contains the product result family, the rib number, description
- piece price = width (rib number) multiplied with unit price
- quantity
- line amount is piece price multiplied with quantity
- On stock. It is the total number of ribs on stock. We take the number of sleeves on stock (key is Nav code pce/sleeve/roll PRICELIST sheet column A) multiplied with sleeve size plus the number of ribs in partial sleeves (key is PRICELIST sheet column L). The unit of measure is rib.
On stock availability
We compare the values in "quantity" and "on stock column" in the result table.
Note: If the number of ribs > 10 price should be "on request"
Config 3
Typical product family that belongs to this configuration is PV and Isoran.
Properties in Excel
- Nav code pce/sleeve/roll (PRICELIST sheet column A)
- profile (PRICELIST sheet column C)
- description (PRICELIST sheet column d)
- unit price (price of a rib or mm in Euro) (PRICELIST sheet column H)
- unit of measure (rib or mm) (PRICELIST sheet column G)
- sleeve price (price of a sleeve in Euro) (PRICELIST sheet column I)
- cutCost ( can be 0 or 1. It is added width during calculation) (PRICELIST sheet column J)
- sleeveSize (a number>0) (PRICELIST sheet column K)
Properties on page
- profile (selection)
- description (selection, data for the chosen profile)
if belt
- Product type. Belt or sleeve (selection)
- width (number of ribs or mm's, edit box, "ribs" or "mm" word after edit box, the width must be less than or equal to sleeveSize). Only appears if belt is chosen.
- quantity (number of belts or sleeves, edit box, "pieces" word after edit box)
Result table columns
- The Description contains in the case of belt the c, product type (belt), description, width, the words "ribs" or "mm". In the case of sleeve : product result family, product type (sleeve), description, sleeve size from excel, "mm" or "ribs".
- piece price = rib number or mm number + cutCost multiplied with unit price if belt, sleeve price if sleeve was chosen. „on request” if belt and price unit is mm and width <6 mm
- quantity
- line amount is piece price multiplied with quantity. „on request” if belt and price unit is mm and width <6 mm
- On stock in case of belt. It is the total number of ribs or mms on stock. We take the number of sleeves on stock (key is Nav code pce/sleeve/roll PRICELIST sheet column A) multiplied with sleeve size plus the number of ribs or mms in partial sleeves (key is PRICELIST sheet column L). The unit of measure is rib or mm (taken from PRICELIST sheet column G).
- On stock in case of sleeve. It is the total number of sleeves on stock. We take the number of sleeves on stock (key is Nav code pce/sleeve/roll PRICELIST sheet column A). The unit of measure is pce.
On stock availability
We compare the values in "quantity" multiplied with given width and "on stock column" in the result table.
Config 4
Typical product family that belongs to this configuration is Rubber Open-end belt.
Properties in Excel
- Nav code pce/sleeve/roll (PRICELIST sheet column A)
- profile (PRICELIST sheet column C)
- pitch ( the value (in mm ) will be used for calculations ) (PRICELIST sheet column E)
- width (a number for standard width in unit of measure below) (PRICELIST sheet column F)
- width unit of measure (mm or inch) (PRICELIST sheet column G)
- unit price (price of a m in Euro) (PRICELIST sheet column H)
Properties on page
- profile (selection)
- width (in mm, edit box)
- pitch (not editable) we show the pitch from excel
- length ( edit box ), must be multiple of the pitch from excel, after the edit box 'mm' text should appear. If the length specified is not multiple of pitch, then we show the correct length that is the closest to multiple of pitch.
- quantity (number of belts, edit box)
Result table columns
- The Description contains the product result family, width in mm, profile, length in mm + the word "mm".
- piece price = take the standard width in mm that is >= with the width given and take the unit price (given in price/meter) of this product and multiply it with the length in mm. The length should be corrected so that is the multiple of the pitch. „on request” if standard width – witdh given < 3 mm or width < 3mm or if lenght > 49 000 mm/49 m.
- quantity
- line amount is piece price multiplied with quantity
- on stock. The number of belts on stock in meters. Unit of measure should be meters.
- Notice: the length in mm must be dividable with the pitch in mm
On stock availability
We compare the values in "quantity" multiplied with given length in m (we have to divide by 1000) and "on stock column" in the result table.
Config 5
Typical product family that belongs to this configuration is Megalinear.
Properties in Excel
- Nav code pce/sleeve/roll (PRICELIST sheet column A)
- NAVcode standard partial sleeve (PRICELIST sheet column L)
- NAVcode sleeve/roll kevlar (PRICELIST sheet column M)
- NAVcode partial sleeve kevlar (PRICELIST sheet column N)
- NAVcode roll NFT steel (PRICELIST sheet column O)
- NAVcode roll NFB steel (PRICELIST sheet column P)
- NAVcode roll NFT NFB steel (PRICELIST sheet column Q)
- NAVcode roll NFT kevlar (PRICELIST sheet column R)
- NAVcode roll NFB kevlar (PRICELIST sheet column S)
- NAVcode roll NFT NFB kevlar (PRICELIST sheet column T)
- profile (PRICELIST sheet column C)
- pitch ( the value (in mm ) will be used for calculations ) (PRICELIST sheet column E)
- width (a number for standard width in unit of measure below) (PRICELIST sheet column F)
- width unit of measure (mm or inch) (PRICELIST sheet column G)
- unit price (price of a m in Euro) (PRICELIST sheet column H)
Properties on page
- open-end belt or joined belt (selection)
- profile (selection)
- width (in mm, edit box)
- pitch (not editable) we show the pitch from excel
- length ( edit box ), must be multiple of the pitch from excel, after the edit box 'mm' text should appear. If the length specified is not multiple of pitch, then we show the correct length that is the closest to multiple of pitch.
- Cord type: 2 selectable boxes: steel cord and kevlar cord. By default the steel is to be selected. one of the two always has to be selected.
- covering (pipes, NFT and/or NFB, optional)
- quantity (number of belts, edit box)
Result table columns
- The Description contains the belt type, product result family, width in mm, profile, length in mm, word "mm", words ’steel cord or kevlar cord the one selected by the user '+NFT'if marked + '+NFB' if marked.
- piece price
= take the standard width in mm that is >= with the width given and take the unit price (given in price/meter) of this product and multiply it with the length in mm (+1000 mm if it is joined belt) + 15% for NFT (if chosen) + 15% NFB (if chosen). The length should be corrected so that is the multiple of the pitch. „on request” if standard width – witdh given < 3 mm or width < 3mm or if lenght > 49 000 mm/49 m.
If the profile is RPP then NFT should be checked and disabled by default. In this case the NFT doesn't generate extra cost.
- quantity
- line amount is piece price multiplied with quantity
- on stock. The number of belts on stock in meters. Unit of measure should be meters. It is calculated by adding together the standard length on stock (standard navcode) plus partial length( partial navcodes if exist). The steel/kevlar and NFT/NFB/NFTB settings should be considered when navcode is chosen. Partial navcodes exist only for non-NFT/NFB/NFTB setting. Steel navcodes come from column A and L. The on stock value should be showed with meter unit of measure.
- Notice: the length in mm must be dividable with the pitch in mm
On stock availability
We compare the values in "quantity" multiplied with given length in m (we have to divide by 1000) and "on stock column" in the result table.
Config 6
Typical product family that belongs to this configuration is Non-standard.
Properties in Excel
Only the FAMILIES page should contain a row with the standard cells. The discount should be 0
Properties on page
- description (edit box, must be non-empty)
- quantity (number of pieces, "pieces" should be written after)
Result table columns
- The Description contains the product result family, description
- piece price = on request
- quantity = the quantity given by the user
- line amount = on request
- on stock. It is always 0.
Config 7
Typical product family that belongs to this configuration is Megapower
Properties in Excel
- Nav code pce/sleeve/roll (PRICELIST sheet column A)
- NAVcode standard partial sleeve (PRICELIST sheet column L)
- NAVcode sleeve/roll kevlar (PRICELIST sheet column M)
- NAVcode partial sleeve kevlar (PRICELIST sheet column N)
- profile (PRICELIST sheet column C)
- description (PRICELIST sheet column D)
- unit price (price of a rib or mm in Euro) (PRICELIST sheet column H)
- price unit (rib or mm) (PRICELIST sheet column G)
- sleeve price (price of a sleeve in Euro) (PRICELIST sheet column I)
- cutCost ( can be 0 or 1. It is added width during calculation) (PRICELIST sheet column J)
- sleeveSize (a number>0) (PRICELIST sheet column K)
Properties on page
- profile (selection)
- description (selection, data for the chosen profile)
if belt
- Product type. Belt or sleeve (selection)
- Cord type: 2 selectable boxes: steel cord and kevlar cord. By default the steel is to be selected. one of the two always has to be selected.
- width (number of ribs or mm's, edit box, "ribs" or "mm" word after edit box, the width must be less than or equal to sleeveSize). This property appears on in the case when belt was chosen.
- quantity (number of belts, edit box, "pieces" word after edit box)
if sleeve
- quantity (number of sleeves, edit box, "pieces" word after edit box) if belt
Result table columns - The Description contains in the case of belt: the product type (belt), description, width, the word "mm", words „steel cord” or „kevlar cord” as selected by the costumer. In the case of sleeve : product result family, product type (sleeve), description, sleeve size from excel, word "mm", words „steel cord” or „kevlar cord” as selected by the costumer.
- piece price = rib number or mm number + cutCost multiplied with unit price if belt, sleeve price if sleeve was chosen. „on request” if belt and price unit is mm and width <6 mm
- quantity
- line amount is piece price multiplied with quantity. „on request” if belt and price unit is mm and width <6 mm
- On stock in case of belt. It is the total number of ribs or mms on stock. We take the number of sleeves on stock (key is Nav code pce/sleeve/roll PRICELIST sheet column A in the case of steel) multiplied with sleeve size plus the number of ribs or mms in partial sleeves (key is PRICELIST sheet column L in the case of steel). The unit of measure is rib or mm (taken from PRICELIST sheet column G). In the case of kevlar we use the navcodes from columns M and N.
- On stock in case of sleeve. It is the total number of sleeves on stock. We take the number of sleeves on stock (key is Nav code pce/sleeve/roll PRICELIST sheet column A if we chose steel). The unit of measure is pce. In the case of kevlar we use the navcodes from column M.
On stock availability
We compare the values in "quantity" multiplied with given width and "on stock column" in the result table.
Cut cost calculation
In the case of config 3 and 7 when belt is chosen a cutting cost is added to the price of the belt.
There are two versions of cutting cost calculations.
- X unit rule. Size proportional cutting cost. It is set by product.
- X% rule. Price proportional cutting cost. It is set by partner.
The versions can be combined in four different way, but only three of four are valid combination. These are:
- None of the versions is used. So no cutting cost is added
- Only the Size proportional cutting cost is used.
- Only the Price proportional cutting cost is used.
All the combinations can be expressed by one formula:
total price = (unit_price x width x discount(partner)) + (unit_price x cut_unit x cut_cost x discount(partner)) + min( cut_max(partner), max(cut_min(partner), unit_price x width x discount(partner) x cut_percent(partner) x cut_cost))
where: unit_price = the price of one unit (mm, rib) belt in euro
width = the width of the belt in mm or rib discount(partner) = a function that returns the discount for partner
cut_unit(partner) = a function that return the number of times the cut_cost is counted in Size proportional version. It is a non-negative number. If zero then Size proportional cutting cost is not calculated.
cut_cost(product) = the cutting cost flag for a product. It can be 0 or 1. If it is one then a cutting cost should be calculated for a product. If zero then no cost is added for the product.
cut_max(partner) = a function that returns the maximum amount of euros to pay by partner for one product as cutting cost
cut_min(partner) = a function that returns the minimum amount of euros to pay by partner for one product as cutting cost.
cut_percent(partner) = a function that returns the amount of cost to add as percentage of product price in the Price proportional version. If it zero then the Price proportional cutting cost is switched of for the partner.
I think this version is good.
OK, I started to implement it.
We have decided to optimise our cost calculation for bets cut from sleeves which we will implement from 01.01.2014. We will have to modify the webshop calculation method as well but only at config 3. We will add the price of 10% of the belt but minimum 1 EUR. It means that in case the price of a cut belt is more than 10 EUR we add 10% otherwise 1 EUR.
Config changes
There are 2 types of our products which are available with 2 versions and are very common to ask. Megapower from config 3 and Megalinear – config 5 are available with steel and with kevlar cord also and we would like to have the possibility to our costumer to order those specifications and we will also be able to show the stock availablity of these products with the following modifications. The following modifications are essential: need for a new config, config 7 for Megapower. This new config is necessary because non of the other products from config 3 (like ISORAN or RPP or PV) have the possibility of various cords. There is no price difference between the steel and the kevlar cords so it will not mix up anything regarding the pricing.
New config should be the same as config 3 with the following changes: Typical product family that belongs to this configuration is PV and Isoran. Megapower. Properties in Excel • profile • description • unit price (price of a rib or mm in Euro) • price unit (rib or mm) • sleeve price (price of a sleeve in Euro) • cutCost ( can be 0 or 1. It is added width during calculation) • sleeveSize (a number>0) Properties on page 1. belt or sleeve (selection) 2. profile (selection) 3. description (selection, data for the chosen profile) 4. Cord type: 2 selectable boxes: steel cord and kevlar cord. By default the steel is to be selected. one of the two always has to be selected. if belt 1. width (number of ribs or mm's, edit box, "ribs" or "mm" word after edit box, the width must be less than or equal to sleeveSize) 2. quantity (number of belts, edit box, "pieces" word after edit box) if sleeve 1. quantity (number of sleeves, edit box, "pieces" word after edit box) Result table columns • The Description contains in the case of belt: the product type (belt), description, width, the word "mm", words „steel cord” or „kevlar cord” as selected by the costumer. In the case of sleeve : product result family, product type (sleeve), description, sleeve size from excel, word "mm", words „steel cord” or „kevlar cord” as selected by the costumer. • piece price = rib number or mm number + cutCost multiplied with unit price if belt, sleeve price if sleeve was chosen. „on request” if belt and price unit is mm and width <6 mm • quantity • line amount is piece price multiplied with quantity. „on request” if belt and price unit is mm and width <6 mm
Similar changes are essentail at config. 5. Config 5 Typical product family that belongs to this configuration is Megalinear. Properties in Excel • profile • pitch ( the value (in mm ) will be used for calculations ) • width (a number for standard width in unit of measure below) • width unit of measure (mm or inch) • unit price (price of a m in Euro) Properties on page 1. open-end belt or joined belt (selection) 2. profile (selection) 3. width (in mm, edit box) 4. pitch (not editable) we show the pitch from excel 5. length ( edit box ), must be multiple of the pitch from excel, after the edit box 'mm' text should appear. If the length specified is not multiple of pitch, then we show the correct length that is the closest to multiple of pitch. 6. Cord type: 2 selectable boxes: steel cord and kevlar cord. By default the steel is to be selected. one of the two always has to be selected. 7. covering (pipes, NFT and/or NFB, optional) 8. quantity (number of belts, edit box) Result table columns • The Description contains the belt type, product result family, width in mm, profile, length in mm, word "mm", words ’steel cord or kevlar cord the one selected by the user '+NFT'if marked + '+NFB' if marked. • piece price = take the standard width in mm that is >= with the width given and take the unit price (given in price/meter) of this product and multiply it with the length in mm (+1000 mm if it is joined belt) + 15% for NFT (if chosen) + 15% NFB (if chosen). The length should be corrected so that is the multiple of the pitch. „on request” if standard width – witdh given < 3 mm or width < 3mm or if lenght > 49 000 mm/49 m. If the profile is RPP then NFT should be checked and disabled by default. In this case the NFT doesn't generate extra cost. • quantity • line amount is piece price multiplied with quantity • Notice: the length in mm must be dividable with the pitch in mm
The formula should changed to
total price = (unit_price x width x discount(partner)) + (unit_price x cut_unit x cut_cost x discount(partner)) + min( cut_max(partner)x cut_cost, max(cut_min(partner)x cut_cost, unit_price x width x discount(partner) x cut_percent(partner) x cut_cost))