[Home] [Overview] [Pre-registration] [Registration] [Volunteers] [Set-up] [Notes] [Updates] [Tutorials]

CONTEST LEDGER 2002

An Excel Spreadsheet for Contest Registration and Orders of Flight, Version 3
George Norris
Phoenix Aerobatic Club, IAC 69

 

Overview       The Excel spreadsheet, Contest Ledger 2002 helps perform many of the tasks required of the aerobatic contest registrar.  On registration day, the “Contest Ledger” worksheet is used to capture pilot / volunteer data and record contest income.  At the end of the registration day, the pilot data is used to establish the orders of flight, using features built into the “Order of Flight” worksheet.  Captured pilot / volunteer data can also be exported to similar spreadsheet tool for the Volunteer Coordinator.  As the contest runs, the “Print OF” and “Boundary Judge Worksheet” worksheets are printed for the Chief Judge, Starter, and Boundary Judge clipboards for each flight program (Known, Free, and Unknown).  At the end of the contest, the final % scores for each pilot are entered into the “Contest Ledger” and trophy winners are computed on the “Results” worksheet.  After the contest, the “Cash Management” and “Check Management” worksheets are used to verify cash and deposit checks in the cash drawer.  It is designed to operate in conjunction with Volunteer Log 2002,  the companion tool for Volunteer Coordinators.  An illustrated guide to the Complete Suite, with input/output of data and printouts is illustrated here.  

Where Are You?

What Do You Need to Do?

Before the Contest

  • Download the spreadsheet 
  • Get current IAC judges list in proper computer format
  • Set up the spreadsheet for the contest
  • Practice with previous contest data

Registration Day

  • Register contestants, 
  • Gather information to set orders of flight later, 
  • Sell merchandise and food

After Registration Closes

  • Set orders of flight for the next day:
  • Provide data for Volunteer Coordinator

During the Contest

  • Adjust orders of flight, 
  • Print orders of flight and clipboards

Before the Awards Ceremony

  • Compute winners of special trophies and awards

After the Contest

  • Reconcile income with receipts (cash, checks, credit slips)

NOTE: All ledger functions are available from  the "Contest Ledger" menu, a few have shortcut keys, and many pages have page-specific buttons placed directly on them.  The choice of which method you use to access these functions is up to you ... use whichever you find the fastest or most intuitive.  If in doubt on where to find a given function, use the menu at the top of the Excel window.  There are also many pages (or worksheets in Excel terminology) in the Contest Ledger; select a desired page by clicking on the tabs on the lower left corner of the window.  A second method is to use the "Contest Ledger" ® "Go To Page" menu, which will display all of the pages in a pop-up window.

Before the Contest    

    A copy of the spreadsheet (736KB) is kept here (will it run on my computer?).  Alternatively, a complete Windows installation package with all of the software and documentation is found here (2.9MB).  Right-click to download.  The spreadsheet should be set up for the contest.   Erase any previous data by going to the "Contest Ledger" menu ("Data" ® "Clear Ledger").   The program will ask if you are sure, since erased data cannot be retrieved if not backed-up.  Enter contest data by going to the "Contest Ledger" ® "Data" ® "Setup Ledger" menu; this will open a window to input a contest name to be printed on all output (include year and name), CD, Location, Chapter, and the amounts to be charged for registration, chapter dues, banquet, lunches, T-shirts, sweatshirts, or any other merchandise (up to 11 individual item names and prices besides registration and dues can be entered):

 

After closing the setup window, the program will display a portion of the spreadsheet that will allow you to change the order of flights and determine where Primary pilots will fly (combined with another category or by themselves).  Change the categories listed for #1-#4, whether Primary will be combined with another category and press “Finish Setup” when done.

 

    Go to the “Cash Management” worksheet and enter the quantities (numbers of bills, not amounts in $) of bills (and coins) put in the cash drawer for change in cells B3 through B11.  The sheet will compute the total in $ and should match what was put in.  Using the "Contest Ledger" ® "Data"® "Import Judge's List" menus, input the current IAC Judge's List from the file judges_list.CSV saved in the same directory.  Go back to the “Contest Ledger” and save these changes using the Backup function ("Contest Ledger" ® "Backup Ledger" menu) .  Go to the “File” ® “Print” menu and select the printer you have connected to the registration computer.  Print a test page to insure proper functioning.  You’re now ready for registration to start.

 

    Note that many of the data import and export functions may be automated using an Automatic Import/Export function, accessible through the "Contest Ledger" ®  "Data" ®  "Automatic Import/Export" menu.  This allows the registrar to schedule automatic backups, as well as  import of pre-registration data saved by the Pre-registration spreadsheet and automatic export of satellite ledger and volunteer log data.  Start the automatic updates by checking the desired files, update interval, and pressing the "Update" button.  Cancel any or all updates by deselecting the check boxes and pressing "Update" (the last scheduled update will still occur).

 

 

 

Registration Day      

    Pilots  As each pilot presents their completed registration paperwork, go to the next blank row in the “Contest ledger” and enter the data in each field, starting with the pilot name.  This is greatly speeded up by using Pre-registration data for Pilots and Volunteers.  For more information on using this process, see the separate web document, "Contest Pre-Registration".   If pre-registration data is available from the web or Pre-Registration workstations on-site, go to the "Contest Ledger" ® "Finish Registration" menu and find the pilot name in the pop-up window.  Double click or press the "Load Data" button to retrieve the data into the next free line in the ledger (or overwrite the existing data if the name is already in the ledger).   The Pilot's volunteer data will pop-up in a dialog box for review.  Modify it if needed and press "Save" to continue.   If the person is a Pilot, the program will then ask if they will be flying any category as for an IAC Achievement Award (or "Patch"); enter a category or leave the field blank if not.  Following this, the cursor will be positioned at the next blank Ledger column for that pilot or volunteer.
   
If entering the data manually,  use the "Contest Ledger" ® "Start Registration" menu to open the pre-registration data entry dialog directly in the ledger.  After pressing "Save Data", the program will offer to print the entry forms and save the pilot data for later access from the "Finish Registration"  menu option.  When entering the Aircraft Type, if no match exists, choose “Monoplane Other” or Biplane Other” as appropriate.  If the Category is anything but “Sale” or "Volunteer", the name and totals will show RED until all required information (frees, tech inspection, waiver sign) is entered.  Do not proceed with taking payment until all these requirements are met.  If all information is present, enter the quantities of items the pilot wishes to buy (registration is automatically set to a quantity 1 if the category selected is a flying one).  Note that the cursor will automatically advance to the next item after you press the enter key or the "Delete" / "Tab" key (for none).  Column “AT” will give the total owed by the pilot.  Once payment is taken, enter the appropriate “Paid Check” or “Paid Cash” in column “AU” (“Paid Credit” can be used if  you can accept charge cards).  Note that payments in fixed denomination notes are "cash" (this includes Traveler's Checks).  Do not change this entry from “Not Paid” until payment is given; this is your best means to keep track of those pilots owing money!  Failing this, you can use a feature called SmartNotes, described elsewhere, to keep track of incomplete registrations.  If money is taken for some item not included in the standard list, enter the amount under “Other” and explain in the “Notes” column.  If the pilot wishes a receipt, press the “Receipt” button on the upper left-hand side of the sheet with any cell in the pilots row selected.  If a pilot returns to buy additional food or merchandise, it is advisable to treat the sale as a new entry, as discussed for non-pilots below.  This way, each entry has one payment (check or cash) associated with it and makes reconciliation at the end of the contest far easier. Another possibility includes one pilot who pays for another.  In this case, add the amount owed for the pilot not paying to the paying pilot’s entry under “Other”.  The pilot not paying should have a negative entry under “Other” to offset the amount paid by the other pilot.  It is very helpful to have both pilots register at the same time for these transactions.  If not, make careful notes in the "Comments" column or use SmartNotes to automatically keep track of outstanding balances.

 

    The guiding principle in all of the foregoing discussion is that each line in the Ledger should give an amount that is actually paid in cash, credit, or check.

   

    Volunteers   Volunteer data should also be collected in the ledger for use by the VC.  Select the “Volunteer” category for non-flying volunteers, which will blank out columns which are only meaningful for Pilots.  When a pilot or volunteer category is entered, the sheet will pop-up a volunteer data window.  Using the “Find” button will search the Judge’s list to locate the person’s IAC number.  If found, the number will be automatically entered in the ledger, lessening the possibilities for data entry error.  Ask the person registering if they have any of the experience listed next to the radio buttons.  Also ask if they need a certain number of flights judging or assisting at this contest.  If so, enter the number of flights in the "Flights Needed" box at the bottom of the window.  Any other comments about availability or category preferences can be entered in the "Comments" field.  When done, press the “Save” button.  If the volunteer or pilot entered a Judge (J) or Chief Judge (C) experience codes, they will be automatically compared to the IAC Judge's List.  If found, the IAC number will be verified and registration can proceed.  If not found, the registrar has the option of entering the Judge into the IAC Judge's List.  The entry will be marked as provisional (with a '!' code) and noted on the IAC Judge's Log forms sent to the IAC at the end  of the contest.  After this, merchandise sales can be entered as above for the pilots.

    Merchandise   Merchandise-only sales should be entered under the standard category ”Sale” with a blank for Pilot/Volunteer name.  This automated by the "Contest Ledger" ® "Sell Merchandise" menu.  This category selection will blank out irrelevant fields in the ledger and will allow these entries to be sorted together.  Enter the quantities of merchandise or food sold as before.  If desired, the purchaser’s name or other information can be entered in the “Notes” column.  Note that this category can also be used to record cash expenses out of the cash box during the contest by entering a negative number in the "Other" column, selecting "Paid Cash", and recording the item(s) in the comments column.

 

In general, it is best to only input moneys owed to the aerobatic club into the ledger, but not to other organizations, such as IAC, which should be tracked elsewhere.  Save the worksheet regularly throughout the day using the "Contest Ledger" ® "Backup Ledger" menu (after each pilot or sale is a very good idea or set up automatic backups as described above).  Organize the data regularly, by using the "Contest Ledger" ® "Tools" ® "Sort Ledger" menu or pressing the “Sort” button with the radio button next to “Pilot Name” selected.  This will sort the entries by pilot name.  One can sort by other fields if the radio button in the respective column header is selected first.  If disaster should strike and the ledger data is lost, a copy should be saved in a “Ledger.BAK” file from the last time the "Backup Ledger" menu was accessed.  Restore by reloading the spreadsheet and pressing the “Restore Data” button.

 

    Satellite Ledger  If warranted by the volume of registrations, the registrar can set up a Satellite Contest Ledger to increase the throughput of pilots/volunteers/merchandise sales.  These are identical copies of the ledger program running on additional computers.  Once the master copy is setup, save the data using the "Contest Ledger" ®  "Backup Ledger" menu.  Copy the Ledger.BAK file to the other computer into the ledger directory and once the satellite copy of Contest Ledger is running, load it using the "Contest Ledger" ®  "Data" ®  "Restore Backup" menu.  Use this copy identically as described before for the master copy to register pilots, volunteers, and merchandise sales.  At regular intervals, save the Satellite Ledger data using the "Contest Ledger" ®  "Data" ®  "Export Satellite Ledger" menu, save the file Ledger.CLS to disk, and import it back into the Master Ledger using the "Contest Ledger" ®  "Data" ®  "Import Satellite Ledger" menu.  This will add any new registrations to the master ledger.  To keep master and satellite copies straight, the Excel window titles will include "Master" and "Satellite" once the "Export" or "Import" commands are executed.  Be sure that the Satellite Ledgers are exported to the Master Ledger at the end of the day and to only use the Master Ledger for later registration duties, such as Order of Flight.  If a LAN is available to connect the Satellite and Master Ledger computers, the process may be partially automated by using the "Contest Ledger" ®  "Data" ®  "Automatic Import/Export" menu to periodically save the satellite ledger information to the master ledger.

 

    Note that the registration function can be speeded up by using a barcode scanner; see Using Barcodes in Contest Automation.

After Registration Day        

    At the end of registration, the contest ledger contains enough information to create orders of flight.  Use the "Contest Ledger" ®  "Order of Flight" ®  "Create Known OF" menu (or go to the “Order of Flight” worksheet and press the “Copy Ledger” button on the upper right hand side).  Pilot data from the ledger will be copied to the order of flight worksheet, be sorted by category, and have a random order of assigned (as per the IAC Red Book, section 4.5.1) for the known flight program.  Were it not for inexperienced pilots, low powered aircraft, or pilots sharing aircraft, the job would now be done.  However, it is important that more experienced pilots fly low lines and higher powered aircraft be used to climb to altitude after flying low lines.  Thus, the randomly selected first pilot in Sportsman and Intermediate should be scrutinized to insure an appropriate choice of pilot or aircraft.  In particular, a first time Sportsman should never be the first flight in the Sportsman category and will be highlighted in red if so placed.  To switch places with another pilot, exchange order numbers in column G (known) and press the “Known” button to re-sort the flight program.  [This is automated by right-clicking the first pilot, followed by double-clicking the second.  A pop-up window will verify the selections.]  

    Pilots sharing aircraft normally present a more difficult problem.  It is important to put enough aircraft between the shared airplane to allow the pilots to fly, land, refuel, reposition, and line up for the next flight.  To identify these pilots, the worksheet will identify aircraft being shared by the same number in red in column “J”.  If a shared aircraft is separated by 3 aircraft or less, “Share?” will appear in column “K” and indicate that the order should be modified.  If possible, reorder the flight so that all shared aircraft are roughly separated by the number of pilots in the category divided by the number of pilots sharing the aircraft minus one.  For example, if there are 16 pilots in the category and 3 pilots sharing an airplane, separate the shared flights by 4 flights (16/3 – 1 ~ 4).  It is possible to separate the pilots by more flights, but this should be sufficient and is desirable for setting later flight programs.  Again, move pilots by exchanging order number in column G and re-sorting until the first flight has the appropriate aircraft and pilot and all shared aircraft warnings no loner occur.  This can get involved, so the program now offers an option:  after performing a "Create Known OF",  the program will offer to fix the shares and complete the Free and Unknown.  If you answer "Yes", the shares will be separated automatically using the methodology above, keeping their random order with respect to all the other shares and then the Free and Unknown will be created from the Known order.  Unless you're keen to get your hands dirty, use the automatic option!

    You decided at the beginning where the Primary pilots would fly.  Typically, this is with another, lightly attended, category, such as Unlimited.  If you picked Unlimited, then the Primary pilots will be combined in the Unlimited order of flight.  Since the number of individual flights for Primary is typically a maximum of two, it is reasonable to schedule the Primary pilots to fly both of their flights together in the second Unlimited (usually Free) order of flight.  To do this, enter a blank for the Primary pilots in the Unlimited Known order of flight; they will then not appear on any of the printed sheets for the Known.

   

At this point, you have established a working order of flight for the Known flight program.  To print out enough copies for the Chief Judge clipboard, Starter, and both Boundary Judge clipboards, use the "Contest Ledger" ®   "Print" menu shown below (clicking the "Clipboards" button selects the items and quantities needed, note that you can print one or all categories).  Note that the program will print not only the order of flight listings, but also a Chief Judge Penalty form for each contestant, in order, that can be directly inserted into the chief judge clipboard.  Complete the clipboards by inserting the appropriate Forms B & C at the back of each.  Additional automation to print Forms A, B, & C is described here.

 

 

 

To complete the Free and Unknown orders of flight, start by using the "Contest Ledger" ®  "Order of Flight" ®  "Make Free and Unknown" menu (or pressing the “Trial Order” button on the OF worksheet).  If you selected the option to automatically separate shares and complete the orders, this will already be done!  This will take the Known order and create Free and Unknown orders by shifting the Known order by 1/3 and 2/3 the total number of pilots, respectively, which will put any given pilot both early and late in the flight orders.  Check the validity of these orders by pressing the “Free” and “Unknown” buttons to sort the new orders and look for warnings of aircraft shares or inappropriate first pilots.  Adjust the orders by changing the order numbers in columns “H” or “I”, re-sorting, and checking the results.  This should result in (1) acceptable 1st pilots, (2) adequate separation between shared aircraft, and (3) every pilot flying in a variety of positions, both early and late.  To assist in monitoring (3), column “L” presents a “position” score that is the average position in all three flights (0% is first, 100% is last, 50% is midway).  Any pilot who is flying too early (<30%) or late (>70%) has their score highlighted in red and should have their Free or Unknown orders adjusted to move their scores into the 30 - 70% range.  Exchange these with flights nearby that have opposite score values (highlighted in red or orange) and re-sort. 

You will then have to deal with any Primary pilots set to fly in the Free program.  Enter an order in the blank order entries for those pilots in the Free column.  If the aircraft is shared, all of the previous considerations apply.  Note that if two sequences for each pilot are flown, they are typically flown in the same flight and only require a single entry in the order of flight.  Leave the Primary entries blank for the other (Unknown) flight. 

Once the Free and Unknown programs are done, print them by selecting the flight "Contest Ledger" ®  "Select Flight" menu and then using the "Contest Ledger" ®  "Print" menu as before.  Be sure and save the order of flight as you work.  The entire process should take less than 10 minutes, even for large, complex flights.  To develop proficiency, previous contest data saved from the spreadsheet is available for practice  (contact the author for a backup data file needed).

The ledger also contains all the information needed by the Volunteer Coordinator to set up the contest volunteer logs.  Export the information needed by the companion spreadsheet, Volunteer Log 2002 by the "Contest Ledger" ®  "Data" ®  "Export Volunteer Data" menu.  After setting the orders of flight. the Volunteer Coordinator will also want a copy of the Contest Ledger back-up file, "Ledger.BAK", used to screen for pilots working immediately before or after they fly.  The ledger can also export pilot data to the IAC scoring program (CONTS.DT and CATEG.* files) from the Contest Ledger" ®  "Data" ®  "Export Pilots for Scoring" menu.  Placing these files in the same directory or floppy disk as the scoring program will prevent the scorer from having to manually enter all of the pilot data. [NOTE] Due to a bug in the IAC scoring program, IAC member numbers greater than 99999 will lose their last digit.  Since all recent member numbers are greater than 400000, this will present a problem with data exported from the Ledger.  A temporary fix has been implemented where new numbers have the '4' removed and a "+" is added to the pilot name.  The "+" is removed and the number restored when scoring data is read back into the Ledger.

During the Contest

     During the contest, an order of flight may require modification.  For example, a contestant may drop out in later flights.  In that case, delete the order number for that pilot in the later flight programs and reprint the orders by re-sorting and accessing the "Contest Ledger" ®  "Print" menu.  Pilots with no order number will not appear on the printed Orders of Flight or Boundary worksheets for those flights.  A contestant may show up late and still be able to register after you have done the orders of flight. In this case, register the pilot in the Ledger and then use the "Contest Ledger" ®  "Order of Flight" ®  "Update OF Pilots" menu (or go to the “Order of Flight” worksheet and press the “ Update Ledger” button on the upper right hand side).  This will add any pilots not currently in the order of flight at the end of their category without any order numbers.  Assign them an order of flight  number in each flight, fitting them early, middle, and late, using a fractional number like 3.5 to place them between pilots number 3 and 4.  Resorting on each category will re-establish the integer flight order.  A more complex situation is presented by a pilot who must fly two flights in a single order of flight.  To do this, add a second pilot entry by simply double-clicking the pilot's name (which will copy information from columns “B”-“F” to a blank row in the same category), add an order number in the desired flight, and re-sort.  This will obviously present issues with aircraft sharing which will require adequate separation between the pilot’s two flights.  To make the intent of the second flight clear, follow the pilot’s name with a special entry, such as “(Known Flight)” for a Known program flown in a Free order of flight.  If no order number is put in the other flights for the second pilot entry, it will not be printed for the other flight programs.

After the Last Flight is Scored       

    The IAC scoring program will determine the final scores for all pilots and list pilot placings in each category.  However, the CD must determine the winners of special trophies, such as Chapter Trophy, IAC Grass Roots Medal, Pitts Trophy, or High Scoring 1st Time Sportsman.  To automate this, enter the final % scores from the IAC scoring program printouts into column “P”.  This is easily accomplished by the "Contest Ledger" ®  "Data" ®  "Import Scoring Data" menu, which reads the SPRSHET.TXT file created by the IAC scoring program.  The window shown to the right will pop-up to match data fields in the SPRSHET file with corresponding fields in the ledger.  Matching is done by last name and either first name OR IAC-number.  If a pilot in the IAC scoring results is not found in the ledger (usually due to a spelling error or punctuation difference), a warning message will be produced and the user will be asked to find the pilot.  Find the corresponding pilot and click the name field in the ledger (Column "B").  Once this is done, go to the "Contest Ledger" ®  "Tools" ®  "Compute Results" .  Winners of the special awards will be computed and entered into the Results page, which can then be printed for the CD.  There is also a "Final Results" page which lists all of the scores for each flight and final standings for each pilot (1st, 2nd, & 3rd place winners are identified in each flight).  This may be exported to a HTML page for publicizing the results of the contest.  The Collegiate Program participants will have their data entered in the "Collegiate Award Form" and IAC Grassroots qualifying pilots will have their data entered in the Grassroots Award form .  Print these results and forms by the "Contest Ledger" ®   "Print" menu (or pressing the "Print Forms" button on the forms) and have the pilots sign and complete any empty fields.

 

After the Contest is Ended             

    After the contest, registration income should be reconciled against the ledger.  Each entry marked by “Paid Check” should have a corresponding check in the cash drawer.  If credit cards were accepted, there should be a credit slip for each ledger entry.  The remaining “Paid Cash” entries are summed into the “Cash Management” worksheet.  Count the bills and change in the cash drawer and enter the quantities of each denomination into cells D3-D11.  The net in cell F13 should equal F15, which is copied from the contest ledger.  The person (often the CD) supplying the initial money in the cash drawer may also have bought food or merchandise using their supplied cash.  In this case, no cash is added to the cash drawer, but rather the initial amount of cash is reduced by the amount of the sale (essentially some of the change has become income).  Enter the amount of the sale by adjusting  the quantities of initial cash in “Cash Management” cells B2-B11.

    The checks may be copied to a deposit slip located on the “Check Management” worksheet.  Pressing the “$” button in the upper left corner or using the "Contest Ledger" ® "Tools" ® "Create Deposit Slip" menu will copy all ledger entries marked “Paid by Check” to the deposit slip, along with the pilot name, check number, and any comments.  Each check in the cash drawer should have an corresponding entry in the deposit slip which may be checked off under the “Deposit” column (turning the amount black from red), although there may be discrepancies if one pilot paying for another or other such occurrence was not properly handled in the ledger (one entry, one check rule).  The comments from the ledger may help resolve these issues and may require that the amounts entered from the ledger for some pilots be modified on the deposit slip.  If each "Paid Check" entry in the ledger reflects a single check in the cash box, this should not occur.  Export the data as a text file by using "Contest Ledger" ® "Data" ® "Export Deposit Slip".

    If discrepancies still exist, check that missing checks aren’t present in cash or missing cash isn’t covered by an unaccounted for check.  Excess checks or cash may have an entry incorrectly listed as “Not Paid”.  Check for this by using the "Contest Ledger" ® "Tools" ® "Check Receipts" menuGood notes made during registration of unusual situations (such as one pilot paying for another) in the “Notes” column of the ledger may help in figuring out these situations.

    Finally, save all of your ledger data in an archive file, using the "Contest Ledger" ® "Data" ® "Save Archive" menu.  This saves a file, identical to the Ledger.BAK file, with all the contest data you have entered into a ".CLA" file with a name you specify.  In the future, you can completely recreate the ledger by loading a current copy of Contest Ledger and restoring from this archive.

 

For More Information        

    Contact George Norris (480)759-5795, (480)413-3958, or gbnv35b@yahoo.com.

 

Will it run on my computer? 

The best answer is perhaps ... it requires Microsoft Windows and Office (Excel).  It runs on Office 2000 under Windows 2000.  It works only partially in Office 97 under Windows 98 and Windows NT 4.0, but with substantial problems, and is NOT RECOMMENDED (if you'd like to persevere, contact me for work-arounds).  There will almost certainly be problems with Excel 5.0 or earlier.  I have not tried it with Windows XP and the matching Office suite, but would hope that there is enough backward compatibility to let it function in that environment.  If you have problems contact me at the address above.

Rev 3.89 (GBN 4/12/2003)