Part 1

The North Philadelphia Alley Cats are a Bowling farm team for a much larger successful team. Looking back on the 2006 season the management needs to figure out what players are producing with a good bowling average and what bonus’ they will each receive.

Step 1:

Start by opening Excel, save the worksheet “Lastname_CIS1055_Xx

Type your full name in A1, Class and Section in B1, Today’s Date C1

Type a Title in Row 5, which has a font size of 20 and is merged and centered from A5 through H5. “N Philly Cats”

Re-Name the Sheet1 “Cats”

Start filling out the spreadsheet with the following information. All salaries should be formatted as currency and have two decimal places. All batting averages should have 3 decimal places. Always use cell references in your formulas.

Step 2:

Thomas Jones ($9,000.00/yr salary) bowled inconsistently for the season. His Monthly bowling average is presented as follows: Start your worksheet by making it look like this example. Put player in A6.

Player / May / June / July / Aug / Sept / Yearly Salary
Thomas Jones / 0.375 / 0.175 / 0.250 / 0.273 / 0.265 / $9,000.00

Continue filling in the next three players using the criteria explained below:

Bryan Michaels ($5,000.00/yr salary): Starting in May Bryan bowled .250. Bryan consistently improves his bowling average by 1.2% over the previous month for the rest of the season. Develop a formula to compute his bowling average for June through September.

Jeanie GoodBatts ($100,000.00/yr salary) bowled .220 in May. She increased her bowling average each month over May’s bowling average by the following: June 1.00%, July 2.00%, August 3.00%, Sept 4.00%. Develop a formula for each month to compute her bowling average for June through September.

Once the spreadsheet is filled in, Sort the players ascending by Yearly Salary

STEP 3:

Continue to expand the spreadsheet by entering the following information after the column labeled “Sept” as demonstrated:

Player / May / June / July / Aug / Sept / Year Salary / Season Avg.

At the bottom of the spreadsheet below the players in column A add the following text description “Team Average”

Develop functions for the following and put in its proper and most logical place:

Develop a function to compute the Team’s Average salary for the year

Develop a function to compute the Team’s bowling average for each month

Develop a function to compute player’s season bowling average

Step 4

Add a new text description towards the bottom of your spreadsheet that reads “Owners Bonus”

1.  The League will give team owners a bonus if his or her team has a season bowling average more than .275. The bonus for a team bowling average more than .275 is 10% of the team’s average salary. If the team has a bowling average of .275 or less the Owner’s bonus will be only 5% of the 2006 average salary. Develop a function in a cell location next to a cell labeled “Owners Bonus:” that will display the amount of the bonus. Since we do not have Team’s average salary figured out, you must include this function in your conditional statement to get full credit.

2.  Add a new column labeled “2007 Status” after ‘Season Avg’ column. Each year the players’ contract states that their bowling average will determine if the player is given a raise of 10.00% 12.00% or 15.00% of their yearly salary. If a bowler averages .290 or more for the current season he or she will be get 15.00% increase for 2007. If the player bats .250 or below for the season he or she will get a 10.00% increase for the 2007 season, otherwise he or she will get a 12.00% increase for the 2007 season. Develop a formula that willcan be auto filled which will tell us how much money the bowler will receive in total for the 2007 Season.

Step5:

Create a cluster column chart that will appear in a separate worksheet. The title of the chart should read “Alley CATS Bowling Avg Player/Month”

Have the X axis show the months, y axis the monthly Bowling Average and the columns should represent each player. Label each axis. Make sure everything is labeled accordingly.

Name the tab on the worksheet, “Alley Cats Bowling Avg Chart”

Step6: ( DO NOT PRINT AT THIS TIME_ THIS IS FOR SHOW ONLY)

(3 printouts will be turned in from this section)

1.  Print out in normal view, landscape, 1:1, gridlines and row and column headings must be displayed. Make sure it prints on one page.

2.  Print out in “formula view”, in landscape, 1:1, gridlines and row and column headings must be displayed. Make sure it prints on one page. Make sure the entire formulas can be seen in each cell location. . If your formulas can be seen, you will have points deducted.

3.  Print out the Batting Avg Chart on 1 page landscape view.