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:
| Setup | Dashboard to enter expedition information and variables |
| Summary | Summary of general and daily travel information |
| Day 1 Plan | Route Plan daily pro-forma for first day of expedition |
| Day 2 Plan | 〃 second 〃 |
| Day 3 Plan | 〃 third 〃 |
| Day 4 Plan | 〃 fourth 〃 |
| Terrain | Descriptions for Easy, Medium, Hard and Tough terrain for each expedition |
| Time Params | Tables of parameters for time models |
| Example | A 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 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.
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.
From the selected Expedition Type, Age and Experience values, the Level Pace is found from tables in the Route Factors sheet.
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.
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.
Details of the maps used are entered on the second row
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.
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.
Details of up to three planned Escape Routes complete the route Day Plan.
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
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.
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.
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.
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.
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.
