In this assignment you will be using Microsoft Excel, a spreadsheet software program, Microsoft Access, a database software program, and Microsoft Word, a wordprocessing software program. This assignment will give you an opportunity to apply what you have learned about these software packages in your tutorials.

Part Ia - Displaying Information [2.5 marks]

You have just taken over as statistician for the National Women's Hockey League (NWHL). Your first task is to analyze the finances of the ten teams in the NWHL. You must present information about the Projected Balance that the teams will carry into the next season.

Each team in the National Women's Hockey League (NWHL) has a balance from the previous season as well as income from ticket sales and sponsorship for the current season. Each team has two categories of expenses for the current season: ice expenses and miscellaneous expenses.

This table gives the finances of each team (rounded to the nearest $10), at the beginning of the current season:

                   Previous Current    Current       Current
                   Balance  Income     Ice Expense   Misc Expense
Brampton (THU)     $ 3 000  $ 95 000   $22 000       $ 55 000
Durham (LIG)       $ 8 000  $ 62 000   $18 000       $ 61 000
Laval (MIS)        $-5 000  $ 42 000   $16 000       $ 52 000
Mississauga (CHI)  $35 000  $ 90 000   $19 000       $ 60 000
Montreal (WIN)     $ 2 000  $ 36 000   $13 000       $ 36 000
North York (AER)   $75 000  $ 90 000   $19 000       $ 60 000
Ottawa (RAI)       $17 000  $ 84 000   $23 000       $ 65 000
Sainte-Julie (PAN) $12 000  $ 72 000   $21 000       $ 60 000
Scarborough (STI)  $11 000  $ 62 000   $18 000       $ 62 000
Vancouver (GRI)    $     0  $500 000   $20 000       $400 000

The projected balance for each team that will carry over to the beginning of the next season can be calculated by adding the Balance and Income and then subtracting all expenses (both ice and misc. expenses). You must then add 14% to this amount to account for any money that would be gained through interest (or lost through loans if a team has a negative projected balance).

You will need to construct a chart in MS Excel that looks like the chart shown below. Some of these values are exactly from the information given above, while others are calculated. The steps below further describe how to generate each of the 8 rows in the chart.

      Calculation of Projected Balance for the NWHL (in $)


                     THU LIG MIS CHI WIN AER RAI PAN STI GRI     
                                                                  _______________ 
  Balance                                                        |Interest Rate: |
  plus Income                                                    |           14% |
  ____________________________________________________________   |_______________
  Total Assets
  less Ice Expense
  less Misc Expense
  ____________________________________________________________
  Current Balance
  plus Interest
  ____________________________________________________________
  Projected Balance
  ============================================================

1. Since your data is unlikely to fit across the page, you must print your document using landscape mode. Use "File->Page Setup" to modify your print settings so that your chart will print in "landscape" mode. If the document is too wide for landscape mode, the part that doesn't fit will be printed on an extra sheet of paper (this is fine).
2. Start by filling in your row and column headings, so that your chart matches the chart shown above. Each of the team names (THU, LIG, MIS, etc) should be centred within their cell. Use bold face and italics as shown in the chart above. The title should be inserted two lines above the chart (two blank rows of cells after the title). It should be centred and in bold, with NWHL in italics.
3. Notice that there are three single horizontal line separators and one double line separator (after Projected balance). You can create the single and double horizontal lines by selecting the cells in the row and using "Format->Cells->Border". You shouldn't leave any blank rows in your chart (the above picture of the chart looks like it has blank rows because it was created using HTML).
4. If necessary, resize your columns to allow the full row headings to appear in one column. Select the column, and then use "Format->Column->AutoFit Selection."
5. The interest rate should be shown in a box on the right side of your chart. Use 14% for the interest rate. It will be used in the formulae below.
6. Using the information given in the table of finances, fill in the rows labeled Balance, plus Income, less Ice Expense, and less Misc Expense, exactly as it was given to you.
7. Calculate the other rows, as described here. Start by putting the formula in for the THU column, and then copy the contents of that cell to the other cells in that row. The calculations will happen automatically for the rest of the row.
   • Total Assets = Balance + plus Income
     
   • Current Balance = Total Assets - less Ice Expense - less Misc Expense
     
   • plus Interest = Current Balance * Interest Rate
     
   • Projected Balance = Current Balance + plus Interest
     
8. Only each number in the Balance row and each number in the Projected Balance row should have a dollar sign ($) in front of it. Format numeric values so that each three digits are separated by a comma.
9. Verify that the results in your chart make sense.
10. Change the header of the page so that National Women's Hockey League is printed at the top left of the page.
11. Change the footer of the page so that your first and last name and the date you prepared the document are printed.
12. Use the Chart Wizard to create a column graph that shows how the Projected Balance varies for each team. Put the three letter team abbreviations on the horizontal axis. Use appropriate titles for the vertical and horizontal axes. The title of your column graph should be "Fluctuation of Projected Balances." Since there is only one colour on the column graph, there is no need to have a legend explaining the colour. Insert this graph below your chart, on the same page as your chart.
    [Note: Be sure to select the row that contains the team names and the row that contains the Projected Balance, before starting the Chart Wizard.]
13. Print this page, which includes both the chart and the graph.
14. Adjust the Page Setup if you are having problems fitting everything on the same page.

The league president would like to know how the projected balances would differ if the interest rate was increased to 25%. Modify your spreadsheet so that it uses 25% for the interest rate instead. The calculated values in your chart should get updated automatically. Print this new page, which will include both the table and the graph.

Now you must make a printout that shows the formulae that you used for your 25% interest analysis. To do this, press the CTRL key and the ` key (this is the backwards single quote, which should be at the top left of your keyboard -- it's not the regular single quote) at the same time to toggle to the formulae mode. You may find that your printout is several pages wide when printed in landscape mode. This is fine, as long as all of your formulae are printed. You can resize your chart or graph, if this helps.

Part Ia Submission

For this part, you will need to submit your printout for 14% interest, your printout for 25% interest, and your printout that shows the formulae used for 25% interest.

Part Ib - Manipulating Information [3.5 marks]

As the new statistician for the NWHL, you have been asked to write a memorandum for the league's president, Allan Dawby. Your report must be written using MS Word. The instructions in this section will describe what should be included in your memo.

Download the part1b.txt data file from the CSC104 course web page, and store it on your hard disk. Import the data into MS Excel by selecting "File->Open..." within Excel, and choosing the part1b.txt file. This will activate the "Text Import Wizard." Select "Delimited" on the first window, and then click "Next>." Select "Comma" on the next window, and then select "Finish."

The imported file contains the following seven fields for each player in the NWHL:

• Last name
• First name
• Name of team
• Number of games played
• Number of goals scored
• Number of assists earned
• Number of minutes of penalties

1. Insert a new row at the top of the table, and label each column of data with a meaningful name.
2. Add a new column (after the penalty minutes column) that contains the total points for each player. The total points for a player is calculated by adding the player's goals and assists.
3. Add a new column (after the total points column) that contains the weighted points for each player. The weighted points for a player is calculated using the formula:
      weighted points = 1.5*(goals - 0.2*penalty_mins) + assists
   This weighted points gives more value to goals, as less goals are awarded than assists, and subtracts penalties, as excessive penalties can be detrimental to a team.
   The weighted points for each player should be rounded to two decimal places.
4. Determine how many players are in the league.
5. Determine the average of all of the weighted points for all players.
6. Use the "Edit->Find..." option to determine the total number of points for each of these players:
   
   • Angela James
     
   • Cassie Campbell
     
   • Mai Len Le
     
   • Andria Hunter
     
   • France St-Louis
     
   Note that Excel can only search on one field at a time.
7. Determine the median weighted points, the highest weighted points, the lowest weighted points, and the mode for the weighted points. Give the names of all players who obtained these four weighted points.
8. Determine how many of the top 25 point-getters play for each of the following teams:
   • North York Aeros (AER)
   • Brampton Thunder (THU)
   • Scarborough Sting (STI)
   • Mississauga Chiefs (CHI)
   It will be helpful to use "Data->Sort" for this question.
9. Filter your data so that only the records for players whose last name begin with S, Team is THU, and total points is at least 10 are shown. Include the complete statistics (the original 7 fields and the 2 new ones) for these players in your report. Make sure your database is sorted by descending total points before doing the filter. The records should be listed by descending total points in your report. [Copy and paste the result from Excel into Word, to eliminate retyping.]
10. Use the IF function (Insert->Function...) and the Function Wizard to assign a ranking to each player's weighted points (WP), as follows:
    • WP smaller than zero ----> assign F
    • WP between 0 and <15 ----> assign D
    • WP between 15 and <30 ----> assign C
    • WP between 30 and <45 ----> assign B
    • WP greater than or equal to 45 ----> assign A
    See the IF help menu for more information. You'll need to add a new column with an appropriate title. Your memo should include all 10 fields for the top 10 players in the list, when sorted by descending total points.
    You must also include the IF function formula that is used to determine the ranking for the person with the most total points. It will likely start like this, =IF(I2 ...
11. Modify the header of your report to say, NWHL Report, and the footer to contain your first and last name and the date on which you prepared the document.

Part Ib Submission

In this part, you will need to submit a printout of your memo (made using MS Word), which contains the following:

• Number of players in the league.
• Average weighted points.
• Total points for 5 players listed.
• Median, mode, highest, and lowest weighted points, and which players achieved these values.
• How many players in the top 25 point-getters for the four teams listed.
• All statistics for players with "S" starting last name, THU team, and at least 10 points.
• All statistics for top 10 players, and the IF function formula for the player with the highest total points.

Part II - Database manipulation [4 marks]

As well as being the statistician for the league, you are also the statistician for the Mississauga Chiefs. Since the General Manager (GM), Ricky Oz, has been sick for the last month, you have been called on to perform tasks that may not have been part of your original job description.

Unlike the NHL, players in the NWHL must contribute funds to the team. It is the job of the Assistant General Manager (AGM) to organize these funds, and to make sure all players have paid in full. Since the AGM is busy doing the work of the GM, you have been asked to implement a database to keep track of the accounts receivable (i.e., which players owe money to the team).

1. Start by creating a database containing the phone numbers of all players on the Chiefs. Start up Microsoft Access, and when it says "Create a New Database Using," select the Blank Database option. Type in chiefs.mdb as the name of the new database file, and click on the "Create" box.
2. With the "Tables" tab chosen, click on "New" to create a new table. Using the Table Wizard, you should select the "Contacts" Sample Table. Choose "FirstName" and "HomePhone" as the two fields in your new table. Name your table "Contacts." Select the option to allow Access to set the primary key for you.
3. Use the Table Wizard to enter the following data directly into your table, and close your table once you have finished.

     FirstName     HomePhone
   
     Coopy         519-888-0001
     Dewey         519-888-0002
     Grims         905-888-0003
     Hurricane     905-888-0004
     JP            519-888-0005
     Mack          416-888-0006
     Lizard        416-888-0007
     Louie         519-888-0008
     Marn          905-888-0009
     Nat           905-888-0011
     Pep           416-888-0011
     Sally         905-888-0012
     Smitty        519-888-0013
     Snipe         416-888-0014
     Stewie        905-888-0015
     Super Mom     905-888-0016

Now create a table for the Mississauga Chiefs' Accounts Receivable.

1. Create a new table as you have done to create the table above. Your new table should have the following three columns: FirstName, TransactionDate, and AmountOwed. If the field name is not in the list of sample fields provided by the wizard, choose any field name, and then rename it before entering your data.
2. Again, select the option to allow Access to set the primary key for you. If you decide to insert a counter as your primary key, you may, but do not insert this field into any of your queries or reports.
3. Name your table "Receivables." For now, you should indicate that your Receivables table is not related to your Contacts table. Select the "Modify the table design" option. Set the type of the TransactionDate field to Date/Time and the type of the AmountOwed field to Currency. Close the modify window.
4. Now open your Receivables table and enter the following data, and then close the table when you have finished.

     FirstName     TransactionDate     AmountOwed
   
     Coopy         1999-09-15          450.00
     Dewey         1999-09-15          400.00
     Snipe         1999-09-15          450.00
     Pep           1999-09-15          450.00
     Smitty        1999-09-15          310.00
     Lizard        1999-09-15          280.00
     JP            2000-02-13           30.00
     Dewey         2000-02-13           30.00
     Pep           2000-02-13           30.00
     Snipe         2000-03-22          255.00
     Dewey         2000-03-22          255.00
     JP            2000-04-02           55.00

Lori Fryman, the AGM, informs you that the players who owe more than $250 on a particular transaction will be charged an interest fee of 3% for that transaction. This should be incentive for the players to make their payments in a more timely fashion. Ms. Fryman wants a list of all transactions that are greater than $250, that have not been paid yet. She also wants a separate column in the list that contains the new transaction amount, once 3% has been added to the qualifying transactions. To do this, you will need to set up a query, as follows:

1. Select the "Queries" tab in the database window, and select the "New" button. Make sure "Design View" is selected, and then click the "OK" button.
2. In the "Show Table" dialog box, click "Receivables" and then "Add." Then close the "Show Table" dialog box. Double click on each of the FirstName, TransactionDate, and AmountOwed fields to select these three fields for your query.
3. Type ">250" for the "Criteria" of the AmountOwed field. For the "Sort" row of the AmountOwed field, select the option that will cause the smallest amounts to be listed first.
4. In the fourth column enter TotalOwed:[AmountOwed]*1.03 in the field row. Set this field to use the "Currency" format. This can be done by right-clicking with your mouse, and selecting "Properties."
5. Run the query by clicking "Query->Run" from the main Access window. Save the query using the name "TotalQuery."

Create a report for Ms. Fryman, as follows:

1. Select the "Reports" tab, and click on the "New" button. Use the "Report Wizard." Create a report on the TotalQuery query, that shows all four fields. Use the "Tabular" layout, and make sure your records are sorted so that the smallest transactions are listed first. Select a title for your report that clearly describes what it shows. Print and save your report.

One advantage of using a database program, rather than a spreadsheet program is that it allows you to define relationships (links) between tables. This is one of the most powerful features of a relational database management system. A common field can be used to relate one table to another. You will combine the information in the "Contact" table with the information in the "Receivables" table, using the "FirstName" field. This will allow you to include the phone number of each person who owes more than $250.

1. Click on the "Queries" tab in the Database menu. Click on "Design" to open the design of your "TotalQuery" query.
2. Click on "Query->Show Table..." to open the "Show Table" dialog box. Highlight the "Contacts" table and click the "Add" button. Then click the "Close" button of the "Show Table" dialog box.
3. Click the "Field:" box in the fifth column of your query and insert the text, "Contacts.HomePhone" in the box. This is how we reference a field in another table. Click the check box next to "Show:" so that this field will be displayed.
4. To link the "FirstName" in the Receivables table to the "FirstName" in the Contacts table, you click FirstName in the Receivables table, and drag and drop it to FirstName in the Contacts table. If you have done this correctly, a solid line will be displayed on your screen, connecting these two fields.
5. Close your query design window, and click "Open" to view the result of the "TotalQuery" query. The home phone number should now be listed with your data.
6. Again, create a report to print the result of this query, including all fields. Make sure that the records are printed in alphabetical order by first name. Choose a descriptive title that is appropriate for the data that is displayed.

Part II Submission

In this part, you will need to submit the output of your TotalQuery, with and without the phone numbers.

• This is not a hard assignment, but it will take a fair amount of time to complete. Budget your time so that you are not rushing to finish your assignment at the last minute. Start early!
• It is your responsibility to make sure you save your work after you have completed each step.
• If you need more help, refer to the help menus that are provided within the software packages being used for this assignment. Be sure to take advantage of the TA office hours too.

Hand in the material listed in each of the "Submission" sections above. Staple the pages for each of the three parts together, if they contain more than one page. You are not required to submit a floppy diskette.

Place your printed pages in an 8.5 x 11 inch unsealed envelope and give it to your TA at the beginning of your tutorial. Make sure that you attach a copy of the A4 cover page to the front of your envelope. The information you write on this cover page must be readable and accurate.

Late assignments will be accepted until 11:30 p.m. on Wednesday August 2nd. They will receive a penalty of -30%.  They must be left in the CSC104 late drop box (just outside SF2305A).

This assignment will be marked out of 10. Part Ia is worth 2.5 marks, part Ib is worth 3.5 marks, and part II is worth 4 marks.

Make sure that you are familiar with the guidelines regarding avoiding plagiarism in the code of academic discipline. Contact your instructor if clarification is needed.