Case Study 32-1: The Complete Arc — Priya's Supply Chain System Goes Live

MILESTONE: Acme Corp Supply Chain Analytics — Full Production Deployment

Characters: Priya Okonkwo (Analyst, promoted to Senior Analyst), Sandra Chen (VP of Sales), Marcus Webb (IT Manager), David Nakamura (VP of Operations)

Setting: Acme Corp headquarters, November quarterly business review

Before Python: What Acme's Supply Chain Looked Like

To understand what Priya built, it helps to understand what existed before.

In 2021, Acme Corp's supply chain management process looked like this: every Monday morning, Priya spent two hours pulling inventory reports from four different regional spreadsheets that Marcus's team had set up, consolidating them manually into a single master file. If a number looked wrong, she would email the regional warehouse coordinator and wait for a response, sometimes for days.

Reorder decisions were made by three purchasing managers who had been with the company long enough to have developed reliable intuitions about which products needed attention. They were good at their jobs. But their intuitions were based on products they had worked with for years — and the catalog had grown. New SKUs, new suppliers, and changed demand patterns were harder for institutional memory to track.

The stockout events in late 2022 were the first real signal that the informal system was breaking down. Three major customers in different regions complained about back-order situations on products they ordered every month. Sandra Chen tasked Priya with "figuring out what is actually happening with our inventory."

That investigation — documented in the early sections of this chapter — revealed three problems that could not have been identified without systematic analysis:

1. Reorder points for high-velocity items had not been updated in three years, leaving them far below what current demand patterns required
2. Two suppliers had significant reliability issues that were forcing Acme to carry excess safety stock without anyone explicitly deciding to do so
3. A long tail of slow-moving and obsolete inventory was quietly consuming $85,000 in working capital

Priya fixed the immediate problems first. She recalibrated reorder points for all 47 Class A items, flagged the two underperforming suppliers for review, and presented the SLOB inventory report to the purchasing team. That took about two weeks of concentrated work.

Then she built the system so no one would need to do that analysis manually again.

The System: Two Years of Work, One Script

By November of the following year, Priya had assembled a supply chain analytics system that consisted of a single Python script — acme_supply_chain.py — that connected to three data sources, ran seven distinct analytical modules, and produced a formatted Excel dashboard in under thirty seconds.

Behind that single script was more than two years of incremental work:

Year One, Q1: The sales analytics system (Chapter 11) — data cleaning, revenue reporting, regional breakdowns. This established the clean acme_sales_2023.csv data pipeline that everything downstream depended on.

Year One, Q3: The forecasting models (Chapter 26) — applying exponential smoothing and seasonal decomposition to product-level demand. This produced the demand variance estimates that safety stock calculations required.

Year Two, Q1: The inventory database (Chapter 23) — Marcus's team migrated from scattered spreadsheets to the acme_inventory.db SQLite database, giving Priya a reliable, queryable source for current stock levels.

Year Two, Q2-Q3: The supply chain analytics themselves — the code in this chapter. Inventory turnover, DIO, ABC analysis, reorder point calculations, supplier scorecards, reorder alert generation.

Year Two, Q4: Dashboard assembly and automation — running everything end to end, output to Excel, automated scheduling.

Each piece was built to solve a specific business problem. The result of assembling them was something more than the sum of its parts: a system that let any analyst answer "how is our supply chain performing right now?" in 23 seconds.

The November Quarterly Business Review

At 9:47 AM on a Tuesday in November, Priya opened her laptop in Acme's main conference room. Present were Sandra Chen (VP of Sales), David Nakamura (VP of Operations), Lynn Park (CFO), the regional operations managers for North, South, East, and West, and three purchasing managers.

Marcus Webb sat in the back of the room. He had tested the system four times the previous Friday to make sure it would run cleanly from the conference room laptop. It had run without errors each time.

Priya typed:

python acme_supply_chain.py --mode dashboard --output quarterly_review.xlsx

The terminal output scrolled:

Loading inventory database...    [623 SKUs, 4 regions]
Loading sales data...            [48,204 transactions, Jan-Oct 2024]
Loading purchase order history... [1,847 POs, 12 suppliers]
Calculating demand velocity...
Running ABC analysis...
Generating reorder alerts...
Building supplier scorecard...
Saving dashboard to quarterly_review.xlsx...
Dashboard saved successfully.

=== SUPPLY CHAIN DASHBOARD COMPLETE ===
Total SKUs analyzed:         623
Total inventory value:       $1,412,840.00
Items below reorder point:   23
Critical items (< 7 days):   3
ABC split:                   47 A / 138 B / 438 C

Twenty-three seconds.

The Excel file appeared on the shared drive. Priya opened it on the projector and clicked to the Summary sheet.

What the Leadership Team Saw

Summary Sheet:

The Summary sheet showed five headline numbers in large, clear text:

Lynn Park, the CFO, asked immediately: "What was inventory value this time last year?"

"$1,284,600," Priya said. "Up 10% year-on-year. But COGS grew 14% in the same period, so inventory turnover actually improved — from 3.8 to 4.1."

This was precisely the kind of answer that came naturally when you had the data organized properly. The CFO nodded.

ABC Analysis Tab:

The ABC analysis showed 47 A items, 138 B items, and 438 C items. David Nakamura read the value percentages quietly: 80% of annual consumption value was concentrated in those 47 SKUs. "We've been treating all 600 products the same way," he said. "That's not right."

The purchasing team lead, Sandra's colleague James, said: "That's not entirely true — the A items get more attention in practice." But the data was right there: the formal reorder parameters, the review cycles, the safety stock calculations — all of those were applied uniformly across the catalog, regardless of ABC tier. The system did not differentiate. It just happened that the experienced purchasers' intuitions aligned reasonably well with the A items.

"Reasonably well is not good enough," David said. "We should have explicit policies for each tier."

Reorder Alerts Tab:

Priya filtered for the West region. Three CRITICAL items appeared: two toner SKUs and a paper product, all with fewer than 3 days of supply remaining.

Sandra Chen leaned forward. "Three critical items," she said. "I got a complaint from a West region customer last week about a back-order situation. This is why." She looked at the West region operations manager, who was already reaching for her phone.

The purchasing manager said, "I'll have emergency orders in by end of day."

"I already prepared draft purchase orders for all three," Priya said. She had sent them to the purchasing team the previous Friday when she'd run the preview analysis. "They are in your inbox."

Sandra looked at Priya. That was a sentence that said: I did not just build the dashboard. I thought through what happens after the dashboard.

Supplier Scorecard Tab:

Priya navigated to the supplier scorecard without comment. The table was sorted by on-time delivery rate, ascending. The first row showed the underperforming supplier — a paper products company that Acme had worked with for eleven years.

On-Time Delivery Rate: 71.3%. Based on 47 purchase orders over twelve months.

The room was quiet for a moment.

"We've known that relationship has been deteriorating," David Nakamura said carefully.

"We've known it qualitatively," Priya said. "Now we have the data. 71% over 47 orders is not an outlier year or a single bad stretch. That is a pattern."

The VP of Operations said, "Schedule a performance review. Ninety-day improvement window. I'll take the lead."

After the Meeting: The Promotion

Three weeks after the quarterly business review, on a Wednesday morning in early December, Priya received an email from Sandra Chen with the subject line: "Formal offer — Senior Analyst."

The offer letter outlined a promotion to Senior Analyst, effective January 1st. The expanded scope included supply chain analytics, demand forecasting, and customer analytics across all four regions. The salary increase was significant.

The letter cited, specifically: "the development and deployment of Acme's supply chain analytics capability, which has materially improved operational decision-making and resulted in demonstrable improvements in inventory efficiency and reorder management."

That afternoon, Marcus Webb sent Priya a message:

"Congratulations. I want to say something for the record: what you've built over the past two years is genuinely good work. Not 'good for an analyst' — just good. The system is reliable, the code is clean, and it produces results that people in this building actually use to make decisions. Python is now essential to how Acme operates. We're not going back to the old way. — Marcus"

Before and After: The Acme Supply Chain Story in Numbers

What Made This Work

Priya's system did not succeed because of any single piece of code. It succeeded because of the architecture: each module solved a specific, well-defined problem, and the modules were designed to compose into a larger whole.

The inventory database (Marcus's contribution) gave the system reliable current-state data. The sales CSV pipeline (built in Chapter 11) gave it historical flow data. The forecasting models (from Chapter 26) gave it forward-looking demand estimates. The supply chain analytics in this chapter gave it the metrics, classifications, and alerts that translated data into action.

Sandra Chen said, at the December all-hands meeting where Priya's promotion was announced: "What Priya did is what good analysis looks like. She did not just answer the questions we asked. She built a system that answers questions we did not know we needed to ask. That is what I hired an analyst to do."

Marcus Webb, in his December message, put it differently: "Python is now essential to how this company operates."

Both of them were right.

Key Python Techniques in This Case Study

The complete Acme supply chain system uses:

• sqlite3 with parameterized queries for safe, reliable database access
• pd.read_sql_query() for loading structured data from the inventory database
• pd.merge() for joining inventory, sales, and supplier data into a unified analytical dataset
• groupby().agg() for calculating demand velocity, ABC values, and supplier metrics
• pd.cut() for converting continuous metrics (days of supply) into categorical urgency levels
• scipy.stats.norm.ppf() for converting service level targets to safety stock z-scores
• pd.ExcelWriter with openpyxl for multi-sheet Excel dashboard output
• Modular function design: each analytical component is a standalone, testable function

The full code is in /code/acme_supply_chain.py.