Product pricing analysis & Shiny app
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.
Chapters
 The problem task
 Capacity
 Revenue and cost analysis
 Price breaks
 Possible scenarios
 Transit time and customer production
 Conclusion
 Shiny App
 Extras
The problem task
Fixed terms

Unit cost: $10/kg

6.5% duty charge on sales price

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
 Production

Which quantities?

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

Logistics

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 588
kg. 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 floorloaded EURO pallets, or 9 STANDARD pallets plus 1 floorloaded STANDARD pallet. Second, a 40FT container having the cost of $6000
can carry 23 EURO pallets plus 1 to 1,5 floorloaded EURO pallets, or 20 STANDARD pallets plus 1 to 1,5 floorloaded 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 inputz
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 ofz
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 nonstacked) 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 "EURO"
or "STANDARD"
pallet type. It returns the output of weight_f()
function:
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.
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
:
Batch details:
## 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:
## 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
:
Batch details:
## 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:
## 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()
.
540kg
batch with EURO pallet + 1620kg
batch with EURO pallet (a total of 2160kg
):
## Input DecimalBoxNumber NetBoxNumber NumberPallet
## 2160 144 144 4
## TotalPalletWeight NetProductWeight TotalWeight
## 192 2160 2352
Finance details:
## 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
):
## Input DecimalBoxNumber NetBoxNumber NumberPallet
## 2340 156 156 4
## TotalPalletWeight NetProductWeight TotalWeight
## 202 2340 2542
Finance details:
## 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
):
## Input DecimalBoxNumber NetBoxNumber NumberPallet
## 2520 168 168 4
## TotalPalletWeight NetProductWeight TotalWeight
## 212 2520 2732
Finance details:
## 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()
:
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 humanreadability 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)
.
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
Extras
 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 –