Automate Budget Planning Using Linear Programming

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

Automate Budget Planning Using Linear Programming

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

Article originally published on Medium.

Objective

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

Introduction

In the Logistics industry, 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. 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 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)

💌 New articles straight in your inbox for free: Newsletter

💡
SUMMARYI. Scenario: Budget Planning ProcessAs a Regional Director you need to allocate your budget on projectsII. Build your Model1. Exploratory Data AnalysisAnalyze the budget applications received2. Linear Programming ModelDecisions variables, objective function and constraints3. Initial Solution: Maximum ROIWhat would be the results if you focus only on ROI maximization?4. Final Solution: Management GuidelinesIII. Conclusion & Next Steps

If you prefer watching, have a look a the Youtube tutorial

I. Scenario

Problem Statement

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

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

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

For each of the 17 warehouses, the Warehouse Manager (reporting to you) lists all the projects that need 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 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)

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 maximizes your profits (ROI) and respect the guidelines of the top management.

(Image by Author)

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

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

II. Build your model

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

1. Exploratory Data Analysis

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

(Image by Author)

Automotive and Luxury markets are representing a large part of the budget allocations because of the warehouse extensions projects.

(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)

2. 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)

Decision Variables

(Image by Author)

Objective Function

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

(Image by Author)

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)

Strategic Objectives (Constraints)

(Image by Author)

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

3. Initial Solution: Maximize 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?

(Image by Author)

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

4. 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?

(Image by Author)

The management guidelines are respected.

III. Conclusion and Next Steps

Conclusion

This simple model provides the capacity to automate decision making while ensuring the compliance of 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)

Next Steps

This script can be implemented in a Flask web application and deployed with a function to upload excel files.

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

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.