PILOT'S ASSISTANT V1.6

INTRODUCTION

Pilot's Assistant is provided as an Excel multi-sheet spreadsheet, with one tool on each sheet. These tools include calculations and information as follows:

1. Flight profiler / fuel planner
2. Performance data for a number of simulated aircraft (can be extended by the user)
3. Altitudes and altimeter settings
4. Standard Atmosphere table
5. Speed conversions (KIAS, KTAS and MACH)
6. Wind corrections
7. Descent planner, climb and descent calculations
8. Lat/Long calculations (distance, heading, great circle routes, magnetic variations)
9. Turns, bank angles and manoeuvres involving turns
10. Unit Conversions

The toolset is not intended to be a "magic calculator" that produces results by hidden methods, but rather something that explains the calculations being carried out, and provides rules of thumb for pilots to carry out calculations quickly or in their heads. In this sense, the toolset could perhaps be useful as a training aid (but see disclaimers/restrictions).

The toolset can be downloaded from the Pilot's Assistant Home Page.

Also provided here is the Pilot's Assistant Directory describing many flight sim related sites, products and sources of information, all personally recommended. If you can't find what you are looking for here, the Directory will point you at other good places to look!

FLIGHT PROFILER / FUEL PLANNER

It then provides a facility to support fuel planning, based on the profile that has been set up, that is intended to quite flexible and fairly realistic - for example, this sheet includes weight calculations for fuel and payload, and will warn you if a weight limit for this aircraft type is exceeded.

For a particular cruise height, this sheet can automatically look up simplified performance data on fuel consumption and time to altitude. Some tables are provided with the toolset (see Aircraft Data), and others can be developed by the user based on measurements during simulated flying.

Note: for an example of real fuel planning, and good info on flying the DC-9 both in real life and in Flight Sim, see www.bahnhof.se/~classic

AIRCRAFT DATA (MASTER SHEET)

1. Some fuel and weight information that is constant (in principle) for each aircraft type, and is used by the Flight Profiler / Fuel Planner. When you change from flying one type of aircraft to another, you select which of your aircraft you are using and the Flight Profiler / Fuel Planner will automatically pick up the corresponding data from the table. The nature of this data is fixed by the spreadsheet tool, but the data values are up to you.
2. Some speed and fuel information that is not constant, but depends on cruise altitude and other factors. This data can, however, be looked up automatically from constants in performance tables on other sheets. The nature of this data is fixed by the spreadsheet tool, but the data values are up to you.
3. Additional data that is not used by the spreadsheet tool, but might be useful to store for your own reference, e.g. minimum take-off distance for a particular aircraft type. Both the nature and values of this data are up to you.

This sheet is protected but may be unprotected using the Tools, Protection menu - it is suggested that you keep it protected when you are using it. The only time you should need to unprotect it is if you want to delete complete columns that contain protected cells.

AIRCRAFT PERFORMANCE DATA SHEETS (DC-9, ATR 72, 757-200 ETC.)

A feature of the provided tables is that the data for a particular altitude (e.g. the selected cruising level) can be looked up automatically. If the altitude falls between two altitudes for which measurements are available, the values are obtained automatically by interpolation. These looked-up values are stored in fixed locations in the spreadsheet, so that they can easily be read by formulas in other tables (in particular, the table on the Aircraft Data sheet).

The aircraft types currently in the toolset can be identified by reading through the updates list below (they appear there in bold type). The user can, of course, create additional sheets for other aircraft types. For ease of maintenance when the toolset is updated, it is suggested that these additional sheets are kept in a separate spreadsheet file. You can link cells from one spreadsheet file to another if you want to (see hints and tips below).

Many of the aircraft for which data sheets are provided were obtained from the Hangar section of Tradewind Caribbean Airlines (follow links to Residence, Services, Hangar, and then either Hangar 1, 4 or 6).

ALTITUDES & ALTIMETER SETTINGS

Its main function is a density altitude calculation, that is basically helping to answer the question:

"Can I really take off or land at that high-altitude airport on a hot day?"

STANDARD ATMOSPHERE TABLE

An automatic lookup facility is provided, as well as some additional information relating to the U.S. Standard Atmosphere.

The following weather and atmosphere related links might also be of some interest:

1. Aviation Digital Data Service (ADDS) (covers the US including Alaska)
2. A Precise Definition of the U.S. Standard Atmosphere 1976

SPEED CONVERSIONS (KIAS, KTAS AND MACH)

It has an accompanying Standard Atmosphere Table that the pilot may use for reference, and from which it can automatically look up temperatures for use in the calculations.

Note that conversions from KIAS or Mach to KTAS are also provided as part of the Altitudes, Temperatures, Speeds and Descent/Climb Rates Table.

WIND CORRECTIONS

This tool generates a "wind correction" value that can be entered into the Flight Profiler / Fuel Planner tool, and also into the Altitudes, Temperatures, Speeds and Descent/Climb Rates Table.

A related sheet provides information on "Winds Aloft". It provides a sample of such data in spreadsheet format, converted from information obtained from the Aviation Weather Centre, and explains how to decode such information "by eye". This kind of data is automatically downloaded, and can be automatically decoded for the user, by FSMeteo, the real-weather generating program for FS98/FS2000.

DESCENT PLANNER / CALCULATOR

ALTITUDES, TEMPERATURES, SPEEDS AND DESCENT/CLIMB RATES TABLE

LAT/LONG CALCULATIONS (DISTANCE, HEADING, GREAT CIRCLE ROUTES, MAGNETIC VARIATIONS)

This facility exists in NavStar, FSNavigator and other tools. Before discovering FSNavigator I found this tool quite convenient when planning VFR flights from the Tactical Pilotage Charts. For example, I calculated the position of a point on the approach path to some minor airport, and then calculated where that point is relative to a VOR located somewhere else. It is still nice to be able to carry out these calculations sometimes without using more sophisticated tools.

A second tool computes waypoints and true and magnetic courses along a great circle route, given the start and end position and a distance to fly between each waypoint.

The toolset now includes a magnetic variations table. This table was generated using Ed Williams' Magnetic Variation program "magi.exe", downloaded from Ed's Aviation Page at www.best.com/~williams/index.htm, using model IGRF95, date set to 16 July 1999. This date has been chosen as likely to be close to the date used by FS2000 (although the author has not been able to confirm this, and would appreciate any words of wisdom on this subject!). You can download and use Ed's program yourself, it currently incorporates models valid to the year 2005. Magnetic variations change from year to year, but generally only by a few minutes per year.

Note that you can also look up magnetic variation (also known as magnetic declination) on-line for anywhere on the earth, as well as a lot of other data, at www.ngdc.noaa.gov/cgi-bin/seg/gmag/fldsnth1.pl. There is also an interesting graphic that shows the magnetic variations for the whole world at geomag.usgs.gov/DOD_D.html#dod_mf_declination.

TURNS, BANK ANGLES AND MANOEUVRES INVOLVING TURNS

It then provides assistance in calculating various common manoeuvres involving turns, using the information that has been set up, e.g. intercepting a radial from a DME Arc, flying teardrop turns within a given remain-inside distance, or traversing between two radials when heading towards a VOR.

UNIT CONVERSIONS

For more comprehensive unit conversions, I recommend downloading the freeware Breitling World Time Calculator.

HINTS AND TIPS

When entering certain values, it is sometimes useful to do so as a simple formula, e.g. "=8000-1450". This just saves you working things out on a calculator before entering the data, and is particularly useful with angles, e.g. an absolute value for an anti-clockwise course change between 020 and 350 might be entered as "=360+20-350".

The Excel "Goal Seek" facility provides many interesting opportunities to do additional calculations. For example, having set up a flight profile the spreadsheet tool will allow you to predict percentage fuel used, including a contingency. If you want to calculate the maximum range of the aircraft under these conditions (which can also check your data), you could change the specified flight length until the output value became 100% fuel used. Doing this manually is tedious; the Goal Seek facility on the Excel Tools menu allows you to do it automatically. If you are not familiar with Goal Seek, there are some instructions for using it on the "Speeds" sheet, or of course you can look in the Excel Help.

In order to replicate formulas (e.g. in aircraft performance tables), put the mouse over the bottom right hand corner of the selected cell(s) you want to replicate and drag it over the cells you want to replicate the formula to. The formula is automatically adjusted for each cell, e.g. if you drag a date this way then each cell has one day later - if you drag a formula with relative cell references (e.g. B26) then these will be changed from cell to cell. If you don't want them to change then you must use absolute references e.g. $B$26.

You may occasionally change the format of one of the green entry cells by mistake, especially when copying data. You can restore it by using the Format Painter brush to copy the format of a similar cell to the one you have changed. In order to avoid this problem, use "Edit, Paste Special" when copying values from one cell to another, and select "Values" or "Formulas" from the menu.

If you want to link an input value to the output from another calculation, so that the two are always in step, then simply enter a formula such as "=B24" into the input cell, where B24 refers to the output cell. The easiest way of doing this is to type "=" and then click on the output cell, and then hit ENTER. This works even if the two cells are in different sheets, or even in different spreadsheet files. Some green input cells in the spreadsheet are set up like this, but you can over- write them with normal input values (which breaks the link). For example, some of the input values on the Flight Profiler / Fuel Calculator sheet are initially linked to values on the DC-9 sheet - if you change the cruise height you will notice that many of the input values automatically change.

However, you must be careful not to set up circular references with links (as the author inadvertently did). This can happen as in the following story:

One of the initial values I had set up was for the Climb Gradient in feet per nautical mile. Instead of entering a value, I had linked this cell to one of the cells on the DC-9 sheet (you can of course over-write this link with any value you want). There was no problem as the initial sheet stood, because it happens that the cruise height had been manually set to 19,000 feet. But if you set the cruise height to 0, in order to allow the spreadsheet to suggest a cruise height, you would get a circular reference warning. This is because the calculated cruise height was copied by the DC-9 sheet (as currently set up), was then used there to index the various tables on that sheet, and the result included a computed average climb gradient to reach that particular cruise height. It was this computed value that I had linked to from the first sheet... this value was used to compute a new cruise height, and round it went in circles. Excel quite rightly complained that it was getting dizzy...

QUESTIONS & ANSWERS

	I want to add new aircraft and tried to copy one of your sheets (ATR) to the next available open sheet but the "Move/Copy" command was grayed out. Does this have anything to do with the protection? I didn't think so as you mention the aircraft sheets were unprotected?
	I tried to unprotect an aircraft data sheet using the Tools, Protection menu but it wouldn't let me. Why?
	I tried to open the spreadsheet file but all I get is gobbledegook. What's wrong?
	I notice that the cruise::climb distance ratio that I set is not quite the same as the one I end up with, even on a short flight where I didn't change the suggested cruise height. Why is that?
	What has happened to climb and descent gradients in the Profiler and Aircraft sheets, in V1.5.3?
	I added another performance table, using one of your tables as an example. When I change aircraft, I now get a circular reference warning. Why is that?

FORMAL STUFF

This spreadsheet tool is freeware, and may be copied and used for any purpose that has absolutely nothing to do with making money, whether directly or indirectly.

For example, this spreadsheet may NOT be included on a CD-ROM that is attached to a book or magazine, nor may it be uploaded to a web site that carries advertising or is associated in any other way with any kind of money-making enterprise, without permission from the author.

The only payment requested is that you send an email to the author at the address below if you find this tool useful, and perhaps make some suggestions for how it might be improved.

DISCLAIMERS/RESTRICTIONS:

The author's knowledge of flight simulators is restricted to Microsoft's Flight Simulator 98 (FS98) and Flight Simulator 2000 (FS2000), although the tool is meant to be useful with other simulators.

THE REALLY IMPORTANT BIT:

• Nick Dargahi for his excellent book ("The Ultimate Flight Simulator Pilot's Guidebook" 1998, ISBN 1-155828-574-1). Any errors are mine, not his, and the diagrams in the spreadsheet are mine, not his. I should also thank Ed Williams, the author of the chapter on Great Circle Navigation within that book.
• Ed Williams for his navigation calculations and his magnetic variation program, which was used to generate the magnetic variation table in this spreadsheet.
• The long line of people who have contributed to FS2000 and its predecessors.
• The endlessly patient people who help out on newsgroup microsoft.public.simulators - Bob, Brett, Dennis, Jughead, Katy, RushMan, Trip, Walt, and many others.
• Last but definitely not least, the friendly and knowledgeable folks at Tradewind Caribbean Airlines, "probably the best Virtual Airline in the world".

DEVELOPMENT HISTORY

CHANGES FROM V1.6.2 TO V1.6.3 (V1.6.3 31-Aug-01)

1. The main change is the addition of fuel/performance data for the DC-3. The model used for measurements was the superb R4D-6 package by Bill Rambow, Roy Chaffin and Jan Visser - actually Jan Visser's TDM cargo variant of this package. Thanks to Trev Morson, Charles Wood and the folk at TCA for helpful answers to several questions (any errors are mine, not theirs!). Note: You should read the DC-3 sheet carefully before your first flight, especially the areas highlighted in yellow.
2. A small enhancement has been made to the way in which you enter payload weight into the flight profiler / fuel planner sheet (cell B107 in V1.6.3). You can still type anything into this cell that you want, but take a look at the existing formula first. What's happening here is that if the sim model assumes a default payload, then that default is used automatically, and if the sim model doesn't then I am supplying a suggested payload of 28 passengers at 200 lb each, plus 500 lb extra cargo. One option that you have is to change just that part of the formula, rather than enter a complete new value, and you will retain some flexibility. I suggest that you try the existing formula with several different aircraft before changing anything, though - you may not need to change it at all. Some aircraft (e.g. MD-83) use kilos rather than pounds, so you may need to make an appropriate adjustment (unless you are transporting a party of Sumo wrestlers to a 10-pin bowling tournament, maybe!).
3. A new sheet has been added providing some commonly-required unit conversions, in response to a request from a Pilot's Assistant user.
4. Suggested cruise height now rounds to nearest 1,000 ft, not 100 ft.
5. Minor changes to comments on the flight profiler / fuel planner and Aircraft sheets.
6. The off-line version of this document no longer contains the information in the Pilot's Assistant Directory - this should always be accessed on-line from the Pilot's Assistant Homepage.

FIX TO V1.6.2 (22-Mar-01 19:00 GMT)

CHANGES FROM V1.6.1 TO V1.6.2 (V1.6.2 18-Mar-01)

1. The main change is an improvement to the way in which the wind speed adjustment is applied in the flight profiler / fuel planner. Previously, one entered an altitude above which the wind speed adjustment was to be made, and the calculations assumed for simplicity that there was no wind below this altitude. Now the profiler / fuel planner estimates the lower wind adjustments for you, based on the wind adjustment at cruise altitude. The gain in overall accuracy is not that great, but it does remove a minor annoyance (at least, it was annoying me!).
2. Related to this, a new sheet has been added providing information on "Winds Aloft".
3. Also, the default values in the Altitudes, Temperatures, Speeds And Descent/Climb Rates Table for wind adjustments now have a range of factors built in, so that if you enter a wind adjustment in the top row it is assumed to apply at 39,000 feet, and the adjustments at lower altitudes are proportionately reduced in a reasonably realistic way. You can of course change this feature if you want, by over-writing the formulae.

CHANGES FROM V1.6 TO V1.6.1 (V1.6.1 20-Jan-01)

1. The main change is the addition of fuel/performance data for the MD-83. This has been measured using the excellent "Mad Dog" simulation from Lago (available from www.justflight.com or www.lagoonline.com).
2. Note that the MD-83 panel uses kilograms for fuel and weight units, and so do the fuel/performance data tables. However, in FS2000, the aircraft fuel menu still uses pounds. The fuel/performance data sheet for the MD-83 also includes a small calculator that may be useful in converting from percentage fuel capacity to be loaded (from Prof&Fuel sheet) to values that can be entered via the aircraft fuel menu. (Of course, one could simply enter the same percentage into each of the fuel tanks, but the calculator makes it easy to be a little more realistic.)

CHANGES FROM V1.5.3 TO V1.6 (V1.6 26-Nov-00)

1. Addition of a magnetic variations table from which magnetic variation can be looked up automatically for any point on the earth's surface, using the provided lat long and great circle calculations.
2. Addition of performance tables for the Mooney Bravo, King Air 350, 737-400, and 777-200. The first two are the FS2000 stock aircraft, but with Steve Small's improved .air files (available from Steve's web site). The 737-400 is the FS2000 stock aircraft. The 777-200 is the excellent "Professional" simulation from PSS, available from www.justflight.com.
3. Addition of wind adjustment to the Descent Planner, and a better treatment of wind adjustment in the Altitudes, Temperatures, Speeds And Descent/Climb Rates Table.
4. Bug fixes for Lat Long calculations and the Altitudes, Temperatures, Speeds And Descent/Climb Rates Table. The effects probably weren't noticeable - you could get negative headings if you were very unlucky, and descent rates were inconsistently (not necessarily inaccurately) calculated at different altitudes.
5. A number of minor cosmetic improvements.

CHANGES FROM V1.5.2 TO V1.5.3 (V1.5.3 10-Jun-00)

1. Addition of performance table for the Pilatus PC-12. This is Marcel Ritzema's great FS98 model, obtained from Tradewind Hangar 6, which I use in connection with David Durst's equally great panel (see links to panel sites above). Thanks also to the folks at Pilatus Aircraft who are mega-helpful about providing information on the PC-12.
2. In V1.5.2, the tool could not look up climb and descent gradients automatically from performance tables because of a problem with circular references. This restriction is removed in V1.5.3, making profile calculations more accurate, but only when you select a cruise height manually (as you will very often do). Further information on this change will be found in the questions & answers section.
3. A number of improvements have been made to the Profiler sheet, with related changes to the Aircraft sheet, in areas where cruise height, fuel, payloads and other weights are entered.
4. All the supplied aircraft performance tables have had a small change made to the lookup algorithm. The effect is that where a cruise height is outside (above or below) the range of heights covered by the table, the values looked up are obtained by linear extrapolation. This improves the accuracy of the profiler for low flights, but has no other benefit at present (for further details on this, especially for people who have developed their own tables, see the questions & answers section). The actual change on each sheet is to the computation of the cell "IX_below" in each of the 3 locations where it occurs, limiting the value to 1..N-1, where N is the number of data rows in the table.
5. Some additions to the acknowledgements sections of this document.
6. With the number of aircraft types growing, I have re-ordered the performance sheets and the corresponding entries in the Aircraft sheet so that the aircraft are grouped into rough categories, and then alphabetically within categories. I hope it makes some kind of sense.

 

CHANGES FROM V1.5.1 TO V1.5.2 (V1.5.2 30-Apr-00)

1. Addition of performance table for the Boeing 767-200ER.
2. DC-9 cruise performance data revised. (This change is in V1.5.2 but wasn't noted until 1-May-00).
3. Bug fix for the calculation that checks for Maximum Landing Weight being exceeded in the Profile sheet.
4. Entry of climb and descent gradients and cruise::climb distance ratio moved from Profile sheet to Aircraft Data Master sheet, allowing these values to be stored per aircraft type.
5. Warning provided when you select a very low or short flight that the results may not be accurate.
6. Presentation of output values changed slightly on Profile and Aircraft Data Master sheets so that the result of changing parameters such as cruise height and the cruise::climb distance ratio can be more quickly seen. Thanks to "Jughead" for his suggestions, which prompted the next change also.
7. The individual aircraft data sheets have been made slightly more self-contained, by replacing the use of one_minute and one_hour by local values on each data sheet. This is just to make life easier for people who copy these sheets into a new spreadsheet file in order to modify the copies for their own aircraft. In future the filename of the spreadsheet file will not change from one release to another, for the same reason.
8. Some additional questions & answers in this document.
9. Addition to this document of a new section providing some recommended links.
10. Slight rewording of my copyright notice since it was obviously not clear enough!

CHANGES FROM V1.5 TO V1.5.1 (V1.5.1 9-Apr-00)

Note that when a station reports pressure, the reported pressure always represents what the pressure would be at sea level, which is more than the actual pressure at the station unless the station is itself at sea level.

CHANGES FROM V1.4.1 TO V1.5 (V1.5 8-Apr-00)

1. Addition of new sheet for Altitudes And Altimeter Settings.
2. The Standard Atmosphere Table has been amplified and extended to cover levels for supersonic flight, and to include negative pressure altitudes. An automatic lookup facility has been added, which uses a combination of interpolation and calculation for best accuracy.
3. The Descent Planner / Calculator sheet has a new section which helps you to check whether you are on the right descent path.
4. Addition of new sheet Altitudes, Temperatures, Speeds And Descent/Climb Rates Table.
5. Turns sheet now indicates classification of bank angle, and calculates the effect of bank angle on stall speeds for level turns.
6. Some additional cosmetic changes.

DOCUMENTATION-ONLY UPDATE (8-Mar-00)

CHANGES FROM V1.4 TO V1.4.1 (V1.4.1 5-Mar-00)

The opportunity has also been taken to improve the presentation of information on the Turns sheet, especially where input and output values cannot easily be seen at the same time.

The cruise::climb ratio on the Profile&Fuel sheet has some additional information, and minor cosmetic changes have been made to a few sheets.

The occasional (harmless) "circular reference" warning has been eliminated, by checking the "iteration" box in the tools, option, calculation menu.

DOCUMENTATION-ONLY UPDATE (31-Jan-00)

DOCUMENTATION-ONLY UPDATE (27-Jan-00)

CHANGES FROM V1.3.2 TO V1.4 (V1.4 23-Jan-00)

CHANGES FROM V1.3.1 TO V1.3.2

1. Errors in cruise fuel consumption data for ATR 72 and 757 performance tables corrected - they were exactly twice what they should have been! Also small correction made in all aircraft performance tables to the climb fuel calculations, so that they compute fuel to altitude from takeoff, not from engine start.
2. A number of minor improvements made to data entry on the Profiler sheet, in the areas of error reporting and feedback of certain output values at the point of data entry.
3. Distinction clarified between climb/descent gradient and climb/descent rate.
4. Conversion between descent angle and descent rate added to Descent sheet.

CHANGES FROM V1.3 TO V1.3.1

1. Added a pop-up note to the spreadsheet for VFR and IFR altitudes and flight levels.
2. Minor correction to Aircraft Data (units of weight for Cessna should be pounds, not gallons).

CHANGES FROM V1.2 TO V1.3 (V1.3 25-Dec-99)

1. Improvement made to turn calculation when traversing from one radial to another, making it more accurate with larger angles between radials.
2. Addition of performance tables for Cessna C182, ATR 72, 757-200. Also, the internal construction of these tables has been improved to make it a little easier to create new tables based on them.
3. More data is now entered on the Aircraft sheet and less data on the Profile sheet. This reduces your workload when switching between aircraft.
4. In conjunction with 3., some additional data on the Aircraft sheet is now looked up automatically from aircraft performance tables on individual sheets. You can create additional aircraft performance tables on additional sheets (which should be in a separate spreadsheet file for your ease of maintenance).
5. You now need to specify the maximum cruise height for each aircraft type.
6. Sometimes it is useful to know for a particular total weight of the aircraft, what percentage fuel that total weight represents (for the given flight model). A calculation for this has been added to the Profile / Fuel sheet.
7. The Turns sheet now allows the bank angle to be limited to a specified maximum value, and removes the statement about 4 minute turns being standard for large and fast jets, which is not believed to be accurate - advice on this topic is welcome!
8. Some terminology has been corrected (hopefully).

CHANGES FROM V1.1 TO V1.2 (V1.2 10-Nov-99)

1. Addition of Descent Planner.
2. Addition of Aircraft Data sheet.
3. Addition of Great Circle calculator.
4. Addition of diagrams to the Turns sheet.
5. Corrections to some calculations in the Turns and Lat-Long sheets.
6. A general tightening up of terminology.
7. Bug fix for fuel planner sheet.
8. Change made to initial input values on the Flight Profiler to avoid circular reference problem.

CHANGES FROM V1.0 TO V1.1 (V1.1 30-Oct-99)

1. Addition of Standard Atmosphere Table.
2. Addition of Turns sheet and Bank sheet.

THE PILOT'S ASSISTANT DIRECTORY

AUTHOR

Brian Tooby
TCA Pilot #2658
brian@tooby.demon.co.uk