I have recently been introduced to a basic but nice problem that involves pricing a product and transport productions. This is a basic cost and revenue analysis problem that consists of defining and analyzing a delivered price, which includes all production, logistics, and duty charges, for an international customer while considering a certain gross margin on all products.
Although this problem can be solved in classic basic math operations, a functional programming solution works much better as that makes the calculations much tidier and less repetitive. The classical solution was my first approach to set the equations in functions, so it must not be underestimated. I decided to work in the R environment because:
I feel more confident when I write scripts in R.
The analysis can easily turn into interactive visualizations and apps with GUI.
On the other hand, another favorite tool, MS Excel, could be quite sufficient for this analysis as well. These programs are the medium, so feel free whatever you choose.
- The problem task
- Revenue and cost analysis
- Price breaks
- Possible scenarios
- Transit time and customer production
- Shiny App
The problem task
Unit cost: $10/kg
6.5% duty charge on sales price
Batch size (kg): min(480) / max(2000)
If batch size equals to
2000kg, additional %5 cost savings in logistics
Costs of containers: 20FT =
$4000/ 40FT =
Recommended sales prices. Keep costs and gross margins in mind.
Goals + Min 5% gross margin on product price + Min 40% gross margin on all logistics costs + A happy customer. They want: - 500 kg per month for the first 6 months. So they use 3000kg in 6 months - Ramp
2500 in the first 6 months
Extra info + Product should be stored and shipped at 0C - Both containers have generators so this term is not included in the problem + Product has 12 months shelf life
First of all, we should find out how many kg a pallet can carry. According to the information, a EURO pallet holding
36 boxes has a gross weight of
588kg. That brings us
15kg x 36 = 540kg so the net pallet weight is
48kg. With the same calculation, the STANDARD net pallet weight returns
Two types of shipping containers are available. First, a 20FT container having a cost of
$4000 can carry 10 EURO pallets plus 2 floor-loaded EURO pallets, or 9 STANDARD pallets plus 1 floor-loaded STANDARD pallet. Second, a 40FT container having the cost of
$6000 can carry 23 EURO pallets plus 1 to 1,5 floor-loaded EURO pallets, or 20 STANDARD pallets plus 1 to 1,5 floor-loaded STANDARD pallets. Both containers have a max gross weight of 18.000 kg; in that sense, a 40FT container looks like a better option for the type of products requiring a high available volume.
As our batch range is between 480kg and 2000kg, and the customer cannot ramp more than 2500kg/mo, a 20FT container obviously has a better fitting capacity with a better price than the 40FT. A 20FT container’s cost is
$4000, and our transport cost is equal to the container cost, assuming there are no other costs related to it. So all the capacity calculations are done by considering the 20FT container.
The first function,
weight_f(), is composite with the
batch() function (please see below). In the
weight_f() function, you enter
n max. number of boxes a pallet can carry, and
p pallet’s net weight. It returns multiple outputs of the following:
DecimalBoxNumber: The product is packed per 15kg net weight boxes. As our input
zis kg, we have to know how many boxes we will have (in decimal numbers).
NetBoxNumber: Like the decimal box number, here we have to know exactly how many boxes we will have. We make an integer division of
zinput by a box’s net weight.
NumberPallet: Gives the info on how many pallets we will have (in decimal numbers) based on the net box number we have.
TotalPalletWeight: The weight of a stacked (or non-stacked) pallet, consisting of the net pallet weight and number of boxes.
NetProductWeight: The result of multiplying the net box number by a package net weight (15kg).
TotalWeight: The sum of the net product weight and total pallet weight.
batch() function is the main one we should run in our analysis, not the
weight_f(). Please inspect both functions to understand better.
Second, our main function,
batch(), which is a composite function with the
weight_f(), calculates the net and gross weights based on the pallet type. In the
batch() function, you enter
z kg and
"STANDARD" pallet type. It returns the output of
We know that the EURO pallet can holds 36 boxes and has a
48kg net pallet weight. Secondly, the STANDARD pallet holds 48 boxes and has a
58kg net pallet weight. By default,
batch() function has the input of the number of boxes a pallet can carry (
n) and a pallet net weight (
p) that are derived from the
Additionally, we learned from the output of
batch(), when looking at the
TotalPalletWeight section, a STANDARD pallet has less weight and carries more than a EURO pallet. That is great to know, especially when we want to carry more and have a less gross weighted container.
Revenue and cost analysis
finance() mainly returns unit, transport costs and margins, duty charge (
6.5%), subtotal cost, selling and cost per kg. Meanwhile, 5% cost saving is omitted because we cannot reach that amount in a batch due to the total number of net boxes fitting in a pallet. In the
finance() function, you enter
z net kg, which is the number of
NetProductWeight you obtained from
batch() function. It returns multiple outputs of the following:
UnitCost: The result of multiplying the net product weight by the cost of unit (
UnitSelling: The selling price of the product at min. 5% margin level (all other costs and prices are omitted).
TransportCost: The container cost of
TransportSelling: The selling price of the transport at min. 40% margin level (all other costs and prices are omitted).
SubTotalSelling: As there is a 6.5% duty charge (customs tax) based on the sales prices, we add this percent to our selling price to keep the margins profitable.
CostPerKg: The sum of unit and transport costs divided by net product weight. Remember that this is only the cost, before the selling price with margins added.
SellingPerKg: Total selling price divided by net product weight.
Please inspect the equations in the function below.
So these price breaks are not discounts; they are basically cost analysis from calculating the price of unit cost per kg at a specific kg delivery, i.e. what is the unit/kg cost price at 540kg, 2000kg, etc. After this function constructing process, we define the quantities we should offer the customer. This section includes logical price breaks both in batches and finances. Finance parts give the calculations for
SellingPerKg, and other finance details for these batches. Cost and selling per kg output can especially be the main indicator for our profitability.
The net weights for the EURO pallets are
## Input PalletType DecimalBoxNumber NetBoxNumber NumberPallet ## 1 540 EURO 36 36 1 ## 2 1080 EURO 72 72 2 ## 3 1620 EURO 108 108 3 ## TotalPalletWeight NetProductWeight TotalWeight ## 1 48 540 588 ## 2 96 1080 1176 ## 3 144 1620 1764
## Input UnitCost UnitSelling TransportCost TransportSelling ## 1 540 5400 5670 4000 5600 ## 2 1080 10800 11340 4000 5600 ## 3 1620 16200 17010 4000 5600 ## SubTotalSelling TotalSelling CostPerKg SellingPerKg ## 1 11270 12002.55 17.40741 22.22694 ## 2 16940 18041.10 13.70370 16.70472 ## 3 22610 24079.65 12.46914 14.86398
The net weights for the STANDARD pallets are
## Input PalletType DecimalBoxNumber NetBoxNumber NumberPallet ## 1 720 STANDARD 48 48 1 ## 2 1440 STANDARD 96 96 2 ## TotalPalletWeight NetProductWeight TotalWeight ## 1 58 720 778 ## 2 116 1440 1556
## Input UnitCost UnitSelling TransportCost TransportSelling ## 1 720 7200 7560 4000 5600 ## 2 1440 14400 15120 4000 5600 ## SubTotalSelling TotalSelling CostPerKg SellingPerKg ## 1 13160 14015.4 15.55556 19.46583 ## 2 20720 22066.8 12.77778 15.32417
The chart below illustrates the
UnitPerCost change between the
2000kg range in the log10 transformation. It clearly concludes that
UnitPerCost decreases while the total
kg in a batch increases (obviously, it is due to the transport costs).
We want to fill the container as much as possible (and as much as makes sense). The most logical possible scenarios are the following:
Below, we find the possible scenarios for the batch kgs.
PalletType column excludes them as it is a character that does not work with
540kg batch with EURO pallet +
1620kg batch with EURO pallet (a total of
## Input DecimalBoxNumber NetBoxNumber NumberPallet ## 2160 144 144 4 ## TotalPalletWeight NetProductWeight TotalWeight ## 192 2160 2352
## Input UnitCost UnitSelling TransportCost TransportSelling ## 1 2160 21600 22680 4000 5600 ## SubTotalSelling TotalSelling CostPerKg SellingPerKg ## 1 28280 30118.2 11.85185 13.94361
1620kg batch with EURO pallet +
720kg batch with STANDARD pallet (a total of
## Input DecimalBoxNumber NetBoxNumber NumberPallet ## 2340 156 156 4 ## TotalPalletWeight NetProductWeight TotalWeight ## 202 2340 2542
## Input UnitCost UnitSelling TransportCost TransportSelling ## 1 2340 23400 24570 4000 5600 ## SubTotalSelling TotalSelling CostPerKg SellingPerKg ## 1 30170 32131.05 11.7094 13.73122
1080kg batch with EURO pallet +
1440kg batch with STANDARD pallet (a total of
## Input DecimalBoxNumber NetBoxNumber NumberPallet ## 2520 168 168 4 ## TotalPalletWeight NetProductWeight TotalWeight ## 212 2520 2732
## Input UnitCost UnitSelling TransportCost TransportSelling ## 1 2520 25200 26460 4000 5600 ## SubTotalSelling TotalSelling CostPerKg SellingPerKg ## 1 32060 34143.9 11.5873 13.54917
Transit time and customer production
The customer informed us that they are going to use
500kg/mo for the first six months, and they can ramp
2500kg/mo within the next six months. As we would like to send
2000kg, we make the following order. So if we send out the first batch on
November 4th, it will take
40 days, and they will get it on
December 7th with a
2000 kg of stock. As they use the product
March 7th they will have the last
500kg. On that basis, if we send a new batch on
March 5th, they will get it on
April 7th. By using this solution, their production will not be halted due to the stock running out, and the product shelf life (12 months) will not expire.
The process is illustrated in a Gantt chart below. On the left legend,
batch_status refers to the 40 days of travelling time. The green color indicates the amount in the batch we sent, red numbers show the stock of out customer, travelling time is calculated as calendar days, and the customer’s month is calculated in 30 consecutive days.
The tables below illustrate the alternative solutions for the task. As seen below, A and B scenarios look the most convenient in what way we should fill the container. The net product weight in Scenario C slightly exceeds the maximum kg (20kg) that a customer can ramp in a month, but it could be considered.
Batch and Finance
B/S Batch/Scenario, P Pallet Type, NP Number Pallet, NBN Net Box Number, TW Total Weight, TS Total Selling, CPK Cost Per Kg, SPK Selling Per Kg
It is possible to combine these two functions into one. It’s certainly useful to develop the Shiny app. Please check the source code in my GitHub repository.
The composite function of
finance() is the following,
Then it will give the following results for these examples:
The function returns 1521 rows of data, as we can only batch min. 480 and max. 2000.
I join these two data frames because I would like to order the rows following one from another to increase the human-readability of the text. It should look like:
##Input | PalletType ... ##480 | EURO ... ##480 | STANDARD ... ##481 | EURO ... ##481 | STANDARD ... ##...
The length of the data frame is 1521, resulting from the
And we reset the row names of the data frame as it returned them unordered.
After we create, clean and transform our data, we export it as a
.csv file into the directory to make it ready to import into our Shiny app, exporting data as
pricing_data.csv. We use
write_csv() function from readr package, instead of
write.csv(), because it is about twice as fast as write.csv.
See what we have in our data:
## 'data.frame': 3042 obs. of 17 variables: ## $ Input : int 480 480 481 481 482 482 483 483 484 484 ... ## $ PalletType : Factor w/ 2 levels "EURO","STANDARD": 1 2 1 2 1 2 1 2 1 2 ... ## $ DecimalBoxNumber : num 32 32 32.1 32.1 32.1 ... ## $ NetBoxNumber : num 32 32 32 32 32 32 32 32 32 32 ... ## $ NumberPallet : num 0.889 0.667 0.891 0.668 0.893 ... ## $ TotalPalletWeight: num 42.7 38.7 42.8 38.7 42.8 ... ## $ NetProductWeight : num 480 480 480 480 480 480 480 480 480 480 ... ## $ TotalWeight : num 523 519 523 519 523 ... ## $ Input.1 : num 480 480 480 480 480 480 480 480 480 480 ... ## $ UnitCost : num 4800 4800 4800 4800 4800 4800 4800 4800 4800 4800 ... ## $ UnitSelling : num 5040 5040 5040 5040 5040 5040 5040 5040 5040 5040 ... ## $ TransportCost : num 4000 4000 4000 4000 4000 4000 4000 4000 4000 4000 ... ## $ TransportSelling : num 5600 5600 5600 5600 5600 5600 5600 5600 5600 5600 ... ## $ SubTotalSelling : num 10640 10640 10640 10640 10640 ... ## $ TotalSelling : num 11332 11332 11332 11332 11332 ... ## $ CostPerKg : num 18.3 18.3 18.3 18.3 18.3 ... ## $ SellingPerKg : num 23.6 23.6 23.6 23.6 23.6 ... ## NULL
- The following function was written in the beginning. It would be useful if the container size and cost were taken into serious consideration. However, using a 20ft container is more reasonable in the given data, so the selection of container is omitted.
- The error messages are declined in the later development of the functions because of the reactive structure of the Shiny app.
– FIN –