Tactical
A 16-week transport plan showing weekly pallet volumes, carrier capacity utilisation and additional truck requirements. Pickup and arrival views show the plan from either end of the journey.
CASE STUDY / 01 PERSONAL PROJECT · RECRUITMENT PORTFOLIO
Supply Chain
Planning & Analytics.
A personal Azure SQL and Power BI project simulating a control tower for an automotive parts manufacturer. Transport planning, inventory projection and risk analysis using synthetic data.
01Project description
Project scope
The project simulates a supply chain control tower for a fictional automotive parts manufacturer. The company supplies components to vehicle assembly plants, with planning starting from customer files containing planned vehicle production data.
The Azure SQL database converts this input into component demand and then into transport plans for carriers. It combines bills of materials, transport routes and carrier allocation rules, and compares transport requirements with available capacity. In parallel, it projects inventory at the company’s factories using opening stock, planned production and demand.
The outputs form the basis for further Power BI analysis of transport costs, product demand, inventory levels, carrier overcapacity risks and stock shortage risks. The project covers the complete flow from a customer file to transport plans, inventory projections and reporting on supply chain risks.
02Database logic
Data flow & calculation rules
From importing a customer production file to transport plans, inventory projections and data for Power BI.
Import & validation
Files are loaded into the input layer, with source data retained in RAW. Formats, completeness and consistency with master data are checked. Invalid records are separated into an exceptions dataset.
Component demand
Planned vehicle production is converted into component quantities using the bill of materials. Vehicle volumes and component demand remain separate measures to avoid double counting.
Transport & capacity
Demand is converted into pallets and allocated to route legs and carriers. The resulting workload is compared with available capacity. Production, pickup and delivery dates are treated separately.
Inventory projection
Closing stock equals opening stock plus planned production minus demand. Each week’s closing balance becomes the next week’s opening balance, making future shortages visible.
Publication & versioning
After validation, the tactical plan is preserved as a snapshot. Current and previous publications remain separate so that successive planning versions can be compared.
Reporting data
SQL views expose the outputs to Power BI for analysis of demand, transport costs, carrier capacity utilisation, inventory and shortage risks.
03Reporting layer
Power BI report
I combine SQL and Power BI skills with an understanding of data and supply chain processes. This project shows how I bring demand, transport, costs and inventory together into a coherent view of the current operational situation. I build reports that help identify relationships and risks and assess scenarios before making business decisions.
The report below provides an overview of key supply chain elements from a control tower perspective. It compares planned transport volumes with available carrier capacity, highlights the risk of insufficient factory stock to meet demand and presents transport costs. It helps identify routes and periods requiring additional transport capacity, compare carrier costs on individual routes and assess whether factory production needs to increase to meet demand.
Report pages
Current vs Previous Tactical
A comparison of the current and previous tactical plans. Shows changes in transport volumes by week, route leg and carrier, highlighting the largest revisions.
Short Term
A 14-day operational transport plan. Shows daily pallet volumes, truck requirements and capacity gaps for selected routes and carriers.
Transport Costs
Transport costs for the tactical and short-term plans: total cost, cost per pallet and carrier rates. Filters allow comparisons by route, carrier and period.
Stock Prediction
Weekly stock projections for a selected factory and product, based on opening stock, production and demand. A what-if scenario shows how a demand change affects stock levels and shortages.
Stock Overview
An overview of projected stock shortages across factories and products. Shows the number of products at risk and shortage volumes by week to help prioritise further analysis.
Where the plan needs attention
Transport costs, changing demand and supply risks—connected to decisions that can be tested.
Synthetic portfolio dataset · forecast: 28 Aug 2026 · OEM production: W36–W51 · transport pickup: W35–W50
+10.0%
More component demand across the same 12 weeks.
12
Extra trucks required across four pickup weeks.
28.0%
Of the W50 shortage addressed in a two-product timing scenario.
01 / TRANSPORT COST
Start with cost concentration, then test alternatives
€2,410,386Estimated transport cost · 2,447 truck movements across 57 physical legs
Top 8 physical legs by estimated cost. A journey using two legs contributes a truck movement to each.
Five legs account for 23.5% of the budget
Ostrava–Antwerp leads at €164,000: 100 truck movements over 1,200 km. Ostrava–Prague has more movements (158), but a lower total (€101,752) because the distance and per-trip rate differ.
Decision to evaluate
Review the largest cost corridors for rate negotiation and consolidation. A high cost per kilometre alone does not identify the largest savings opportunity.
Can carrier allocation reduce cost?
Prague–Valladolid · 1,900 km · 35–48 pallets per week · 30 pallets per truck
02 / DEMAND CHANGE
More units do not always mean more transport
Current vs Previous · shared production weeks W36–W47
CurrentPrevious
Exact weekly values
+214,456 component units
Demand rises from 2,142,823 to 2,357,279 units in the shared window (+10.0%). Ignition Coil Universal contributes the largest absolute increase: +22,904 units.
The overlap is compared deliberately: Current covers W36–W51, while Previous covers W32–W47. Comparing full horizons would mix different weeks.
Decision to evaluate
Recheck production and transport requirements against the revised mix. The increase alone does not prove the cause of shortages: capacity and production in this demo were deliberately calibrated.
Which products drive the workload?
Different leaders for different decisions
Ignition Coil Universal leads in units (329,196), but requires only 685.8 pallet equivalents. Battery 74Ah Universal and Leaf Spring Universal together account for 40.1% of the component pallet equivalent.
Use units to assess production demand and pallet equivalents to assess transport workload. This measure is units ÷ units per pallet, before leg-level rounding; it is not a count of completed shipments.
03 / CAPACITY RISK
Protect four pickup weeks
Additional trucks required by pickup week
Blue cells show truck movements on the six busiest legs. The red calendar shows network-level capacity gaps; it does not identify these six legs as overloaded.
159 pallets beyond contracted capacity
W36: 30 pallets · W41: 49 · W48: 29 · W49: 51. Each week needs three additional trucks, calculated separately for the affected legs.
The stress scenario selects legs with only one eligible carrier contract. Reordering existing carriers therefore cannot close these gaps.
Decision to evaluate
Confirm three extra truck movements in each flagged week with the affected carriers, or qualify an alternative contract. Their price must be confirmed; the estimate uses base rates and contains no spot-market premium.
04 / INVENTORY & PRODUCTION
Test timing before increasing total production
Current planEarlier production
Weekly stock calculation
The shortfall builds up before W49
For Ignition Coil Universal, weekly production stays at 9,147 units while demand consumes the opening stock. Only 8 units remain after W48. The balance falls to −4,577 in W49 and −8,270 in W50.
The model still schedules production in W51, when factory pickup demand is zero. Bringing 8,270 coils and 4,232 brake calipers forward removes the shortages for these two pairs while keeping total production and final stock unchanged.
Decision to evaluate
Check whether materials and production capacity allow these quantities to be ready by the W49 pickup. This is a timing scenario, not a capacity-validated production schedule; other products still need action.
44 652Shortage units in W50 · 47 factory-product pairs · FAC001 + FAC002
Largest W50 shortages. The two-product timing scenario removes 12,502 units, leaving 32,150 units across the other pairs. Weekly shortages are balances and are not summed across weeks.
Sources, calculation rules and limits
Reconstructed from the Current and Previous input CSVs, the SQL base dataset and the final SQL register. Control totals agree with the published audit: 165,000 vehicles, 3,139,964 component units, 61,122 allocated pallet-leg movements and 944 carrier-plan rows. Previous: 150,000 vehicles and 2,854,788 units.
Cost = rounded truck count per carrier, physical leg and pickup week × contract rate. Stress overrides affect single-carrier capacity, not the required truck count or base-rate estimate. Savings scenarios enumerate whole trucks within contract limits. Rates are synthetic; costs are estimates, not invoices.
Capacity totals come from the acceptance audit of 11 September 2026. The retained extract does not list the twelve affected leg IDs, so no leg-level overload locations are inferred. Stock balances and product detail come from the production calibration and the confirmed W36 opening-stock rebase. The W50 shortage detail reconciles to 44,652 units.
Production what-if: move the pair’s peak shortage quantity from its W51 production to W49; keep opening stock and demand unchanged. Recalculate each weekly balance. This tests timing only, without modelling machine, labour, material or intra-week constraints.
Download database documentationLoading the recording…
Report demonstration
Selected excerpts from Power BI Desktop: data filtering, forecast comparison, transport costs and inventory scenarios.
The video could not be loaded. You can open the recording using the link below.
Fullscreen is available in the player.Open recording ↗
04Project resources
Files to download
Database and Power BI report documentation, the complete SQL register and a concise guide to its scripts. Project files are available in English and Polish.
Database documentation
Architecture, data model, processing flow, calculation rules and data quality controls.
SQL register
Complete T-SQL definitions of the database objects, arranged in execution order.
SQL catalogue
A concise description of each SQL entry, its object type and purpose.
Power BI report documentation
Semantic model, business logic, report pages and guidance on using the report.
Kamil Soja — CV
Professional experience, skills and project responsibilities. CV in English.