The world’s leading publication for data science, AI, and ML professionals.

Automate Budget Planning Using Linear Programming

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

Budget Planning Problem with Linear Programming - (Image by Author)
Budget Planning Problem with Linear Programming – (Image by Author)

Companies often need to invest in IT capabilities, modern handling equipment or additional warehouse space to improve the efficiency of their operations.

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

As a data scientist, how can you support this decision-making process?

Because of budget constraints, they must decide 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 the…

  • 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)
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
1. Automate Decision Making
2. ESG-Friendly Budget Planning
3. Connect the model to GPT: Optimized User Interface

Budget Planning Process

Problem Statement: Operational Budget Planning

As a data scientist, you support Regional Director of an international logistics company.

He is responsible for logistics operations in four countries.

56 Projects in your Scope of Responsibility - (Image by Author)
56 Projects in your Scope of Responsibility – (Image by Author)

His teams manage operations for 48 customers grouped in over 8 market verticals (Luxury, Cosmetics …).

For each of the 17 warehouses, ** the Warehouse Manager (reporting to him) lists all the projects that need Capital Expenditure (CAPEX)**.

What parameters you have on hand to select projects?

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

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

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

Example of a CAPEX application form information - (Image by Author)
Example of a CAPEX application form information – (Image by Author)

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

What do we want to achieve?

Maximize the Return on Investments

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

(Image by Author)
(Image by Author)

Because there are 58 projects under his responsibility, let us build a simple tool to automate this decision-making process.


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


Build your model of linear optimization

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

Exploratory Data Analysis

For this year, you have 58 projects covering nine vertical markets.

(Image by Author)
(Image by Author)

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

(Image by Author)
(Image by Author)

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

(Image by Author)
(Image by Author)

What is the objective?

Linear Programming Problem

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

(Image by Author)
(Image by Author)

a. Decision Variables

(Image by Author)
(Image by Author)

b. Objective Function****Your objective is to maximize the total return on investment of the portfolio of projects you selected

(Image by Author)
(Image by Author)

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

(Image by Author)
(Image by Author)

d. Strategic Objectives (Constraints)

(Image by Author)
(Image by Author)

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

Initial Solution: Maximize the ROI

To understand the added value of this model, let’s examine the allocation if we remove strategic objectives constraints.

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

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

What about the allocation by strategic objectives?

(Image by Author)
(Image by Author)

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 Euros
34/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?

(Image by Author)
(Image by Author)

The management guidelines are respected.

You can find the complete code with dummy data in my GitHub repository

GitHub – samirsaci/budget-planning: Automate Budget Planning with Linear Programming

💡 Follow me on Medium for more articles related to 🏭 Supply Chain Analytics, 🌳 Sustainability and 🕜 Productivity.


Conclusion and Next Steps

Automate Decision Making

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

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)

What about the non-financial performance?

ESG-Friendly Budget Planning

Environmental, Social and Governance (ESG) reporting can be defined as a method corporations use to disclose their governance structures, societal impacts and environmental footprint to shareholders.

ESG Pillars - (Image by Author)
ESG Pillars – (Image by Author)

We can assume that our director must decide which project(s) to allocate her budget based on the financial aspect (ROI) and ESG criterion.

How to maximize the Return On Investment while meeting ESG requirements?

With linear programming, we can automate the selection of projects that maximize ROI while respecting constraints on CSR, HSE, or sustainability.

For more information on the parameters to add to your model, 👇

What is ESG Reporting?

Have you heard about generative AI?

Connect the model to GPT

With the recent adoption of Generative AI, we can enhance the user experience of any analytics product using large language models.

Supply Chain Control Tower Agent with LangChain SQL Agent [Article Link] - (Image by Author)
Supply Chain Control Tower Agent with LangChain SQL Agent [Article Link] – (Image by Author)

You can then automate this meticulous process, help managers with additional visual insights and accelerate decision-making.

Users: What if we put 30% of sustainability invest?

Agent: We have to reduce to 7% of Business Dev invest

In this article, you can explore the potential of LMMs used to boost analytics products and improve user experience.

Example of Architecture - (Image by Author)
Example of Architecture – (Image by Author)

With custom GPTs, you can share your core model (Python script), sample data, and prompt instructions for deploying a model on ChatGPT’s UI.

For more information,

Create GPTs to Automate Supply Chain Analytics

Leveraging LLMs with LangChain for Supply Chain Analytics – A Control Tower Powered by GPT


About Me

Let’s connect on Linkedin and Twitter. I am a Supply Chain Engineer who uses data analytics to improve Logistics operations and reduce costs.

For consulting or advice on analytics and sustainable supply chain transformation, feel free to contact me via Logigreen Consulting.

If you are interested in Data Analytics and Supply Chain, look at my website.

Samir Saci | Data Science & Productivity

💌 New articles straight in your inbox for free: Newsletter 📘 Boost your Productivity with Data Analytics: Productivity Cheat Sheet


Related Articles