Production planning problem

Production planning at a drone factory

drone


In this tutorial we show you how to solve a production planning operations research problem, using XLOPTIM, the fastest and most reliable solver for Microsoft Excel.

1. Data

To download the data for this XLOPTIM use case, please click the link below:

Download data for production planning problem

2. The problem

The OptiDrones company specializes in the production of drones for both individuals and professionals. It manufactures 20 different drone models.

Your colleagues from the statistics team have prepared monthly sales forecasts for drones of the Super21 type for the first 6 months of the year:

table

Following measurements carried out over several weeks, the total assembly time of a Super21 in this workshop was estimated at 3.5 hours. The assembly shop operates 7.5 hours per day in normal times and each month has an average of 22 working days.

OptiDrones can increase its production while working overtime within the limit of 25 extra hours per employee per month. Employees are paid $1,850 per month for normal work and $15 for each hour of overtime.

OptiDrones can hire staff every month, or transfer them to another workshop. Hiring a new employee represents a cost of $2,500 during the first month of hiring. Transferring a worker to another shop also represents a cost of $800 because of the procedures associated with changing the shop.

The collective agreement stipulates that the OptiDrones assembly workshop must always employ at least 10 people. Taking into account its size, it cannot accommodate any more than 50 people at the same time.

It is possible to store drones. The cost of storing a drone is estimated at $2 per month and per drone. We want to permanently keep a safety stock of 350 drones, in particular to ensure after-sales service.

It is estimated that on 31 December before starting the period concerned, OptiDrones employs 20 employees in the workshop, and has a stock of 600 drones of the Super21 type.

We seek to propose a linear modeling of this production planning problem as well as a decision support tool in XLOPTIM.

3. Modeling

We propose a modeling according to the following 5 steps:

Step #1: What are the dimensions / clues of the problem?

  • t: 1 .. 6: the months of the year

Step #2: What are the data of the problem?

  • Monthly need for drones (1,200 / 2,300 / 2,500…):

$$\small{B_t}$$

  • Daily hourly volume (7.5 h)
  • Number of hours per drone (3.5 h)
  • Number of working days per week (22 days)
  • Maximum number of overtime hours (25 h)
  • Monthly salary for normal hours ($1,850.00)
  • Salary per additional hour ($15.00 / h)
  • Rental fees ($2,500.00)
  • Transfer fee ($800.00)
  • Initial number of employees (20)
  • (Minimum Number of Employees)
  • Maximum number of employees (50)
  • Storage costs ($ 2.00)
  • Storage security level (350)
  • Initial stock level (600)

Step #3: What are the decision variables?

  • Number of monthly recruitments (Decision variable):

$$R_t$$

  • Number of monthly transfers (Decision variable):

$$T_t$$

  • Number of monthly workers present (Redundant variable) :

$$E_t$$

  • Number of drones produced in normal hours (Decision variable):

$$N_t$$

  • Number of drones produced in overtime (Decision variable):

$$U_t$$

  • Stock of drones at the end of the month (Redundant variable):

$$S_t$$

Step #4: What are the constraints?

  • Non-negativity of decision variables:

$$R_t \geqslant0,\;T_t \geqslant0,\; E_t \geqslant0,\; N_t \geqslant0,\; U_t \geqslant0,\; S_t \geqslant0$$

  • Writing of the redundant variable on the monthly workforce:

The number of workers present in month t is equal to those in month t-1 plus recruitments made in month t minus transfers made in month t

$$\forall t \in[1..6], E_t=E_{t-1}+R_t-T_t$$

  • Writing of the redundant variable on monthly stocks:

The number of drones in stock at the end of month t is equal to those for month t-1 minus sales made in month t plus new drones produced in month t during normal hours and overtime

$$\forall t \in[1..6], S_t=S_{t-1}-B_t+N_t+U_t$$

  • Constraints on the min and max staff in the workshop each month:

$$\forall t \in[1..6], E_t \geqslant10$$

  • Constraints on the strategic stock:

$$\forall t \in[1..6], S_t \geqslant350$$

  • Work capacity constraints in normal hours:

The number of drones produced in normal hours cannot exceed the total production capacity of the workforce present in the workshop

$$\forall t \in[1..6], N_t \leqslant\frac{7,5.22}{3,5}.E_t$$

  • Constraints of capacity of work in overtime:

The number of drones produced in overtime cannot exceed the total capacity of production of manpower in overtime. Note that since these overtime hours are more expensive than normal working hours, the solver will favor the choice of normal hours before calling upon overtime if necessary each month.

$$\forall t \in[1..6], U_t \leqslant\frac{25}{3,5}.E_t$$

Step #5: What is the objective of the problem?

The objective here is to minimize all the costs over the 6 month period, namely the costs of emplyees (in normal hours and overtime), new hires, transfers as well as storage costs. Represent by the following formula:

$$Min\sum_{t=1}^{6}{1\,850\;.\;E_t+15\;.\;3,5\;.\;U_t+2\,500\;.\;R_t+800\;.\;T_t+2\;.\;S_t}$$

4. Translation into XLOPTIM

xloptim1

  • Definition of the 4 main decision variables (with their limits):

xloptim2 xloptim3 xloptim4 xloptim5

  • Addition of the constraint on the strategic stock:

xloptim6

  • Attachment of the objective:

xloptim7

  • Calculation of the optimum:

xloptim8

  • which gives a value of the following optimal objective function:

xloptim9

5. Conclusion

The displayed result is optimal and obtained after 2 seconds. To meet future demand for drones, the company will need to hire a total of 23 employees (see decision variables). The total cost of this hiring policy will amount to € 570,683.

This case study allows you to discover an example of a production problem. They are frequent in factories or in supply chain problems.

You can change the size of the problem and the assumptions to change the problem and therefore the solutions. Do not hesitate to contact us if you have any questions following the use of the XLOPTIM® solver.

To download the 14-day free trial version of the software, go to: Download the trial version