© PENN FOSTER, INC. 2016
Graded Project
Advanced PC
Applications
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 1
Graded Project
INTRODUCTION 2
RETAILERS DATABASE 2
HOOK RESULTS SPREADSHEET 6
INVITATION LETTER 7
EVENT PRESENTATION 9
SCORING GUIDELINES 13
SUBMISSION CHECKLIST 16
SUBMITTING YOUR PROJECT 17
INTRODUCTION
CONTENTS
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 2
Graded Project
GRADED PROJECT
ADVANCED PC APPLICATIONS
INTRODUCTION
This project uses the skills you’ve acquired for Word, Excel, PowerPoint, and Access to
prepare for a promotional event to introduce a new line of fishing hooks to local retailers.
You’ll assume the role of company president for a small fishing tackle company that is
just getting started. For this project, you must complete the following steps:
1. Create a database of retailers for prospective shops that might carry your hooks.
2. Create a spreadsheet with statistics and a chart.
3. Complete a mail merge letter inviting sales reps to a fishing lodge to try out
your product.
4. Produce a presentation to give at the fishing lodge.
RETAILERS DATABASE
Follow the steps below to create the Retailers database.
1. Start Access and create a new Blank database named “Retailers.”
2. Name the new table “Retailers” and then display it in Design view.
3. Edit the ID field to be “Retailer ID” and verify that it is data type AutoNumber and
that it has been selected as the Primary key.
4. In Design view, add the remaining fields and corresponding data types:
Field Name Data Type
Store Name Short Text
Address Short Text
City Short Text
State Short Text
Zip Short Text
5. Save and then close the Retailers table.
6. Create a new table named “Sales Reps” and then display it in Design view.
7. Edit the ID field to be Sales Rep ID and verify that it is data type AutoNumber and
that it has been selected as the Primary key.
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 3
Graded Project
8. Add a field named “Retailer ID” and then select the Lookup Wizard as
the Data Type.
a. Use the Wizard to select Retailers as the table for the Lookup field.
b. Click Retailer ID as the field with the values to choose from.
c. Sort the values in Ascending order.
d. Click Finish.
9. In Design view, add the remaining Sales Reps fields and corresponding
data types:
Field Name Data Type
First Name Short Text
Last Name Short Text
Job Title Short Text
10. Save and then close the Sales Reps table.
11. Create a Retailers form that looks similar to Figure 1.
Figure 1
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 4
Graded Project
13. Use the Retailers form to populate the Retailers table with the records listed below.
12. Create a Sales Reps form that looks similar to Figure 2.
Figure 2
Retailers Records
Retailer ID 1
Store Name Fishing World
Address 160 Trout Circle
City Any Town
State PA
Zip 16754
Retailer ID 2
Store Name Everything Bait Shop
Address 1336 Ocean Parkway
City Any Town
State PA
Zip 16755
Retailer ID 3
Store Name Major Sporting Goods
Address 14998 Atlantic Ave.
City Any Town
State PA
Zip 16759
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 5
Graded Project
14. Use the Sales Reps form to populate the Sales Reps table with the records listed
below. Note that the Retailer ID is a lookup field and you will be able to select the
value from a list.
Sales Reps Records
Sales Rep ID 1
Retailer ID 2
First Name Marty
Last Name Hu
Job Title Owner
Sales Rep ID 2
Retailer ID 1
First Name Chris
Last Name Tryniski
Job Title Fishing Dept. Rep
Sales Rep ID 3
Retailer ID 1
First Name Karm
Last Name Howard
Job Title Outdoor Goods Manager
Sales Rep ID 4
Retailer ID 3
First Name Liz
Last Name Bennett
Job Title Tackle Sales Rep
Sales Rep ID 5
Retailer ID 3
First Name Turner
Last Name Ward
Job Title Store Manager
15. Format Table Datasheet view for the Retailers and Sales Reps table, adjusting
column widths as appropriate.
16. Create a query in Design view that displays complete contact information in
alphabetical order by store name, as shown in Figure 3. The tables should already
display a relationship because of the Lookup field. If not, join the tables
appropriately to correctly run the query.
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 6
Graded Project
17. Run the query and save it as Contacts.
18. Close the query and any open tables and forms.
19. Close the Retailers database.
HOOK RESULTS SPREADSHEET
Follow the steps below to create the Hook Results spreadsheet.
1. Start Excel and create a new Blank workbook.
2. Enter data as shown in Figure 4.
a. Merge and center titles as shown.
b. Format cell titles and labels as bold where indicated.
c. Display borders around all the cells with data.
d. Change the spreadsheet orientation to landscape to accommodate the
wide columns on one sheet of paper.
3. In column F, enter formulas that use a function to calculate % Caught with Table (fish caught with Table hook divided by the total number of fish caught).
4. Format the % Caught with Table values as a percent with one decimal place.
Figure 4
Figure 3
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 7
Graded Project
5. Insert a Column Chart of type 2-D Clustered Column that charts data in cells A4 through E6.
6. Change the title to Fish Caught by Hook Brand.
7. Change the Chart Style to Style 11, so that your chart looks similar to Figure 5.
Figure 5
8. Be sure the chart is sized appropriately and positioned under the data.
9. Save the workbook naming it “Hook Results.”
INVITATION LETTER
Follow the steps below to create the Invitation Letter.
1. Start Word and create a new single-spaced (blank) document.
2. Type Table Trout Hooks and then press Enter.
3. Insert an online image of a trout (use trout as the search text) and then
press Enter.
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 8
Graded Project
4. Optional. You can remove the background color of your image, if needed, by
following these steps:
a. Select the image.
b. On the Picture Tools Format tab, click Color > Set Transparent Color.
c. Click on the background color of the selected object.
5. Size the image appropriately and then center align the paragraph containing the image.
6. Format the Table Trout Hooks text as Georgia 22 point. Your letter should look similar to Figure 6.
Figure 6
7. Start the mail merge process.
8. When you select recipients, use an existing list and select the Retailers.accdb database you created earlier and then select the Contacts query.
9. Type the text shown in Figure 7, inserting merge fields and leaving a blank paragraph where indicated. You will need to Match Fields when you insert the AddressBlock field code so that you can match the Company to the
Store Name field.
Figure 7
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 9
Graded Project
10. Be sure that you have formatted the indicated text as bold and used your name in the closing. If the email address isn’t automatically formatted as a hyperlink, then click Insert > Hyperlink to format it.
11. In the second blank paragraph after “. . . two recent tournaments:” paste a copy of the chart from the Hook Results spreadsheet. Size the chart appropriately, so that it looks similar to Figure 8.
Figure 8
12. Save the document naming it “Lodge Lunch”.
13. Use the appropriate command to merge the field data and create a document containing the individual letters.
14. Scroll through the letters and be sure that the content requires only one page. If the chart is so big that your letter requires two pages, then close the merged document without saving it and resize the chart in the Lodge Lunch document before performing the merge again. Repeat this process until you produce letters that will print on one page each.
15. Save the merged document naming it “Merged Lodge Letter”.
EVENT PRESENTATION
Follow the steps below to create the Event Presentation.
1. Start PowerPoint and create a new Blank Presentation.
2. Change the Theme of the presentation to Wood Type or another appropriate style if you do not have Wood Type.
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 10
Graded Project
3. Verify slide 1 is Title Slide layout and then enter text so that your slide looks similar to Figure 9.
Figure 9
4. Add a new slide with a Content with Caption layout (slide 2) and then insert the Table Hook.jpg picture, a data file for this Graded Project. Add text and size the image so that slide 2 looks similar to Figure 10.
Figure 10
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 11
Graded Project
5. Add a new slide with a Title and Content layout (slide 3) and add text as shown in Figure 11.
Figure 11
6. Add a new slide with a Title and Content layout (slide 4) and add text as shown in
Figure 12.
Figure 12
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 12
Graded Project
7. Add a new slide with a Title and Content layout (slide 5). Edit the title and paste
the chart from the Hook Results spreadsheet so that the slide looks similar to Figure 13.
8. Add a new slide with a Title and Content layout (slide 6) and add content so that the slide looks similar to Figure 14. Refer to the Hook Results spreadsheet to get the figure for the first bullet (Hint: The average should be about 35%). Insert an online picture of the same trout (if possible) used in the Lodge Letter.
Figure 13
Figure 14
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 13
Graded Project
9. Apply the Wind transition to all the slides.
10. Apply the Wipe animation to each of the bullet items on slides 3, 4, and 6.
11. Save the presentation naming it “Sales Presentation” and then play the slide show from the beginning to check your presentation.
SCORING GUIDELINES
RUBRIC
SKILL/GRADING CRITERIA
EXEMPLARY (4)
PROFICIENT (3)
FAIR
(2)
POOR
(1)
NOT
EVIDENT (0)
Create a database
Correct field names and field types have been used to create a table and form.
Mostly correct field names and field types have been used to create a table and form.
Some correct field names and field types have been used to create a table and form.
An attempt has been made to
create a table and form.
No attempt has been made to
create a database with a table and form.
Populate a database
All the records have been entered with no errors.
Most of the records have been entered with no errors.
Some records have been entered and there are errors.
There are a few records but they may not contain the correct data.
No attempt has been made to populate the database.
Create a database query
The query
criteria include correct fields and sort, and the tables have a defined relationship.
The query
criteria include mostly correct fields and sort, and the tables have a defined relationship.
The query criteria include some fields without a sort, and the tables have a defined relationship.
A query has been created with only one table.
No attempt has been made to
create the query.
Create a spreadsheet
A spreadsheet with correct data, formatting, and formulas has been created.
A spreadsheet with mostly correct data, formatting, and formulas has been created.
A spreadsheet with some correct data, formatting, and formulas has been created.
A spreadsheet with formulas that do not use functions has been created.
No attempt to create a spreadsheet has been made.
(Continued)
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 14
Graded Project
Create a chart
A column chart of the correct type and style with the appropriate title has been created.
A column chart of the correct type with the appropriate title has been created.
A column chart with the appropriate title has been created.
A chart of any type has been created.
No attempt has been made to
create a chart.
Type
document text
The specified letter has been correctly typed with correct spacing,
formatting, and online image.
The specified letter has been typed with few mistakes.
The specified letter has been typed with many mistakes and does not
contain most of the specified formats.
The specified letter has been typed with numerous mistakes, incorrect spacing, and lacks an image.
No attempt to type the
letter has been made.
Insert merge fields
The letter contains all the correct merge fields in the correct positions.
The letter contains most of the correct merge fields in the correct positions.
The letter does not contain an AddressBlock merge file and other merge fields are missing.
The letter contains text in place of the merge fields.
No attempt has been made to place merge fields or typed placeholders at the designated positions.
Merge field
letters
Database merge fields have been merged to
create a
document with individual letters.
Database merge fields are being
previewed in the letter.
N/A
N/A
No attempt has been made to merge fields and create a document containing individual letters.
Create a presentation
A presentation in the specified theme with six slides in the correct
layouts has been created.
A presentation in the specified theme with most of the slides in the correct layout has been created.
A presentation in the specified theme with some of the slides in the correct layout has been created.
A presentation with less than six slides in varying layouts has been created.
No attempt has been made to create a presentation.
Insert an image from the computer
A data file image has been inserted, sized, and positioned.
A data file image has been inserted, sized, but without optimal positioning.
A data file image has been inserted without sizing or positioning.
An image other than that specified has been inserted.
No attempt to insert an image has been made.
(Continued)
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 15
Graded Project
Paste an Excel chart
The specified Excel chart has been pasted into slide 5 and appropriately sized and positioned.
The specified Excel chart has been pasted into slide 5 but not sized or positioned.
The specified Excel chart has been
recreated in the presentation by using the Insert Chart command.
The chart has been inserted as an Excel Worksheet Object and displays the entire worksheet.
No attempt has been made to add a chart to the presentation.
Insert an online
picture
An appropriate clip art image has been inserted on slide 6 and appropriately sized and positioned.
An appropriate clip art image has been inserted on slide 6 but may not be sized or positioned.
N/A
An image unrelated to the topic has been inserted.
No attempt has been made to insert a clip art image.
Apply a Transition
The Wind
transition has been applied to all slides.
The Wind
transition has been applied to more than one slide.
The Wind transition has been applied to only one slide.
The wrong transition has been applied to the presentation.
No attempt has been made to apply a transition to any of the slides.
Apply
Animations
The Wipe animation has been applied to each bullet item separately on slides 3, 4, and 6.
The Wipe animation has been applied to all the bullet items together on slides 3, 4, and 6.
A different animation has been applied to each bullet item separately on at least one slide.
A different animation has been applied to all the bullet items together on at least one slide.
No attempt has been made to apply an animation.
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 16
Graded Project
SUBMISSION CHECKLIST
q Create a database with multiple tables and a Lookup field table.
q Create forms corresponding to database tables.
q Populate a database table by using forms.
q Create a query based on two related tables.
q Specify a sort in a database select query.
q Create a spreadsheet and apply formatting, including merge and center, to cells.
q Use a function in a formula to calculate a value.
q Create a Column chart of type 2-D Clustered Column.
q Edit a chart title.
q Change a chart style.
q Type and format text in a Word document.
q Insert, size, and position an online clip art image.
q Create a mail merge document using an existing database query as a data source.
q Insert merge fields, including an AddressBlock, in a Word letter.
q Match fields in an AddressBlock merge field.
q Paste an Excel chart in a Word document.
q Merge field data with actual data to create individual letters.
q Create a PowerPoint presentation.
q Apply a theme to a presentation.
q Insert an existing image into a slide and then size and position it.
q Paste an Excel chart into a slide.
q Refer to a spreadsheet for content to add to a slide.
q Insert an online clip art image into a slide and then size and position it.
q Apply a transition.
q Apply animations separately to bullet items on a slide.
© PENN FOSTER, INC. 2016 ADVANCED PC APPLICATIONS PAGE 17
Graded Project
SUBMITTING YOUR PROJECT
Each project is individually graded and therefore could take approximately 5–7 business days to grade.
Make sure the following information is in the heading of each document:
O O
Your name
O O
Your email address
O O
Your student number
O O
Course name and number
O O
Project number (584808)
Follow this procedure to submit your assignment online:
1. Go to http://www.pennfoster.edu and log in to your student portal.
2. On your student portal, click on Take Exam.
3. In the box provided, enter the examination number. The number for this
exam is 584808.
4. Click on Submit.
5. On the next screen, enter your email address. (Note: This information is required for online submission.)
6. If you wish to tell your instructor anything specific regarding this assignment, enter it in the Comments.
7. 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 the additional files, click on the next Browse box and repeat steps b and c. Repeat until all files are uploaded.
8. Click on Submit.
Be sure to keep a backup copy of any files you submit to the school!