A multi-stage inventory management model based on the example of optimizing fertilizer production

Introduction

This example shows how to use Engee to create an intelligent production and inventory planning model. It is necessary to make strategic decisions for the whole year ahead.

Let's imagine that we run a factory that produces two types of fertilizers. We have several types of raw materials (ingredients) at our disposal, the price of which varies from month to month according to a well—known schedule (for example, seasonally). At the same time, we know exactly how many tons of each type of fertilizer customers will order each month. The
goal is to maximize profits. To do this, you need to find the perfect balance between:

Produce exactly as much as you need (or you can sell it profitably);
Purchase of raw materials in the cheapest months;
Storing finished products in a warehouse when it is economically feasible.

For such a long-term strategy, you can use financial instruments (for example, futures contracts) to fix commodity prices for the future and protect yourself from risks. Our mathematical model will help you calculate the most profitable plan, taking into account all these factors.

We will connect the necessary libraries.

using JuMP, DataFrames, GLPK, NamedArrays, Measures

Fertilizers and their composition

Granular fertilizers contain three key nutrients: nitrogen, phosphorus and potassium. At the factory, we do not produce them from scratch, but mix ready—made raw materials to obtain commercial brands of fertilizers with the right composition.
We have several "ingredients" - each with its own unique set of nutrients. By combining them in the right proportions, we create a finished product — a balanced fertilizer that our customers need.

Initial data

We will determine the data on the basis of which we will optimize production.:

the amount of demand for fertilizers;
list of months in a year;
Initial stock values;
percentage of nutrients in each type of fertilizer;
the size of fertilizer orders;
prices for fertilizers;
Storage capacity;
cost of storage;
Production capacity;
cost of raw materials;
the content of nutrients in the raw materials.

Demand for food = DataFrame(
    Balanced = [750, 800, 900, 850, 700, 700, 700, 600, 600, 550, 550, 550],
    High nitrogen = [300, 310, 600, 400, 350, 300, 200, 200, 200, 200, 200, 200]
)
месяцы = ["January", "February", "March", "April", "May", "June", 
          "July", "August", "September", "October", "November", "December"]
initial reserves = DataFrame(
    Balanced = [200, 200],
    High nitrogen = [200, 200]
)
nutritional content in fertilizers = DataFrame(
    Balanced = [10, 10, 10],
    High nitrogen = [20, 10, 10]
)
Food order = DataFrame(
    Balanced = [200, 400, 400, 400, 400, 400, 200, 0, 0, 0, 0, 0],
    High nitrogen = [0, 100, 200, 200, 200, 200, 200, 0, 0, 0, 0, 0]
)
Prices for fertilizers = DataFrame(
    Balanced = [400],
    High nitrogen = [550]
)
Storage capacity = 1000
unit storage cost = 10
production_power = 1200

raw materials cost = DataFrame(
    Monoammonium Phosphate = [350, 360, 350, 350, 320, 320, 320, 320, 320, 310, 310, 340],
    Potassium chloride_ = [610, 630, 630, 610, 600, 600, 600, 600, 600, 600, 600, 600],
    Ammiachna_seliter = [300, 300, 300, 300, 300, 300, 300, 300, 300, 300, 300, 300],
    Ammonium Sulfate_ammonium = [135, 140, 135, 125, 125, 125, 125, 125, 125, 125, 125, 125],
    Triple Superphosphate = [250, 275, 275, 250, 250, 250, 250, 240, 240, 240, 240, 240],
    Sand = [80, 80, 80, 80, 80, 80, 80, 80, 80, 80, 80, 80]
)

feed_containment_in raw materials = DataFrame(
    Monoammonium phosphate = [11, 48, 0],
    Potassium chloride_ = [0, 0, 60],
    Ammoniacal_seliter = [35, 0, 0],
    Ammonium sulfate_ = [21, 0, 0],
    Ternar_superphosphate = [0, 46, 0],
    Sand = [0, 0, 0]
)
types of fertilizers = names(in demand for fertilizers)
вещества = ["Nitrogen", "Phosphorus", "Potassium"]
types of raw materials = names(cost of raw materials) 
number of types of fertilizers = length(types of fertilizers)
number of types of raw materials = length(types of raw materials)
quantity of nutrients = length(substances)
number of months = length(months)
monthly demand = copy(demand for fertilizers)
insertcols!(demand in months, 1, :Month => months)
cost of raw materials in months = copy(cost of raw materials)
insertcols!(cost of raw materials in months, 1, :Month => months)
The content of the fertilizers with the names = copy(the content of nutritious fertilizers)
insertcols!(the content of fertilizers with names, 1, :Nutrition_material => substances)
The contents of the raw materials with names = copy(the contents of the nutritious materials)
insertcols!(the content of raw materials with names, 1, :Nutrition_material => substances)

Let's define a function for displaying data in the form of a table.

function вывести_таблицу(df::DataFrame, заголовок::String="")
    if !isempty(header)
        println("\n$header:")
    end
    column name = names(df)
    column width_ = []
    for column in column name_
        width = length(string(column))
        for i in 1:nrow(df)
            value = df[i, column]
            if isa(value, Number)
                string_values = string(round(Int, value))
            else
                string_values = string(value)
            end
            width = max(width, length(string_values))
        end
        push!(column width_, width + 2) 
    end
    заголовок_таблицы = ""
    for (i, column) in enumerate(column name_)
        table heading_= rpad(column, column width_[i])
    end
    println(table header)
    разделитель = ""
    for width in column width_
        разделитель *= repeat("-", ширина)
    end
    println(separator)
    for i in 1:nrow(df)
        строка = ""
        for (j, column) in enumerate(column name_)
            value = df[i, column]
            if isa(value, Number)
                string_values = string(round(Int, value))
            else
                string_values = string(value)
            end
            row *= rpad(row_values, column width_[j])
        end
        println(string)
    end
    println()
end

There are two types of fertilizers available:

Balanced — the standard composition is 10% nitrogen, 10% phosphorus, 10% potassium.
High nitrogen — nitrogen-enhanced version: 20% nitrogen, 10% phosphorus, 10% potassium.

The only difference is the doubled proportion of nitrogen in "High Nitrogen", which makes it more expensive to produce.

println("Nutrient content in fertilizers (in percent):")
view the table(the content of the improvements with the names)

Содержание питательных веществ в удобрениях (в процентах):

Питательное_вещество  Сбалансированное  Высокоазотное  
-------------------------------------------------------
Азот                  10                20             
Фосфор                10                10             
Калий                 10                10

Raw materials and their nutritional composition (in % by weight):

println("The content of nutrients in the raw materials (in percent):")
view the table(the contents of the raw materials with names)

Содержание питательных веществ в сырье (в процентах):

Питательное_вещество  Моноаммонийфосфат  Хлорид_калия  Аммиачная_селитра  Сульфат_аммония  Тройной_суперфосфат  Песок  
-----------------------------------------------------------------------------------------------------------------------
Азот                  11                 0             35                 21               0                    0      
Фосфор                48                 0             0                  0                46                   0      
Калий                 0                  60            0                  0                0                    0

Sand is a neutral filler. There are no nutrients in it. It is used as a "diluent" to accurately maintain the required percentage in the finished mixture when the active ingredients are too concentrated.

Volume of orders and prices for fertilizers

For the entire planning period, we know the exact volume of orders by month for each brand of fertilizers.

println("Order volume:")
check the table(demand in months)

Объём заказов:
Месяц     Сбалансированное  Высокоазотное  
-------------------------------------------
Январь    750               300            
Февраль   800               310            
Март      900               600            
Апрель    850               400            
Май       700               350            
Июнь      700               300            
Июль      700               200            
Август    600               200            
Сентябрь  600               200            
Октябрь   550               200            
Ноябрь    550               200            
Декабрь   550               200

Prices are fixed for the whole year. This means that our revenue depends only on the volume of sales, not on time. This stability simplifies planning and allows you to focus on cost optimization.

println("Fertilizer prices:")
view the table(prices for fertilizers)

Цены на удобрения:
Сбалансированное  Высокоазотное  
---------------------------------
400               550

The cost of raw materials

Raw material prices are a key variable in our model. Unlike stable prices for finished products, the cost of ingredients changes every month (for example, due to seasonality or market conditions).

println("The cost of raw materials:")
view the table(cost of raw materials in months)

Стоимость сырья:
Месяц     Моноаммонийфосфат  Хлорид_калия  Аммиачная_селитра  Сульфат_аммония  Тройной_суперфосфат  Песок  
-----------------------------------------------------------------------------------------------------------
Январь    350                610           300                135              250                  80     
Февраль   360                630           300                140              275                  80     
Март      350                630           300                135              275                  80     
Апрель    350                610           300                125              250                  80     
Май       320                600           300                125              250                  80     
Июнь      320                600           300                125              250                  80     
Июль      320                600           300                125              250                  80     
Август    320                600           300                125              240                  80     
Сентябрь  320                600           300                125              240                  80     
Октябрь   310                600           300                125              240                  80     
Ноябрь    310                600           300                125              240                  80     
Декабрь   340                600           300                125              240                  80

Production and storage

We will display the cost of storing a unit of production, the warehouse capacity, and the production capacity.

println("Unit storage cost: $unit storage cost")
println("Warehouse capacity: $Warehouse capacity")
println("Production capacity: $production_power")

Стоимость хранения единицы: 10
Вместимость склада: 1000
Производственная мощность: 1200

If we were unable to produce and ship the entire volume of orders in one month, the balance will not be transferred to the next month. This forces the model to always plan production ahead of demand. Thus, the model is looking for a balance: to produce enough to fulfill orders and reach the target balances, but not to produce too much, so as not to incur storage costs.

Setting the task

The model is based on an objective function that needs to be maximized. In our case, it's a profit.

model = Model(GLPK.Optimizer)

A JuMP Model
├ solver: GLPK
├ objective_sense: FEASIBILITY_SENSE
├ num_variables: 0
├ num_constraints: 0
└ Names registered in the model: none

Variables

The variables in the task are the volumes of fertilizer mixtures that we produce and sell each month, as well as the raw materials used to make them.

@variable(model, produce[1:number of months, 1:number of types of improvements] >= 0)
@variable(model, sell[1:number of months, 1:number of types of improvements] >= 0)
@variable(model, use[1:number of months, 1:number of types of raw materials, 1:number of types of fertilizers] >= 0)

In addition, we will create a variable representing the amount of inventory at each point in time.

@variable(model, 0 <= stocks[1:number of months, 1:number of types of fertilizers] <= warehouse capacity)

The upper limit for sales is the demand for each time period and each brand of fertilizer.

for i in 1:number of months, b in 1:number of types of fertilizers
    set_upper_bound(sell[i, b], demand for fertilizers[i, b])
end

Expressions

To calculate the objective function using task variables, you need to calculate revenue and costs. Revenue is the sales volume of each type of fertilizer multiplied by its price, summed up over all time periods and all types of fertilizers. Let's define an expression for revenue.

@expression(model, revenue, sum(prices for fertilizers[1, j] * sum(sell[i, j] for i in 1:number of months) for j in 1:the number of types of fertilizers))

Let's define an expression for the cost of raw materials. The cost of raw materials is the cost of each ingredient used at each point in time, summed up over all periods. Since the volume of raw materials used at each moment is divided by the amount used for each fertilizer, the summation is also carried out by types of fertilizers.

@expression(model, raw materials used[i=1:number of months, r=1:number of types of raw materials], sum(use[i, r, b] for b in 1:number of types of fertilizers))
@expression(model, total cost of raw materials, sum(cost of raw materials[i, r] * raw materials used[i, r] for i in 1:number of months for r in 1:number of types of raw materials))

Let's define an expression for storage costs. Storage costs are the cost of maintaining stocks during each time period, summed up by time and types of fertilizers.

@expression(model, total storage cost, unit storage cost * sum(stocks))

Target function

Let's define the objective function.

@objective(model, Max, revenue - general value of raw materials - general value of storage)

Limitations

The model must comply with key production rules.:

Inventory balance: the balance in the warehouse is the sum of the balance of the previous month and the difference in the quantities of products produced and sold;
target stocks: by the end of the year, it is necessary to reach the set balance level;
Limits: Warehouse capacity and factory capacity are limited;
balance of materials: the amount of finished products is equal to the amount of consumed raw materials;
Quality: the chemical composition of each batch must strictly comply with the formulation.

All these rules are formalized into mathematical constraints and loaded into the model to find the optimal plan.

@constraint(model, material_balance1[i=2:number of months, b=1:number of types of fertilizers], 
    stocks[i, b] == stocks[i-1, b] + produce[i, b] - sell[i, b])
@constraint(model, material_balance2[b=1:number of types of improvements],
    stocks[1, b] == initial stocks[1, b] + produce[1, b] - sell[1, b])
@constraint(model, final stocks[b=1:number of types of fertilizers], stocks[number of months, b] == initial stocks[2, b])
@constraint(model, storage limite_[i=1:number of months], sum(stocks[i, :]) <= storage capacity)
@constraint(model, capacity limite_[i=1:number of months], sum(produce[i, :]) <= production capacity)
@constraint(model, raw materials usage[i=1:number of months, b=1:number of types of fertilizers], 
    sum(use[i, r, b] for r in 1:quantity of raw materials) == produce[i, b])
@constraint(model, quality of fertilizers[i=1:number of months, n=1:number of nutrients, b=1:number of types of fertilizers],
    sum(nutritional content in raw materials[n, r] * use[i, r, b] for r in 1:quantity of raw materials) == nutritional content in fertilizers[n, b] * produce[i, b])

Solving and visualizing the results

Let's start solving the optimization problem.

optimize!(model)

Let's display the solution as a table (if the table does not fit in width, zoom out in the browser).

status = termination_status(model);
if status == MOI.OPTIMAL
    profit = round(Int, objective_value(model))
    println("\Optimal solution found")
    println("Profit: $profit")
    value_product = value.(produce)
    value_sell = value.(sell)
    stock_values = value.(stocks)
    produce rounded = round.(Int, values to produce)
    sell_ rounded = round.(Int, sell values)
    stock_ rounded = round.(Int, stock_values)
    production table_ = DataFrame()
    for b in 1:number of types of fertilizers
        таблица_производства[!, Symbol("to produce_" * replace(виды_удобрений[b], " " => "_"))] = производить_округленные[:, b]
    end
    sales table_ = DataFrame()
    for b in 1:number of types of fertilizers
        таблица_продаж[!, Symbol("to sell_" * replace(виды_удобрений[b], " " => "_"))] = продавать_округленные[:, b]
    end
    inventory table_ = DataFrame()
    for b in 1:number of types of fertilizers
        таблица_запасов[!, Symbol("stocks_" * replace(виды_удобрений[b], " " => "_"))] = запасы_округленные[:, b]
    end
    Production plan = hcat(production table, sales table, inventory table)
    production_plan[!, :Month] = months
    production plan = production plan[:, [end, 1:end-1...]]
    println("\Nproduction plan:")
    view the table(production plan)
end
produce balanced = produce rounded[:, 1];
produce high-nitrogen = produce rounded[:, 2];
sell balanced = sell rounded[:, 1];
sell high-nitrogen = sell rounded[:, 2];
stock_balanced = stock_ rounded[:, 1];
high-nitrogen stocks = round stocks[:, 2];

Оптимальное решение найдено
Прибыль: 2247394

Производственный план:
Месяц     произвести_Сбалансированное  произвести_Высокоазотное  продать_Сбалансированное  продать_Высокоазотное  запасы_Сбалансированное  запасы_Высокоазотное  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Январь    1100                         100                       750                       300                    550                      0                     
Февраль   600                          310                       800                       310                    350                      0                     
Март      550                          650                       900                       600                    0                        50                    
Апрель    850                          350                       850                       400                    0                        0                     
Май       700                          350                       700                       350                    0                        0                     
Июнь      700                          300                       700                       300                    0                        0                     
Июль      700                          200                       700                       200                    0                        0                     
Август    600                          200                       600                       200                    0                        0                     
Сентябрь  600                          200                       600                       200                    0                        0                     
Октябрь   550                          200                       550                       200                    0                        0                     
Ноябрь    550                          200                       550                       200                    0                        0                     
Декабрь   750                          400                       550                       200                    200                      200

We will display a histogram of optimal production, sales, and inventory of a balanced fertilizer.

p1 = bar(месяцы, производить_сбалансированное, title="Produced by", xlabel="", color=:green, ylabel="Quantity")
p2 = bar(месяцы, продавать_сбалансированное, title="Sold", xlabel="", color=:green, ylabel="Quantity")
p3 = bar(месяцы, запасы_сбалансированное, title="Stocks", color=:green, ylabel="Quantity")
graph1 = plot(p1, p2, p3, layout=(3, 1), size=(1000, 900), margin=10mm, legend=false)
display(graph1)

We will display a histogram of optimal production, sales, and inventory of high nitrogen fertilizers.

p1 = bar(месяцы, производить_высокоазотное, title="Produced by", xlabel="", color=:orange, ylabel="Quantity")
p2 = bar(месяцы, продавать_высокоазотное, title="Sold", xlabel="", color=:orange, ylabel="Quantity")
p3 = bar(месяцы, запасы_высокоазотное, title="Stocks", color=:orange, ylabel="Quantity")
graph2 = plot(p1, p2, p3, layout=(3, 1), size=(1000, 900), margin=10mm, legend=false)
display(graph2)

Conclusion

The conducted modeling of a multi-stage production task demonstrates the effectiveness of optimization methods for strategic planning in a dynamic market. The solution provides an accurate and practical plan.

Key findings:

Release strategy: The model has identified the priority of producing high-margin products during periods of peak demand, while the base product supports overall capacity utilization. This is directly consistent with the goal of maximizing profits.
Dynamic role of reserves: The analysis showed that reserves do not serve as a reserve, but as an active buffer smoothing the imbalance between variable demand and fixed capacities. A strategy of planned accumulation and subsequent reduction of stocks is used to minimize total costs.
Impact of constraints: Tight constraints on capacity and target end balances form a specific structure of the plan, causing production spikes to meet the boundary conditions. This confirms the high sensitivity of the system to the control parameters.

Thus, the model adequately formalizes a complex planning task, transforming it into an optimization one. The result is a strategic map that identifies profit growth points and system bottlenecks, which makes this approach a valuable tool for justifying management decisions.