1

The files for this portion of the exam are to be downloaded from the Final Exam module in Canvas.

0

2

Download and open the i01A_Locations database.Save the database as Lastname_Firstname_1A_Locations and enable the content.

1

3

Open the Managers table. For Manager ID WA-100, type in your Firstname and Lastname in the appropriate field. Save and close the Managers table.

4

4

Use the Managers Form to add a new manager:

Manager ID: OK-100 First Name: BrianLast Name: Rickel

Save and close the Managers Form.

4

5

Open the Store Locations table. Add a new record at the bottom:

Address: 100 Campus DriveMonthly Rent: 5000City: WeatherfordManager ID: OK-100

Save and close the Store Locations table.

4

6

Use Query Design to create a new query based on both tables. Include the Lastname, Firstname, City, and Manager ID fields (in this order). Sort the data by City in Ascending order. Set criteria so the query only shows data for Manager ID WA-100. Do not show the Manager ID in the query results. Run the query. (There should be 4 records.) Save the query as: Manager WA-100 Cities

5

7

Export the Store Locations by Manager Query to an Excel spreadsheet. Do this by:

• Save and close all open objects.
• Click on the External Data tab, then click Excel in the Export section
• On the Export Excel Spreadsheet that appears, click to select the “Export data with formatting and layout” radio button, then click Browse.
• Save to your location as Lastname_Firstname_1A_Sales_Chart in the File format Excel Workbook.
• Do not save the export steps, and click Close.
• When completed, close Access.

1

8

Open Excel and then open Lastname_Firstname_1A_Sales_Chart.

0

9

Download and open i01A_State_Sales. Save as Lastname_Firstname_1A_Sales

1

10

Insert the File Name in the footer.

3

11

Set the Table Style to List Table 4 – Accent 1. Resize the cells to AutoFit to Contents. Then center the table horizontally to the page.Save your Word document.

5

12

At the beginning of the paragraph of text, search for an online picture using the phrase fitness apparel. Insert an appropriate picture of your choice. Change the picture height to 0.75 inches. Set text wrapping to square. Set the horizontal alignment to Right relative to Margin. Set the vertical alignment to Top relative to Line. Save your Word document.

7

13

Select and copy the table. Make Excel the active Window. Add a New sheet and rename it Sales. (No period.) With cell A4 as the active cell, paste the table.

5

14

With Column A selected, set the width to 16. Set the width for B4:E4 to 12.Format the Row Height for A4:A16 to 17.

4

15

In cell A1, type Ultimate Sports Action Gear. (No period) In cell A2, type Sales by State. (No period)Merge & Center A1:G1 and A2:G2. In A1, format the Cell Style to Accent 1 and the Font Size to 16. In cell A2, format the Cell Style to 60% – Accent 1 and the Font Size to 14. Save your workbook.

5

16

In cell F4, type Total Sales and resize the column width to 13. In cell F5 sum the total of the Fishing Gear sales.Copy and paste the formula in F5 to F6:F16. In cell A17, type Total.In cell B17, sum the total of all Colorado merchandise.Copy and paste the formula in B17 to C17:F17.

5

17

In cell G4, type Percent of Sales and resize the column width to 15. In cell G5 (use absolute cell references as necessary), enter a formula that will divide total sales of fishing gear by the total of all sales to calculate the percent of total sales.Format cell G5 as % with 2 decimal places. Center cell G5. Copy and paste the formula in G5 to G6:G16.

7

18

Remove the border for range A5:E16. Format the Cell Style for B17:F17 to Total. Save the workbook.

3

19

Insert a 3-D Clustered Bar Chart for range A4:E16 on a New sheet with the name Merchandise Chart.

5

20

Type Retail Sales by State for the Chart Title. Set the Chart Style to Style 2. Move the Legend to the Bottom. Save the workbook.

5

21

On the Sales worksheet, select range B4:E4 and B17:E17. Insert a 3-D Pie chart. Move the chart so the top left corner is in the middle of cell A19. Change the width of the chart to 6” and the height to 4”. Format the chart style as Style 6 Type in the Chart Title State Sales Remove the Legend from the chart.

7

22

Format the Data Labels as Category Name and Percentage in the Center position and Font Size 11.

4

23

Format the Chart Area to Gradient Fill with a Preset gradient of Top Spotlight – Accent 1. Close any open panes.

4

24

Download and open the i01A_Presentation. Save it as Lastname_Firstname_1A_Presentation.

1

25

Click the Excel icon on the taskbar. On the Merchandise Chart sheet, select and copy the bar chart. Click the PowerPoint icon on the taskbar. With Slide 2 active, add a new Blank Slide. Hide the Background graphics, and paste the bar chart on the blank slide. Close the Clipboard pane (if necessary) and save the presentation.

5

26

With Slide 2 active, convert the names to SmartArt Vertical Block List. Set the SmartArt Style to 3D Polished. Add the Float In Animation, found in the Entrance section, to the SmartArt graphic. View the slide show to see if the changes are present. Save the presentation and close PowerPoint.

5

27

Save and close Excel. Save and close Word.

0

28

Save, Close and upload the following 4 files for grading:

• Lastname_Firstname_1A_Locations.accdb
• Lastname_Firstname_1A_Sales_Chart.xlsx
• Lastname_Firstname_1A_Sales.docx
• Lastname_Firstname_1A_Presentation.pptx

Each file should be uploaded separately. When finished, all 4 files should be showing in the Dropbox.

0

Total Points

100