Following File can be found here:
Nicks Contacts.accdb
Nicks Presentation.mht
Nicks Presentation.ppt
Nicks Sporting Goods Letter.docx
Nicks Sporting Goods.xlsm
OutputLetters.docx

Final Graded Project
Advanced PC
Applications
INTRODUCTION 1
SCENARIO 1
YOUR TASK 2
PART 1: CREATING YOUR ACCESS DATABASE 2
PART 2: CREATING YOUR EXCEL WORKSHEETS 3
PART 3: CREATING YOUR POWERPOINT
PRESENTATION AND PREPARING IT FOR
THE INTERNET 7
PART 4: CREATING YOUR LETTER 11
PART 5: MERGING YOUR LETTER WITH
DATABASE DATA 12
GRADING 15
SUBMITTING YOUR FILES 16
iii
C o n tt e n tt s
1
INTRODUCTION
Welcome to the final part of the course. Now that you’ve worked
with and integrated Microsoft Word, Excel, PowerPoint, and
Access, you’re going to use these skills in a final graded
project. You’ll use many of the concepts and techniques
featured in this course to complete this project, which
your instructor will grade.
Your project is divided into five parts:
1. Creating a database of names and addresses in
Microsoft Access
2. Creating worksheets with Microsoft Excel
3. Creating a presentation with Microsoft PowerPoint
(which includes Excel data) that you’ll prepare to be
shared on the Internet
4. Creating a letter with Microsoft Word
5. Merging your Access database contents with the Word
document
SCENARIO
You work for the marketing department of a store called
Nick’s Sporting Goods. Your boss is impressed with your
Office skills, especially how you can create Internet presentations.
She asks you to create a marketing letter introducing
your store to several potential retailers. The letter will include
a link to your Web site, where you’ll upload a PowerPoint
Advanced PC Applications
2 Advanced PC Applications
presentation to be viewed as a slide show. She’s handed
you a list of names and wants you to add them to a database
so you can easily keep track of these contacts and send
them letters. For the Internet presentation, she wants you
to include data introducing your company and its products,
highlight some best-selling items, and create an order form
for retailers who wish to carry your company’s products.
YOUR TASK
For this project, you’ll create a database using Microsoft
Access in which you’ll input the contact information of potential
customers. Then, you’ll create an order form in Microsoft
Excel. You’ll then create a Microsoft PowerPoint presentation
that will be accessible from your company’s Web site. Then,
you’ll create a marketing letter using Microsoft Word, which
includes the link to the PowerPoint presentation. Lastly, you’ll
merge your Access database contents with your Word letter.
PART 1: CREATING YOUR
ACCESS DATABASE
1. Open Microsoft Access and create a new database.
2. Save the new database as “Nick’s Contacts.”
3. Add the following fields and data types:
Last Name Short text
First Name Short text
Title Short text
Company Name Short text
Address Short text
City Short text
State/Province Short text
ZIP Code Number
Work Phone Number
E-mail Name Short text
Advanced PC Applications 3
4. Add contact information for at least five contacts to
your database.
5. Save and close your database (Figure 1).
PART 2: CREATING YOUR
EXCEL WORKSHEETS
1. Open Microsoft Excel and create a new worksheet
2. Save your worksheet as “Nick’s Order Form.”
3. Record the following macro and save it as “Nick”: Using
Arial 14 pt bold font, type “Nick’s Sporting Goods” in
cell A1.
4. Delete “Nick’s Sporting Goods” from cell A1 and then
run the Nick’s Sporting Goods macro to insert the title
back into cell A1.
5. Enter the data into your worksheet as shown in
Figure 2.
Make sure you set your columns to AutoFit so all the
data can be seen.
Enter the figures in columns C and D as currency.
6. In cell D9, create a formula that multiplies B9 by C9.
7. Copy this formula to cells D10–D16.
8. In cell D18, use a function or create a formula that adds
cells D9–D16.
FIGURE 1—Your database should include these fields.
4 Advanced PC Applications
9. In cell D19, use an IF function. The conditions are
• If the number in cell D18 is over 500, then insert 0.
• If the number in cell D18 is under 500, then insert
5% of the number in cell D18 (Hint: D18*.05).
10. In cell D22, use a function or create a formula that adds
cells D18 and D19.
11. Select cells A9–D16 and sort your products alphabetically
by name.
12. In cells E9–E16, use a function or create a formula that
calculates the percentage of sales.
13. Compare your worksheet to Figure 3.
14. Save your worksheet.
FIGURE 2—Your worksheet
should appear similar to
the one shown here.
Advanced PC Applications 5
To use multiple worksheets, you should follow these steps:
1. With the Nick’s Sporting Goods Order Form Excel sheet still
open, rename the tab labeled Sheet 1 as “Order Form.”
2. Rename the tab labeled Sheet 2 as “Pie Chart.”
3. Press the Pie Chart tab to show this sheet.
4. Run the Nick macro to enter the title into cell A1.
5. In cell B1, type “Best-Sellers.”
6. Create a pie chart using the data from cells A9–A16 and
E9–E16 of your Order Form Worksheet.
• Be sure to choose a pie chart.
• Enter the title as “Best-Sellers.”
• Under Data Labels, include Category Name
and Value From Cells.
FIGURE 3—Your figures
should appear like the
ones shown here.
6 Advanced PC Applications
Hint: To select the data from the Order Form worksheet,
highlight the cells that contain the data when you’re selecting
the data range in your chart wizard as seen in Figure 4.
FIGURE 4—To select data from your Order Form worksheet, include it in the Data range
selection.
7. Enlarge your chart so you can view more of the data.
8. Change the font size to Arial 10 so you can read all the
product names.
9. Move the chart below the worksheet as shown in
Figure 5.
Advanced PC Applications 7
PART 3: CREATING YOUR
POWERPOINT PRESENTATION
AND PREPARING IT FOR
THE INTERNET
1. Open Microsoft PowerPoint and create a
new presentation.
2. Save your document as “Nick’s Presentation.”
3. Choose a Design theme and apply it to your slides.
(Figure 6).
FIGURE 5—Your pie chart should appear similar to the one shown here.
8 Advanced PC Applications
4. On your first slide, type Nick’s Sporting Goods, as in
Figure 7. Use Arial font, size 54. Be sure to add an image
representing sporting goods.
5. On your second slide, enter the data as seen in Figure 8.
FIGURE 6—Apply a slide design to your slides.
FIGURE 7—Type
the data into
your title slide as
seen here. Be
sure to include
an image related
to sporting
goods.
Advanced PC Applications 9
6. Add the Shape Circle transition to this slide.
7. On your third slide, enter the data as seen in Figure 9.
8. On your fourth slide, enter the data as seen in
Figure 10.
FIGURE 8—Enter
the data into
Slide 2 as seen
here.
FIGURE 9—Create
your third slide
as seen here.
FIGURE 10—Use
the custom animation
“bounce”
that’s activated
upon entrance.
10 Advanced PC Applications
Create a custom animation in which clip art of a ball
bounces in during the slide show.
9. Create your fifth slide with the text as seen in Figure 11.
Be sure to include your pie chart from the Excel worksheet
you created earlier in this graded project.
11. Create your sixth slide as seen in Figure 12. Be sure to
create a hyperlink to www.nickssportinggoods.com/
order.htm (not an existing site).
12. Save this presentation as a single file Web page that can
be uploaded to the World Wide Web as seen in Figure 13.
FIGURE 11—
Include your
Best-Sellers pie
chart in Slide 5.
FIGURE 12—Type
Slide 6 as you
see here.
Advanced PC Applications 11
PART 4: CREATING
YOUR LETTER
1. Open Microsoft Word and create a new document.
2. Save your document as “Nick’s Sporting Goods Letter.”
3. Type and format the rest of your letter as shown in
Figure 14. Be sure to
• Type the title “Nick’s Sporting Goods” in Arial font,
size 16.
• Center Nick’s Sporting Goods address information.
• Insert an image representing sporting goods.
• Insert the current date beneath the address. Make
sure you select the Update Automatically option
when inserting the date.
FIGURE 13—Save
your presentation
as a single file
Web page as
seen here.
12 Advanced PC Applications
PART 5: MERGING YOUR LETTER
WITH DATABASE DATA
1. Open your Nick’s Sporting Goods letter in Microsoft
Word, if it isn’t already open.
2. Walk through the steps in the Mail Merge Wizard to add
the contacts from your Access database into this letter
as seen in Figure 15.
FIGURE 14—Your letter should appear similar to the one shown here.
Advanced PC Applications 13
3. Complete the merge and compare your letter to
Figure 16. Your letter will include the data from your
Access database.
FIGURE 15—Insert the Address Block and Greeting Line from the Mail Merge Wizard.
14 Advanced PC Applications
FIGURE 16—An Example of a Merged Letter
Advanced PC Applications 15
GRADING
Your project will be graded on the following elements:
Part 1 (total 20 points)
Successfully creates a database
in Access 5 points
Inserts all the fields shown in
Figure 1 10 points
Enters data into at least five fields 5 points
Part 2 (total 30 points)
In Order Form sheet, data entered
and formatted correctly as in Figure 3 10 points
All functions and formulas work correctly 10 points
In Pie Chart tab, pie chart created
and formatted correctly as in Figure 5 10 points
Part 3 (total 30 points)
Slides created and formatted correctly 5 points
Selected slide design applied to
all slides 5 points
Clip art added appropriately to the
title slide 5 points
Transition successfully added to
second slide 5 points
On Slide 4, ball created as custom
animation, bounces in when clicked
upon during slide show 5 points
Best-Seller Chart added to slide 5 and
presentation successfully saved as an
MHTML file 5 points
16 Advanced PC Applications
Part 4 (total 10 points)
Nick’s Sporting Goods letter is
typed and formatted correctly 5 points
A graphic is successfully inserted
into the letter 5 points
Part 5 (total 10 points)
Five Access database contacts successfully
merged into Nick’s Sporting Goods letter 10 points
SUBMITTING YOUR FILES
Follow this procedure to submit your project online:
1. Log onto the Penn Foster Web site and go to “My Courses.”
2. Click Take Exam.
3. Attach your files as follows:
a. Click the Browse box.
b.Locate the file you wish to attach.
c. Double-click on the file.
d.Click Upload File.
e. Since you have more than one file to attach, click
on the Browse box again, and repeat steps b, c,
and d for each file.
4. Enter your e-mail address in the box provided. (Note:
This information is required for online submission.)
5. If you wish to tell your instructor anything specific
regarding this assignment, enter it in the Message box.
6. Click Submit File.