Expedition Route Plan spreadsheet (there is no macro code in this sheet.)

SHA-256

1e88cf56 6f7cfec0 37597e1f 8d154451 4b42c75d 39a6f098 0ec67334 9da51f38

This page describes a spreadsheet that calculates the route times based on one of three timing rules: Naismith, Tobler, or a novel method introduced here, called the Extended Generic (EG) model. The use of a spreadsheet allows for a more complex formula to be used to calculate route segment times. 

The spreadsheet automates the calculation of route segment times and progressive waypoint times throughout each day, for up to four expedition days. It contains lookup tables of timing factors for six expedition types, four age groups, three levels of fitness and experience. The user may also enter custom values if required. Values may also be entered for fatigue and contingency.

Overview

The spreadsheet contains nine sheets:

SetupDashboard to enter expedition information and variables
SummarySummary of general and daily travel information
Day 1 PlanRoute Plan daily pro-forma for first day of expedition
Day 2 Plan                    〃                         second           〃
Day 3 Plan                    〃                         third              〃
Day 4 Plan                    〃                         fourth            〃
TerrainDescriptions for Easy, Medium, Hard and Tough terrain for each expedition
Time ParamsTables of parameters for time models
ExampleA completed example (assumes Hiking by Adult with Average experience)

For normal use, only the Setup, Summary and each of the Day # Plan sheets need to be accessed. All sheets are protected with a password, which in the distribution is a blank, to only allow data entry into cells that require user input. This prevents inadvertent alteration of the formulas in the protected cells.

The page layout of each Day # Plan sheet uses Landscape orientation for printing on A4 paper.  The other sheets are structured vertically for ease of using on mobile devices and print in Portrait layout.

Dashboard Setup

The spreadsheet should open with the Setup sheet selected, otherwise click on its tab to select it. This sheet contains a dashboard to enter expedition and participant information, and other factors related to route timing. Values can only be entered in the white cells on this sheet (if Sheet Protection is on.)
The top section is for general information, starting with the name of the expedition Leader, the expedition Title, the Start Date, and the Magnetic Variation which are transferred to each of the Day # Plan sheets.
An entry in Nearest Main Town creates a link to an external website (timeanddate.com) where the Sunrise and Sunset times can be located. Not all locations are available so try another if the result is ‘not found’, or just use the site functionality to determine a suitable location.
The radar buttons allow selection of the desired Time Model, or the index value (1,2 or 3) can be entered directly (for use on devices which don’t support Excel controls.)
Route Plan Setup Dashboard
Route plan model warning
The Extended Generic model is universal. The Naismith and Tobler models can only be used for Hiking expeditions, and an alert message appears if either are selected.

The next section down is for entry of specific route information. Drop down lists allow selection of Expedition Type, Age and Experience. A text combination of these selections is transferred to the heading row in each Day # Plan sheet.

Six Expedition Types are available, including a Custom type for which the various time model parameters can be entered by the user.

Route plan select expedition type

The selection of Expedition Type is linked to that for the Time Model. If Naismith or Tobler have been nominated, and anything other than Hiking is selected, a warning message appears, and the selection cell is shaded red. Proceeding with an invalid combination will mean that the route times cannot be calculated automatically.

Route plan expedition warning
Four Age groups and three Experience levels can be selected.

From the selected Expedition Type, Age and Experience values, the Level Pace is found from tables in the Route Factors sheet.

The Level Pace may be over-ridden by entering a value in the Override Pace cell. Pace is the time in minutes for the slowest member of the expedition to travel 1km on level ground (or slack water if paddling.)

The final two entries are Fatigue Hours, and Daily Time Contingency.  The Fatigue Hours reduces the speed throughout the day. It is entered as the number of hours to be added at 5 hours of travel but is applied linearly throughout the whole journey.

The Daily Time Contingency is the percentage of the total Travel Time added to the Plan Finish time. The result is represented on each Day # Plan sheet as a Pessimistic Finish time.

Route plan select age and experience
Route plan adjustments
The blue rows show the Level Pace used for route timing, and its equivalent Average speed.

Day Plan sheets

The layout of each of the Day # Plan sheets mimics that of the Word or pdf pro-forma which can be downloaded from here.

The information in the top row, and the Magnetic Variation at the right end of the second row, is transferred from the general information section of the Setup sheet. This ensures all Day # Plan sheets have consistent information. The Date and Day number are automatically incremented for each sheet.
Route plan example headings

Details of the maps used are entered on the second row

Route plan example maps

The main part of the sheet contains the waypoint list and route segment directions, descriptions and time estimates. Click here for general information about how to enter grid references, segment distances and bearings, and elevation changes.

Route plan example waypoints

The Place and Grid Reference column is split into two rows for each waypoint. Enter a brief waypoint name in the first row and the grid reference in the second.

For the Start point, the planned departure time is entered in the ETA (Estimated Time of Arrival) cell. All other ETAs are calculated. The ATA column is for use during the expedition to enter the Actual Time of Arrival at each point. It then becomes a guide to whether the expedition is on schedule and assists with re-planning the remainder of the day if it isn’t.

The next five columns capture the segment navigation information and the values for the time calculation. Bearing is the departure bearing and Distance is the route distance to the next waypoint. Terrain only allows one of E, M, H or T to be entered, corresponding to one of the four terrain types Easy, Medium, Hard or Tough. If blank, the assumed default is E. The amount of climb and descent are entered in the Height Change ~Gain and ~Loss columns.

Description of Route is a more detailed description of the key segment features. There should be enough information to clearly indicate the required route, to highlight potential matters that may confuse, and describe any alternative routes.

Travel time is calculated using the nominated Time Model and the segment parameters. Break Time is entered (in minutes) for any additional time spent either at this waypoint or along this route segment. The Plan minutes heading includes the name of the Time Model being used to calculate the segment times. The right column has the Running Total time.

Below the waypoint area are totals for Distance, Height Gain, Height Loss, Travel Time, and Break Time. The running total time in the right column is also carried down to the total row.
The Plan Finish is the ETA at the final waypoint. The Contingency allowance is the product of the Daily Time Contingency from the Setup sheet and the total Travel Time. This is added to the Plan Finish and shown as the Pessimistic Finish. An adjacent coloured cell will indicate green ‘OK’ if the Pessimistic Finish is before Sunset, and red ‘Too late’ if after.
Route plan total times

Details of up to three planned Escape Routes complete the route Day Plan.

Route plan escape routes

Summary sheet

The Summary sheet displays the key expedition information together with a summary of each day’s travel distances and times, and the overall totals.

The duration times here, as on the Day # Plan sheets, are displayed like 2h 36m

Route plan summary

Reference Tables

The Terrain and Time Params sheets contain the descriptions of and the timing factors for the range of expedition types, terrain difficulty, age groups, fitness and experience and time model parameters.

Each of these tables contains a Custom row into which the user may enter their own values. The Custom row is not shown in these images.

The four successive levels of obstacle negotiation and navigation difficulty are described in the Terrain Descriptions table for each Expedition Type.
Route plan terrain descriptions
For each combination in the Terrain Descriptions table, a Terrain Factor value is provided. These factors multiply the Pace. The values for hiking generate the same changes as for the Naismith template.
Route plan terrain factors

Values of Level Pace in min/km for each of the five expedition types and for four age groups are in the Pace and Experience table.

These values may be adjusted if required, if evidence warrants changes. Remove sheet protection to do so, and then re-apply it after to prevent further changes before distributing the spreadsheet.

Route Plan pace table

On the right side of the the table are the Experience factors, which multiply the pace and therefore the total time. A reduction of one-third for fit and experienced walkers is used for all expedition types. For novice or inexperienced participants, different factors are used for the different expedition types. Most people do not have much difficulty walking, even if carrying a pack is a novel experience. Skiing and paddling each require considerable skill, and novices will quickly find that repeated tumbling into snow, or recovering from a capsize, can cause significant delay.

The time model parameters are contained in two separate tables, one for the Extended Generic model, and the other for the Naismith and Tobler models.

For the latter, only Hiking parameters are provided, since these models are not applicable to any of the other expedition types.

Route Plan slope factors EG model
Route plan slope table traditional

Model Comparison

The following chart demonstrates the difference in segment time using each of the three models, for hiking over a 5km route segment and a range of elevation change from 1000m descent to 1000m climb. All other factors are neutral (Experience and Terrain factors equal to one, and zero Fatigue.)

It is contended that the Naismith Rule significantly overestimates the required time for shallow and medium sloping ground.

Comparison of EG Naismith and Tobler times

Example Results

The following charts illustrate results using the EG model for various expedition types. The examples include terrain factors and profiles. The hiking example is that included in the distribution spreadsheet. 

Excel Route Plan Hiking example
Excel Route Plan Paddling example
Excel Route Plan Skiing example