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 retailer

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.

 

Lesson Objectives

• Prepare for a promotional event to introduce a new product to local retailers

Prepare for a Promotional Event to Introduce a New Product to Local Retailers

Graded Project: Advanced PC Applications

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:

• 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.
• 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 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.

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.

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.

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

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:

a.                Table Trout Hooks

b.                Fish Caught

c.                Tournament  Table  Leading Brand #1  Leading Brand #2  Leading Brand #3  % Caught with Table

d.                Local Trout Tournament  185  126  90  130

e.                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.

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.

9.                Be sure the chart is sized appropriately and positioned under the data.

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 press Enter.
3. Insert an online image of a trout (use “trout” as the search text) and then press Enter.
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, click Color > 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.

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

¶

<<AddressBlock>>

¶

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 <<Job_Title>> at <<Store_Name>>, 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 pres@tabletrouthooks.com 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

10.             Your letter should look like the figure below.

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.

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!

4.                Your slide should look similar to the figure below.

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.

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.

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.

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.

10.             Add a new slide with a Title and Content layout (slide 6) and add content as follows. 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.

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)
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.
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.
• 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.

 

Graded Assignment: Advanced PC Applications Graded Project

Listen

Instructions

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:

• 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.

Be sure to keep a backup copy of any files you submit to the school!

  260301PAQP_584127 7/2/2026 Thank you for your recent project submission. However, it appears there are required key elements of the project that are not correct or missing. Your project will not be graded at this time and an RTN will be posted to your record. (What does RTN mean? When you upload your work an RCD is posted alerting us that you have submitted files. An RCD prevents you from uploading any other files for that particular project until your work has been reviewed. An RTN means that your work was reviewed and the project has been returned rather than graded. RTN is not a penalty of any sort. Most importantly, an RTN allows the system to accept your corrected work.) Please do not be discouraged and think that we are going to post a failing grade for your project. You do need to correct your work and resubmit it as if it were the first attempt. Most students do not necessarily successfully complete the project on the first attempt. It is important that you understand how to correctly use the software and the necessary skills in this project in order to be successful in your future endeavors. If you have any questions, please reach out to us via phone at 1-888-427-1000 or send us an email through the Help & Support section of your Student Portal. An appointment to speak to an instructor can be scheduled at https://pennfoster.libcal.com/appointments/college?g=6758. Here are those areas you need to improve upon: ⮚On the right track! Missing the Retailers database file which must be an Access database. Also, check the first line of the second paragraph in Lodge Lunch for Job Title and Store Name. See the examples below. Please review the project instructions to see which files should be submitted and submit all the files at the same time for grading. Contact our office for assistance. ⮚One of the files required for this project is t