Introduction

Hoobuy, as an international purchasing agent platform, relies heavily on efficient logistics management to maintain profitability. One critical aspect is optimizing logistics costs while ensuring timely deliveries. This article explores methods to analyze Hoobuy's logistics data in spreadsheets and design optimized shipping combinations that balance cost and delivery speed.

Spreadsheet Analysis Methodology

1. Data Normalization

First we normalize the spreadsheet data for structured analysis:

=ARRAYFORMULA(IF(ISBLANK(A2:A),,{
 A2:A, // Package ID
 B2:B*C2:C, // Actual Weight Cost
 (D2:D*E2:E/100), // Volumetric Weight Cost
 MAX(B2:B*C2:C, D2:D*E2:E/100) // Chargeable Weight
}))

2. Cost Determinant Analysis

Key metrics to analyze in spreadsheets:

• Base shipping cost per kg/ml by carrier/route
• Insurance cost as percentage of declared value
• Tariff penetration rate by country
• Cost per shipping day (CPD) metric

3. Optimization Modeling

The optimization model considers three constraint categories:

1. Client requirements (max delivery days)
2. Regulatory restrictions (prohibited items)
3. Operational constraints (carrier weight limits)

Optimization Scheme Design