Penn Foster 03821200 – Nick's Sporting Good
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.
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.
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
