HOMEWORK 2 EXCEL Project
ewazx31j
584801.pdf
© PENN FOSTER, INC. 2016
Graded Project
Excel 2016
© PENN FOSTER, INC. 2016 PAGE 1EXCEL 2016 Graded Project
INTRODUCTION 2
INSTRUCTIONS 2
SCORING GUIDELINES 6
SUBMITTING YOUR PROJECT 8
INTRODUCTION
CONTENTS
© PENN FOSTER, INC. 2016 PAGE 2EXCEL 2016 Graded Project
GRADED PROJECT
EXCEL 2016
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 1, sizing columns as necessary, and then save your file, naming it School Supplies.
Figure 1
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.
© PENN FOSTER, INC. 2016 PAGE 3EXCEL 2016 Graded Project
Figure 2
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.
6. Format the Cost/Unit values as Currency with 2 decimal places. Your spreadsheet should look like Figure 2.
© PENN FOSTER, INC. 2016 PAGE 4EXCEL 2016 Graded Project
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 3.
(Continued)
Figure 3
Figure 4
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 4.
2. Insert a 3-D Clustered Column chart.
3. Change the chart title to read “Cost of Supplies.”
© PENN FOSTER, INC. 2016 PAGE 5EXCEL 2016 Graded Project
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 5.
Figure 5
5. Put the following information into cell A41:
OO Your name
OO Your email address
OO Your student number
OO Course name and number
OO Project number (584801)
6. Save and close the workbook.
© PENN FOSTER, INC. 2016 PAGE 6EXCEL 2016 Graded Project
SCORING GUIDELINES
RUBRIC
Skill/ Grading Criteria
Exemplary (4)
Proficient (3)
Fair (2)
Poor (1)
Not Evident
(0)
Enter data
The correct data has been entered into the appropri- ate cells.
Most of the correct data has been entered into the appropri- ate 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 appro- priate labels have been formatted as instructed.
Some of the appropriate labels have been for- matted as instructed.
Appropriate labels have some format- ting but may not be as instructed.
Some labels have been formatted.
No attempt to format labels has been made.
Format values as Currency
The appro- priate values have been formatted as Currency with 2 decimal places.
The appro- priate values have been formatted as Currency without 2 deci- mal places.
The appro- priate values display a $ but have not been formatted as Currency.
The wrong values have been for- matted as Currency.
No attempt has been made to format values as Currency.
Format values as
Percentage
The appro- priate values have been formatted as Percentage with 0 decimal places.
The appro- priate values have been formatted as Percentage displaying a decimal portion.
The appro- priate 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.
(Continued)
© PENN FOSTER, INC. 2016 PAGE 7EXCEL 2016 Graded Project
Use formulas
with relative cell references
All Cost values have been cal- culated 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 calcu- lated using a SUM function.
N/A Grand Total values have been calcu- lated 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 calcu- lated 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 alpha- betical order or by something other than supply name.
N/A Only the Supply data in column A has been sorted, making the spread- sheet data inaccurate.
No attempt has been made to sort the data.
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.
© PENN FOSTER, INC. 2016 PAGE 8EXCEL 2016 Graded Project
SUBMISSION CHECKLIST
Before submitting your project, make sure you’ve correctly completed the following:
q Create, save, and name an Excel file.
q Enter data and labels.
q Format labels with indicated styles.
q Merge and center labels.
q Format data with Currency and Percentage formats to the correct number of decimal places.
q Size columns appropriately.
q Use formulas that include a function where applicable.
q Use relative and absolute cell references in formulas.
q Sort data.
q Create a chart from nonadjacent data.
q Edit a chart.
SUBMITTING YOUR PROJECT
Each project is individually graded and therefore could take approximately 5–7 business days to grade.
Follow this procedure to submit your assignment online:
1. Make sure the following information is in cell A41:
OO Your name
OO Your email address
OO Your student number
OO Course name and number
OO Project number (584801)
2. Go to http://www.pennfoster.edu and log in to your student portal.
3. On your student portal, click on Take Exam.
4. In the box provided, enter the examination number. The number for this exam is 584801.
© PENN FOSTER, INC. 2016 PAGE 9EXCEL 2016 Graded Project
5. Click on Submit.
6. On the next screen, enter your email address. (Note: This information is required for online submission.)
7. If you wish to tell your instructor anything specific regarding this assignment, enter it in the Comments.
8. Attach your file or files as follows:
a. Click on the first Browse box.
b. Locate the file you wish to attach.
c. Double-click on the file.
d. To attach any additional files, click on the next Browse box and repeat steps b and c. Repeat until all files are uploaded.
9. Click on Submit.
Be sure to keep a backup copy of any files you submit to the school!
