4
further depends on the accuracy of the input parameters.
Within the tool, calculations, especially over longer peri-
ods, must rely on average values (such as for transportation
routes), although different phases of the mining operation
can be defined. The assessment is conducted on an annual
basis. The main focus is on automating the calculations.
This means that the calculations must be automated to the
extent that, after a change of (almost) any parameter, the
new results should be immediately available “at the click of
a button,” without the need for manual entries or search-
ing for values in charts or tables. Due to the inference of
the different activities in an underground mine, these new
results will also be applied to all the other processes within
the mine. Calculations should be presented in a traceable
manner, with interim and final results clearly displayed and
easily usable.
DEVELOPMENT OF AN AUTOMATED
CALCULATION TOOL
Comprehensive software research was initially conducted [8
pp. 41–44], and with the prerequisites that the tool should
be useful to any employee, even at the smallest mines the
choice for the software foundation was made in favor of
Microsoft Excel. The calculation tool is a self-contained
Excel file without links to other files, structured into vari-
ous worksheets, as exemplified in Table 2 (p. 5) for a mine
with drilling and blasting operations.
The tool is generally divided into worksheets for data
input, calculation of individual technological steps and pro-
cesses, and presentation of results. It is essential that each
parameter is entered only once and then referenced by cell
links for further calculations to prevent input errors. A clear
display of all input values is provided in the input work-
sheets. Additional worksheets are used for variant compari-
sons or the definition of different scenarios. The number of
worksheets and the calculations conducted within them are
individually tailored to the technical processes and require-
ments of each mine.
To guide users and enhance visualization, a special
color-coding scheme is applied within the cells. All values
are displayed with units, and each value is documented
with a source to identify its origin and reliability. Using
the automatic data validation function, specific ranges for
numerical values or dropdown lists for selecting values or
parameters are provided. Additionally, for cost values, the
year of origin must be entered, and an automatic adjust-
ment of costs is carried out for a target year using cost indi-
ces according to Equation 1, ensuring that the calculations
remain up-to-date.
Costs today Price index then
Price index today
=
Updating costs [1 p. 13]
All calculations and intermediate results are linked through
cell references. For automation purposes, essential tables
and charts were digitized in Excel. Logical functions, such
as IF statements, along with more complex formulas like
VLOOKUP, INDEX, array formulas, and pivot tables,
enable automation.
Technical equipment, including its specifications and
costs, is recorded on a separate worksheet, which also con-
solidates utilization rates and operating times from all other
processes for future cost calculations. Figure 3 (p. 6) shows
schematically which input variables and complex dependen-
cies are to be taken into account when calculating the costs
for the technical equipment. The calculation of required
personnel and associated costs is also automated based on
machine utilization, with additional factors accounting for
other necessary tasks.
A central results worksheet summarizes all costs and
the CO₂ balance, providing both annual results and cumu-
lative values over a defined time period or extraction prog-
ress. Furthermore, costs are categorized into different cost
categories (e.g., OPEX vs. CAPEX, with further subdivi-
sions within OPEX) and are also presented as specific costs
for particular drift cross-sections or mining areas.
A dedicated worksheet is provided for conducting vari-
ant comparisons, where input parameters for each individ-
ual comparison can be selected in a tabular format. This
sheet allows for documenting both the input parameters
and results for different variants. The values can then be
directly analyzed through additional calculations or graphi-
cal representations. [8 pp. 41–100]
Validation on a Real Project for a Planned Hard Rock
Mine in Germany
The initial implementation of the developed approach for
an automated calculation tool was carried out using the
example of the planned quartz porphyry mine of Basalt-
Actien-Gesellschaft in Großsteinberg, Germany. Currently,
the deposit is being mined in an open pit, with plans to con-
tinue resource extraction underground. The planned access
involves two ramps from the existing open pit. Mining will
be conducted using drilling and blasting in a room-and-pil-
lar method. Initially, a top drift will be driven, followed by
chamber stoping. The dimensions of the future long cham-
ber are planned to be 20 meters wide and 32 meters high
in total. The deposit has been explored to a depth of at least
600 meters, with initial plans for mining the upper three
further depends on the accuracy of the input parameters.
Within the tool, calculations, especially over longer peri-
ods, must rely on average values (such as for transportation
routes), although different phases of the mining operation
can be defined. The assessment is conducted on an annual
basis. The main focus is on automating the calculations.
This means that the calculations must be automated to the
extent that, after a change of (almost) any parameter, the
new results should be immediately available “at the click of
a button,” without the need for manual entries or search-
ing for values in charts or tables. Due to the inference of
the different activities in an underground mine, these new
results will also be applied to all the other processes within
the mine. Calculations should be presented in a traceable
manner, with interim and final results clearly displayed and
easily usable.
DEVELOPMENT OF AN AUTOMATED
CALCULATION TOOL
Comprehensive software research was initially conducted [8
pp. 41–44], and with the prerequisites that the tool should
be useful to any employee, even at the smallest mines the
choice for the software foundation was made in favor of
Microsoft Excel. The calculation tool is a self-contained
Excel file without links to other files, structured into vari-
ous worksheets, as exemplified in Table 2 (p. 5) for a mine
with drilling and blasting operations.
The tool is generally divided into worksheets for data
input, calculation of individual technological steps and pro-
cesses, and presentation of results. It is essential that each
parameter is entered only once and then referenced by cell
links for further calculations to prevent input errors. A clear
display of all input values is provided in the input work-
sheets. Additional worksheets are used for variant compari-
sons or the definition of different scenarios. The number of
worksheets and the calculations conducted within them are
individually tailored to the technical processes and require-
ments of each mine.
To guide users and enhance visualization, a special
color-coding scheme is applied within the cells. All values
are displayed with units, and each value is documented
with a source to identify its origin and reliability. Using
the automatic data validation function, specific ranges for
numerical values or dropdown lists for selecting values or
parameters are provided. Additionally, for cost values, the
year of origin must be entered, and an automatic adjust-
ment of costs is carried out for a target year using cost indi-
ces according to Equation 1, ensuring that the calculations
remain up-to-date.
Costs today Price index then
Price index today
=
Updating costs [1 p. 13]
All calculations and intermediate results are linked through
cell references. For automation purposes, essential tables
and charts were digitized in Excel. Logical functions, such
as IF statements, along with more complex formulas like
VLOOKUP, INDEX, array formulas, and pivot tables,
enable automation.
Technical equipment, including its specifications and
costs, is recorded on a separate worksheet, which also con-
solidates utilization rates and operating times from all other
processes for future cost calculations. Figure 3 (p. 6) shows
schematically which input variables and complex dependen-
cies are to be taken into account when calculating the costs
for the technical equipment. The calculation of required
personnel and associated costs is also automated based on
machine utilization, with additional factors accounting for
other necessary tasks.
A central results worksheet summarizes all costs and
the CO₂ balance, providing both annual results and cumu-
lative values over a defined time period or extraction prog-
ress. Furthermore, costs are categorized into different cost
categories (e.g., OPEX vs. CAPEX, with further subdivi-
sions within OPEX) and are also presented as specific costs
for particular drift cross-sections or mining areas.
A dedicated worksheet is provided for conducting vari-
ant comparisons, where input parameters for each individ-
ual comparison can be selected in a tabular format. This
sheet allows for documenting both the input parameters
and results for different variants. The values can then be
directly analyzed through additional calculations or graphi-
cal representations. [8 pp. 41–100]
Validation on a Real Project for a Planned Hard Rock
Mine in Germany
The initial implementation of the developed approach for
an automated calculation tool was carried out using the
example of the planned quartz porphyry mine of Basalt-
Actien-Gesellschaft in Großsteinberg, Germany. Currently,
the deposit is being mined in an open pit, with plans to con-
tinue resource extraction underground. The planned access
involves two ramps from the existing open pit. Mining will
be conducted using drilling and blasting in a room-and-pil-
lar method. Initially, a top drift will be driven, followed by
chamber stoping. The dimensions of the future long cham-
ber are planned to be 20 meters wide and 32 meters high
in total. The deposit has been explored to a depth of at least
600 meters, with initial plans for mining the upper three