Function Problems

How to Fix Excel Solver Ignoring Purchases in Inventory Optimization

Elise Williams Oct 10, 2026 869 views

Question details

Users need to resolve an issue where Excel Solver ignores purchasing requirements or generates unrealistic plans when optimizing inventory and production models.

How to Fix Excel Solver Ignoring Purchases in Inventory Optimization

Product: Microsoft Excel
Device & OS: not provided
Scenario: Running an inventory optimization model using the Solver Add-in to determine how many assemblies to build and parts to purchase while minimizing leftover inventory costs.
Observed behavior: Solver ignores necessary component purchases, consumes parts incorrectly, or outputs an unrealistic production plan because the model lacks sufficient supply and demand limits.

Before you start

Verify that your Excel Solver Add-in is enabled and ensure that the objective cell is correctly linked to your variable cells using formulas.

Solution 1Recommended

Add Comprehensive Demand and Supply Constraints

Adding precise constraints ensures Solver understands the practical limits of your business environment and forces it to account for necessary purchases.

Without demand or supply limits, Solver defaults to the absolute mathematical minimum, often concluding that producing nothing or ignoring purchases is the best way to reduce inventory costs. You must explicitly define market demands, capacity limits, and ordering rules.

Open Solver Parameters

Navigate to the Data tab on your Excel ribbon and click on 'Solver' in the Analyze group to open the Solver Parameters dialog.

Add Customer Demand Limits

Click 'Add' next to the constraints box. Select the cell representing total assemblies produced, choose the '>=' operator, and link it to your minimum customer demand requirement.

Set Production Capacity Restrictions

Add another constraint for maximum production by selecting your production quantity cells, choosing '<=', and entering the maximum available factory capacity.

Enforce Minimum Order Quantities (MOQs)

If your suppliers require minimum purchase sizes, add constraints ensuring that your component purchase variable cells are greater than or equal to the respective MOQs.

Apply Non-Negative Limits

Ensure the checkbox labeled 'Make Unconstrained Variables Non-Negative' is checked to prevent Solver from buying negative quantities of parts, then click 'Solve'.

Add Comprehensive Demand and Supply Constraints

Result: With proper constraints applied, Solver will realistically balance component purchases to meet demand while successfully minimizing the overall inventory cost.

Solution 2

Redefine Objective and Variable Cells

Use this solution if Solver still ignores variables; it ensures the model structurally connects purchasing decisions to the final inventory calculation.

Easily Optimize Complex Inventory Models with WPS Spreadsheet

WPS Spreadsheet features a powerful, built-in Solver tool that enables you to define complex objective functions and constraints to optimize inventory, purchasing, and production plans with ease.

1. Open Your Inventory Model: Launch WPS Spreadsheet and open your existing .xlsx inventory optimization workbook.
2. Access the Solver Tool: Navigate to the Data tab on the main ribbon and select the 'Solver' tool to bring up the optimization parameters.
3. Define Model Parameters: Set your objective cell to minimize leftover inventory costs, then select your purchasing and production variable cells.
4. Add Constraints and Solve: Easily add limits for customer demand and minimum order quantities in the constraints box, then click 'Solve' to generate a realistic plan.

Advanced Solver tool integrated directly for complex linear and non-linear optimization tasks.High compatibility with Microsoft Excel (.xlsx) formats, formulas, and existing optimization models.Intuitive constraint management interface makes troubleshooting mathematical models significantly easier.Lightweight architecture ensures lightning-fast calculations even for large supply chain datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel Solver give me negative numbers for inventory purchases?

This happens when your variable cells aren't restricted to positive values. In the Solver Parameters dialog, you must check the box that says 'Make Unconstrained Variables Non-Negative' before running the calculation.

What does it mean when Solver says it cannot find a feasible solution?

A 'no feasible solution' error occurs when conflicting constraints are present. For instance, your customer demand minimum might strictly exceed your maximum production capacity limits or available supply limits. Review your constraints for logical conflicts.

How do I ensure Solver buys parts in whole batches rather than fractions?

You can enforce whole-number results using integer constraints. When adding a constraint for your purchase quantities in Solver, select 'int' (Integer) from the middle dropdown menu to restrict the results to whole numbers.

Why does my Solver objective cell value not change when running the calculation?

This usually indicates that the formulas in your objective cell are not mathematically linked to your variable cells. Check your spreadsheet logic to ensure the cells you are asking Solver to change directly impact the final calculation in your objective cell.

Excel & Data Problems