I am currently working with inventory fluctuation data for a company and facing some issues. The raw data is stored in an xls file, recorded on a monthly basis. According to the logic, the sum of the beginning balance and the increase minus the consumption should be equal to the ending balance for each month.

However, due to poor management, there are discrepancies between the data, causing the above equation to not hold true. My goal is to find a corrective value for each row of data through optimization, ensuring that the adjusted inventory fluctuation table satisfies the mentioned equation.

I have an Excel file containing the original data with the following structure:

Column 1: Item code Column 2: Item name Column 3: Initial balance Starting from Column 4, every three columns represent the increase, decrease, and ending balance for each month. The ending balance of each month becomes the beginning balance for the next month. The xls file includes hundreds of rows of inventory item data and records for nearly 50 months. I need to find corrective values that meet the following objectives:

1、Some corrective values must be integers, as certain inventory items cannot exist in decimal form, such as televisions. 2、Other corrective values can be decimals, for example, a cable length of 20.5 meters.

Additionally, I could annotate the unit of measurement for each project's corrective value, such as precision to 0.1 or precision to 1.

While satisfying the above objectives, I aim to minimize the absolute value of each corrective value to minimize adjustments to the original data.

My current challenge is determining which algorithm to use to solve the mentioned problem.

  • $\begingroup$ If you are implementing an optimization model through Excel, you will need to add the slack and surplus variables into the mentioned constraint. As the form $LHS + S - R = RHS$. Then control the value of those by adding appropriate BigM value on the objective function to capture the amount of the fluctuations on the inventory. $\endgroup$
    – A.Omidi
    Commented Jun 12, 2023 at 9:27

1 Answer 1


You can solve an integer programming model for each item. Using the subscript $m$ for month (with the initial inventory occurring in "month 0") and letting $S_m,$ $D_m$ and $I_m$ be respectively the supply, demand (consumption) and closing inventory in month $m$ (all parameters), your inventory balance constraints for items that come in integer quantities (such as televisions) become $$I_{m-1} + x_{m-1} + S_m - D_m = I_m + x_m$$ where $x_m$ is a free integer variable. "Free" means unrestricted in sign (can be positive or negative). For an item like cable, decide on the precision $p$ with which it is measured and multiple $x$ by $p$ on both sides of the constraint. For instance, if cable is sold in multiples of six inches and the data is recorded in feet, $p=0.5$ and $x$ is the number of half-foot adjustments.

In the objective function, you want to minimize $\sum_m \vert x_m \vert.$ Since we cannot use absolute value directly, we introduce a second (continuous, nonnegative) variable $y_m$ for each month and minimize $\sum_m y_m.$ We add the constraints $x_m \le y_m$ and $-x_m \le y_m$ for each month, which makes $y_m = \vert x_m \vert$ in the solution.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.