Due Date: Sunday November 18th, 2007 at 11:59pm
Weight: 25%

Part A: Shape Up Fitness Centre

Introduction

For this part of the assignment, you have been given a partially completed workbook called PartA.xls. Your task is to complete this workbook so that it matches the picture of the workbook displayed here. You do not have to match the formatting exactly, but you are required to duplicate the functionality.

Your Task

Perform the following steps:

1. Click in cell C4 and enter the appropriate VLOOKUP function to determine the cost of the individual's membership. Your formula should contain an absolute reference to the table of membership costs that is contains in cells A17 through C19.
2. Click in cell E4 and use an IF function to determine the annual total, which consists of the cost of membership plus an optional locker fee. Now click in cell G4 to compute the total amount due, which is the annual total multiplied by the number of years the individual has chosen.
3. The down payment in cell H4 depends on the type of membership and is not dependent on the number of years. Thus Brown, Hathaway, and Street all have a required down payment of $300 for the Deluxe membership, even though they selected a different number of years.
4. Compute the balance (which is the total due minus the down payment) in cell I4. Use the PMT function to determine the monthly payment in cell J4.
5. Copy the formulas in row 4 to the remaining rows in the worksheet.
6. Enter your last name in cell A9 in place of "Hunter". Sort the client list in alphabetical order, then select the row containing your name and shade it.
7. Enter the appropriate statistical functions in column H to determine summary statistics. Your values should match those shown above.
8. Format the completed worksheet as you see fit. Insert clip art somewhere in the worksheet to serve as a logo. You do not need to use the same logo that is used in the worksheet shown above.
9. Save your file using PartASoln.xls as the filename.

Part B: Gadgets Galore

Introduction

For this part of the assignment, you have been given a partially completed workbook called PartB.xls. Your task is to complete this workbook as described in the "Your Task" section below. In step 2, you will create a chart that looks like this:

In steps 3-11, you will create a PivotTable that looks like this:

You do not have to match the formatting exactly, but you are required to duplicate the functionality.

Your Task

Perform the following steps:

1. Click in cell B3 in the Documentation worksheet, and fill in your name.
2. Go to the Summary worksheet, and create a clusted column with a 3-D visual effect chart (in its own chart worksheet named Chart) that plots the data in rows; that is, the X axis should display the four quarters, and the legend should display the product names. Give your chart a title at the top.
3. Click the Summary worksheet tab, and click on a cell within the table. Go to menu command Data --> PivotTable and PivotChart Report.... Click the option buttons to select Multiple consolidation ranges and to specify PivotChart report (with PivotTable report). Click Next.
4. Click the option button that says I will create the page fields. Click Next.
5. Specify the range in step 2b of the PivotTable Wizard through pointing. Click the Sheet tab for Atlanta, select cells A2 through E5, then click the Add command button. You should see Atlanta!$A$2:$E$5 in the All ranges list box. Repeat this step for the other two cities.
6. Remain in step 2b of the PivotTable Wizard. Click the option button for 1 page field. Select (click) Atlanta!$A$2:$E$5 within the All ranges list box, then click in the Field one list box and type Atlanta. Do not press the Enter key. Repeat for Boston and Chicago.
7. Click Next. Click the option button to create the PivotTable on a New worksheet. Click Finish.
8. Rename the Chart worksheet as PivotChart and the PivotTable worksheet as PivotTable.
9. Click on the PivotTable worksheet, and notice that it does not match the diagram of the PivotTable shown above. Click in cell B3 (the entry in the cell is Column), then click the formula bar and type Quarter, replacing the previous entry. Change the entry in cell A4 from Row to Product. Change the entry in cell A1 from Page1 to City.
10. Pivot the table to match the diagram of the PivotTable shown above. Drag Quarter to the row position (cell A4), Product to the column position (cell C3), and City to the row position below Quarter (cell A5). Release the mouse, and the Quarter and City labels will move to the positions shown in the diagram above.
11. Format the PivotTable so that it matches the diagram of the PivotTable shown above.
12. Make sure the colours, names, and order of the worksheet tabs are the same as those shown in the Chart diagram above.
13. Save your file using PartBSoln.xls as the filename.

Submission

Part A: Shape Up Fitness Centre

Submit the following file to Blackboard:

• PartASoln.xls

Part B: Gadgets Galore

Submit the following file to Blackboard:

• PartBSoln.xls

Using Blackboard

To submit your files for this assignment, login to Blackboard by entering your UTORid and password.

• click on the link for the CSCA01 course
• click on View/Complete Assignment: A3
• if you haven’t completed all of this assignment or if your assignment is not working correctly, please leave a note for your TA in the Comments box, explaining the missing parts of your assignment.
• next to Attach local file, click on Browse... to select your first file and then click the Add Another File button to add this file. For each additional file, repeat this process (i.e., click on Browse… and then Add Another File). The name of each file to be submitted will be shown in a list.
• click either Save or Submit. Make sure all of the files that you want to submit are listed before clicking Submit, as you CANNOT RESUBMIT AN ASSIGNMENT or individual file for an assignment! If you are not ready to submit your files, just click Save and visit this web site again later to complete the submission.

Always be sure to keep a backup of your submitted files, just in case something goes wrong.