Following File can be found here:
Nicks Sporting Goods Letter.docx
Nicks Sporting Goods.xlsm
Final Graded Project
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
SUBMITTING YOUR FILES 16
C o n tt e n tt s
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
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
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.
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
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
5. Save and close your database (Figure 1).
PART 2: CREATING YOUR
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
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
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
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
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
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
9. Move the chart below the worksheet as shown in
Advanced PC Applications 7
PART 3: CREATING YOUR
AND PREPARING IT FOR
1. Open Microsoft PowerPoint and create a
2. Save your document as “Nick’s Presentation.”
3. Choose a Design theme and apply it to your slides.
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.
the data into
your title slide as
seen here. Be
sure to include
an image related
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
the data into
Slide 2 as seen
your third slide
as seen here.
the custom animation
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.
chart in Slide 5.
Slide 6 as you
Advanced PC Applications 11
PART 4: CREATING
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,
• 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.
as a single file
Web page as
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
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
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.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at firstname.lastname@example.org.
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at email@example.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at firstname.lastname@example.org.
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************