Automate Budget Planning Using Linear Programming

Select the projects that maximize the return on investment, follow the management guidelines while respecting budget constraints

Need Help?

Automate the decision-making process for the yearly budget allocation of an International Logistics Company.

In the Logistics industry, companies often need to invest in IT capabilities, modern handling equipment or additional warehouse space.

Regional Operational Directors receive budget applications from their local teams for mid-term projects.

Because of budget constraints, they need to decide for which projects the organization will allocate resources.

Spending money is much more difficult than making money.
— Jack Ma, Co-founder of Alibaba Group

In this article, we will design a simple linear programming model with Python to automate this decision-making process, considering

Return on investment of each project after three years (€)
Total costs and budget limits per year (€/Year)

We will also include the company’s top management guidelines for…

Sustainable Development (CO2 Reduction)
Digital Transformation (IoT, Automation and Analytics)
Operational Excellence (Productivity, Quality and Continuous Improvement)

💌 New articles straight to your inbox for free: Newsletter

💡

SUMMARY
I. Scenario: Budget Planning Process
As a Regional Director you need to allocate your budget on projects
II. Build your Model
1. Exploratory Data Analysis
Analyze the budget applications received
2. Linear Programming Model
Decisions variables, objective function and constraints
3. Initial Solution: Maximum ROI
What would be the results if you focus only on ROI maximization?
4. Final Solution: Management Guidelines
III. Conclusion & Next Steps

If you prefer watching, have a look at the YouTube tutorial

Scenario

Problem Statement

As a Regional Director of an international logistics company, you have the responsibility for logistics operations in four countries.

Illustration showing the operational structure of an international logistics company responsible for four countries (China, Korea, Singapore, India) with 17 warehouses and 48 customers from v — 56 Projects in your Scope of Responsibility — (Image by Author)

Your teams manage operations for 48 customers across more than 8 market verticals (Luxury, Cosmetics,…).

For each of the 17 warehouses, the Warehouse Manager (reporting to you) lists all projects requiring Capital Expenditure (CAPEX).

In an application form, he puts all the information that can help to justify (financially) this investment

To which customer this project will benefit?
What are the estimated costs per year (M€)?
What is the estimated return on investment after 3 years (M€)?

He can also add all the non-financial outcomes linked to the company’s long-term strategy.

Table comparing four logistics projects (electric trucks, voice picking, space rental, and rack sprinkler) with amortization costs over three years, direct ROI, and non-financial benefits suc

For instance, a project can contribute to initiatives for sustainable development, corporate social responsibility (CSR) or digital transformation.

Objective

Find the right budget allocation that maximises your profits (ROI) and respects the guidelines of the top management.

Because you have 58 projects under your responsibility, let us build a simple tool to automate this decision-making process.

Build your model

We will use the PuLP Python library, a modelling framework for Linear (LP) and Integer Programming (IP) problems.

Exploratory Data Analysis

For this year, you have a total of 58 projects covering 9 vertical markets.

Bar chart showing the number of projects across nine vertical markets for the current year. The tallest bars represent markets with 12 and 13 projects, while others range from 1 to 11 project

Automotive and Luxury markets represent a large part of the budget allocations because of the warehouse extension projects.

Donut chart showing budget allocations across different markets. The largest portions of the budget are allocated to the automotive market (38.4%) and the luxury market (28.1%), primarily due

A majority of the projects are related to Business Development i.e bringing additional turnover (and profit) for the company

Bar chart illustrating the distribution of projects, with the majority related to business development, aimed at bringing additional turnover and profit for the company. Some projects are ass

Linear Programming Problem

Let us build a model using the analogy with this process and the definition of a linear programming model.

Diagram illustrating the management guidelines for budget allocation, showing maximum ROI as the objective, with constraints on strategic objectives like operational excellence, sustainable d

Decision Variables

Objective Function

Your objective is to maximise the total return on investment of the portfolio of projects you selected

Mathematical equations representing decision variables in a linear programming model for budget planning. These variables are used to select projects based on the defined constraints and obje

Budget Limitations (Constraints)

You have a budget of 4.5 M€ that you split into three years (1.25M€, 1.5M€, 1.75M€).

Graphical representation of the objective function in a linear programming model, aiming to maximize the total return on investment of selected projects in the budget planning process.

Strategic Objectives (Constraints)

Graph depicting strategic objectives as constraints within the linear programming model. These objectives ensure that selected projects align with the company’s long-term goals, including ope

We will fix the minimum budget at 1M€ for the three key pillars.

Initial Solution: Maximise the ROI

In order to understand the added value of this model, let‘s have a look at what would be the allocation if we remove strategic objectives constraints.

💡

Return of Investment = 1,050,976 Euros36/58 Projects Accepted with a Budget Allocation of 4.07/4.5 M€

The results are satisfying with a good ROI and more than 80% of the budget allocated.

What about the allocation by strategic objectives?

Bar chart showing the initial solution for maximizing ROI in budget planning without strategic objectives constraints. The chart visualizes budget allocation for 36 accepted projects out of 5

When you ask the model to focus on profitability, you do not reach the management targets.

Final Solution

If we have the requirements of minimum budget allocation for the key pillars of the company’s long-term strategy:

💡

Return of Investment = 909,989 Euros34/58 Projects Accepted with a Budget Allocation of 4.15/4.5 M€

The return on investment is slightly impacted.

What about the management targets?

Bar chart illustrating the final solution with strategic objectives constraints for key company pillars in budget planning. The chart shows budget allocation for 34 accepted projects out of 5

The management guidelines are respected.

💡

You can find the full code with dummy data in my Github (Follow me :D) repository: Link

Conclusion

💡

If you have any question, feel free to here: Ask Your Question

This simple model provides the capacity to automate decision-making while ensuring allocation compliance.

It can be easily improved by adding constraints on

Maximum budget allocation per country, market vertical or warehouse
Budget allocation target (95% of the budget should be allocated)

This script can be implemented connected to an AI Agent to support the analysis of multiple scenarios and decision-making.

This is exactly what I did here,

About Me

Let’s connect on LinkedIn and Twitter, I am a Supply Chain Engineer that is using data analytics to improve logistics operations and reduce costs.

If you’re looking for tailored consulting solutions to optimize your supply chain and meet sustainability goals, feel free to contact me.