Advanced PC Applications : Advanced PC Applications
Graded Project
Lesson 7 Overview
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’s
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.
7.1 Prepare for a promotional event to introduce a new product to local
retailers
Graded Project: Advanced PC Applications
READING ASSIGNMENT
Your project must be submitted as a Word document (.docx, .doc)*. Your project will be individually
graded by your instructor and therefore will take up to a few weeks to grade. Be sure that each of your
files contains the following information:
Your name
Your student ID number
The lesson number (584085)
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 your operating system:
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 1
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:
Go to http://www.pennfoster.edu (www.pennfoster.edu) .
Log in to your student portal.
Click on Take Exam next to the lesson you’re working on.
Follow the instructions provided to complete your exam.
Be sure to keep a backup copy of any files you submit to the school!
Retailers Database
Follow the steps below to create the Retailers database.
Click to download the files (lessons.pennfoster.com/pdf/584085_Practice_Files.zip) you’ll
need to complete this graded project.
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’s 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 display it in Design view.
7. Edit the ID field to be “Sales Rep ID” and verify that it’s data type AutoNumber and that
it has been selected as the primary key.
8. Add a field named “Retailer ID” and then select the Lookup Wizard as the Data Type.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 2
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 close the Sales Reps table.
11. Create a Retailers form that includes the following information:
Retailer ID
Store Name
Address
City
State
Zip
12. The form should look similar to the figure below.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 3
[An image of the Retailers form]
13. Create a Sales Reps form that includes the following information:
Sales Rep ID
Retailer ID
First Name
Last Name
Job Title
14. The form should look similar to the figure below.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 4
[An image of the Sales Rep form ]
15. Use the Retailers form to populate the Retailers table with the records listed below.
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
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 5
State PA
Zip 16755
Retailer ID 3
Store Name Major Sporting Goods
Address 14998 Atlantic Ave.
City Any Town
State PA
Zip 16759
16. 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’ll 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
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 6
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
17. Format Table Datasheet view for the Retailers and Sales Reps table, adjusting column
widths as appropriate.
18. Create a query in Design view that displays complete contact information in alphabetical
order by store name. The tables should already display a relationship because of the
Lookup field. If not, join the tables appropriately to run the query correctly.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 7
[An image of the query]
19. Run the query and save it as “Contacts.”
20. Close the query and any open tables and forms.
21. 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 the following data:
Table Trout Hooks
Fish Caught
Tournament Table Leading Brand #1 Leading Brand #2 Leading Brand #3 %
Caught with Table
Local Trout Tournament 185 126 90 130
Tri-State Annual Derby 568 445 229 330
3. Format the workbook as follows (shown in the figure below):
a. Merge and center the titles. “Table Trout Hooks” is the overall title, and “Fish
Caught” is the heading for the Table and Leading Brand columns.
b. Format cell titles and labels as bold.
c. Display borders around all the cells with data.
d. Change the spreadsheet orientation to landscape to allow the wide columns to be
printed on one sheet of paper.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 8
[An image of the spreadsheet with formatting applied.]
4. 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).
5. Format the % Caught with Table values as a percent with one decimal place.
6. Insert a Column Chart of type 2-D Clustered Column that charts data in cells A4
through E6.
7. Change the title to “Fish Caught by Hook Brand.”
8. Change the Chart Style to Style 11, so that your chart looks similar to the figure.
[An image of the finished chart.]
9. Be sure the chart is sized appropriately and positioned under the data.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 9
10. 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 pressEnter.
3. Insert an online image of a trout (use “trout” as the search text) and then pressEnter.
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, clickColor > 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 the figure.
[An image of the heading and fish image]
7. Start the mail merge process.
8. When you select recipients, use an existing list, select the Retailers.accdb database
you created earlier, and then select the Contacts query.
9. Type the text shown below, inserting merge fields and leaving a blank paragraph where
indicated. You’ll need to Match Fields when you insert the AddressBlock field code so
that you can match the Company to the Store Name field.
March 30, 20xx
¶
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 10
<>
¶
Are you as excited as I am about the opening day of Trout Season? In preparation for the
Big Day, I invite you to join me at the Local Fishing Lodge for a morning of fishing followed
by a catered lunch on April 8, 20xx at 7:30 am. You will get to experience firsthand the
difference a Table trout hook makes.
¶
As <> at <>, I know you want to make the best fishing tackle
available to your customers. Table trout hooks hook more trout. Just look at the results
from two recent tournaments:
¶
¶
¶
Please RSVP by emailing me at [email protected] and let me know if you would
like to bring a guest. Looking forward to personally meeting with you.
¶
Tight lines,
¶
¶
¶
¶
Your name here
President
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 11
10. Your letter should look like the figure below.
[An image of the typed letter.]
11. Be sure that you’ve formatted the date and Table trout hooks name 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.
12. 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 the figure.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 12
[An image of the chart inserted in the letter.]
13. Save the document, naming it “Lodge Lunch.”
14. Use the appropriate command to merge the field data and create a document
containing the individual letters.
15. Scroll through the letters and check 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.
16. 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
don’t have Wood Type.
3. Verify slide 1 is in Title Slide layout and then enter the following text:
Title: Table Trout Hooks
Subtext: Bring home dinner!
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 13
4. Your slide should look similar to the figure below.
[An image of the title slide with text inserted]
5. Add a new slide with a Content with Caption layout (slide 2) and then insert the Table
Hook.jpg picture, the data file you downloaded for this graded project. Add the following
text and size the image so that slide 2 looks similar to the figure.
Title: The Table Trout Hook
The patented design of the Table trout hook combines sharpness, ability to
penetrate, and strength to offer a top rate product for putting dinner on the table.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 14
[An image of slide 2 with text and image added.]
6. Add a new slide with a Title and Content layout (slide 3) and add the following text.
Title: Table Trout Hook Design
The double feature has been tournament tested with overwhelmingly positive
response.
Pro and weekend fishers alike will see immediate results with the double hook and
curved shank.
The hook is made of carbon steel for strength and a black nickel finish for
durability.
7. Format the title and text (as a bullet list) as shown in the figure.
[An image of the formatted slide 3.]
8. Add a new slide with a Title and Content layout (slide 4) and add text as follows:
Title: Name Recognition
Hook-a-Permit promotion gave a Table hook to the first 250 in-person Trout permit
purchases.
Local Trout Tournament sponsor with every angler receiving a free Table hook.
Tri-State Annual Trout Derby sponsor is providing more than 2,000 Table hooks.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 15
[An image of the completed slide 4.]
9. Add a new slide with a Title and Content layout (slide 5). Use the same text as Slide 1
for the title, but combined. Edit the title and paste the chart from the Hook Results
spreadsheet so that the slide looks similar to the figure below.
[An image of the formatted slide 5.]
10. Add a new slide with a Title and Content layout (slide 6) and add content as follows.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 16
Refer to the Hook Results spreadsheet to get the figure for the first bullet.
Title: Conclusion
35% of tournament trout have been caught with Table hooks
Competitive wholesale prices
Customer support and satisfaction guaranteed
Free setup of in-store displays
Place your order today and get a free shipping discount
11. Insert a picture of the same trout (if possible) used in the Lodge Letter. The slide should
look similar to the figure.
[An image of the completed slide 6.]
12. Apply the Wind transition to all the slides.
13. Apply the Wipe animation to each of the bullet items on slides 3, 4, and 6.
14. Save the presentation, naming it “Sales Presentation,” and then play the slide show
from the beginning to check your work.
Scoring Guidelines
Rubric
SKILL/GRADING
CRITERIA
EXEMPLARY
(4)
PROFICIENT
(3)
FAIR
(2)
POOR
(1)
NOT EVIDENT
(0)
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 17
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
don’t use
functions has
been created.
No attempt to
create a
spreadsheet has
been made.
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
typed correctly
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
doesn’t 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 doesn’t
contain an
AddressBlock
merge field 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
layouts has been
created.
A presentation in
the specified
theme with some
of the slides in the
correct layouts has
been created.
A presentation
with fewer than
six slides in
varying layouts
has been created.
No attempt has
been made to
create a
presentation.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 18
Insert an image
from the
computer
A data file image
has been
inserted, sized,
and positioned.
A data file image
has been inserted
and 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.
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.
Submission Checklist
Before submitting your graded project, make sure you’ve done all the required tasks.
Create a database with multiple tables and a Lookup field table.
Create forms corresponding to database tables.
Populate a database table by using forms.
Create a query based on two related tables.
Specify a sort in a database select query.
Create a spreadsheet and apply formatting, including merge and center, to cells.
Use a function in a formula to calculate a value.
Create a Column chart of type 2-D Clustered Column.
Edit a chart title.
Change a chart style.
Type and format text in a Word document.
Insert, size, and position an online clip art image.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 19
Create a mail merge document using an existing database query as a data source.
Insert merge fields, including an AddressBlock, in a Word letter.
Match fields in an AddressBlock merge field.
Paste an Excel chart in a Word document.
Merge field data with actual data to create individual letters.
Create a PowerPoint presentation.
Apply a theme to a presentation.
Insert an existing image into a slide and then size and position it.
Paste an Excel chart into a slide.
Refer to a spreadsheet for content to add to a slide.
Insert an online clip art image into a slide and then size and position it.
Apply a transition.
Apply animations separately to bullet items on a slide.
© 2021 Penn Foster Inc. Advanced PC Applications (v2) : Lesson 7 : Page 20