ICTICT308 advanced features of computer applications

Assessment activities

Employment scenario

You work in the IT section of ABC Company. The company does a variety of work for different clients, including a large local sporting group, Rathdowney Park Sporting Association (RPSA) which is planning a trip to Melbourne to compete in the World Masters Games. As one of the MS Office experts in your company you have been assigned the task of designing organisational documents using advanced features of MS Word, MS Excel and MS Access.

RPSA has members and coaches. There are also medical staff in the local community who offer services from time to time, and will be travelling to the Masters Games.

Templates The first organisational document you have been asked to create is a Word template. The RPSA president would like to set up a consistent professional look to all written communication produced by the association. After much thought, she has outlined her specifications for standard and professional looking correspondence in the document DocumentGuide.doc.

Create a Word template for Rathdowney Park Sporting Association which includes styles as per this specification for all future communications. Call your template file RPSA (the extension that Word adds to your filename will depend on the particular version of Word you are using).

The second organisational document you have been asked to create is an Excel template. The RPSA president would like to set up a consistent professional look to all workbooks produced by the association. After much thought, she has outlined her specifications for standard and professional looking workbooks in WorkbookGuide.doc.

Create an Excel template for Rathdowney Park Sporting Association which includes styles as per this specification for all future workbooks. Call your template file RPSA. The file extension that Excel adds to your filename will depend on the particular version of Excel you are using.

Trip planning

The Rathdowney Park Sporting Association president has now asked you to apply your skills to the task of planning to send a group of players and support staff to Melbourne to compete in the World Masters Games. To date the following information has been collected:

games registration fees
several alternative accommodation costs
two alternative airfares
spending money estimate
bus rental.

You are to organise, analyse and present the information in a logical and useful format so it can be distributed to participants showing each person their own personal estimated expenses for the trip.

List of participants

Note the following from the database Rathdowney.mdb:

Members include players and accompanying persons
Each member is associated with just one coach
Each coach is dedicated to just one level of competition (Juniors, Seniors or Masters)

Your first task is to create a text file with a single list of participants from the Access database. The list will include:

All members (players and accompanying persons) who are willing to travel and who are associated with either of the two Masters coaches
The two Masters coaches
The three Medical staff.

The participant data is to include first name, surname, and codes for status and frequent flyer. This data is to be selected from the three tables in database Rathdowney.mdb. Data selection must be accomplished with the aid of three Access queries. The data output of each query is exported to a text file, and the three text files combined into one named Rathdowney.txt. The combining of the three text files may be done by simple copy and paste. Your list of participants in Rathdowney.txt will be imported into Excel as part of the workbook described below.

Workbook for calculating the trip cost

Create a new Excel workbook called MastersGames based on Excel template RPSA created earlier. Recall that Excel template RPSA was created using the design guidelines set down in WorkbookGuide.doc, so MastersGames will automatically take on the same formatting. Be sure to save MastersGames as a macro-enabled workbook.

Workbook MastersGames is to calculate trip costs for each participant. Most aspects about this trip are subject to change, as deals are brokered and arrangements are finalised. For this reason, your workbook must be flexible to cater for changes to travel deals. For example trip duration may change depending on availability of flights, or the hotels may offer increased discounts to secure business. The figures under “Data to be incorporated” below, are your first figures for the purposes of building the workbook.

Notes

Your workbook MUST show the principles of efficient spreadsheet design, and must contain the four sheets listed in WorkbookGuide.doc.
Your marker will test your workbook by altering some of the data in your Data sheet, and will expect to see figures in your Summary sheet adjust automatically.

Data to be incorporated

There are six (6) data items to be taken into account in calculating trip costs:

Duration of trip: 8 days (7 nights of accommodation)
Spending money needed = number of days away x daily allowance of $50
Bus rental $445 per day (or part thereof) shared equally by those taking trip
Games registration fees as shown in the following table

Participant Registration

Player $150

Accompanying person $100

Coach $80

Medical Staff $80

Participant	Registration
Player	$150
Accompanying person	$100
Coach	$80
Medical Staff	$80

Accommodation costs per night as shown in the following table

Hotel	Standard rate per person	Masters Games discount (players, coaches and medical staff ONLY)
Holiday Inn	$100	5%
Hyatt	$120	15%
Ramada Inn	$115	10%
Sheraton	$95	N/A
Marriott	$100	20%
Hilton	$80	N/A
Best Western	$75	5%

All the above data should appear in the Data sheet of workbook MastersGames, available to be used in the calculations outlined below.

Calculations

Trip costs = spending money + bus rental + registration fee + accommodation + flight

Notes:

Accommodation costs are subject to change as negotiations continue with the seven chosen hotels. Not all participants will be accommodated in any one hotel, so it will be necessary to have both minimum and maximum figures to allow participants to make an informed decision about the funds necessary. Players, coaches and medical staff all qualify for Masters Games discount rates where they are offered by a hotel. Non-players will pay the standard rates.

Flight costs with the chosen airline are subject to change as negotiations continue. The flight cost is to be calculated for each participant depending on whether they are a frequent flier or not.

A totals area of the Summary worksheet should contain:

average total trip minimum cost and average total trip maximum cost for all participants
total counts of players and non-players
total counts of frequent fliers and non-frequent fliers.

Design an Excel workbook which has all this information in a suitable format and complies with design principles in WorkbookGuide.doc. The summary worksheet should contain details of the trip cost (estimated minimum and maximum) for each person tailored to the individual’s status (player, accompanying person, coach, medical staff, frequent flier or not).

The association is considering financial help for players who are not frequent fliers and who are looking at a minimum trip cost of more than $1200. Create an advanced filter in the proper sheet to list players concerned. Create a macro attached to a button to refresh the filter.

The MastersGames workbook will be the data source for a merge with the information sheet described below.

Information sheet

The last step in the project is to send an information sheet to each participant. The starting point is to create a primary document with merge codes from the Excel workbook created above. This primary document is then merged with the Excel workbook to produce a multi-page document with one page for each participant.

Creating the primary information sheet document

Beginning with your Word template RPSA create a standard information sheet called InfoSheet to be used as the primary document in a Word mail merge. Use document SampleInformationSheet.pdf as a guide to the general layout of the document.

Your document should include merge codes for the person's name, status (P, A, M, C), whether a frequent flier, each trip expense category and an estimate of total maximum and minimum costs (depending on accommodation) for the trip. Note that it is made clear that cost estimates are current as of the date the sheet is printed and are likely to change as arrangements firm up. This sheet will be re-generated and sent to all participants several times in the coming months with changing details, as arrangements and numbers are confirmed.

The information sheet you design must follow specifications in DocumentGuide.doc. Your information sheet must:

be created from your Word template, RPSA
include a page border
show the system date
include a table for costings data (using cell shading, cell merging and formatting to produce a result similar to that shown in SampleInformationSheet.pdf)
insert merge codes at right places in the Word document to enable a successful merge with the source data in Excel workbook MastersGames.

Lost Password