Production and Inventory Problem solved using Solver in Excel

Linear Programming allows us to tackle various real life problems, some of which we have already gone over in previous articles, such as the Transportation Problem, the Product Mix Problem and the Diet Problem. In the following article we will analyze a different classic application known as the Production Inventory Problem.

This problem is essentially used for establishing a policy for production within a time period that satisfies certain demand requirements, respecting the manufacturing limits and at a minimal cost. These types of models can be extended to use with several products, however, for this example we will only consider one single product:

production and inventory problem

We define the following linear optimization model: (Given: there is an initial inventory of 50 units => I0=50).

1. Decision Variables:

Xt: Units to produce in month t (t=1,…,6 with t=1 =>January; t=6=>June)
It: Units to be stored as inventory until the end of month t (t=1,…,6 with t=1 =>January; t=6=>June)

2. Objective Function: Minimize the production and inventory costs during the planning period, defined as: 60X1 + 60X2 + 55X3 + 55X4 + 50X5 + 50X6 + 15I1 + 15I2 + 20I3 + 20I4 + 20I5 + 20I6

3. Constraints:

Satisfy the demand requirements (known as the inventory balance constraint):

  • X1 + 50 – I1 = 100 (January)
  • X2 + I1 – I2 = 130 (February)
  • X3 + I2 – I3 = 160 (March)
  • X4 + I3 – I4 = 160 (April)
  • X5 + I4 – I5 = 140 (May)
  • X6 + I5 – I6 = 140 (June )

Respect the monthly production maximum capacity:

  • X1 <= 120   X2 <= 120   X3 <= 150   X4 <= 150   X5 <= 150   X6 <= 150

Non-negativity conditions:

  • Xt >= 0       It >= 0    For all t.

The following tutorial demonstrates how to implement this Linear Programming model using Solver in Excel:

The optimal solution is shown below with an optimal value of $43,450. One can see that a total of 780 units are produced between January and June, which together with the initial inventory of 50 units satisfies the monthly demand requirements.

optimal solution production and inventory

Would you like to receive the Excel file with Solver’s solution to this problem? get free and immediate access to the file now: Production and Inventory

Rating: 5.0/5. From 2 votes.
Please wait...

, , , , ,

No comments yet. Be the first to comment!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Our Site is Proudly Hosted on