The Overproduction Problem—Mathematical Optimization of Production Levels in a Confectionery

The perennial problem faced by bakers and confectioners is how much to produce so that it does not later have to be thrown away. It is well known that, in a confectionery, only products made today count. Yesterday’s assortment is returned or sold for no more than half its previous value. If the confectioner is too cautious, they will bake too few cakes and will not earn as much as they could have earned if they had taken the risk and baked more. An additional cost is the level of customer dissatisfaction and disappointment. In the future, customers may find another confectionery where their favorite cake is always available.

A risk-taking confectioner, in turn, will bake too many cakes and will later have to sell them off or dispose of them. Such a situation also demoralizes customers. Some will refrain from buying a cake today in order to buy it more cheaply tomorrow.

Ideally, enough cake should be produced for everyone, with nothing wasted.

W-MOSZCZYNSKI-2021-6-45

Machine learning comes to the rescue

To deal with the problem, it is sufficient to build a linear-regression model based on weather data, days of the week, the season and other explanatory variables. The result will be a forecast of demand for cakes on a particular day. In the case of stable sales, regression forecasts are usually sufficiently effective. They can predict future sales volumes at a level of r² = 85–95%. The mysterious r² is the so-called coefficient of determination, which specifies the degree to which the forecast fits the actual state. A model at the level of r² = 87% would be wrong by 13% in either direction.

Building such forecasts is, of course, work for a specialist who, when carrying out such a commission, most often launches every regression model known and available to them, including convolutional neural networks. This is a major undertaking and, in this case, completely unjustified. It resembles shooting flies with a cannon.

Building models makes sense when the scale of the undertaking is appropriate. Such a scale would be, for example, the sales level on every day of the following two months for several dozen products in a dozen or so locations. Such a model would be permanently embedded in the production-management system and would generate ready production orders assigned to specific sales locations. It could be expanded with further functions, such as forecasts for particular sales hours, allowing the optimal quantity of bread to be delivered for the second shift.

What should a confectioner who records everything in a notebook do?

Machine-learning solutions are expensive and may prove uneconomical on a small scale. An old truth says that, in order to predict something in the future, records from the past should be used.

Imagine a confectioner who records in a notebook how many cakes were sold each day. Let us assume that tomorrow is an ordinary Thursday. The confectioner opens the notebook and examines the sales level on the last 35 ordinary Thursdays. The data are then copied into a spreadsheet. The latest sales are summarized below. The first column contains the number of cakes sold. The second shows how many times that sales volume occurred during the 35 days analyzed.

For example, the confectioner achieved sales of 51 cakes on four different days during the 35-day period analyzed.

Table 1. Distribution of the number of cakes sold over 35 days

How many cakes were sold?	How many times did this occur?
50	3
51	4
52	2
53	3
54	3
55	5
56	3
57	4
58	3
59	3
60	2

The confectioner then expanded the table with two further columns: “Probability” and “Cumulative probability.”

Table 2. Probability distribution of cake sales over 35 days

How many cakes were sold?	How many times did this occur?	Probability	Cumulative probability
50	3	0.086	0.086
51	4	0.114	0.200
52	2	0.057	0.257
53	3	0.086	0.343
54	3	0.086	0.429
55	5	0.143	0.571
56	3	0.086	0.657
57	4	0.114	0.771
58	3	0.086	0.857
59	3	0.086	0.943
60	2	0.057	1.000

The calculations for these columns are very simple. If the confectioner sold 50 cakes three times during the 35 days—the first row in the table—the probability of that sales level is calculated as 3/35 = 0.086.

“Cumulative probability,” in turn, is merely the sum of consecutive probabilities. For sales of 52 cakes, it will therefore be 0.086 + 0.114 + 0.057 = 0.257.

The confectioner also consulted the production costs and selling prices recorded in the notebook:

unit production cost of a cake: c₁ = PLN 14
unit selling price of a cake: c₂ = PLN 25
unit selling price of a cake after its sell-by date: c₃ = PLN 9

It is easy to calculate that the profit from daily product sales is the revenue from sales of full-value and reduced-value products, less the production costs of the products sold.

If the confectioner produced 60 cakes and sold 50, the profit obtained was PLN 500:

500 = (50 × 25) + (10 × 9) − (60 × 14)

Had the confectioner produced 50 cakes instead, the profit would have been PLN 50 higher:

550 = (50 × 25) − (50 × 14)

To optimize the production level, the simplest rules must first be written in the form of inequalities.

c₂ > c₁ > c₃

(c₂ − c₁) = z, profit on a sale
(c₁ − c₃) = s, loss arising from a markdown                 (1)

In our example, the profit from selling a cake is the difference between the selling price and the production cost: PLN 25 − PLN 14 = PLN 11.

The unit loss, meanwhile, can be written as PLN 14 − PLN 9 = PLN 5.

Everything therefore depends on whether the confectioner produced more cakes—the quantity produced, k—than were demanded—the quantity sold, p.

When the confectioner produced too few cakes, k, there would not be enough for all customers, p; that is, p > k.

The relationship between demand and supply can be written using a simple formula, where z is unit profit and s is the unit loss described in formula (1).

          { (z × p) − (s × (k − p)),  if p < k
g(k,p) = {                                      (2)
          { z × k,                    if p ≥ k

The next formula is simply the product of all possible profits from the production and sales volumes and their probabilities. To make the optimal decision, the largest value in vector d(k), described by formula (3), must be selected.

         N
d(k) =  ∑ g(k,p) × p(p)                              (3)
        p=n

The confectioner copied formula (2) into a spreadsheet function and pasted it into the cells of the following table.

Table 3. Profit table by production and sales variant

Production Sales	50	51	52	53	54	55	56	57	58	59	60	d(k)
50	550	550	550	550	550	550	550	550	550	550	550	550.0
51	545	561	561	561	561	561	561	561	561	561	561	559.629
52	540	556	572	572	572	572	572	572	572	572	572	567.429
53	535	551	567	583	583	583	583	583	583	583	583	574.314
54	530	546	562	578	594	594	594	594	594	594	594	579.829
55	525	541	557	573	589	605	605	605	605	605	605	583.971
56	520	536	552	568	584	600	616	616	616	616	616	585.829
57	515	531	547	563	579	595	611	627	627	627	627	586.314
58	510	526	542	558	574	590	606	622	638	638	638	584.971
59	505	521	537	553	569	585	601	617	633	649	649	582.257
60	500	516	532	548	564	580	596	612	628	644	660	578.171

The number of cakes sold, from 50 to 60, is entered in the table header (the table columns); the number of cakes produced, from 50 to 60, is entered in the stub (the table rows).

The body of the table is filled with calculations of sales profit. Thus, according to formula (2), when 54 cakes were produced—the fifth row—and 50 cakes were sold—the first column—the operating profit was PLN 530. How was this calculated?

530 = (50 × 25) + (4 × 9) − (54 × 14)

As we remember, PLN 25 in the formula above is the selling price of a cake, PLN 9 is the price of a discounted cake, and PLN 14 is the technical cost of making a cake. The confectioner produced 54 cakes, of which 50 were sold and four had to be discounted.

How many cakes should be produced to earn as much as possible?

It is not difficult to see that the more we sell, the more we earn. On the other hand, overproduction causes profit to decline. In other words, every cake produced in excess reduces our daily profit.

To find the optimal production level, the probability distribution in Table 2 must be used. If we multiply the probability of a particular sales volume by the profit from every production and sales variant in Table 3, we obtain Table 4.

Table 4. Profit adjusted for the probability of sale

Production Sales	50	51	52	53	54	55	56	57	58	59	60	d(k)
50	47.143	62.857	31.429	47.143	47.143	78.571	47.143	62.857	47.143	47.143	31.429	550.000
51	46.714	64.114	32.057	48.086	48.086	80.143	48.086	64.114	48.086	48.086	32.057	559.629
52	46.286	63.543	32.686	49.029	49.029	81.714	49.029	65.371	49.029	49.029	32.686	567.429
53	45.857	62.971	32.400	49.971	49.971	83.286	49.971	66.629	49.971	49.971	33.314	574.314
54	45.429	62.400	32.114	49.543	50.914	84.857	50.914	67.886	50.914	50.914	33.943	579.829
55	45.000	61.829	31.829	49.114	50.486	86.429	51.857	69.143	51.857	51.857	34.571	583.971
56	44.571	61.257	31.543	48.686	50.057	85.714	52.800	70.400	52.800	52.800	35.200	585.829
57	44.143	60.686	31.257	48.257	49.629	85.000	52.371	71.657	53.743	53.743	35.829	586.314
58	43.714	60.114	30.971	47.829	49.200	84.286	51.943	71.086	54.686	54.686	36.457	584.971
59	43.286	59.543	30.686	47.400	48.771	83.571	51.514	70.514	54.257	55.629	37.086	582.257
60	42.857	58.971	30.400	46.971	48.343	82.857	51.086	69.943	53.829	55.200	37.714	578.171

Thus, using formula (3), when 50 cakes were sold—the first column—and 54 cakes were produced—the fifth row—the operating profit is PLN 530.

We multiply this value by the probability of selling 50 cakes, which can be found in Table 2:

45.428573 = 530 × 0.08571429

The sum of all probabilities in a row gives us the value in the last column of Table 4, denoted d(k). This value is also described by formula (3).

Our task now is to find the highest value in column d(k). It turns out that, taking into account sales profit, the loss from overproduction and the probability of that configuration, producing 57 cakes is the most profitable option. The sum of probable profit will then amount to PLN 586.31.

Is it worthwhile?

The method presented is probably the simplest mathematical optimization that can be carried out. Anyone who wants to maximize profit must, sooner or later, confront the need to calculate losses and profits together with their probabilities.

The method presented can be introduced very easily into a spreadsheet, allowing it to be used to optimize the production quantities of many different products.

Wojciech Moszczyński — graduate of the Department of Econometrics and Statistics of Nicolaus Copernicus University in Toruń; specialist in econometrics, finance, data science, and management accounting. He specializes in the optimization of production and logistics processes. He conducts research in the area of the development and application of artificial intelligence. For years he has been engaged in the popularization of machine learning and data science in business environments.