Case Study

A few key concepts:

• Buy and sell:

Buy rates are a freight forwarder’s cost to procure space on the vessel (container on a ship or space on an airplane) and move cargo on shipments.

Sell rates are the prices we charge our customers to move those shipments.

• Air cargo comes in many shapes and sizes and in order to move freight most efficiently, it’s best to have a mixture cargo density. The buy costs and sell rates are $/kg where kgs are“chargeable weight.”
• Note: We only expect you to use information provided within this document and the data set given (No external trend analysis or market knowledge is required).

Summary:

The excel spreadsheet attached describes Air shipments from a client 9 and also quote data (won and lost) and aligned pricing. Task here is to build a model using the data provided, assess future pricing for 2018 and present findings / proposal in the form of a PowerPoint presentation. Below we have described assumptions to take into consideration when doing this analysis. For the Data Sanitization & Model exercises please keep all formulas / state how the data has been sanitized so we can see how you arrived at your results. Please complete tasks in Excel. If visualizatoins would like to be presented by other means that is ok.

Note:

You are expected to complete all tasks(1, 2, 3, 4) in the below exercise but when prioritizing time please make sure a good attempt is made at Task 3.

Assumptions:

Data Set

Every row of the data set signifies a shipment.

All procurement costs and revenue are stated per shipment.

Client Volumes

Client’s expected Air Chargeable Weight will grow by 30% from 2017 to 2018.

2018 expected Chargeable Weight per shipment = Average of 2017 chargeable weight per shipment.

Expected Market Procurement Buy Rate (Task 1 & 2)

Air

Model general Air Freight Rate trend for 2018 on Procurement Buy Costs from Jan 2016 – July 2017. Expectation is that Air Freight Buy rates will be 20% higher in 2018 than 2017. For 2018 we have a discount on our Buy rate (30% benefit) if shipments are volumetric (volumetric nature > 0%). Also note we did not have any discount in 2016 / 17.

e.g. Shipment Profile: Chargeable Weight = 1500kgs; Actual Weight = 1000kgs ->

Volumetric Nature = (1500kgs – 1000kgs) / 1000 = 50%

Buy Rate (pre-discount) = $3.00 per chargeable weight kg (1500 kgs)

Discounted weight =1500kgs – (1500kgs – 1000kgs) x 30% = 1350kgs

New Revenue at discounted weight = 1350kgs x $3.00 =$4,050

Buy Rate per chargeable weight kgs achieved =$4,050/1500 =$2.70

Buy Rate (post-discount) = $2.70 per chargeable weight kg (1500 kgs)

Quoting process and Market Data (Task 3)

Quotes are sent to a client and either won or lost. A won quote turns into a shipment.

Data has been provided stating the buy cost per shipment & per kg. This is not shown to the client.

A market buy rate (indicator of what rate level other freight forwarders can buy in the market). This is not shown to the client.

The sell rate to the client per shipment & per kg. This is seen by the client at time of quoting.

Assume that all quotes lost in this exercise are due to price.

Task 1: Data Sanitization (Data: Fixed – Client 9)

1. Populate ‘origin’ and ‘destination’ for Air shipments with relevant 3-character airport codes, using

information in column ‘consolidated location’.

1. Origin is from SZX.
2. Destination location is one from the airports stated in 2. a.
3. For destination location if more than one within the string, assume the final one is the

required destination code (only from 2. a.)

2. Use the below classification for destination ports for East Coast and West Coast and generate a

new column for this:

1. Air – Airport Code: PHX, LAS = West Coast ; MIA, BOS, DFW = East Coast
2. Shipment ID’s should be either 5 or 6 numeric characters long. There are non-numeric

characters in the data. Can you remove these and generate a new column with only the 5 or 6

numeric characters long Shipment ID.

4. Remove all rows with a blank ‘cost’.
5. If ‘air_volume_chargeable_weight’ is blank, increase the actual weight of the shipment by 15% and set that as the ‘air_volume_chargeable_weight’.
6. Store this sanitized data set in a separate tab.

Please make sure that you are leaving all formulas/process you have used for this exercise. Parts of this exercise that have only ‘hard coded’ values and no explanation will be assumed to have been done manually and these will be negatively marked.

Task 2: Model – Fixing rates (Data: Fixed – Client 9)

Historical

1. Generate a dashboard highlighting the metrics below for the time period stated below:
2. Month by month; Time period: Jan 2016 – Sept 2017
3. Air: Revenue, Procurement Cost, Chargeable Weight, Average Buy Rate per chargeable

weight kg, % Margin, Volumetric nature

1. Add any other metrics within this dashboard that you deem important.
2. Which day of the week do the most Air shipments depart in 2017?

Future

1. Base Buy Rate Forecast – Using the assumptions above generate a forecast of expected Air

Buy Rate ($ per kg) for 2018.

1. When doing this look at rates at destination regional level (East / West Coast).
2. Base Volume Forecast – Using the assumptions above generate a forecast of expected Air

(Chargeable Weight kgs), and No. of shipments for 2018.

2017. Volumetric nature is the same as 2017.
2018. State 2 pricing options that you believe to be reasonable to propose to the client 9 for 2018.

Why might you recommend them? Why might a client prefer one over the other? (Make sure at

least 1 case has a Fixed Sell Price for the year)

1. We are looking to generate the same % Margin in 2018 for the client as we did in 2017

Future.

4. Using 2018 Yearly Fixed Sell Price Case, 2018 Base Buy Rate Forecast, 2018 Base Volume

Forecast complete the below:

1. Before 2018 starts, you want to sensitize your Fixed Yearly Sell Price to a potentially

more expensive Q4 2018. If the Air Buy Rate for the West Coast in Oct, Nov, Dec

increases 10% higher for those 3 months than Base Buy Rate Forecast what Fixed

Yearly Sell Price should be set to keep the same overall deal % margin.

1. Generate a table where Air Buy Rate % increase for the West Coast in the 3 months of Oct, Nov, Dec starts from 6% up to 30% in increments 4% and output what Fixed Yearly Sell Price should be set to keep the same overall deal % margin. Example of Table below (in blue required outputs – Yearly Fixed Sell – Air West Coast).

1. Make table in b. dynamic where ‘% increase starts for the West Coast in the 3 months of Oct, Nov, Dec from A% up to B% in increments C% to keep the same overall client % margin. Ability to amend A, B & C and the model will output what Fixed Yearly Sell Price should be set at each increment.
2. If the Air Buy Rate for the West Coast in Oct, Nov, Dec increases 10% higher for those 3 months than Base Buy Rate Forecast & Volumetric nature is 15% less than expected what Fixed Yearly Sell Price should be set to keep the same overall deal % margin.

Task 3: Model – Pricing Sensitivity (Data: Pricing Sensitivity)

For this exercise please use the data in tab ‘Price Sensitivity’

1. What is the quote conversion % rate for each of the port pairs?
2. What insights can you drive about the price sensitivity from this with respect to %

margin, buy rate per kg & market buy rate per kg?

2. What lanes do you think we have good/strong buy rates on?
3. Why do you think this?
4. Looking into SZX -> PHX: Assuming the % margin is kept static for all quotes.
5. For SZX -> PHX, estimate the ($ per kg) amount we need to reduce our buy rate cost by to increase our conversion by 5%?
6. What increase or decrease of revenue would be generated by actioning 3.a. on port pair SZX -> PHX?
7. Do you think this is a logical action to take? Please state reasoning and methodology.

Task 4: Presentation

1. Draw up a presentation (no more than 5 slides, excluding cover) explaining the analysis that you

have done.

2. Touch on the topics highlighted below & others that seem relevant
3. Task 1 & 2: Client 9 – Fixed pricing
4. 2018 Buy Rate & Volume forecasts and methodology.
5. 2018 Pricing options for Client 9.
6. Task 3: Pricing Sensitivity insights