1. The Big Treasure Hunt
A tramping club runs an annual fun team event called The Big Treasure Hunt. In this
project you will calculate the times and scores for teams in this event. You will also
determine the winning team, individual’s scores and times and various other statistics.
Teams of 2 or more people compete in The Big Treasure Hunt. The event has three "legs"
and each leg has a different route. Each leg also has up to twelve separate "treasures" that can be found. These are firmly secured cards on which a message has been printed. For each leg the teams are given a list of clues, one for each treasure. When they locate the treasure they write its message beside its clue on their list. Each correct message subsequently earns one point. The winning team (or teams) is the one that scores the most points. Time taken is not usually an issue, although teams like to know how long they have taken, but see below. Rules
The Big Treasure Hunt has the following rules:
1. Each team must have a minimum of two and a maximum of 12 members.
2. The official start time is 10 am.
3. Not all team members have to participate on all three legs but the leader and at least one
other member of each team must complete all legs. (i.e. At least two people from each
team (the captain and one other) must do the entire race. Any other people in the team
can do as many legs as they like.)
4. Each team must take at least a half-hour "food break" in the Hash House between legs. 5. All members who start a leg must finish the leg. i.e. The team doesn't "finish" the leg
until all those who started it have returned.
6. The last leg must be completed within 12 hours of the official start time. 7. A one mark per five minute (or part five minute) penalty applies if a team does not finish
by the official finish time. (e.g. Finishing 45 seconds late will attract a penalty of 1
point and 7? minutes late a penalty of 2 points.)
Failure to abide by any one of the rules above results in disqualification of the team.
(Note: This exercise has to some extent been modelled on a 24 hour orienteering style team event called Twalk run annually by the University of Canterbury, New Zealand Tramping Club (http://www.cutc.org.nz).
In this project you will use the information above and the data provided below to set up an Excel spreadsheet.
The Completed Spreadsheet
At minimum this will have the following named sheets:
; A Start sheet which will also describe the structure/layout of your workbook. ; A Teams sheet which will have the columns containing data and calculations about the
; A People sheet containing the details of the people in the teams and their results. ; A chart sheet called Chart of Results displaying the teams' scores and times.
It may also have one or more macro modules and other sheets for:
; Summaries including a frequency distribution of times and other statistics. ; One or more sheets for pivot tables.
; Income from the event.
; A scenario summary sheet containing various income scenarios. Data Sources
You will be supplied with the two spreadsheets People.xls and Results.xls.
The details of the people who competed in the event have been entered into People.xls.
This spreadsheet also has the details of who has completed which leg. The Results.xls
contains the data for each team, including each team leader’s Race No., in the treasure hunt. It gives the end times for the first leg and the start and end times for the other two legs. It
also has each team's points for each leg.
A. Using whatever technique you like merge the single sheets from each workbook into a
new workbook called TreasureHunt.xls. Make sure you also rename the Results
sheet as Teams.
B. On the Teams sheet set up columns for, and calculate, the following: (note: you may
also include other columns to assist calculations if you want) :
Food Break 1 Duration of the first "food break". (Hint: if your answers look
strange check the formatting.)
Food Break 2 Duration of the second "food break".
Total Racing Time Total time (formatted to show hrs, mins and secs) that the team
spent on the course on the actual legs. (i.e. excluding the food
breaks). (Assume they started the first leg at 10 am.)
Total Points The accumulated points from each leg.
Time Penalties Any points to be deducted for finishing too late. (Hint: check
out the time and rounding functions.)
Corrected Total Points Final points after time penalties have been deducted.
More columns will be added to this sheet in Task D.
C. On the People sheet set up a column to calculate whether or not each person has
completed all three legs. (Hint: it is best to calculate the result as 1 (for yes) and 0 (for
no) because this means it will be easier to compute the number of people in each team
who have completed all 3 legs in Task D.)
D. On the Teams sheet set up columns to calculate the following:
Completed all Legs The number of people in the team who have completed all 3
legs. (Hint: The SUMIF function could be very useful
Leader all Legs Indicator as to whether or not the leader of the team has
completed all legs. (Hint: It is easiest to use a VLOOKUP
where the People sheet is used as a lookup table with Race No
as the matched column.)
Fail (all legs) An indicator as to whether a team has been disqualified - if the
conditions in rule 3 are not met, the team is disqualified.
Fail (breaks) An indicator as to whether a team has been disqualified for
having had a break of less than half an hour.
Rank Final ranking of the team (provided it hasn’t been disqualified).
(Hints: look at the on-line help for the RANK function. You
might want to insert an extra column (Points for Ranking) that
only displays corrected total points for the teams who haven’t
Please include named cells as follows:
FirstTotalTime The cell containing the total racing time for team no 1.
FirstPenalties The cell containing the time penalties for team no 1.
FirstAllLegs The cell containing the number of people who have completed
all legs for team no 1.
FirstRank The cell containing the ranking for team no 1.
E. On a single chart, (use column type), show the Total Racing Times and Corrected Total
Points for all teams including the disqualified ones. Your graph will need a secondary
axis with the scales and gap widths adjusted to make both series clearly visible. Make
sure the location is a chart sheet called Chart of Results.
F. Set up the Start sheet so that it provides useful documentation of your project with sheet,
chart and range names and descriptions.
Add the following features to your workbook. Make sure you also extend the Start sheet to
include details of these additions.
G. On a new sheet called Summary set up a bin range, named bins, with about 10 steps,
suitable for generating a frequency distribution of the Corrected Total Points. Use
this range to create a chart (histogram) on the same sheet.
H. Insert a table called stats on the Summary sheet containing the maximum, median,
minimum and modal values of the Corrected Total Points.
I. The Big Treasure Hunt is intended to be a fundraiser as well as a fun event. There is
some sponsorship (which we will ignore) and each team is charged a $20 fee. In
addition the individuals within each team pay $8 each if they are a club member and $12
if they are not.
J. On an Income sheet set up a table to calculate the income from participants. As well as
named cells (TeamFee, MemberFee, NonMemberFee) for the various fees, include
cells with formulas for the number of teams (NoTeams), number of club members
(NoMemb) and number of non-members (NoNonMemb). Include formulas to
calculate the income from each category (team, member and non-member) as well as the
K. Set up 3 or 4 scenarios for different combinations of the three fees (e.g. Include a
scenario where there is no team fee and members pay $10 and non-members $15.)
Generate a Scenario Summary to illustrate the income that would be generated from the
various scenarios. You must show how the income from each category and the overall
total income changes.
L. On a Pivot Tables sheet create pivot tables (and optionally pivot charts) to show:
i. The number of participants for each combination of Gender and Club Membership.
Page this table by Team.
ii. The total number of people in each team and how many completed all the legs and
how many are club members.
M. Macro tasks
The data in People.xls and Results.xls were carefully checked to make sure they did not
contain errors. In practice, this might well not happen especially if the data is entered on a laptop, in poor lighting, in the Hash House during the event. Write a report, about one page long, describe some of the errors that might occur and how easy it would be to anticipate and correct them.
Recycling this Project
This project could be reused without needing any changes to the data as it uses only time and not date data.
An earlier version of the same project used a simpler version of rule 3 above:
3. Not all team members have to participate on all three legs but two members of each team
must complete all legs. (i.e. At least two people from each team must do the entire
race. Any other people in the team can do as many legs as they like.)
It also had an additional task:
All individuals taking part will be issued with a named certificate stating the teams' name,
corrected total points and ranking. Add 3 additional columns to People containing this
information so that it can be subsequently used in a form letter to make the certificates.
(Hint: the Teams sheet can be used as a large lookup table. Check something sensible
is done for members of disqualified teams.)
This problem has been used twice in a second course (200 level) in end user computing. In the same class, students have completed a subsequent project to convert the spreadsheet into a database. The database had tables for Team, Person and PersonLeg (who completed each leg). To keep it simple only the teams’ overall times were stored. The results were
also displayed on a web page with a gallery of photos and a map of the course, etc. While the web page used in this class used static results a web enabled database could also have been used.
2. Household Telephone Account
Yaldhurst-Avonhead Communication Key Systems (YACKS) is a company supplying telecommunication services. You will use YACKS rates to calculate telephone toll charges using sample data for a local household. The household has a landline phone. Local calls to other landlines are free so only national and international calls and calls to mobile phones are charged. The charging scheme is described in the next section.
You will compute charges for toll calls (national, international and mobile calls) made by the family over a six month period. Every call is charged for a minimum of one minute.
After that, international calls (those with an access code beginning "00") are charged to the nearest second, whereas mobile calls (those with access codes of 021, 025 or 027) and
national calls are charged in whole minutes for each part of a minute in duration (e.g., a
mobile call of duration 00:03:00 is charged as 3 minutes, a mobile call of duration 00:03:07 is charged as 4 minutes). All calculated charges should be rounded to the nearest cent.
The rate charged for each call depends on where called and when. Each call is charged at the rate applicable at the beginning of the call. The Weekend rate applies from 6:00pm
Friday until 8:00am Monday (but not including 8:00am Monday). The Peak rate applies
from 8:00am until 6:00pm (but not including 6:00pm) on weekdays (Mon, Tue, Wed, Thu,
Fri). The OffPeak rate applies at all other times. This description of rates applies every day of the year (ie, there are no special rates during holiday periods).
YACKS also allows the family to nominate one national telephone number as its "Friendly Ears" number. Whenever the family calls its Friendly Ears number (currently 03 4350698), the rate charged is a flat 10 cents per minute, regardless of when the call is made.
YACKS offers various schemes. The family is currently on the "Standard Yack" scheme which means it pays $42.95 per month for line and Internet access, and all national calls
charged during the weekend period are capped at $5.00 (i.e., any national weekend call is charged at a maximum of $5).
You can assume that all calls are shorter than 24 hours (YACKS automatically disconnects calls if they get to 24 hours!). Calls may go over midnight (e.g., start at 11:30pm and stop at 1:09am). You do NOT need to deal with goods and services or any other taxes.
Your will set up an Excel spreadsheet that will include data and calculations and a chart. The Completed Spreadsheet
At minimum this will have the following named sheets:
The workbook will include at least the following named sheets:
; A Start sheet which will also describes the structure/layout of your workbook. ; A Calls sheet which will have the columns containing data and calculations for the
individual toll calls.
; A Tables sheet containing the reference tables used.
; A chart sheet called Chart of Calls
It may also have one or more macro modules and other sheets as follows: ; A sheet containing a frequency distribution and a histogram of call lengths as well as
the total six-month charges and other statistics and scenarios.
; A Scenario summary sheet containing various scenarios.
; A sheet with pivot tables.
You will be supplied with the two files Places.xls and Yackity.txt. Places.xls contains peak,
offpeak and weekend charging rates by place for national calls, by country for international calls and the rates that apply for mobile calls to any location. The sample data is in the file Yackity.txt. Each record in this file gives details of the date and time a call started, its end time and the telephone number and its place/mobile. Place/mobile contains the place for national calls, the country for international calls and the type of scheme for mobile calls. Basic Tasks
A. Obtain your own copies of the files Yackity.txt and Places.xls.
B. Open Yackity.txt in Excel ensuring the data goes into five columns and save it as
Yacks.xls in Excel workbook format. Rename this sheet Calls.
C. Correct the obvious errors and inconsistencies in the call data. Keep a list of the
changes you have made to the data for Written Task i.
D. The three sheets in Places.xls each contain a table showing the per minute rate for
telephoning various places or mobile phone schemes. These tables are suitable to use
as lookup tables. Put the data from all three sheets into a sheet called Tables in
Yacks.xls. (Hint: You might find your formulas at task E, below, easier to manage if
you combine the data from the three lookup tables into one big table for this exercise,
with repeated data as appropriate for the international and mobile calls.)
Name your new lookup table AreaTable (or, if you have kept the tables separate, name
them AreaT1, AreaT2, AreaT3). Also into this sheet put the Friendly Ears number
and rate, suitably labelled and named, and the weekend cap ($5 – again labelled and
E. Familiarise yourself with the following functions (you may find some of them useful for
tasks E and H!):
F. Set up columns for, and calculate, the following on the Calls sheet:
Type Whether the call is International, National or Mobile. (Hint for mobile
calls: national calls never have an access code of "02".) Day The day of the week each call started (a number from 1 to 7). Duration The length of the call, formatted as a time. Where the stop time is
before the start time, assume the call has gone over midnight (e.g., a call
starting on 7 Aug at 10:00pm and stopping at 2:00am, is four hours long).
Your formula must deal with this situation.
Minutes The number of actual minutes in the duration (e.g., a duration of
01:25:30 is 85.5 minutes).
(Hint: If you think you have the formula correct but get a strange result,
such as 00:00:00, reformat the cell as a number!)
Mins Charged The number of minutes the family will be charged for. Remember that
all calls are charged for a minimum of one minute, and that national and
mobile calls are charged in full minute steps (e.g., 00:12:15 would be
charged as 12.15 minutes if it were international, but charged as 13
minutes if it were national or mobile). Do not round at this stage. Period Whether the call is to be charged as Weekend, Peak or OffPeak (a
lookup table index is OK here, if you prefer).
Minute Rate The rate per minute for the call. Your formula must also allow for the
Friendly Ears number.
Amount The total charge for the call, in dollars, rounded to the nearest cent.
Remember that the rates in AreaTable are in cents, and that national calls
at weekends are capped.
Notes: You may also include other columns to assist calculations if you want.
If you reference any additional tables then store these on the Tables sheet.
G. Set up a page header for the Calls sheet with your name(s) left-justified, the page number in the centre and the date and time right-justified. Hide all columns except the five
original data columns, Mins Charged, Period and Amount columns. Using landscape
orientation, print out just the first two pages.
H. On a chart sheet called Chart of Calls make a column chart to show the Mins Charged
and Amount for the first 50 calls. Your graph will need a secondary axis with the scales and gap widths adjusted to make both series clearly visible.
I. Set up the Start sheet so that it provides useful documentation for your project with sheet, chart and range names and descriptions.
Add the following features to your workbook. Make sure you also extend the Start sheet to
include details of these additions.
J. On a new sheet called Summary set up a bin range, named MinuteBin, suitable for
generating a frequency distribution of the call length, with frequencies representing the
number of calls:
up to 3 minutes
> 3 to 10 minutes
> 10 to 15 minutes
> 15 to 30 minutes
> 30 minutes to 1 hour
over 1 hour
and use it to create a chart (histogram) on the same sheet.
K. Into the Summary sheet insert the number of months of data (currently 6) and the
monthly access charge (currently $42.95), named Months and Access. Use data
validation to ensure only sensible values can be entered into those cells. Create a table
called Stats on the Summary sheet containing the count, median and average Amount
for all the calls, and the Overall Total (including the total amount of the calls and all
monthly access charges). Also include in your Stats table a count of the Mobile calls
(name this count Mobile).
L. The family is considering changing its Friendly Ears number to 03 3089595 and also
changing its pricing scheme. Remember the current scheme is "Standard Yack"
which means it pays $42.95 per month for line access, and all national calls charged
during the weekend period are capped at $5.00. Possible new pricing schemes include
"Prime Yack" ($45.95 per month, $3.50 national weekend cap) or "Mega Yack"
($49.95 per month, $2.60 cap). Set up scenarios, attached to the Summary sheet for
different combinations of Friendly Ears and scheme. Include the current Friendly Ears
number and scheme. (Hint: This is likely to require a change in where you store
some of the input values used in the calculations.)
M. Generate a Scenario Summary to illustrate the different average amounts and overall
totals from your Stats table that would be generated from the various scenarios.
N. On a Pivot Tables sheet create pivot tables to show:
The total number of calls and their total and average Amounts for each Type on each
Day. Make sure the Day labels displayed are user-friendly. Name this pivot table
The total number of calls and their total Amounts broken down by Type and Day, and
paged by Period. The days Monday-Thursday should be grouped together. Again, all
labels must be user-friendly. Name this pivot table PTable2.
Answer the following in a brief report:
i. List the changes you made to the data in Task C.
ii. YACKS also offers a "Global Yack" scheme. The monthly charge for this scheme
is $59.95, there is a cap on national calls of $5 which applies during OffPeak
periods as well as at Weekends, there is a cap on international calls of $10 which
applies during OffPeak and Weekends periods, and any mobile calls longer than 5
minutes made between 10pm and 2am are charged at much cheaper rates than
standard. Describe all the changes to your workbook that would be needed to
accommodate this scheme. Do not implement the changes.
iii. Suppose another student would like a copy of just the mobile call data and
calculations (from the Calls sheet). Write a set of instructions, suitable for the
other person to use, of how to smoothly copy the mobile call information to a new
workbook by using Excel's filtering facility. This should be about half a page long.
Recycling this Project
The existing data, for this project, relates to calls made form Christchurch, New Zealand to other locations in NZ and elsewhere. It could readily be adapted for another city in another country. This would require new places in the National sheet and possible removal of the country from the international sheet in Places.xls. Similar modifications would be required
to the Places/Mobile field of Yackity.txt. The dates in Yackity.txt could also be updated easily.
In the past other variations of this problem have been successfully used. Two are briefly described below. Similar tasks to those above can be completed for these problems.
This problem uses details of all international toll calls made by a New Zealand family over a six month period. Each call is charged for a minimum of three minutes and thereafter to
the nearest second. All calculated charges are rounded up to the nearest cent.
TeleStar bases its charges on co-operation agreements negotiated with providers in each country as follows:
Country Called Agreement Type