High School Computer Science – Week 2.6

Microsoft Excel – F604 Spreadsheet PowerPoint

Letter of Application Assignment

Documents -F604 Spreadsheet PowerPoint Online

F604-02 Spreadsheet Charting Vocabulary

Homework -F604-04 Create Column & Pie Charts

F604-05 Create Spreadsheet & Chart

F604-07 Expenses Spreadsheet & Chart

Homework Online -Excel Basics Quiz

Handout F604-02

Spreadsheet Charting Vocabulary

Chart — visual display of data in a spreadsheet, also know as a graph.

Column chart — chart that shows how values change over a period of time; most used chart type. Rectangular shapes make comparisons of data values easy.

Data marker — object that represents the individual values; marker can be a bar, column, etc.

Data point — single value or piece of data from the data series.

Data series — a collection of related values from the worksheet; one row/column on the worksheet.

Embedded chart — chart that appears on the same sheet as the worksheet data.

Gridline — horizontal or vertical line that extends across the plot area of the chart to make it easier to read/understand the values.

Legend — the object that explains the symbols, colors, or patterns used to differentiate the series in the chart.

Line chart — chart that shows a trend in data over a period of time.

Pie chart — chart that shows only one data series and is used to compare sizes of each part to the whole; percentage.

Plot area — the rectangular are bounded by the category and values axes.

Tick mark — small line or maker on the X-axis and Y-axis to help in reading the values.

X-axis —category axis that describes what is shown in the chart.

XY chart — chart that is also known as a scatter chart and used to show relationships between multiple values.

Y-axis — the value axis that shows the range of values (or scale) of the chart.

Activity F604-04

Create Column and Pie Charts

Step 1: Create a spreadsheet by entering the data showing auto sales figures for the month of March, 2005.

Auto Sales Report
Auto Makers / March
General Motors / 417,281
Ford Motor / 285,659
Chrysler Group / 212,978

Step 2: Select the spreadsheet data appropriately to create an embedded column chart. Title the chart “March Sales in 2005.” The category axis should be labeled “Major Domestic Automotive Corporations,” and the value axis should indicate “Automobiles Sold.” Use the format options to change the default color of the data series to redand plot area to yellow. Add the value labels to the tops of the data series.

Step 3: Copy the chart to another area of the spreadsheet. Change the chart type to a pie chart. Show the percent data labels for each auto maker. Explode the data series with the smallest percent of auto sales.

Step 4: Save the activity as MarchSales. Email to Instructor.

Activity F604-05

Create a Spreadsheet and Chart

Step 1: Create a chart using the information below. Follow the seven steps below to complete the spreadsheet for the company.

Hot Dog Express
Cafeteria Lunch Wagon
Food Item / Package / Servings Per Pkg / Cost / Item Cost / Markup / Retail Cost / Percent of Cost
Weiners / 48 count / 48 / 5.95
Buns / 24 count / 24 / 3.76
Cheese, Shredded / 3 lb. / 48 / 9.95
Mustard / 2 lb. / 32 / 1.98
Mayonnaise / 1 qt. Bottle / 64 / 3.29
Pickle Relish / 1 qt. Bottle / 32 / 2.15
Catsup / 2 lb. / 24 / 4.98
Onions / 3 lb. / 60 / 1.98
Chili / 5 lb. / 66 / 8.95
Hot Dog Price / 0.00 / 0.0%
Sales Tax 7% / 0.00
Hot Dog Total / $0.00

1. Insert the appropriate formula to calculate the individual item cost.

2. Calculate markup (additional money to ensure a profit and to pay expenses of having the hot dog cart) at 200% the individual item's cost.

3. Total the item cost and markup to calculate the retail cost of the individual food item to the customer.

4. Use =sum function to total food items' retail cost.

5. Calculate sales Tax at 7%.

6. Find the total price for hot dogs sold on the cafeteria wagon.

7. For each food item calculate the percent of total hot dog price (excluding tax). Remember: To get percent of a whole you must have a total. You should also remember about absolute cell reference of the total when filling down the formula.

Step 2: From the completed spreadsheet, create a pie chart showing the percent of total cost for each of the food items. Appropriately title the chart. Include category and percent data labels. “Pull” the data labels away from the pie chart so a “callout” is formed (line drawn from the piece of the chart to the data label and percent).

Step 3: Save as Hotdog and Email to instructor

Handout F604-07

Expenses Spreadsheet and Chart

Directions: Using your spreadsheet program enter the expense data shown below. Then complete the spreadsheet and create a pie chart.

  1. Open the spreadsheet program.
  2. Enter the following expense data in Columns A and B.

Rent / 650
Automobile Payment / 295
Automobile Insurance / 108
Automobile Gas / 95
Telephone / 21
Cell Phone / 48
ISP Provider / 40
Electricity / 83
Gas / 49
Entertainment / 150
Food / 300
Savings / 150
  1. Using the sort functions, sort Column B in a descending sort so that the expense data will be organized from the largest expenses down to the smallest expenses.
  2. Create a pie chart showing the allocation of expenses.
  3. Save the file as expenseschart and Email to instructor.