can it be done before this midnight ?

Step

Instructions

Points Possible

1

Open the Access database i01_PCPT_data1. Save the database as Lastname_Firstname_Washington_Database replacing Lastname_Firstname with your name. Make sure to select a location you can easily find it such as a folder on your USB or a folder on the Desktop of your computer. All files for this project should be saved in this same folder. If needed, Enable Content.

1

2

Open the table Category Experts. Edit the last record replacing Firstname Lastname with your name. Close the table.

1

3

Open the query Qtrly Sales by Category Expert. Verify your name displays. Note that the query is sorted by First Name so depending on the spelling of your first name your records may not be listed first.

1

4

Export the query to Excel. Browse and save it in the same folder you saved your database file as Lastname_Firstname_Washington_Sales replacing Lastname_Firstname with your name. Verify the file format is Excel Workbook (*.xlsx). Select the Export data with formatting and layout check box. Do not save the export steps. Close the query.

5

5

Open your exported Excel workbook Lastname_Firstname_Washington_Sales. Create a table with headers. Sort the Merchandise column in ascending order. Add a Total Row. Set the Merchandise total currently displayed to None. Sum the Quarterly Sales column. Save the edited workbook and leave it open.

5

6

Open the Word document i01_PCPT_data2 Save the document in the same folder as your other project files as Lastname_Firstname_Washington_Memo replacing Lastname_Firstname with your name. In the document footer, insert the FileName field.

3

7

Select the table. Apply the Table Style Light List – Accent 1. Use the AutoFit Contents option, and center the entire table horizontally. Copy the selected table to the Clipboard.

2

8

In your Excel workbook, insert a new worksheet. Rename the sheet Sales by Store Select A3 and paste the Word table.

2

9

For column A, set the Column Width to 18. For columns B:F, set Column Width to 14. For rows 4:15, use AutoFit Row Height. Select the range A3:E15, and set No Border.

2

10

In F3 type Total Sales In F4 insert the Sum function to total the sales for Fishing merchandise. Use the fill handle to copy the Sum function through F15. With the range F4:F15 still selected, increase the font size to 12.

4

11

In A16 type Totals and center this text. In B16 insert the Sum function to total the sales for the Spokane store. Use the fill handle to copy the Sum function through F16. With B16:F16 still selected, apply the Cell Style under Titles and Headings called Total and also increase the font size to 12.

3

12

In A1 type the title Quarterly Sales for Washington Stores and then merge and center the title over the data. Apply the Cell Style Heading 1.

1

13

Select A3:E15 and insert a 3-D Clustered Column chart. Move the chart to a new sheet named Sales Comparison Add a chart title above the chart called Quarterly Sales for Washington Stores and show the legend at the top.

3

14

On the Sales by Store sheet, select A4:A15 and F4:F15. Insert a 2-D Pie chart. Move the chart to a new sheet named Merchandise Sales and select Chart Layout 1. Edit the chart title to be Merchandise Sales for Washington Stores

3

15

Select the Sales Comparison and Merchandise Sales sheets. In the footer in the left section, insert the File Name. Now select the Qtrly Sales by Category Expert and the Sales by Store worksheets. In the footer in the left section, insert the File Name.

2

16

Display the Clipboard task pane. On the Merchandise Sales sheet, select the chart and copy it. On the Sales Comparison sheet, select the chart and copy it.

2

17

Display the Word document Lastname_Firstname_Washington_Memo Display the Clipboard task pane. At the end of the Word document, paste the Excel column chart. Save the Word document and leave it open. Save the Excel workbook and leave it open.

2

18

Open the PowerPoint presentation i01_PCPT_data3 Save the presentation in the same folder as your other project files as Lastname_Firstname_Washington_Slides replacing Lastname_Firstname with your name. In the footer for Notes and Handouts, check the Date and time Update automatically, Page number and Footer and type the footer text Lastname_Firstname_Washington_Slides and apply it to all slides.

2

19

Display Slide 2, insert two new blank slides. Slides 3 and 4: hide the background graphics. Display the Clipboard task pane. On Slide 3, paste the column chart. On Slide 4, paste the pie chart.

3

20

On Slide 2, replace Firstname Lastname with your name. Convert this placeholder to SmartArt selecting Vertical Block List from the gallery. Apply the animation Split. Select the entire SmartArt graphic, and copy it to the Clipboard task pane.

3

21

Save the PowerPoint presentation. You will submit it electronically when you have completed this project. Exit PowerPoint.

1

22

In your Excel workbook, display the Sales by Store sheet. Select A18 and paste the SmartArt graphic. Set the width to 6.5 inches.

2

23

On the Qtrly Sales by Category Expert sheet, select A18:D21 and copy the range.

1

24

In the Word document, click the blank line below the table at the bottom of page 1 and press Enter. Type Our fitness merchandise continues to be our top-selling category. These numbers are highlighted below and illustrated in the column chart on the next page. and press Enter.

1

25

Use Paste Special to paste the Excel range as a linked Microsoft Excel Worksheet Object. Center the pasted object horizontally. Save and close the Word document.

4

26

On the Qtrly Sales by Category Expert sheet, locate the Quarterly Sales amount where the City is Tacoma and the Merchandise is Fitness. Change the amount from 164653 to 200000

3

27

Save the Excel workbook. You will submit it electronically when you have completed this project. Exit Excel.

2

28

Open the Word document Lastname_Firstname_Washington_Memo and update the data from the linked file. At the bottom of page 1, verify the Tacoma sales updated to $200,000. On page 2, notice the chart did not update to reflect the $200,000 amount for Tacoma Fitness sales because the chart was pasted without creating a link to the Excel file. In the table on the middle of page 1, edit the Fitness amount for Tacoma to $200,000

4

29

Display your Access database. Select the Merchandise table. Export the table to Word as Rich Text (RTF File). Browse and save it in the same folder as your other project files as Lastname_Firstname_Rich_Text replacing Lastname_Firstname with your name. Do not open the destination file after the export, and do not save the export steps. Exit Access.

5

30

At the end of the Word document, press Enter. Type Below is an alphabetical list of the new IDs assigned to our merchandise categories: and press Enter.

1

31

Insert the Rich Text file Lastname_Firstname_Rich_Text Apply the Table Style Light List – Accent 1. Use the AutoFit Contents option, and center the table horizontally. Save the document.

5

32

Use Mail Merge to select recipients from Lastname_Firstname_Washington_Database database and the Sales Associates table. Edit the recipient list. Filter the Store ID field to equal WA71 and display just the two sales associates for Bellingham, Washington—Byron Hoese and Kelley Zeck.

5

33

At the top of the Word document in the MEMO TO: line, delete the text Michael Dixon, President. Insert the Merge fields for <<Firstname>> <<Lastname>>, <<Title>>

4

34

Preview the results. In the MEMO TO: line, verify correct spacing between the fields as shown here: Byron Hoese, Sales Associate. Save the document.

1

35

Finish and merge to a new document to create memos for all of the filtered records. Verify the merged result contains two 2page memos—Byron Hoese and Kelley Zeck.

3

36

Save the Letters1 document in the same folder as your other project files as Lastname_Firstname_Merged_Memos replacing Lastname_Firstname with your name. Edit the footer and delete the existing FileName field; insert the FileName field again so the inserted text matches the merged document name.

3

37

You will submit the merged memos electronically when you have completed this project. Save and close Lastname_Firstname_Merged_Memos.

1

38

Display the Word document Lastname_Firstname_Washington_Memo again. Turn off the merge results preview so the Merge fields display again. Save the document. You will submit this file electronically when you have completed this project. Exit Word.

2

39

Submit the following 6 files to the Integrated Projects Dropbox:

1. Lastname_Firstname_Merged_Memo

2. Lastname_Firstname_Rich_Text

3. Lastname_Firstname_Washington_Database

4. Lastname_Firstname_Washington_Memo

5. Lastname_Firstname_Washington_Sales

6. Lastname_Firstname_Washington_Slides

2

Total Points

100