#;I...,;\

:‘- ‘%‘b>

w

United States

L.2 Department of

Agriculture

@” ‘h,

0

U4S 1.111 Forest Service

Southern Forest

Research Nate

Experiment Station so-337

Ami1 1987

Spreadsheet Analysis of Harvesting Systems

R.B. Rummer and B.L. Lanford

SUMMARY SPREADSHEET SOFTWARE

Harvesting systems can be modeled and analyzed on

microcomputers using commercially available

“spreadsheet” software. The effect of system or external

variables on the production rate or system cost can be

evaluated and alternative systems can be easily examined. The tedious calculations associated with such

analyses are performed by the computer. For users familiar with harvesting cost analysis, a minimum amount

of training is necessary to use the methodology described. This paper presents the basic procedure for

implementing system analysis with a spreadsheet program.

An electronic spreadsheet is a grid of rows and

columns stored in computer memory and displayed on

the computer screen. The intersection of a row and a

column is called a cell. For example, cell Al is the intersection of column A with row 1. The user can put words,

numbers, or formulas into any cell of the spreadsheet.

The computer automatically performs all the calculations

indicated by the formulas placed in the cells.

INTRODUCTION

The repetitive calculations involved in determining an

optimum mix of harvesting equipment, predicting system

costs and production rates, or evaluating alternative systems can be a tedious chore for a logging manager or

forest engineer. Simply because of the time required,

optimization of harvesting systems “by hand” is difficult

and expensive. Microcomputers, with their speed and

relatively low cost, can inexpensively perform the same

calculations in a fraction of the time. Using an electronic

spreadsheet program as a calculation tool, a person with

no previous programming experience can successfully

formulate and solve many harvesting system problems.

VisiCalc, SuperCalc, and Lotus l-2-3’ are examples of

commercially available spreadsheet software widely

used in business and accounting applications. Many logging managers may already be familiar with the mechanics of spreadsheet operation.

The power of spreadsheets derives from the use of

cell addresses, or references, in formulas. Cell B3, for

example, could contain the formula “Bl +B2”. The computer would add the value in Bl to the value in B2 and

display the result in 83. The actual contents of cell Bl

and 82 could be numbers or formulas that generate a

number. Using cell references in formulas establishes an

interrelationship between the cells of a spreadsheet.

Changing any formula or number is a simple matter and

the entire spreadsheet is automatically recalculated

showing the effect of the change. The ease of recalculation makes the spreadsheet a valuable tool, providing

the ability to answer “what if” questions.

HARVESTING SYSTEM ANALYSIS

‘The use of trade or firm names in this publication is for reader

information and does not imply endorsement by the U.S. Department

of Agriculture of any product or service.

Traditionally, harvesting system analysis is done by

hand using an iterative, what-if approach. Starting with

some basic assumptions-average d.b.h., acres in the

tract, machine rate, etc.-production rates for each element in the harvesting system are estimated with appropriate regression equations. The production rates for all

of the elements in the system are examined to determine

the element with the minimum production rate. This limiting element determines the actual utilization of every

other element in the system and fixes the system production rate and cost. After calculating an initial system

production rate and cost, the logging manager adjusts

the system, altering the balance of equipment for exam

Southern Forest Experiment Station/T-l0210 U.S. Postal Services Bldg., 701 Loyola Avenue, New Orleans, La. 70113

Forest Service, U.S. Department of Agriculture.

Serving Alabama, Arkansas, Louisiana, Mississippi, E. Oklahoma, Tennessee, E. Texas, Puerto Rico, U.S. Virgin Islands

1
pie, and recalculates the system production rate and

cost. It is possible to arrive at a practical optimum system

balance or to determine the effect of changing stand and

system variables with this approach, but it can be tedious and time consuming.

The procedure outlined above is easily incorporated

into a spreadsheet. A simple spreadsheet model of a

harvesting system will have two sections-a section of

basic assumptions about the stand and system, and a

section to calculate a production rate for each element in

the system. A spreadsheet function that determines the

minimum value in a specified range of cells is used to

find the element with the lowest productivity. Given the

basic system configuration and the limiting production

rate, the spreadsheet can calculate system production

rate, system cost, and actual utilization rates for each of

the system elements. A simple example will serve to

illustrate the mechanics of calculating a production rate

with a spreadsheet.

SHORTWOOD SYSTEM EXAMPLE

An analysis of a shortwood harvesting system might

require the determination of a production rate for manually felling, limbing, and bucking the wood. A regression

equation that estimates a volume production rate for this

system element might be:

Cords/Productive MachineHour =

-0.6035 + 0.2536*DBH - O.O052*(DBH*)

Given an average d.b.h. for the stand and a utilization

rate (productive hours/scheduled hour), the production

rate for this element can be determined.

Cords/Scheduled Hour = $$$ x i

where: PMH = Productive Machine Hours

SMH = Scheduled Machine Hours

Production costs can then be calculated using appropriate fixed and variable costs.

This example, entered in a spreadsheet program and

displayed on the computer screen, might look like figure 1. Cell Al contains the title “Av. DBH”. Cell Bl contains the value of the average d.b.h., 10 inches. The

regression equation, entered in cell 83, calculates a production rate based on the average d.b.h. value in Bl.

Production per SMH is calculated by the equation in cell

84. The assumed fixed and variable costs are applied to

the calculated production rate to arrive at a cost for this

element (cell B5).

Once the spreadsheet has been laid out, it is a simple

matter to change numbers and recalculate. For example,

to determine the effect of larger timber on production

cost, change the d.b.h. in cell Bl. The spreadsheet automatically recalculates all the formulas and displays the

new values.

An entire harvesting system can be set up in a similar

fashion. Figure 2 illustrates a section of a spreadsheet

model for a shortwood system that includes manual

felling, limbing, bucking, handpiling, forwarding to setout trailers, and hauling to a wood yard or mill. This

spreadsheet calculates a production rate (column E) for

each element of the system using the maximum utilization for the element, a regression equation, and the number of production units (i.e., men or machines) assigned

to the element. The minimum production rate of all the

elements is the limiting factor that determines the system

production rate. In figure 2, the system production rate is

the minimum value of cells El9 through E22 and is calculated in E23.

The system production rate is used to calculate the

actual utilization of each element. The actual utilization

is, in turn, used to calculate element costs based on a

cost equation and the number of production units.

Woods labor costs, in this example, are calculated as a

separate item based on production. Cell H24 simply multiplies the number of workers times the system production rate by the labor cost per man per cord. Total system

cost is the sum of all the previously calculated costs.

Starting with a system description like figure 2, the

spreadsheet can be expanded to the required level of

detail. If, for example, the user wanted to evaluate the

effect of haul distance on system costs, more data might

be included to determine the hauling production rate. An

additional section could be added to the spreadsheet to

calculate the hauling production rate as a function of

haul distance and average travel speed. Similarly, when

the effect of stand composition is a concern, the value of

d.b.h. used in the regression equations can be developed from a stand table included in the spreadsheet.

APPLICATIONS

A spreadsheet can be used to help solve many system

analysis problems. Depending on the complexity and

configuration of the spreadsheet model, the user can

evaluate the effects of system balance or external variables on production rate and system costs. With a

spreadsheet, the system configuration can be altered to

F!. B. Rummer is a graduate co-op research engineer, Southern Forest Experiment Station, Forest Service-USDA, Auburn, AL. B. L. Lanford is

associate professor, Alabama Agricultural Experiment Station, Auburn University, AL.

2
1

2

3

4

5

6

A B C D

‘Av. DBH ; ‘DBH SQ ; B la*2

-----------------------------

‘Fixed $

I

, 4.94 1, , Variable $I 3.00 I

------------------------m--m- I I

I

‘Cds/PMH , -.6035+.2536 Bl-.0052aDl ’, Utiliz’n =

I

.5 I

------------------m--------e- I I

‘Cds/SMH 1 B3*D3 I I I

I I I -----------------------------

‘$/SMH ; B2+D2*D3 I

----------------

‘$/Cd 1 B5/B4

------------

Figure 1 .-Sample spreadsheet showing the formula content of the cells.

A B C D E F G Ii

18 “Element ‘Units

19 l Feil,L6B 5

‘Cdr/SMH

---em

C19tD19

“Act. Utll ‘WSMH

----- ----

E23/ClQ B 1 Q*Cost

20 ‘Handpile 5 C20*D20 E23/C20 B2oIcost

21 “Forward 1

2

--e--w

SUM(B19:B22)

‘Cds/PMH ‘Max Util

----- -----

B 1 %Eq’n .5

B20&q’n .5

82 1rEq’n 39

B22aEq’n .9

-m-s- m---e

C2 l*D2 1 E23/C21 62 1*cost

22 ëHClUl

-----

‘No. men

C22*D22

--e-e

MIN(E19:E22)

E23IC22

- -----

23

B22*ost G22/E23

---- _----

SlJM(G19:G22) SlJM(HlQzH22)

24 ‘System cds/SMH E23 “Labor S/cd

25 ‘Total S/cd

‘S/Cd

---

G19/E23

G20/E23

G2l/E23

B23*E23*Coet

--seH23+H24

Figure 2.-Spreadsheet model of a hypothetical shortwood harvesting system.

3
determine the performance of alternative systems. Because of the ease of manipulation and speed of calculation, many alternatives can be examined and an optimum configuration identified.

LIMITATIONS

While spreadsheet analysis has many possibilities,

some limitations and cautions should be noted. As with

any computer application, the results of the spreadsheet

analysis are only as good as the information used to

calculate them. The user must insure that the formulas

and numbers used in the spreadsheet are appropriate

for his application. For example, a regression equation

for skidder production based on studies conducted in

steep terrain may not accurately predict production rates

in a swampy setting. Likewise, data from sparsely

stocked, natural stands may generate misleading results

in an analysis of a system for evenly spaced, planted

stands. Cubbage (1983) discusses th,e various kinds of

production data available and notes some of the limitations associated with using each type.

Finally, users should understand that a spreadsheet

model, developed with the methodology presented here,

is a static model. There is no provision for dynamic interaction between system elements or the occurrence of

random events. The interference between skidders and

feller-bunchers might, for example, cause significant delays that would not be accounted for in a spreadsheet.

Mill shutdowns could cause system delays that are not

included in the model. Additionally, there is always variation in production rates that cannot be explained by the

regression equations. While the spreadsheet may give a

reasonable approximation of system performance, actual production rates will vary due to random events and

the effect of factors not included in the model.

CONCLUSION

The prudent application of the methodology presented

here can provide a useful tool for forest engineers and

logging managers. There is a large body of formulas and

data that can be used in this type of analysis. Cubbage

(1983) summarizes some of the available material relating to the harvest of southern pines. Requiring only a

moderate amount of effort and investment, spreadsheet

analysis offers an alternative to costly simulation programs and allows the user to concentrate on decisionmaking rather than calculating.

LITERATURE CITED

Cubbage, F.W. 1983. Harvesting productivity information for southern pines. Southern Journal of Applied

Forestry. 7(3):128-134.

4