Advanced PC Applications

Follow the steps below to create the Hook Results spreadsheet.

1. Start Excel and create a new blank workbook.

2. Enter the following data:

· Table Trout Hooks

· Fish Caught

· Tournament  Table  Leading Brand #1  Leading Brand #2  Leading Brand #3  % Caught with Table

· Local Trout Tournament  185  126  90  130

· Tri-State Annual Derby  568  445  229  330

3. Format the workbook as follows (shown in the figure below):

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

c. Format cell titles and labels as bold.

c. Display borders around all the cells with data.

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