Company Q is located in Sweden. They are exporting and importing goods from England. Their prices are calculated per shipment or loading meter according to receiver postcodes in England. The carrier also provides a 10% discount for the postcodes in Zone 1.
Pricesheet from the carrier:
Even though it often says "per loading meter" in price sheets, loading meters are usually priced for every 0.1 loading meter.
When calculating import, you have to define a "home" zone. In this example, it is the Swedish postcode 111 24.
Below, you can see how this setup is made in the Excel template. You can download the sample Excel file in the bottom of this article, replace the prices with the prices from your carrier, and add more rows. Once the Excel template has been filled in, it can be uploaded using the CPC interface. Read more about file upload here: CPC Lesson 1 - Activation and file upload
Tabs in the Excel template
The following shows the tabs in the Excel sheet that are used to configure this use case. Names of fields and columns must remain exactly as in the template for the system to read and convert the Excel sheet to XML.
PriceCalculationSet
The main settings of the file.
For a detailed explanation of each field, see CPC Excel template: PriceCalculationSet tab explained
CountryList-ZipCodeInterval
Postcode ranges are defined as ranges in the below zones, notice the home zone which must be specified for import.
Routes
Routes are defined based on the zones:
BasePrices
BasePrices are defined according to the loading meter intervals:
It is important to specify intervals correctly to avoid missing prices.
ValueFrom = included in the interval
ValueTo = up to but not included in the interval
The values in ValueFrom and ValueTo in consecutive intervals must always be identical to avoid intervals without prices.
In the price sheet, the first two intervals are priced "Per Shipment", so we can set them to a fixed price for each route. However, the next two intervals are defined as "Per loading meter", which, as previously mentioned, usually means the price will increase per every 0.1 loading meter. This is where "Steps" come in, as they can be used to define a price increase for every incremental step. The value in each step thus represents 1/10th of the loading meter price per 0.1 loading meter increase.
The price values in the 5.01 - 13.61 interval are calculated by multiplying the loading meter price by 5.1 as this is the starting point in the interval.
Discount-1
The discount of 10% is defined here.
Note that the "IndicatorType" field can be changed from "Percentage" to "Value" if the discount consists of a set value instead of a percentage.
Download sample file
The "Loadmeter" file contains all the tabs and values from the use case.