To learn to use formulas with anchors and graphs in Microsoft Excel.

• Use Anchors in Formulas
• Create Pie, Line, Bar, and Column Charts
• Format Chart Elements

NOTE: This lab builds on the previous one, so be sure you have completed that one before beginning this one.

Make sure that money amounts are always formatted as Currency (ie. with dollar signs, and with two decimal places if cents are involved).

Be sure to use formulas with cell addresses everywhere calculations are done. Don't just type in the answers as plain numbers, even if you can do them in your head or on a calculator. The purpose of this assignment is to give you practice using formulas and cell references.

For all the charts, make sure the chart title is appropriate and complete. The X and Y axis labels should indicate what is on the axis, including units where needed. Adjust the chart elements so that everything is displayed clearly, neatly, and proportionately. For examples of what I expect for this, please refer to Crunch the Numbers Part 2. Be sure to save frequently!

Be sure to save frequently!

Your results should look similar to Extra Crunchy.

Sheet 1: Pay Me Now, Pay Me Later

1. Make a copy of your Excel document from the previous lab, and rename the new copy using the normal naming pattern:
   John Smith Lab 6 Extra.

2. Open the new document in Excel and go to the first sheet, Pay Me Now.

3. Create a Bar chart to compare the average salaries. The job titles should be on the left had side with average salary on the bottom. Each job should have a horizontal line showing the base pay for the job.

4. On the bar chart, include the average salary amounts to the right of each bar.

Sheet 2: Market Watch

1. In cell B6 enter a fictitious amount that you fictitiously invested in your company on the first day you have listed in your data, many years ago.

2. In cell B7 enter a formula to calculate how many shares that would have been. The formula is the cell containing the total you invested divided by the cell containing the closing price of the earliest day of your data.

3. In cell C10 enter a formula to calculate how much your investment is worth on the last day of your data (ie. last week). The formula is the cell containing the number of shares you own times the cell containing the closing price for that day.

4. Fill down from cell C10 for the remaining dates in your data.

5. Beside the labels, enter formulas for the Minimum, Maximum, and Average of the values in column E.

6. Create a Line chart to show how the value of your investment changed over the years. The dates should appear on the bottom and value should appear on the left.

Sheet 3: How Hot Can You Get

1. In cell E6 enter a formula to calculate the temperature difference (High minus Low) for your first city.

2. Fill down from cell E6 for the rest of the Difference column.

3. In cell H6 enter a formula to calculate the daylight hours for your first city. Since we want hours and subtracting would give us days, we need to multiply the difference by 24. This means you need to enter a formula like this: Sunset minus Sunrise (in parenthesis) times 24. If the answer looks strange, the next step will fix that.

4. Format cell H6 as a Number with 2 decimal places.

5. Fill down from cell H6 for the rest of the Daylight Hours column.

6. Create a Column chart comparing the Daylight Hours for the cities you chose. The cities should appear on the bottom and hours of daylight should appear on the left.

7. Create a Column chart comparing the High, Low, and Difference temperatures for your cities. The category (High, Low, Difference) should appear on the bottom and the temperature should appear on the left. Each city is a series, containing a High, Low, and Difference. Each city (series) is represented by a different color and listed in the legend. If you can't make the chart work all at once, make a chart with just one city, and add each city as new series under Chart Data, one at a time. Be sure to look at the sample chart for this one.

Sheet 4: What People Believe

1. Sort the religions by adherents from greatest first to least last. To do this, first highlight the religion numbers (not including the title), and without letting go of the mouse, move the mouse left to select the religion names. (This way, both columns end up selected, but the numbers were selected first. The computer will sort by the numbers, not by names.) Click the Z-A Sort button (the greatest number will be first on the list). Be careful not to sort only one column, or the numbers will get scrambled!

2. Create a large Pie chart showing the adherents for each religion. Make sure the chart shows the religion and the percent for that religion, and that they display clearly and without overlapping. Do not include the total on the chart. Do not show a legend on the side.

Sheet 5: Countries of the World, Unite

1. For each of the two sets of demographics statistics, insert formulas in the two People columns showing how many people pertain to that group. The formula is the total number of people multiplied by the percent for that group. You should be able to enter one formula for the first cell of each of the two sets and fill down for the remaining groups (ie. use anchors in the formulas).

2. For each of the two sets create a Pie chart showing the groups making up the set (ie. the different religions or ethnic groups). Make sure both charts show the group name, the number of people, and the percent for each group.

Sheet 6: Gas Guzzlers

1. In cell D5 enter a formula to calculate the average oil consumption per person in the United States (ie. divide the cell with the number of barrels by the number of people).

2. Format cell D5 to be a Number with 3 decimal places.

3. Fill down from cell D5 for the rest of the countries.

4. Create a Column chart comparing the total oil consumption for the countries you chose. The countries should appear on the bottom and consumption in bbls/day on the left.

5. Create a Column chart comparing the average personal oil consumption for the countries you chose. The countries should appear on the bottom and consumption in bbls/day on the left.

Sheet 7: Widgets, Inc. Payroll

In this section you will calculate payroll data for a fictitious company.

1. Create a new sheet in your document with a tab named Widgets and move it so it is the seventh and final sheet.

2. Type in labels as shown below.

   

3. Open Widgets Employees in Excel. Copy the names, pay rates, and hours. Paste them into the Widgets sheet at cell A7 of your lab assignment document.

4. In the row after the last employee enter a right-side label Total: in column B.

5. In the same row enter a formula in column C to calculate the total number of hours that the employees worked all together.

6. In the cell for Gross Pay for the first employee, enter a formula which calculates their gross pay, which is the pay rate for that employee multiplied by the hours he or she worked.

7. Fill down from the Gross Pay cell for the first employee for the rest of the employees.

8. In the row containing the total hours worked, enter a formula after the last Gross Pay to calculate the total Gross Pay for all the employees together.

9. In cell B3 enter a Social Security deduction rate of 3.5%.

10. In cell E3 enter an Income Tax rate of 12.25%.

11. In cell E7, enter a formula for the Social Security deduction for the first employee. (The Social Security deduction is calculated by multiplying the Gross Pay by the Social Security deduction rate).

12. Fill down from this formula for the remaining employees.

13. In cell F7, enter a formula for the Taxes deduction for the first employee. (The Taxes deduction is calculated by multiplying the Gross Pay by the Tax rate).

14. Fill down from this formula for the remaining employees.

15. In cell G7, enter a formula for Net Pay for the first employee. (Net Pay is calculated by taking the Gross Pay and subtracting both the Social Security deduction and the Taxes deduction from it).

16. Fill down from this formula for the remaining employees.

17. On the row for Totals after the last employee, enter a formula to calculate the total Gross Pay under the gross pay of the last employee.

18. Fill right from this formula to get totals for Social Security, Taxes, and Net Pay.

19. Create a Bar chart compare the net pay for all the employees. The employee names should appear on the left and the net pay on the bottom. Do not show a legend. Be sure the name of each employee is visible.

20. Save your document and close Excel.

21. Save your presentation (be sure it is named according to the usual pattern: John Smith Lab 6 Extra)and use the Drop Box to submit it.

Grading

Pay Me: bar chart includes correct data	1 pt
Pay Me: base pay amounts displayed beside the bars	1 pt
Pay Me: bar chart formatted correctly, including titles	2 pts
Market Watch: investment amount in B6 and formula for number of shares in B7	1 pt
Market Watch: value formula correct in C10, and filled down for each date	1 pt
Market Watch: minimum, maximum, and average formulas correct in E5-E7	1 pt
Market Watch: line chart includes correct data	1 pt
Market Watch: line chart formatted correctly, including titles	2 pts
How Hot: temperature difference formulas correct and formatted as numbers	1 pt
How Hot: daylight hours formulas correct and formatted as numbers	1 pt
How Hot: daylight hours chart includes correct data and formatted correctly with titles	1 pt
How Hot: actual temp chart has temp on left and high, low, diff on bottom	1 pt
How Hot: actual temp chart has each city as a different series	2 pts
How Hot: actual temp chart formatted correctly, including legend and titles	2 pts
Believe: religions properly sorted by adherents, greatest to least	1 pt
Believe: pie chart data includes correct data, formatted correctly with titles	1 pt
Believe: pie chart proportions correct and all lables are readable	2 pts
Country: first demographic formulas correct (with anchors), including totals	1 pt
Country: second demographic formulas correct (with anchors), including totals	1 pt
Country: first demographic chart data and formatting correct	1 pt
Country: second demographic chart data and formatting correct	1 pt
Guzzlers: average personal consumption formulas correct, with 3 decimals	1 pt
Guzzlers: total consumption chart data and formatting correct	1 pt
Guzzlers: average consumption chart data and formatting correct	1 pt
Widgets: all labels correct, including soc. sec. and tax rates, and money as currency	1 pt
Widgets: gross pay formulas correct	1 pt
Widgets: social security formulas correct (with anchors)	1 pt
Widgets: taxes formulas correct (with anchors)	1 pt
Widgets: net pay formulas correct (with anchors)	1 pt
Widgets: total formulas correct	1 pt
Widgets: bar chart data and formatting correct	1 pt
Widgets: all employee names are visible on bar chart	1 pt
sheet tabs named correctly, in correct order and spreadsheet name follows pattern	1 pt
spreadsheet name follows normal pattern	2 pts
Total:	40 pts