Computer Applications : Microsoft Excel 2016 Graded Project
Lesson 4 Overview
In this lesson, you'll create a spreadsheet that tracks the cost of
elementary school supplies distributed to teachers over a two-month
period in order to illustrate correct usage of Microsoft Excel 2016.
4.1 Design a spreadsheet that tracks the cost of elementary school supplies distributed to teachers over a two-month period Excel 2016
READING ASSIGNMENT
Your project must be submitted as an Excel workbook (.xlsx, .xls). Your project will be individually graded by your instructor and therefore may take up to five to seven days to grade. Be sure that each of your files contains the following information:
Your name Your student ID number The exam number Your email address
Note: If you have more than 10 attachments, you’ll need to collect all of your files into a compressed folder. To do this, follow these instructions based on
Page 1Copyright Penn Foster, Inc. 2019 Course Version: 1
your operating system:
Windows: Select the files you want to compress, right-click and select Send to. . . and then Compressed (zipped) folder Mac: Select the files you want to compress, control-click and select Compress
To submit your graded project, follow these steps:
Log in to your student portal. Click on Take Exam next to the lesson you’re working on. Find the exam number for your project at the top of the Project Upload page. Follow the instructions provided to complete your exam.
Be sure to keep a backup copy of any files you submit to the school!
Introduction
This project requires you to develop a spreadsheet that tracks the cost
of elementary school supplies distributed to teachers over a two-
month period. You’ll assume the role of Office Manager, who has
multiple responsibilities, including distributing and maintaining
classroom supplies. For this project, you must prepare an Excel
spreadsheet and chart that will be sent to the school District Manager.
Instructions
Create a Spreadsheet
1. Start Excel and create a new workbook.
2. Enter data as shown in Figure below, sizing columns as
necessary, and then save your file, naming it School Supplies.
Page 2Copyright Penn Foster, Inc. 2019 Course Version: 1
An image of a new excel workbook with data entered and named
School Supplies.
3. Merge and center cells A1 through H1. Format the school name
as Title style.
4. Merge and center cells A2 through H2. Format the label as
Heading 1 style.
5. Bold the labels in row 4 and right align the labels above numeric
values.
6. Format the Cost/Unit values as Currency with 2 decimal places.
Your spreadsheet should look like Figure below.
An image of the School Supplies excel workbook with formatted data.
Page 3Copyright Penn Foster, Inc. 2019 Course Version: 1
7. In column D, enter appropriate formulas to calculate the
September costs for each school supply. Your formulas must use
cell references. Format the calculated values as Currency with 2
decimal places.
8. In cell A16, enter the label “Grand Total” and then format it as
bold.
9. In cell D16, enter a formula that uses a function to calculate the
total of the column.
10. In column E, enter formulas that calculate the percentage of the
grand total for each type of school supply (total cost of each item
divided by the grand total). Your formulas must use appropriate
absolute cell references. Format the calculated values as
Percentage with 0 decimal places.
11. In cell E16, enter a formula that uses a function to calculate the
total of the column (it should total 100%).
12. Repeat the process you used in steps 7–11 to calculate values
for October.
Page 4Copyright Penn Foster, Inc. 2019 Course Version: 1
13. Sort the school supplies alphabetically. (Be sure to select cells
A5:H14 to sort the entire row of data.) Your spreadsheet should
look similar to Figure below.
An image of the School Supplies excel workbook with the data sorted
in alphabetical order.
14. Save the modified spreadsheet.
Create a Chart
1. Create a chart by first selecting the cell ranges for the chart. Drag
from cell A4 to A14 to select the range and then press and hold
the Ctrl key while you drag from cell D4 to D14. Continue to press
and hold the Ctrl key while dragging from cell G4 to G14. Your
selected cells should look like Figure below.
An image of selected cell ranges in the School Supplies excel
workbook.
Page 5Copyright Penn Foster, Inc. 2019 Course Version: 1
2. Insert a 3-D Clustered Column chart.
3. Change the chart title to read “Cost of Supplies.”
4. Position the chart with the upper-left corner in cell A18 and then
size the chart until it extends to column H, as shown in Figure
below.
An image of a chart made with the selected cell ranges in the School
Supplies excel workbook.
Page 6Copyright Penn Foster, Inc. 2019 Course Version: 1
5. Put the following information into cell A41:
Your name
Your email address
Your student number
Course name and number
Project number (584801)
6. Save and close the workbook.
Scoring Guidelines
Rubric
Page 7Copyright Penn Foster, Inc. 2019 Course Version: 1
Skill/ Grading Criteria
Exemplary (4)
Proficient (3)
Fair (2)
Poor (1)
Not Evident (0)
Enter data
The correct data has been entered into the appropriate cells.
Most of the correct data has been entered into the appropriate cells.
Some of the correct data has been entered into the appropriate cells.
Minimal correct data has been entered anywhere.
No attempt has been made to enter data into the spreadsheet.
Size columns
All columns are sized to appropriately display labels and values.
Some columns are sized to appropriately display labels and values.
Few columns are sized to appropriately display labels and values.
Only one column is sized to appropriately display labels and values.
No attempt has been made to change column widths.
Apply an Excel style
All cells are in the indicated style.
All cells have a style applied, but they are in the wrong style.
One cell has an applied style, in the indicated style.
One cell has an applied style, but it is the wrong style.
No attempt has been made to apply Excel styles.
Merge and center cells
Labels in rows 1 and 2 have been merged and centered above the appropriate cells.
The label in one row has been merged and centered.
Labels in either row 1 or 2 have been merged without centering.
An attempt has been made to center labels above the data.
No attempt to merge cells or center labels has been made.
Format labels
The appropriate labels have been formatted as instructed.
Some of the appropriate labels have been formatted as instructed.
Appropriate labels have some formatting but may not be as instructed.
Some labels have been formatted.
No attempt to format labels has been made.
Format values as Currency
The appropriate values have been formatted as Currency with 2 decimal places.
The appropriate values have been formatted as Currency without 2 decimal places.
The appropriate values display a $ but have not been formatted as Currency.
The wrong values have been formatted as Currency.
No attempt has been made to format values as Currency.
Page 8Copyright Penn Foster, Inc. 2019 Course Version: 1
Format values as
Percentage
The appropriate values have been formatted as Percentage with 0 decimal places.
The appropriate values have been formatted as Percentage displaying a decimal portion.
The appropriate values display a % but have not been formatted as Percentage.
The wrong values have been formatted as Percentage.
No attempt has been made to format values as Percentage.
Use formulas
with relative cell references
All Cost values have been calculated using 2 relative cell references.
Cost values have been calculated using 1 relative cell reference.
N/A N/A Cost values do not use any cell references.
Use formulas
with functions
All Grand Total values have been calculated using a SUM function.
N/A
Grand Total values have been calculated using a SUM function but did not include all cell references.
N/A
Grand Total values have not been calculated with a SUM function.
Use formulas
with absolute
cell references
The % of Grand Total values have been calculated with a formula that includes an absolute cell reference.
N/A N/A N/A
The % of Grand Total values have not been calculated using absolute cell references.
Sort data
Supply data (rows 5 through 14) has been sorted alphabetically by supply name.
Supply data (rows 5 through 14) has been sorted in reverse alphabetical order or by something other than supply name.
N/A
Only the Supply data in column A has been sorted, making the spreadsheet data inaccurate.
No attempt has been made to sort the data.
Page 9Copyright Penn Foster, Inc. 2019 Course Version: 1
Create a chart
A 3-D Column chart has been created using the correct data.
A chart of any type has been created using the correct data.
A chart has been created using some of the correct data.
An attempt to create a chart has been made.
No attempt has been made to create a chart.
Edit a chart title
The chart title has been appropriately changed.
The chart title has been changed to unspecified text.
N/A
An attempt has been made to change the chart title.
No attempt has been made to change the chart title.
Move and size a chart
The chart has been moved and sized to the specified location and dimensions.
The chart has been moved to the specified location or sized, but not both.
N/A N/A
No attempt has been made to move or size the chart.
Submission Checklist
Before submitting your project, make sure you’ve correctly completed
the following:
Create, save, and name an Excel file.
Enter data and labels.
Format labels with indicated styles.
Merge and center labels.
Format data with Currency and Percentage formats to the correct
number of decimal places.
Size columns appropriately.
Use formulas that include a function where applicable.
Use relative and absolute cell references in formulas.
Sort data.
Create a chart from nonadjacent data.
Page 10Copyright Penn Foster, Inc. 2019 Course Version: 1
Edit a chart.
Be sure to keep a backup copy of any files you submit to the school!
Key Points
READING ASSIGNMENT
Key Points
You resize column widths by dragging with your mouse or by
entering a numeric value in the Column Width dialog box, which
is accessed from the Format, Column Width menu on the Home
Ribbon.
You align cell contents using the Align Left, Center, and Align
Right buttons on the Home Ribbon.
You use formulas to perform mathematical calculations on
numbers in your spreadsheet.
To select non-contiguous cells for inclusion in a chart, hold down
the Ctrl key while you drag.
You position and size charts by dragging the chart handles with
your mouse.
You can apply formatting directly to the components of a chart by
clicking on them.
Page 11Copyright Penn Foster, Inc. 2019 Course Version: 1
Lesson 4 Review
Self-Check
1. You created a budget in Excel. You'd like to quickly see how each
budget category compares to the others to make up the total. What is
the best way to accomplish this?
a. A pie chart.
b. Sorting the category totals low to high or high to low.
c. The @SUM function to automatically generate category totals.
d. A formula calculating category percentages.
2. You entered an Excel function to perform a mathematical
calculation on several columns of numbers in your spreadsheet. The
cell where the function resides displays #####. What is the reason for
this?
a. The format of the cell is not set to display a numeric value.
b. There is an error in the data the formula is pulling from the
columns.
c. There is an error in the formula that you entered.
d. The column width is too small to display the formula's resulting
value.
3. You have a spreadsheet with data in rows 4 through 10 in columns
B, C, E, and F. How would you select the data to create a chart?
a. Drag to select B4 through C10. Hold down the Shift key and drag
to select E4 through F10.
b. Click the Select All button in the upper-left corner of the
spreadsheet.
Page 12Copyright Penn Foster, Inc. 2019 Course Version: 1
c. Drag to select B4 through F10.
d. Drag to select B4 through C10. Hold down the Ctrl key and drag
to select E4 through F10.
4. The title of your worksheet is 2017 Budget. You've created a pie
chart to show the budget category percentages, but the chart title
reads a generic Chart Title. How do you change it to match the
worksheet title of 2017 Budget?
a. Delete and recreate your chart making sure you include a header
row with the title 2017 Budget in it.
b. Select the Chart Title text box and delete it. Insert the Excel
TEXT function and link it to a cell in the worksheet with the text 2017
Budget in it.
c. Click on and then select the text Chart Title in the chart and link
to the worksheet name.
d. Click on and then select the text Chart Title in the chart and type
2017 Budget.
5. A colleague gives you a spreadsheet containing the personnel
records of all department employees. Employees are added to a row
at the bottom of the spreadsheet as they are hired. As a result, you
have to scan the entire spreadsheet to find the information for
employees. What is the best way to resolve this problem?
a. Request that the colleague restructure the spreadsheet so that
employees are added in alphabetical order instead of by hire date.
b. Search for each employee's data you want to work with.
c. Apply a filter to display just the employee data you want to work
with.
Page 13Copyright Penn Foster, Inc. 2019 Course Version: 1
d. Apply a data sort to your copy of the spreadsheet, so that
employees are in alphabetical order by name.
Self-Check Answer Key
1. A pie chart.
Explanation: A pie chart is a graphical representation of data
where the entire circle equals the whole and the individual
sections are graphically represented as sections showing their
proportion of the whole.
Reference: Section 4.1
2. The column width is too small to display the formula's resulting
value.
Explanation: When Excel displays ##### in a cell, it means one
of two things:
1. The column is not wide enough to display the information in
the cell.
2. There is a negative date or time in the cell.
Reference: Section 4.1
3. Drag to select B4 through C10. Hold down the Ctrl key and drag
to select E4 through F10.
Explanation: To select non-contiguous cells in Excel, drag to
select your first range of cells. Then hold down the Ctrl key while
Page 14Copyright Penn Foster, Inc. 2019 Course Version: 1
you drag to select the additional cell ranges.
Reference: Section 4.1
4. Click on and then select the text Chart Title in the chart and type
2017 Budget.
Explanation: A text box contains the title of the chart. It is editable
just like any other text box.
Reference: Section 4.1
5. Apply a data sort to your copy of the spreadsheet, so that
employees are in alphabetical order by name.
Explanation: The Sort feature in Excel allows you to change the
view of your data, rearranging it into a specified order. For
example, text can be sorted alphabetically or reverse
alphabetically, and numbers can be sorted in ascending or
descending numeric order.
Reference: Section 4.1
Flash Cards
1. Term: Chart
Definition: Visual representation of data
2. Term: Formula
Definition: Instructions to perform mathematical calculations on
specified values in a spreadsheet
Page 15Copyright Penn Foster, Inc. 2019 Course Version: 1
3. Term: Function
Definition: A preset formula in Excel
Page 16Copyright Penn Foster, Inc. 2019 Course Version: 1
Lesson 4 Overview
Introduction
Instructions
Create a Spreadsheet
Create a Chart
Scoring Guidelines
Rubric
Submission Checklist
Key Points
Lesson 4 Review