How to calculate the optimal log breakdown in Excel

Calculate the optimal log sawing using an Excel program. For every sawmill, minimizing raw material waste production is crucial, significantly impacting the profitability of producing construction logs.

 

Example of Log Sawing Calculation in Excel

The log sawing program will be beneficial for small entrepreneurs ready to start their business in producing cylindrical logs and building wooden houses from them.

A certain quantity of logs, each 10 meters long, is processed for sawing. The sawmill has a contract for the supply of wood in the following quantities:

• 100 logs of 5 meters;
• 200 logs of 4 meters;
• 300 logs of 3 meters.

Sawing Options	Number of Logs			Waste
	5m	4m	3m
1	2	0	0	0
2	1	1	0	1
3	1	0	1	2
4	0	1	2	0
5	0	2	0	2
6	0	0	3	1

What will be the optimal number of logs to be sawed to meet the contract conditions? In other words, how to cut the logs with the minimum amount of production waste?

Solving the Log Sawing Problem

To create a solution model, it is necessary to determine all possible options for sawing logs, each 10 meters long, into 3, 4, and 5-meter segments, considering the length of the waste. The result of such calculations reveals 6 sawing options:

1. 2 logs of 5m (0m waste).
2. 2 logs of 5m + 4m (1m waste).
3. 2 logs of 5m + 3m (2m waste).
4. 3 logs of 3m + 3m + 4m (0m waste).
5. 2 logs of 4m (2m waste).
6. 3 logs of 3m (1m waste).

According to the task condition, X_i are sawed using method i. Then the formula F = X₁ + X₂ + X₃ + X₄ + X₅ + X₆ => min is correct.

A system can be created as follows:

The next step is to prepare a Microsoft Excel table containing the initial conditions of the problem.

Formulas are introduced to calculate the left side of the constraint and the objective function. Cells J3:J5 are filled with these formulas:

=B3*$B$7+C3*$C$7+D3*$D$7+E3*$E$7+F3*$F$7+G3*$G$7

And in cell B8, enter the formula: =SUM(B7:G7).

Next, fill out the "Solver" and "Solver Parameters" dialog boxes. To do this, go to cell B8 and use the tool on the "DATA" - "Analysis" - "Solver" tab. If you do not have this tool on this tab, then follow the instructions as shown below:

Fill in the dialogue boxes with parameters as shown below and click the "Solve" button.

Download an example of calculating the optimal log breakdown in Excel

The results obtained in the table indicate that to meet the contract conditions, 225 logs will need to be sawed:

• 50 logs by method #1;
• 150 logs by method #4;
• 25 logs by method #5.

By using this Excel program template, you can fill in the input parameters with your values and choose the optimal solution in the same way.

Cubic table for calculating the volume of round timber based on the diameter and length of the log

D,см/ L,м	3	3.5	4	4.5	5	5.5	6	6.5	7	7.5	8
10	0.026	0.031	0.037	0.044	0.051	0.058	0.065	0.075	0.082	0.09	0.1
11	0.032	0.037	0.045	0.053	0.062	0.07	0.08	0.09	0.098	0.108	0.12
12	0.038	0.046	0.053	0.063	0.073	0.083	0.093	0.103	0.114	0.125	0.138
13	0.045	0.053	0.062	0.074	0.085	0.097	0.108	0.12	0.132	0.144	0.158
14	0.052	0.061	0.073	0.084	0.097	0.11	0.123	0.135	0.15	0.164	0.179
15	0.06	0.071	0.084	0.097	0.11	0.125	0.139	0.153	0.169	0.182	0.199
16	0.069	0.082	0.095	0.11	0.124	0.14	0.155	0.172	0.189	0.2	0.22
17	0.077	0.092	0.107	0.124	0.14	0.157	0.174	0.191	0.209	0.225	0.25
18	0.086	0.103	0.12	0.138	0.156	0.175	0.194	0.21	0.23	0.25	0.28
19	0.097	0.115	0.134	0.154	0.173	0.193	0.212	0.235	0.255	0.275	0.305
20	0.107	0.126	0.147	0.17	0.19	0.21	0.23	0.26	0.28	0.3	0.33
21	0.119	0.14	0.163	0.185	0.21	0.23	0.255	0.285	0.31	0.335	0.365
22	0.13	0.154	0.178	0.2	0.23	0.25	0.28	0.31	0.34	0.37	0.4
23	0.144	0.169	0.194	0.22	0.25	0.275	0.305	0.335	0.37	0.4	0.435
24	0.157	0.184	0.21	0.24	0.27	0.3	0.33	0.36	0.4	0.43	0.47
25	0.171	0.197	0.23	0.26	0.295	0.325	0.36	0.395	0.43	0.465	0.505
26	0.185	0.21	0.25	0.28	0.32	0.35	0.39	0.43	0.46	0.5	0.54
27	0.203	0.23	0.27	0.305	0.345	0.38	0.42	0.46	0.495	0.54	0.585
28	0.22	0.25	0.29	0.33	0.37	0.41	0.45	0.49	0.53	0.58	0.63
29	0.235	0.27	0.31	0.355	0.395	0.44	0.485	0.525	0.57	0.62	0.675
30	0.25	0.29	0.33	0.38	0.42	0.47	0.52	0.56	0.61	0.66	0.72
31	0.265	0.31	0.355	0.405	0.45	0.5	0.555	0.6	0.655	0.71	0.77
32	0.28	0.33	0.38	0.43	0.48	0.53	0.59	0.64	0.7	0.76	0.82
33	0.3	0.35	0.405	0.46	0.51	0.565	0.625	0.68	0.74	0.805	0.87
34	0.32	0.37	0.43	0.49	0.54	0.6	0.66	0.72	0.78	0.85	0.92
35	0.34	0.395	0.455	0.515	0.57	0.635	0.7	0.76	0.83	0.9	0.97
36	0.36	0.42	0.48	0.54	0.6	0.67	0.74	0.8	0.88	0.95	1.02
37	0.375	0.44	0.505	0.57	0.635	0.705	0.78	0.85	0.925	1	1.075
38	0.39	0.46	0.53	0.6	0.67	0.74	0.82	0.9	0.97	1.05	1.13
39	0.41	0.48	0.555	0.63	0.705	0.78	0.86	0.945	1.02	1.105	1.19
40	0.43	0.5	0.58	0.66	0.74	0.82	0.9	0.99	1.07	1.16	1.25
41	0.45	0.53	0.61	0.695	0.775	0.86	0.95	1.035	1.125	1.22	1.315
42	0.47	0.56	0.64	0.73	0.81	0.9	1	1.08	1.18	1.28	1.38
43	0.495	0.585	0.67	0.765	0.85	0.945	1.045	1.14	1.24	1.34	1.445
44	0.515	0.61	0.7	0.8	0.89	0.99	1.09	1.2	1.3	1.4	1.51
45	0.543	0.64	0.735	0.835	0.935	1.035	1.14	1.25	1.355	1.465	1.58
46	0.57	0.67	0.77	0.87	0.98	1.08	1.19	1.3	1.41	1.53	1.65
47	0.595	0.7	0.805	0.91	1.02	1.13	1.245	1.355	1.475	1.6	1.725
48	0.62	0.73	0.84	0.95	1.06	1.18	1.3	1.41	1.54	1.67	1.8
49	0.645	0.76	0.875	0.99	1.105	1.23	1.355	1.475	1.605	1.74	1.875
50	0.67	0.79	0.91	1.03	1.15	1.28	1.41	1.54	1.67	1.81	1.95
51	0.7	0.825	0.95	1.075	1.2	1.335	1.47	1.605	1.74	1.89	2.035
52	0.73	0.86	0.99	1.12	1.25	1.39	1.53	1.67	1.81	1.97	2.12
53	0.765	0.895	1.03	1.165	1.3	1.445	1.59	1.735	1.885	2.045	2.205
54	0.8	0.93	1.07	1.21	1.35	1.5	1.65	1.8	1.96	2.12	2.29
55	0.83	0.97	1.115	1.26	1.405	1.56	1.715	1.875	2.035	2.2	2.375
56	0.86	1.01	1.16	1.31	1.46	1.62	1.78	1.95	2.11	2.28	2.46
57	0.89	1.045	1.205	1.36	1.515	1.68	1.845	2.015	2.19	2.365	2.545
58	0.92	1.08	1.25	1.41	1.57	1.74	1.91	2.08	2.27	2.45	2.63
59	0.955	1.12	1.29	1.46	1.625	1.8	1.98	2.155	2.345	2.535	2.72
60	0.99	1.16	1.33	1.51	1.68	1.86	2.05	2.23	2.42	2.62	2.81
61	1.025	1.2	1.38	1.565	1.74	1.925	2.115	2.3	2.495	2.7	2.9
62	1.06	1.24	1.43	1.62	1.8	1.99	2.18	2.37	2.57	2.78	2.99
63	1.095	1.285	1.475	1.67	1.855	2.05	2.25	2.445	2.65	2.865	3.08
64	1.13	1.33	1.52	1.72	1.91	2.11	2.32	2.52	2.73	2.95	3.17
65	1.165	1.365	1.565	1.77	1.965	2.17	2.38	2.59	2.805	3.03	3.275
66	1.2	1.4	1.61	1.82	2.02	2.23	2.44	2.66	2.88	3.11	3.38
67	1.235	1.445	1.655	1.87	2.075	2.29	2.505	2.735	2.965	3.21	3.485
68	1.27	1.49	1.7	1.92	2.13	2.35	2.57	2.81	3.05	3.31	3.59
69	1.305	1.53	1.75	1.97	2.19	2.415	2.645	2.89	3.14	3.41	3.695
70	1.34	1.57	1.8	2.02	2.25	2.48	2.72	2.97	3.23	3.51	3.8
71	1.375	1.615	1.85	2.08	2.315	2.55	2.795	3.055	3.325	3.615	3.91
72	1.41	1.66	1.9	2.14	2.38	2.62	2.87	3.14	3.42	3.72	4.02
73	1.45	1.705	1.955	2.2	2.45	2.695	2.95	3.23	3.52	3.82	4.135
74	1.49	1.75	2.01	2.26	2.52	2.77	3.03	3.32	3.62	3.92	4.25
75	1.53	1.8	2.065	2.325	2.595	2.845	3.115	3.415	3.715	4.03	4.365
76	1.57	1.85	2.12	2.39	2.67	2.92	3.2	3.51	3.81	4.14	4.48
77	1.615	1.9	2.18	2.455	2.745	3	3.29	3.605	3.925	4.255	4.6
78	1.66	1.95	2.24	2.52	2.82	3.08	3.38	3.7	4.04	4.37	4.72
79	1.7	2	2.295	2.59	2.895	3.16	3.475	3.8	4.15	4.485	4.835
80	1.74	2.05	2.35	2.66	2.97	3.24	3.57	3.9	4.26	4.6	4.95
81	1.785	2.1	2.41	2.73	3.05	3.325	3.66	4.005	4.365	4.71	5.085
82	1.83	2.15	2.47	2.8	3.13	3.41	3.75	4.11	4.47	4.82	5.22
83	1.875	2.205	2.53	2.87	3.205	3.495	3.845	4.215	4.585	4.945	5.345
84	1.92	2.26	2.59	2.94	3.28	3.58	3.94	4.32	4.7	5.07	5.47
85	1.965	2.315	2.65	2.985	3.34	3.675	4.035	4.43	4.82	5.195	5.595
86	2.01	2.37	2.71	3.03	3.4	3.77	4.13	4.54	4.94	5.32	5.72
87	2.06	2.425	2.78	3.13	3.5	3.86	4.235	4.655	5.06	5.445	5.86
88	2.11	2.48	2.85	3.23	3.6	3.95	4.34	4.77	5.18	5.57	6
89	2.16	2.535	2.915	3.3	3.685	4.045	4.45	4.88	5.3	5.7	6.135
90	2.21	2.59	2.98	3.37	3.77	4.14	4.56	4.99	5.42	5.83	6.27
91	2.255	2.65	3.045	3.45	3.855	4.24	4.67	5.105	5.545	5.96	6.41
92	2.3	2.71	3.11	3.53	3.94	4.34	4.78	5.22	5.67	6.09	6.55
93	2.355	2.77	3.18	3.605	4.025	4.43	4.89	5.345	5.795	6.225	6.69
94	2.41	2.83	3.25	3.68	4.11	4.52	5	5.47	5.92	6.36	6.83
95	2.46	2.89	3.32	3.76	4.2	4.625	5.11	5.58	6.045	6.495	6.975
96	2.51	2.95	3.39	3.84	4.29	4.73	5.22	5.69	6.17	6.63	7.12
97	2.565	3.01	3.46	3.92	4.38	4.83	5.335	5.81	6.3	6.77	7.28
98	2.62	3.07	3.53	4	4.47	4.93	5.45	5.93	6.43	6.91	7.44
99	2.67	3.135	3.6	4.085	4.56	5.035	5.565	6.06	6.565	7.055	7.585
100	2.72	3.2	3.67	4.17	4.65	5.14	5.68	6.19	6.7	7.2	7.73