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:

  1. I feel more confident when I write scripts in R.

  2. 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.

Chapters

The problem task

Fixed terms

  1. Unit cost: $10/kg

  2. 6.5% duty charge on sales price

  3. Batch size (kg): min(480) / max(2000)

  • If batch size equals to 2000 kg, additional %5 cost savings in logistics

  • Costs of containers: 20FT = $4000 / 40FT = $6000

Questions

  1. Production
  • Which quantities?

  • Recommended sales prices. Keep costs and gross margins in mind.

  1. Logistics

  2. Customs charges

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

Capacity

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 58kg.

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 z kg, 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 z is 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 z input 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.

#Input:
#z: global variable (kg)
#n: number of boxes in a pallet
#p: pallet net weight
#pallet_type: type of the pallet
weight_f <- function(z, n, p, pallet_type) {
  decimal_box_number = z / 15 #a box net weight
  net_box_number = z %/% 15 #rounding to an integer
  number_pallet = decimal_box_number / n
  total_pallet_weight = number_pallet * p
  net_product_weight = net_box_number * 15
  total_weight = net_product_weight + total_pallet_weight
  output = list(Input = z,
                PalletType = pallet_type,
                DecimalBoxNumber = decimal_box_number,
                NetBoxNumber = net_box_number,
                NumberPallet = number_pallet,
                TotalPalletWeight = total_pallet_weight,
                NetProductWeight = net_product_weight,
                TotalWeight = total_weight)
  return(output)
}

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 "EURO" or "STANDARD" pallet type. It returns the output of weight_f() function:

#Input:
#z: global variable (kg)
#pallet_type: type of the pallet
batch <- function(z, pallet_type) {
  if(pallet_type == "EURO") {
    output = weight_f(z, 36, 48, pallet_type)
    return(output)
  }
  if(pallet_type == "STANDARD") {
    output = weight_f(z, 48, 58, pallet_type)
    return(output)
  }
}

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 weight_f() function.

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

The function 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 ($10).

  • UnitSelling: The selling price of the product at min. 5% margin level (all other costs and prices are omitted).

  • TransportCost: The container cost of $4000.

  • 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.

#Input:
#z: batch net kg (mainly: NetProductWeight)
finance <- function(z) {
  unit_cost = z * 10
  unit_selling = unit_cost + (unit_cost*5.00)/100
  transport_cost = 4000
  transport_selling = transport_cost + (transport_cost*40.00)/100
  subtotal_selling = unit_selling + transport_selling
  total_selling = subtotal_selling + (subtotal_selling*6.5)/100 #after duty charge
  cost_per_kg = (unit_cost + transport_cost) / z
  selling_per_kg = total_selling / z
  output = list(Input = z,
                UnitCost = unit_cost,
                UnitSelling = unit_selling,
                TransportCost = transport_cost,
                TransportSelling = transport_selling,
                SubTotalSelling = subtotal_selling,
                TotalSelling = total_selling,
                CostPerKg = cost_per_kg,
                SellingPerKg = selling_per_kg)
  return(output)
}

Price breaks

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 CostPerKg, 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 540kg, 1080kg and 1620kg:

be.n = c(540, 1080, 1620)

Batch details:

(be_batch_df <- do.call(rbind, lapply(be.n, function (x) data.frame(batch(x, "EURO")))))
##   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

Finance details:

(be_finance_df <- do.call(rbind, lapply(be.n, function (x) data.frame(finance(x)))))
##   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 720kg and 1440kg:

bs.n = c(720, 1440)

Batch details:

(bs_batch_df <- do.call(rbind, lapply(bs.n, function (x) data.frame(batch(x, "STANDARD")))))
##   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

Finance details:

(bs_finance_df <- do.call(rbind, lapply(bs.n, function (x) data.frame(finance(x)))))
##   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 480kg and 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).

Possible scenarios

We want to fill the container as much as possible (and as much as makes sense). The most logical possible scenarios are the following:

Scenario A

Below, we find the possible scenarios for the batch kgs. PalletType column excludes them as it is a character that does not work with colSums().

be_batch_df$PalletType = NULL
bs_batch_df$PalletType = NULL

540kg batch with EURO pallet + 1620kg batch with EURO pallet (a total of 2160kg):

(A_batch <- colSums(data.frame(be_batch_df[(be_batch_df$Input) %in% c(540, 1620), ])))
##             Input  DecimalBoxNumber      NetBoxNumber      NumberPallet
##              2160               144               144                 4
## TotalPalletWeight  NetProductWeight       TotalWeight
##               192              2160              2352

Finance details:

(A_batch_finance <- data.frame(finance(A_batch[["NetProductWeight"]])))
##   Input UnitCost UnitSelling TransportCost TransportSelling
## 1  2160    21600       22680          4000             5600
##   SubTotalSelling TotalSelling CostPerKg SellingPerKg
## 1           28280      30118.2  11.85185     13.94361

Scenario B

1620kg batch with EURO pallet + 720kg batch with STANDARD pallet (a total of 2340kg):

(B_batch <- colSums(rbind.data.frame((be_batch_df[ (be_batch_df$Input) %in% c(1620), ]),
                               bs_batch_df[ (bs_batch_df$Input) %in% c(720), ])))
##             Input  DecimalBoxNumber      NetBoxNumber      NumberPallet
##              2340               156               156                 4
## TotalPalletWeight  NetProductWeight       TotalWeight
##               202              2340              2542

Finance details:

(B_batch_finance <- data.frame(finance(B_batch[["NetProductWeight"]])))
##   Input UnitCost UnitSelling TransportCost TransportSelling
## 1  2340    23400       24570          4000             5600
##   SubTotalSelling TotalSelling CostPerKg SellingPerKg
## 1           30170     32131.05   11.7094     13.73122

Scenario C

1080kg batch with EURO pallet + 1440kg batch with STANDARD pallet (a total of 2520kg):

(C_batch <- colSums(rbind.data.frame((be_batch_df[ (be_batch_df$Input) %in% c(1080), ]),
                                     bs_batch_df[ (bs_batch_df$Input) %in% c(1440), ])))
##             Input  DecimalBoxNumber      NetBoxNumber      NumberPallet
##              2520               168               168                 4
## TotalPalletWeight  NetProductWeight       TotalWeight
##               212              2520              2732

Finance details:

(C_batch_finance <- data.frame(finance(C_batch[["NetProductWeight"]])))
##   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 500kg/mo, on 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.

Conclusion

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

B/S P NP NBN TW TS CPK SPK
540 E 1 36 588 12.002,55 17,407 22,226
1080 E 2 72 1176 18.041,10 13,703 16,704
1620 E 3 108 1764 24.079,65 12,469 14,863
720 S 1 48 778 14.015,4 15,555 19,465
1440 S 2 96 1556 22.066,8 12,777 15,324
A E 4 144 2352 30.118,2 11,851 13,943
B E+S 4 156 2542 32.131,05 11,709 13,731
C E+S 4 168 2732 34.143,9 11,587 13,549

Shiny App

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 batch() and finance() is the following, pricing():

#Input:
#z: global variable kg
#pallet_type: type of the pallet
pricing <- function(z, pallet_type) {
  batch_details = batch(z, pallet_type)
  npw <- batch(z, pallet_type)$NetProductWeight
  finance_details = finance(npw)
  output = list(batch_details,
                finance_details)
  return(output)
}

Then it will give the following results for these examples:

pricing(2000, "EURO")
pricing(2000, "STANDARD")

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 nrow(pricing_euro_df).

pricing_df <- do.call(rbind, lapply(1:1521, function(x) rbind(pricing_euro_df[x,], pricing_standard_df[x,])))

And we reset the row names of the data frame as it returned them unordered.

rownames(pricing_df) <- NULL

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.

write_csv(pricing_df, "pricing_data.csv")

See what we have in our data:

print(str(pricing_df))
## '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

Extras

  1. 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.
function(z, container_size, pallet_type) {
  box_number_by_kg = z / 15 #a box net weight
  net_box_number_by_kg = z %/% 15 #rounding to an integer
  if(container_size == 'FT20'){
    total_cost = unit_cost + 4000 #ft20_shipping_cost
  }
  if(container_size == 'FT40'){
    total_cost = unit_cost + 6000 #ft40_shipping_cost
  }
  output = list(box_number_by_kg, net_box_number_by_kg)
  names(output) = c("BoxNumber", "NetBoxNumber")
  if((container_size == 'FT20') & (pallet_type == 'EURO')){
    if(net_box_number_by_kg < 432) { #maximum boxes in container
      return(output) }
    else {
      print("Maximum box capacity is reached!") }
  }
  if((container_size == 'FT20') & (pallet_type == 'STANDARD')){
    if(net_box_number_by_kg < 480) { #maximum boxes in container
      return(output) }
    else {
      print("Maximum box capacity is reached!") }
  }
  if((container_size == 'FT40') & (pallet_type == 'EURO')){
    if(net_box_number_by_kg < 882) { #maximum boxes in container
      return(output) }
    else {
      print("Maximum box capacity is reached!") }
  }
  if((container_size == 'FT40') & (pallet_type == 'STANDARD')){
    if(net_box_number_by_kg < 1080) { #maximum boxes in container
      return(output) }
    else {
      print("Maximum box capacity is reached!") }
  }
}
  1. The error messages are declined in the later development of the functions because of the reactive structure of the Shiny app.
#Error messages below
if (length(z) == 0 & z == 0) {
  warning("`z` was empty") }
if (container_size != c('FT20', 'FT40')) {
  warning("Container size fault. Try 'FT20' or 'FT40'.") }
if (pallet_type != c('FT20', 'FT40')) {
  warning("Pallet type fault. Try 'EURO' or 'STANDARD'.") }

– FIN –