Need a computer savy tutor please. please look over work before committing.

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 t e n t s

C o n t e n t 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 presenta- tions. 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

Advanced PC Applications2

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 poten- tial 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.

Advanced PC Applications4

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.

Advanced PC Applications6

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.

Advanced PC Applications8

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 ani-

mation “bounce”

that’s activated

upon entrance.

Advanced PC Applications10

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 work- sheet 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.

Advanced PC Applications12

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.

Advanced PC Applications14

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

Advanced PC Applications16

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 student portal.

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. Click Submit File.