Spreasheet first version to suppress
I have to corrected the calculation of VAT
and separate B to B and B to C
Link to the spreadsheet
Description
| US (link to Github) | Descpription |
|---|---|
For one article, Quantity 1, Without Ecotax, Price without decimal point, without reduction, fixed VAT, no shipping | |
For one article, Quantity parametrable, Without Ecotax, Price without decimal point, without reduction, fixed VAT, no shipping | |
For one article, Quantity parametrable, Without Ecotax, Price without decimal point, without reduction, VAT parametrable, no shipping | |
For several items, Quantity parametrable, Without Ecotax, Price without decimal point, without reduction, VAT parametrable, no shipping | |
For several items, Quantity parametrable, Without Ecotax, Price without decimal point, without reduction, VAT parametrable, shipping including - Total Tax excluded, Tax included | |
[US-Pricing][MOC][Without rounding] : MOCCT- 006 : Cart rules amount Tax excluded without conditions - Display excluded | |
[US-Pricing][MOC][Without rounding] : MOCCT- 007 : Cart rules amount Tax excluded without conditions - Display included | |
[Lot1][US-Pricing][MOC][Without rounding] : MOCCT- 006bis : Cart rules amount Tax included without conditions - Display excluded | |
[Lot1][US-Pricing][MOC][Without rounding] : MOCCT- 007-bis : Cart rules amount Tax included without conditions - Display included | |
[Lot1][US-Pricing][MOC][Without rounding] : MOCCT- 008 : Cart rules - Code | |
[Lot1][US-Pricing][MOC][Without rounding] : MOCCT- 009 : Cart rules with conditions - Status | |
[Lot1][US-Pricing][MOC][Without rounding] : MOCCT- 010 : Cart rules percentage excluded without condition | |
[Lot1][US-Pricing][MOC][Without rounding] : MOCCT- 010 : Cart rules with conditions - Priority behavior | |
[Lot1]:[US-Pricing][MOC][Without rounding] : MOCCT - 005.1 : Taking into account specific price on Price |
Steps of the price calculation
Level 1 is the list of one or several products or value shared by all productd
Level 2 is for each product
The following calculation are this one used in the sheet. The key between this specification and the sheet
is the id Step_XX_[..]_XX
1 - Standard Price and impact price
| Level | Formula |
|---|---|
2 | |
2 | |
2 | |
2 |
2 - Discount Specific price
| Level | Formula |
|---|---|
2 | Step_Specific_price_Retail_price_tax_excluded Description : Replace the default price. Tis new price is tax excluded Formula : In the BO if Set specific price is true retrieve the value in the field Retail price (tax excl.) to set Specific_price_Retail_price_tax_excluded US : MOCCT-005.1 |
2 | Step_Unit_price_tax_excluded_after_Specific_price_discount_tax_excluded Description: Deduct the amount tax excluded from the initial price which could be the default price or the Unit_price_tax_excluded_after_Specific_price_amount_tax_excluded if it exists. Formula : if Specific_price_amount_tax_excluded is defined and the the discount of the specific price defined in the BO is excluded Unit_price_tax_excluded_after_Specific_price_amount_tax_excluded = Specific_price_Retail_price_tax_excluded - the discount of the specific price defined in the BO else if Specific_price_amount_tax_excluded is NOT defined and the the discount of the specific price defined in the BO is excluded Unit_price_tax_excluded_after_Specific_price_amount_tax_excluded = CP_By_item - the discount of the specific price defined in the BO else skip this step US : MOCCT-005.1 |
2 | Step_Unit_price_after_Retail_or_default_price_tax_included Description : Retrieve Specific_price_Retail_price_tax_excluded if exist or the default price and add the taxes to get the unit price tax included. Formula : if Specific_price_amount_tax_excluded is defined Unit_price_after_Retail_or_default_price_tax_included = Specific_price_amount_tax_excluded * (1+ (Taxes of the item/100)) else if Specific_price_amount_tax_excluded is NOT defined Unit_price_after_Retail_or_default_price_tax_included = CP_By_item * (1+ (Taxes of the item/100)) US : MOCCT-005.1 |
2 | Step_Unit_price_tax_included_after_Specific_price_discount_tax_included Description: Deduct the amount tax included from Specific_price_Retail_price_tax_excluded Formula : If the the discount of the specific price defined in the BO is included Unit_price_tax_included_after_Specific_price_discount_tax_included= Unit_price_after_Retail_or_default_price_tax_included - the discount of the specific price defined in the BO US : MOCCT-005.1 |
2 | Step_Unit_price_tax_excluded_after_Specific_price_discount_tax_included Description: Retrieve the price taxes excluded in case of specific price tax included Formula: If the discount of the specific price defined in the BO is included Unit_price_tax_excluded_after_Specific_price_discount_tax_included =Unit_price_tax_included_after_Specific_price_discount_tax_included/ (1+ (Taxes of the item/100)) US : MOCCT-005.1 |
2 | Step_Unit_price_tax_excluded_depending_on_specific_price_or_not Description: At this step depending on if there is no specific price or specific price with discount tax excluded or tax included the current variable will be set with the adapted price tax excluded. Formula: If no specific price Unit_price_tax_excluded_depending_on_specific_price_or_not = CP_by_Item else if In the BO if Set specific price is true but no specific price ase discount excluded or included Unit_price_tax_excluded_depending_on_specific_price_or_not = Specific_price_Retail_price_tax_excluded else if In the BO if Set specific price is true and there is a discount tax excluded Unit_price_tax_excluded_depending_on_specific_price_or_not = Unit_price_tax_excluded_after_Specific_price_discount_tax_excluded else if In the BO if Set specific price is true and there is a discount tax included Unit_price_tax_excluded_depending_on_specific_price_or_not = Unit_price_tax_excluded_after_Specific_price_discount_tax_included US : MOCCT-005.1 |
3 - Discount Catalog Price Rules
| Level | Formula |
|---|---|
4 - Discount - Customer Group
| Level | Formula |
|---|---|
2 | Step_Price_AfterDiscountCatalog_by_item Description: Retrieve the price HT for each product after Specific Price and/or Catalog Price Rules and/or Group Discount Price_AfterDiscountCatalog_by_item Formula : NA US: MOCCT-005.1 |
2 | Step_Calculation_item_tax_included_from_item_tax_excluded Description : Calculation of the item price tax included from item taxe excluded Formula : Price_AfterDiscountCatalog_by_item * (1+ (Taxes of the item/100)) |
5 - Discount- Cart Rules
Cart rules percent
| Level | Formula |
|---|---|
Step_Cart_rules_price_excluded_after discount_percent_excluded Description : Apply the discount taxes excluded on the precedent unit price tax excluded 1) If first cart rule Cart_rules_price_excluded_after discount_percent_excluded = Price_AfterDiscountCatalog_by_item -( Percent_of_the_discount_tax_excl/100*Price_AfterDiscountCatalog_by_item ) 2) if it exist a precedent discount Cart_rules_price_excluded_after discount_percent_excluded = Price_after_Cart_rules_*_By_Item -( Percent_of_the_discount_tax_excl/100*Price_after_Cart_rules_*_By_Item) US : MOCCT_010 | |
Step_Cart_rules_price_Taxe_included_before_discount Description : Price with TVA on the last price taxe excluded or discount taxe excluded Formula : 1) If first cart rule Cart_rules_price_Taxe_included = Price_AfterDiscountCatalog_by_item * ((1+ (Taxes of the item/100)) 2) if it exist a precedent discount Cart_rules_price_Taxe_included = Price_after_Cart_rules_*_By_Item * ((1+ (Taxes of the item/100)) US : MOCCT_010 | |
Step_Cart_rules_price_included_after_discount_percent_included Description : New price taxes included after Cart rules discount percent included Formula : Cart_rules_price_included_after_discount_percent_included = Cart_rules_price_Taxe_included - Percent of the discount/100*Cart_rules_price_Taxe_included_before_discount US : MOCCT_010 | |
Step_Cart_rules_price_tax_included_after_discount_percent included_convert_price_tax excluded Description : Cart rules discount Percent taxes included - price taxe excluded deduct from price tax included Formula Cart_rules_price_tax_included_after_discount_percent included_convert_price_tax excluded = Cart_rules_price_included_after_discount_percent_included /((1+ (Taxes of the item/100)) US : MOCCT_010 | |
Step_Cart_rules_price_tax_excluded_after discount_percent_excluded_mult_quantity Description : Sum of Price taxes excluded after discount multiplied by quantity Formula : ∑ (Cart_rules_price_excluded_after discount_percent_excluded * Quantity of the item) US : MOCCT_010 | |
Step_Cart_rules_price_tax_included_after_discount_percent included_mult_quantity Description : Sum of Price taxes included after discount multiplied by quantity Formula : ∑ (Cart_rules_price_included_after_discount_percent included * Quantity of the item) US : MOCCT_010 | |
Step_Price_taxe_excluded_after_Cart_rules Description : Unit price tax excluded depending on alternative (percent excluded or included) Formula if display excluded retrieve Cart_rules_price_tax_excluded_after discount_percent_excluded else retrieve Cart_rules_price_tax_included_after_discount_percent included_convert_price_tax excluded US : MOCCT_010 |
Cart rules amount
| Level | Formula |
|---|---|
2 | Step_Cart_rules_amount_excluded_By_Item_step1 Description: Calculation of the new price of chart after a discount amount Tax excluded Step 1 consist to Calculation of the prorata of the discount on each product Formula: 1) If first cart rule Cart_rules_amount_excluded_By_Item_step1 = CP_Q_by_item / SUM(CP_Q_by_item )*Amount of the discount 2) if it exist a precedent discount Cart_rules_amount_excluded_By_Item_step1 =(Precedent Prix reduit * Quantity of the product)/ SUM(Precedent Prix reduit * Quantity of the product)*Amount of the discount. US: MOCCT-006 |
2 | Step_Cart_rules_amount_excluded_By_Item_step2 Description:Deduct the prorata of cart rules from Price of the product Formula: Cart_rules_amount_excluded_By_Item_step2= (Price_AfterDiscountCatalog_by_item-Cart_rules_amount_excluded_By_Item_step1)/Quantity of the product US: MOCCT-006 |
2 | Step_Cart_rules_amount_excluded_By_Item_prorata_display_included Description:Prorata of the discount on each product * quantity Cart_rules_amount_excluded_By_Item_prorata_display_included = CP_Q_by_item / Sum ( CP_Q_by_item) on each item *(amount of the discount taxes excluded )*((1+ (Taxes of the item/100)) US : MOCCT_007 |
2 | Step_Cart_rules_amount_excluded_By_Item_unit_price_display_included Description : Unit price after application of prorata on the price for display included (B to B) Formula : Cart_rules_amount_excluded_By_Item_step2 *((1+ (Taxes of the item/100)) US : MOCCT_007 |
2 | Step_Cart_rules_amount_excluded_By_Item_prorata_display_included Description : For now retrieve Cart_rules_amount_excluded_By_Item_unit_price_display_included Formula : US : MOCCT-007 |
Step_Price_after_Cart_rules_amount_By_Item Description : Retrieve the price HT after the last discount. Formula : Price_after_Cart_rules_amount_By_Item =
Cart_rules_amount_excluded_By_Item_step2
US : MOCCT-006 | |
Step_Cart_rules_amount_included_By_Item_prorata_display_included Description : If the discount is "Amount" "Taxe included" Add taxes to price without taxes Formula : Cart_rules_amount_included_By_Item_prorata_display_included=Retrieve the last unit price HT * (1+ (Taxes of the item/100) US : MOCCT-006bis | |
Step_Cart_rules_amount_included_mult_quantity Description : If the discount is "Amount" "Taxe included" Unit price with taxes multiply by quantity Formula : Cart_rules_amount_included_mult_quantity = Cart_rules_amount_included_By_Item_prorata_display_included * Quantity of the item US : MOCCT-006bis | |
2 | Step_Cart_rules_amount_included_By_Item_prorata_discount_display_included Description : If the discount is "Amount" "Taxe included" Prorata of the discount amount on each article Formula : Cart_rules_amount_included_By_Item_prorata_discount_display_included = Cart_rules_amount_included_By_Item_prorata_display_included / Sum ( Cart_rules_amount_included_mult_quantity ) on each item *(amount of the discount taxes excluded ) US : MOCCT-007bis |
2 | Step_Cart_rules_amount_included_prorata_discount_multiply_by_quantity Description : If the discount is "Amount" "Taxe included" Formula : Cart_rules_amount_included_prorata_discount_multiply_by_quantity = Cart_rules_amount_included_By_Item_prorata_discount_display_included * Quantity of the product US : MOCCT-007bis |
2 | Step_Cart_rules_amount_included_price_after_prorata_display_included Description : If the discount is "Amount" "Taxe included" Formula : Cart_rules_amount_included_By_Item_prorata_display_included - Cart_rules_amount_included_By_Item_prorata_discount_display_included US : MOCCT-007bis |
2 | Step_Cart_rules_amount_taxes_included_convert_HT_mult_by_quantity Description :If the discount is "Amount" "Taxe included" conversion HT (each article * quantity) Formula : Cart_rules_amount_taxes_included_convert_HT_mult_by_quantity = Cart_rules_amount_included_By_Item_prorata_discount_display_included * (1+ (Taxes of the item/100) * quantity of the product US : MOCCT-007bis |
2 | Step_Cart_rules_Amount_display_taxes_excluded Description: Using for the display of the discount in the chart tax excluded Formula: if the discount is "Amount" and "Taxes excluded" retrieve Cart_rules_amount_excluded_By_Item_step1 else if the discount is "Amount" and "Taxes included" retrieve Cart_rules_amount_taxes_included_convert_HT_mult_by_quantity else 0 US:MOCCT-006bis |
2 | Step_Cart_rules_Amount_display_taxes_included Description: Using for the display of the discount in the chart tax included Formula: if the discount is "Amount" and "Taxes excluded" retrieve Cart_rules_amount_excluded_By_Item_prorata_display_included else if the discount is "Amount" and "Taxes included" retrieve Cart_rules_amount_included_prorata_discount_multiply_by_quantity else 0 US:MOCCT-007bis |
2 | Step_Price_after_Cart_rules_amount_By_Item Description : Retrieve the unit price taxes excluded after discount Cart_rules_amount_excluded_By_Item_step2 if amount different of 0 else last unit price without taxes Formula : if amount excluded retr US : MOCCT-006 |
2 | |
1 |
5 - VAT
| Level | Formula |
|---|---|
2 | |
2 | |
1 |
6 - Retrieve cost of Shipping
| Level | Formul |
|---|---|
1 | Step_Shipping_Tax_excluded Description: Retrieve the shipping tax excluded (Shipping_tax_excluded) Formula: NA US: MOCCT_005 |
8 - Display of the chart tax excluded for B to B
| Level | Formula |
|---|---|
1 | Step_Discount_Cart_rule_1_Tax_excluded Description: Using for the display of the discount in the chart tax excluded in case of discount percent tax excluded Formula: 1) If first cart rule Discount_Cart_rule_1_Tax_excluded = -1 * (∑ Price_AfterDiscountCatalog_by_item for each item - ∑ Cart_rules_price_tax_excluded_after_discount_percent_excluded_mult_quantity for each item) 2) if it exist a precedent discount = -1 * (∑ Cart rules price tax excluded after discount_XX * quantity for each item - ∑ Cart_rules_price_tax_excluded_after_discount_percent included_mult_quantity) US-MOCCT-010 |
1 | Step_Discount_Cart_rule_3_Tax_excluded Description :Description: Using for the display of the discount in the chart tax excluded Formula : Sum for each item Step_Cart_rules_Amount_display_taxes_excluded US : MOCCT_007bis |
1 | Step_Sum_Cart_rules_discount_Tax_excluded Description : Calculation of the total of discount tax excluded Formula: Sum of each Step_Discount_Cart_ruleX_Tax_excluded US : MOCCT_007bis |
1 | Step_Taxes_excluded_with_shipping_cost_excluded_total Description: Calculation of the total tax excluded adding shipping cost excluding Formula: Total_tax_excluded_with_shipping_cost_excluded=Shipping_tax_excluded + Taxes_excluded_Total US: MOCCT_005 |
1 |
8 - Display of the chart tax included
| Level | Formula |
|---|---|
1 | Step_Discount_Cart_rule_1_Tax_excluded Description: Using for the display of the discount in the chart tax excluded in case of discount percent tax excluded Formula: 1) If first cart rule Discount_Cart_rule_1_Tax_excluded = -1 * (∑ Price_AfterDiscountCatalog_by_item for each item - ∑ Cart_rules_price_tax_excluded_after_discount_percent_excluded_mult_quantity for each item) 2) if it exist a precedent discount = -1 * (∑ Cart rules price tax excluded after discount_XX * quantity for each item - ∑ Cart_rules_price_tax_excluded_after_discount_percent included_mult_quantity) US-MOCCT-010 |
1 | Step_Discount_Cart_rule_3_Tax_excluded Description :Description: Using for the display of the discount in the chart tax excluded Formula : Sum for each item Step_Cart_rules_Amount_display_taxes_excluded US : MOCCT_007bis |
1 | Step_Sum_Cart_rules_discount_Tax_excluded Description : Calculation of the total of discount tax excluded Formula: Sum of each Step_Discount_Cart_ruleX_Tax_excluded US : MOCCT_007bis |
1 | Step_Taxes_excluded_with_shipping_cost_excluded_total Description: Calculation of the total tax excluded adding shipping cost excluding Formula: Total_tax_excluded_with_shipping_cost_excluded=Shipping_tax_excluded + Taxes_excluded_Total US: MOCCT_005 |
1 | |
1 | Step_Shipping_Tax_included Description: Retrieve the shipping tax included ( Shipping_tax_included) Formula: NA US: MOCCT_005 |
1 | Step_Discount_Cart_rule_1_Tax_included Description : Using for the display of the discount in the chart tax included in case of discount percent tax included Formula: 1) If first cart rule Discount_Cart_rule_1_Tax_excluded = -1 * (∑ (Price_AfterDiscountCatalog_by_item * (1+ (Taxes of the item/100)) for each item) - ∑ Cart_rules_price_tax_included_after_discount_percent included_mult_quantityfor each item) 2) if it exist a precedent discount = -1 * (∑ Cart rules price tax included after discount_XX * quantity for each item - ∑ Cart_rules_price_tax_included_after_discount_percent included_mult_quantity) US-MOCCT-010 |
1 | Step_Discount_Cart_rule_3_Tax_included Description : Using for the display of the discount in the chart tax included Formula : -1 * Sum for each item Step_Cart_rules_Amount_display_taxes_included US : MOCCT_006bis, US-MOCCT-010 |
1 | Step_Sum_Cart_rules_discount_Tax_included Description : Calculation of the total of discount taxes included Formula: Sum of each Step_Discount_Cart_ruleX_Tax_included US : MOCCT_006 |
1 |
Rules of calculation for cart rules
During the process of pricing you have to check Edit or add Cart Rule for following rules
MR_Pricing_calc_001 : If field Code in cart rules is blank, the rule will automatically be applied to benefiting customers. (MOCCT-008)
MR_Pricing_calc_002 : If field Code in cart rules is filled, the discount will be apply only if the code has been added in the chart.(MOCCT-008)
MR_Pricing_calc_003 : Status. Apply the cart rules if Status is "Yes".If Status is "No" the cart Rule is not applied (MOCCT-009)
MR_Pricing_calc_004 : Priority behavior (MOCCT-011)
MR_Pricing_calc_005 : Partial use The price of an item after prorata discount for an amount could not be lower than 0.
Amount of the second voucher :
If the amount discount is included : sum of product prices taxes included before discount - Sum of products prices taxes included after discount.
If the amount discount is excluded : sum of product prices taxes excluded before discount - Sum of products prices taxes excluded after discount.
Last updated
