ReadMe File for Bid Tracking Software: CU_TRACK.xls v12/18/96

Summary. CU_TRACK.xls is software for tracking the progress of the Federal Communications
Commission's Cellular Unserved Auction.

With this bid tracking tool, bidders can import round results and analyze the bidding. The
software is a Microsoft  Excel  v5 WorkBook, which runs on Microsoft Windows  v3.1 or
later.

Disclaimer. The Commission makes no warranty whatsoever with respect to the auctions
tracking software packages.  In no event shall the Commission, or any of its officers, employees,
or agents, be liable for any damages whatsoever (including, but not limited to, loss of business
profits, business interruption, loss of business information, or any other loss) arising out of or
relating to the existence, furnishing, functioning or use of the auctions tracking software
packages that are available to the public in connection with the Commission's spectrum auctions. 
Moreover, no obligation or liability will arise out of the Commission's technical, programming or
other advice or service provided in connection with the auctions tracking software packages.

Software and Hardware Requirements. At a minimum, you will need the following:
      Microsoft Windows v3.1 or later
      Microsoft Excel v5 or later
      a computer able to run the above software
      8MB of available hard disk space

User Requirements. This software is not designed for novice computer users. To be used
effectively, you must have extensive experience with:
      Microsoft Windows v3.1 or later
      Microsoft Excel v5 or later
      the FCC simultaneous multiple round auction format
This document assumes that you have such experience. It does not attempt to educate you about
Windows, Excel, or the FCC auctions. You are urged to consult the appropriate manuals
elsewhere.

Installation.

0. Make sure you have 8MB of available space on your hard disk.

1. Download the file README.TXT or README.WP (.txt is an ascii text file and .wp is a
   Word Perfect file) and CU_TRACK.EXE to your c: drive. [You can install CU_TRACK on
   a different drive, but you will have to change a constant in CU_TRACK.xls after
   installation. This is not recommended.]  For illustrative purposes, this example assumes that
   you download the files to the root directory c:\.

2. At the DOS prompt (i.e. c:\>) type:CU_TRACK
   This will create the required subdirectories and uncompress the necessary files. These
   subdirectories contain the auction data as described at the end of this file. [If you get an error
   message after running CU_TRACK.exe, it is likely that the file was corrupted in
   downloading. Download the files again and retry.] The tracking tool is contained in the
   Excel v5 WorkBook,CU_TRACK.xls and a backup is in CU_TRACK.bak.

4. To begin using the tool, start Windows, load Excel, and open the file CU_TRACK.xls. This
   file is in the directory c:\fcc\cu\.

Customizing the Workbook. Once you gain experience using the tracking tool, you will see
ways to change or extend the workbook for your particular needs. You should think carefully
before modifying the workbook.  First, your changes may inadvertently damage the existing
features of the workbook.  Second, when a new version of the software is released, your changes
would be lost if you switch to the new version. Hence, the best way to introduce your extensions
is to create another workbook that is linked to the desired data in CU_TRACK.xls. Both
workbooks can be open at the same time in Excel and the links can be updated automatically (see
the Excel user manual). Most data items are named ranges in CU_TRACK.xls. In defining the
links, you should use the named ranges.

Trouble Shooting. If you are having a problem with the tracking tool, first try to isolate the
problem. The most common problem is that a required data file is missing (you will get a "File
not found." run-time error message). For example, you tried to import data for round 10, but you
did not yet download the round results from round 10 or failed to place them in the required
subdirectories. To solve the problem, download the results, place them in the right subdirectories
(see below), and retry the import.

Try to duplicate the problem. If the problem persists, it may be that CU_TRACK.xls was
damaged in some way. Close CU_TRACK.xls, and then use the Windows FileManager to copy
it to CU_TRACK.old and then copy the backup version CU_TRACK.bak to CU_TRACK.xls.
Open CU_TRACK.xls (which should be the original, unmodified version) and attempt to
duplicate the problem.  Technical assistance can be obtained from the FCC Tech Support Hotline
at (202) 414-1250.

Description of the Workbook.CU_TRACK.xls consists of several worksheets, summarizing the
auction data, and several modules written in Visual Basic for Applications , containing the
source code. Each is described below.

   Worksheets with Permanent Auction Data

         MarketData. Contains the basic market data. The data in this sheet are set before the
         auction begins and do not change during the auction. Since this workbook was designed
         for the Cellular Unserved auction, the relevant markets are the PHASE I markets and
         PHASE II markets being auctioned.

         BidderData. Contains the basic bidder data.

   Worksheets Based on the Latest Round Results

         Summary. Contains a summary of the results for the current round. The summary
         includes measures of revenue, bidding activity, prices, and eligibility. The sheet
         highlights essential information from the round. What markets are most active? Are
         bidders using jump bids or raising their own bids? Were there any bid withdrawals?
         Were there any changes in eligibility? Who has to bid in the next round to avoid a drop
         in eligibility?

         Markets. Presents data for each license. How active is the license? Who was (is) the
         high bidder? Is there an outstanding withdrawal?

         Bidders. Displays data for each bidder. Eligibility, current activity, and other measures
         are given.

         Rounds. Shows several summary measures for each round of the auction. Activity,
         revenue, price, and eligibility measures are listed for each round.

         Withdrawals. Lists all withdrawals up to the current round and any outstanding
         withdrawal payments.  The withdrawal payments shown here are calculated as the lesser
         of the difference between the current net high bid and the net withdrawn bid and the
         difference between the current gross high bid and the gross withdrawn bid.

         Rules. This gives the current stage, the stage transitions, and the required activity levels
         in each stage.

         UserSort. Stores the user defined sort orders.

         subMarkets. A table which specifies which licenses are in each Phase.

   Modules in CU_TRACK.xls (This is the source code for the CU_TRACK.xls tool. It should
   not be modified.)

         ModConst. Global constants available to all modules. If you installed CU_TRACK.xls
         in a directory other than c:\fcc\cu, you will need to change the AuctionDir
         constant to your installation directory.

         ModImport. Contains the procedures used to import the round results.

         ModSelect. Contains the procedures used to select market or bidder data for analysis.

         ModSorts. Contains the procedures used to sort the worksheets in the desired order.

         ModTesting. A temporary module used for testing.

Auction Menu. After opening CU_TRACK.xls, you will notice a new menu item, Auction12, in
the Excel Menu Bar. This menu item (Alt-A to select it) is used to access the auction procedures:
to import round results, analyze the data, or define user sorts. For example, to import a round,
you would type Alt-A I. The main procedures are described below.

   Importing Round Results. The steps used to import the results for round 1 are given below.
These steps assume that you download the data files over the Internet using FTP. [Alternatively,
you can download the files using a Web browser. Connect to
http://www.fcc.gov/wtb/aucdata.html]

   The preliminary (pre-withdrawal) round results are available for ftp approximately 10
minutes after the close of the bid submission period. At this point, the first three data files (S, X,
and E) are posted. These data can be imported following the steps below. The final round results
are available for ftp approximately 10 minutes after the close of the withdrawal period. At this
point, the remaining three data files (Y, M, and W) are posted. Simply reimport the round to
update the preliminary results.

1. In a DOS session, type the following commands:

   Command                          Comment
   ftp fcc.gov                      Begins ftp session with fcc.gov
   anonymous                        Your user name is anonymous
   password                         Any password will do
   cd c:\fcc\cu\temp                Change local directory to \temp
   cd /pub/Auctions/PMRS/Auction_12/Results   Change to where auction data is
   binary                           You are getting binary files
   mget 12_001?.dbf                 You want all dbf files for rnd 1
   bye                              This ends your ftp session

2. Change to the temporary subdirectory, c:\fcc\cu\temp, and type
   file
This runs the batch program file.bat, which places the round results (stored in the
c:\fcc\cu\temp subdirectory) into the appropriate subdirectories.

3. Switch back to Excel and with CU_TRACK.xls open select Import All from the Auction12
   menu. Type 1 when asked which round to import. It may take one minute to import a round
   on a 90 MHz Pentium computer. Please be patient. If you get an error message saying that a
   file is not found, it is probably because the dbf files are not in the appropriate subdirectories.
   Make sure that the required files are in place.

   If you want to see the data from an earlier round, simply reimport the desired round.

   Sorting Tables. The data in the sheets, MarketData, BidderData, Markets, and Bidders, can
be sorted any way you like. Simply click on the Sort button and then select the desired sort. You
can change the User Defined sort by selecting User Sort in the Auction12 menu. After an import,
the data is automatically sorted according the User Defined sort for each sheet.

   Analyzing Auction Data. A main feature of the bid tracking tool is the ability to analyze the
auction data in many different ways. Often you will want to look at data from many rounds at
once. This is accomplished by selecting Analysis from the Auction11 menu. Data can be selected
by market or by bidder. For example, suppose that you want to see all the bids for the entire
auction in Markets 1 and 2. Then you would select Analysis Market from the Auction12 menu. It
asks you to select "Sort Col 1", "Sort Col 2" or ,  Click on "Sort Col 2" to select by Market. Then
it asks you for which rounds you want to select data. Type 1-10 for rounds 1 to 10. Then it asks
you which markets you would like to select. Type 1,2,3 to select markets (Sort Col 2) 1 to 3.
Finally, it asks what type of data you would like to select. Click on All Bids to select all the bids
in these markets. It then selects the specified data and puts it in a workbook, Market.xls. If this
file already exists, it asks if you would like to replace it. Sheet2 of this workbook is a PivotTable
(see the Excel User's Guide), which displays all the bids for the specified markets and rounds.
This is handy to see who the competitors are in a market and see the progress of the bids. Each
row in the table is a round in which a bid was placed in one of the markets. The markets are
listed alphabetically as are the bidders in each market.

   There are several things to note about the Analysis procedure:
   
         The display of the results in Pivot Table format is limited to 256 columns, if your query
         exceeds this limitation, you will be prompted by software and directed to the raw data
         for analysis ("sheet 1" of either market.xls or bidder.xls).
         When selecting markets, you can use commas in any combination and dashes. For
         example, 1-3,4,8,10-12,16.
         You can select by phase ("Sort Col 1"), market ("Sort Col 2"), or Last Activity. Last
         Activity selects all markets that were active during the rounds selected.
         Once the PivotTable is created, you can modify it to see the selected data in different
         ways. For example if you want the data organized by bidder and then by market, simply
         drag the Bidder button to the left of the Market button in the PivotTable. If you want to
         save your modified table, select Save As from the File menu to save the file under a
         different name.
         To select data by bidder, select Analysis Bidder from the Auction12 menu. For
         example, you might want to know what markets bidder 3 has bid on in the first 10
         rounds. Specify rounds 1-10, bidder 3, and All Bids. A PivotTable is created in the
         workbook, Bidder.xls.
         You can change the orientation (for example, have rounds as columns and bidders as
         rows) of the pivot table by clicking on the Pivot Table button and dragging the data
         items to the appropriate spots. If you are not familiar with PivotTables, you should
         spend some time reading the Excel User's Guide.

   *** Printing Reports. You can define and print standard reports by selecting Print Reports in
the File menu. The report "Round Results" has been created as a sample. This report is easily
customized. Again see the Excel User's Guide.

Definitions. Below are definitions of some of the items that may not be clear from the headings
in the worksheets. Many of items in the Cellular Unserved auction are best displayed in
thousands (k). These are the units that are predominantly used in CU_TRACK.xls. Chg denotes
the change from the prior round in the item. The definitions are organized by worksheet.

         Summary
        Gross Revenue = total auction revenues, ignoring bidding credits, if applicable, for
         small businesses and ignoring current withdrawal payments.
        Net Revenue = total auction revenues, net of bidding credits, if applicable, for small
         businesses, ignoring current withdrawal payments.
        Withdrawal payments = current withdrawal payments, calculated as the lesser of the
         difference between the current net high bid and the net withdrawn bid and the difference
         between the current gross high bid and the gross withdrawn bid.
        New Bids = gives the number of new bids in the round and the quantity of new bids (in
         bidding units) relative to the total quantity of spectrum (in bidding units) being
         auctioned.
        New High = gives the number of new high bids in the round and the bidding units
         covered by the new high bids relative to the total bidding units up for auction.
        Top Ten Most Active Markets = the most active markets in terms of the number of new
         bids in each market.
        Top Ten Bid Increments = the new bids that increased by the largest increments. An
         increment of 2 means that the raise was equal to 2 minimum bid increments. The
         smallest acceptable increase is 1 bid increment.
        Raising Own Bid = firms that raise their own prior high bid are listed.
        Average Price = unweighted is a simple average of price on each license; pop weighted
         is an average weighted by population (Total gross $'s/Total Population).
        Eligibility Ratio = total eligibility (in bidding units) / total spectrum being auctioned (in
         bidding units).
        Chg in Eligibility = change in eligibility in bidding units.
        Bidders at Risk of Drop in Eligibility = a list of bidders who will lose eligibility if they
         do not bid in the next round; required activity is the number bidding units in thousands
         on which they must bid to maintain their current eligibility.

         Markets
        Sort Col 1 = Phase number.
        Sort Col 2 = Market number.
        Num Bids = number of new bids in the round.
        P = (Depending upon auction rules, this may or may not apply.) an "s" indicates high
         bidder has a 15% small business preference, a "v" indicates high bidder has a 25% small
         business preference.
        Round = the round in which the current high bid was placed.
        Bidding Units = Number of bidding units assigned to the market.
        Population = Total Population of the market based upon 1990 census.  This figure does
         not take the level of incumbency of each license into account.
        Net Bid = (Depending upon auction rules.) the bid net of bidding credit for small
         business.
        Price($/Pop) = high bid/population.
        # of Bid Increments = raise in bid from prior round measured in the number of
         minimum bid increments (e.g. 1 means that the minimum bid was placed).

         Bidders
        Eligibility (BUs) = Total number of bidding units in which the bidder is eligible to bid
         in the upcoming round and the change made during the current round.
        Coverage (Pops) = New is the total population covered by the bidder's new bids. High is
         the total population covered by the bidder's high bids.
        Activity Measures = High Dollars is the sum of the bidder's high bids. Activity is the
         sum of the bidder's high bids and active bids (i.e., a bid that was topped by another in
         the current round). Both are net of any bid credit, and ignore current withdrawal
         payments. Required Activity is the required activity in bidding units that the bidder
         must have in the next round in order not to lose eligibility.

Directory Structure. Proper installation creates several subdirectories, where the basic auction
data are stored. All are subdirectories of the auction directory (c:\fcc\cu in the default
installation). Six subdirectories of the auction directory contain the round results in dbf format.
These data files are downloaded from the FCC.

Subdirectory   Sample File Description
\dbfAll        12_001S.dbf All submitted bids for round 1
\dbfHigh       12_001X.dbf High bids before withdrawal
\dbfHighW      12_001Y.dbf High bids after withdrawal
\dbfMin        12_001M.dbf Minimum accepted bids for round 2
\dbfWdraw      12_001W.dbf Bid withdrawals in round 1
\dbfElig       12_001E.dbf Eligibility after round 1

Four subdirectories of the auction directory contain the round results in a form used by the
workbook. These data files are created when you import a round.

Subdirectory   Sample File Description
\ExcelDat\AllBids          bids1.dat All bids in round 1
\ExcelDat\Bidders          bid1.dat  Bidder data
\ExcelDat\Markets          mark1.dat Market data
\ExcelDat\Withdraw         with1.dat Withdrawal data

Finally, the subdirectory \Temp is used as a temporary location for downloading the dbf files.
See step 1 of Importing Round Results.