AI Use Case: Boutique Manufacturers Using Excel To Calculate Optimal Manufacturing Batch Sizes To Minimize Setup Costs

This use case shows how boutique manufacturers can use Excel-based models and AI-assisted workflows to determine optimal manufacturing batch sizes, reducing setup costs while maintaining delivery reliability. It outlines practical steps, tooling options, and safeguards for small and medium manufacturers with limited IT resources.

Direct Answer

A practical batch-size optimization starts with a transparent cost model: each batch incurs a fixed setup cost, while holding costs apply to inventory over time. Combine this with demand forecasts and capacity constraints, and an Excel-based optimization (with Solver) can propose batch sizes that minimize total cost. AI assists by improving forecasts, suggesting parameter updates, and generating scenario analyses. The result is auditable, repeatable guidance that lowers total production cost without sacrificing service levels.

Current setup

Manual Excel models exist but are often siloed and hard to audit.
Batch sizes are set by rule-of-thumb or rough planning, leading to unnecessary setups.
Demand forecasting is informal or historical-only, with limited scenario testing.
Data resides in separate systems (ERP, inventory, BOMs) with manual exports.
There is little or no automated workflow to pull data, run optimizations, and share results.

What off the shelf tools can do

Data ingestion and integration: connect ERP or inventory exports into a single workbook using Google Sheets, with automated updates via Zapier or Make.
Forecasting and parameter tuning: leverage built-in forecasting in spreadsheets and ChatGPT or Claude for scenario reasoning and explanations.
Optimization workflow: use Solver in {Excel} or add-ons in Google Sheets to minimize total cost subject to demand and capacity constraints; automate scenario runs and reporting.
Dashboards and audits: build lightweight dashboards in Notion or Airtable to track inputs, assumptions, and results over time.
Contextual examples: this approach aligns with an Excel-based optimization use case in finance, where batch-like reconciliation or cost minimization is automated—see the bookkeepers use case for a related pattern.

Where custom GenAI may be needed

Multi-product lines with varying setup costs and changeover times require more nuanced modeling than a single-SKU example.
Highly volatile demand or irregular cadence benefits from adaptive forecasting and explainable AI-assisted scenario planning.
Automated decision explanations are needed for operations teams and auditors to understand why a batch size was chosen.
Integration with legacy ERP often requires a lightweight GenAI layer to translate procurement or scheduling constraints into model inputs.

How to implement this use case

Define the cost model: identify fixed setup cost per batch, holding cost per unit, demand horizon, lead times, and plant capacity.
Gather data and set up a simple workbook: import past demand, existing inventory, and production constraints into a single workbook; create named inputs for easy reference.
Build the optimization: implement an objective to minimize total cost and set constraints for demand satisfaction and capacity; use Solver (or a similar add-on) to compute batch sizes per period.
Incorporate forecasting: add a forecast sheet (or AI-assisted forecast) for each period; use this as the input to the optimization to reflect expected demand.
Automate updates and reviews: connect data sources with Zapier/Make to refresh inputs on a schedule; generate a report of recommended batch sizes and rationale for the operations team.
Validate and iterate: run scenarios (e.g., optimistic vs. pessimistic demand, capacity changes) and document decisions for audits and training.

Tooling comparison

Aspect	Off-the-shelf automation	Custom GenAI	Human review
Data integration	Good for schedule-based updates and data pulls	Can tailor multi-source feeds and explainability	Important for data validation
Batch size optimization	Solver in Excel or Sheets supports basic optimization	Adaptive, scenario-aware optimization with explainability	Final sign-off on production planning
Forecasting	Forecast sheets or add-ins	Adaptive AI-driven forecasts with confidence intervals	Review of forecast plausibility
Auditability	Manual logging and versioning	Traceable prompts and model inputs/outputs	Manual verification of decisions

Risks and safeguards

Privacy and data governance: limit access to sensitive production data and keep logs of who changed inputs or assumptions.
Data quality: implement data validation rules, defaults, and periodic cleansing to avoid garbage-in, garbage-out results.
Human review: require operator sign-off on batch-size recommendations before release to production scheduling.
Hallucination risk: use deterministic calculation for optimization and have AI-generated explanations accompany numbers, not replace them.
Access control: restrict who can modify the model, forecasts, and optimization parameters; maintain versioned backups.

Expected benefit

Lower total production cost through fewer costly changeovers and better inventory utilization.
Greater predictability of production schedules and on-time delivery.
Auditable decision logs and repeatable processes that scale with product lines.
Faster what-if analysis for capacity planning and supplier commitments.
Non-disruptive deployment using familiar tools and minimal IT overhead.

FAQ

What exactly is batch size optimization in this context?

It is the process of determining how many units to produce in each production run to minimize total costs (setup plus holding costs) while meeting forecasted demand and respecting capacity constraints.

Do I need custom GenAI or can I do this with Excel alone?

You can start with Excel (Solver) and basic forecasting. Custom GenAI adds adaptive forecasting, scenario reasoning, and explainable decisions, but is not strictly required for a functional solution.

How often should forecasts and batch sizes be updated?

Update forecasts weekly or after significant demand changes; recompute batch sizes on the same cadence to reflect new inputs while preserving an auditable change log.

How do I ensure data quality?

Automate data validation, implement consistent export formats, and run periodic reconciliation between ERP, inventory, and production records to catch anomalies early.

Is this approach suitable for multiple products?

Yes, but the model must include product-specific setup costs, changeover constraints, and demand patterns; consider a multi-SKU extension with shared capacity constraints.

AI Use Case for Boutique Manufacturers Using Excel To Calculate Optimal Manufacturing Batch Sizes To Minimize Setup Costs