DOC

# The Big Treasure Hunt

By Betty Clark,2014-09-03 12:47
10 views 0
The Big Treasure HuntThe

1. The Big Treasure Hunt

Introduction

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.

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

teams.

; 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.

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

here.)

2

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

been disqualified

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

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

3

calculate the income from each category (team, member and non-member) as well as the

overall total.

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.

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.

Other Variations

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.)

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.)

4

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.

5

2. Household Telephone Account

Introduction

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.

YACKS Charges

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.

6

; 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.

Data Sources

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

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

named).

E. Familiarise yourself with the following functions (you may find some of them useful for

=DAY

=WEEKDAY

=HOUR

=MINUTE

=SECOND

7

=ROUND

=ROUNDDOWN

=ROUNDUP

=MEDIAN

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.

8

Add the following features to your workbook. Make sure you also extend the Start sheet to

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

PTable1.

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.

9

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.

Other Variations

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.

TeleStar Charges

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

Australia PR

Chile AC

China A1