Office 2016 Integrated Applications – Project 1 Creating a Company’s Presentation

SIMnet 2016: Word, Excel, Access, and PowerPoint Integrated Project 1

1 | Page Last Modified: 11/20/19

Office 2016 Integrated Applications – Project 1 Creating a Company’s Presentation In this project, you will create a presentation for the Top’t Corn popcorn company using resources from a Word

document, an Access database, and two Excel files. First, you will format an outline in Word so it can be

imported as slides and content for the presentation. You will format the look of the text before and after

importing and use Format Painter to copy and paste formatting between slides. Next, you will import data from

Excel into an Access database and then create two queries based on that imported data. You will export the

query results to a new Excel workbook where you will add formulas and create a table and a chart. Finally, you

will copy and paste the chart and table data from Excel into your PowerPoint presentation.

Skills needed to complete this project:

Word Skills

• Apply heading styles

• Change the font theme

PowerPoint Skills

• Import slides from a Word outline

• Change the font

• Align text

• Use Format Painter

• Change slide layout

• Add a table to a slide

• Paste data from Excel

• Change font color

• Paste a chart from Excel

• Move an object on a slide

Access Skills

• Add records to a table by importing from Excel

• Rename a field

• Create a simple select query to combine fields from multiple tables

• Add text criteria to a query

• Add date criteria to a query

• Apply multiple criteria to a query

• Add numeric criteria to a query

• Specify a sort order in a query

• Export data from a query to Excel

Excel Skills

• Enter text and numbers in cells

• Create a formula using multiplication

• Use an absolute reference in a formula

• Copy a formula

• Format data as a table

• Sort data in a table

• Use the AVERAGE function in a formula

• Use the SUM function in a formula

• Apply a number format

• Modify the font size

• Autofit columns

• Create a PivotTable using a Recommended PivotTable

• Create a pie chart

• Hide the chart title

• Apply a chart style

IMPORTANT: Download the resource file needed for this project from the Resources link. Be sure to extract the file after downloading the resources zipped folder. Please visit SIMnet Instant Help for step- by-step instructions.

1. Open the start file OF2016-Integrated-Project1. If the document opens in Protected View, click the

Enable Editing button in the Message Bar at the top of the document so you can modify it.

2. The file will be renamed automatically to include your name. Change the project file name if directed to

do so by your instructor, and save it.

Step 1

Download start file

SIMnet 2016: Word, Excel, Access, and PowerPoint Integrated Project 1

2 | Page Last Modified: 11/20/19

3. Open the Top’t Corn Outline Word document from the location where you saved the data files for this

project. (Downloaded from the Resources link.)

a. Apply the Heading 1 style to numbered items in the list.

b. Apply the Heading 2 style to the lettered items in the list.

c. Change the color theme of the document to Orange Red.

d. Save the document with the name Top’t Corn Outline for Import. Close the document.

e. Return to the PowerPoint presentation you downloaded from SIMnet.

4. Import the Top’t Corn Outline for Import Word file into OF2016-Integrated-Project1 presentation.

a. Use Slides from Outline… option to import the Top’t Corn Outline for Import file.

Note: When you are finished with this step, your presentation should contain 10 slides. If it does not,

your project will not grade properly and you may lose a significant number of points. Check your

work carefully.

b. Verify that the Heading 1 style items from the Word file appear as the titles, Heading 2 style items

appear in the body.

c. Verify that the title font on the imported slides appears to be an Orange Red color.

d. Select the title placeholder on Slide 2 (the placeholder with the word Overview). Change the font to

Calibri and left align the text.

e. With the title placeholder still selected, use Format Painter to copy the formatting and paste the

formatting each of the title placeholders on Slides 3 through 10.

5. Open the Top't Corn Sales Database Access file from the location where you saved the data files

for this project. (Downloaded from the Resources link.)

a. Import records from the Excel file Nov4-10Sales (located with the data files for this project) and

append a copy of the records to the OnlineSales table. (Hint: After the import, there should be 234

records in the OnlineSales table.)

b. In the OnlineSales table, rename the ID field: SaleID

6. Create a query to display sales of Old Bay flavored popcorn from buyers in Maryland and then export the

query results to an Excel file.

a. Include the following fields in this order: SaleID, Date, Quantity, and State fields from the

OnlineSales table and the ItemName field from the Items table.

b. Add criteria to the query to limit the results to records where the Item Name is Old Bay, the State is

MD, and the Date is greater than or equal to November 6, 2016. Run the query to check your

work. (Hint: There should be 8 records in the query results.)

c. Return to Query Design view and hide the ItemName and State fields in the query results.

d. Run the query again to check your work.

e. Save the query as OldBayMDQuery and then close the query.

Download Resources

Download Resources

SIMnet 2016: Word, Excel, Access, and PowerPoint Integrated Project 1

3 | Page Last Modified: 11/20/19

f. Export the results of the OldBayMDQuery to a new Excel file named: OldBayMDSale. Do

not export the data with formatting and layout. Save the file with the other resources for this project.

7. Create a query to display sales of truffle flavored popcorn with a quantity greater than six from the

OnlineSales table.

a. Include the following fields in this order: State and Quantity from the OnlineSales table and

ItemName field from the Items table.

b. Add criteria to the query to limit the results to records where the Item Name is Truffle and the

Quantity is greater than 6.

c. Specify the sort order in the query, so the results are sorted alphabetically by state. Run the query to

check your work. (Hint: There should be 26 records in the query results.)

d. Save the query as HighQuantityTruffleQry and close it.

e. Export the results of the HighQuantityTruffleQry to a new Excel file named:

HighQuantityTruffleSales. Do not export the data with formatting and layout. Save

the file with the other resources for this project.

f. Close any open database objects and then close the database and exit Access.

8. Open the OldBayMdSale Excel file you just created and create a table to copy into PowerPoint.

a . In cell D1, type: Total Sale

b . In cell F1, type: Price per Box

c . In cell G1, type: $11.00

d. In cell D2, enter a formula to calculate the total sale. Multiple the quantity sold (cell C2) by the price

per box (cell G1). You are going to copy this formula to cells D3:D9, so use relative and absolute

references as appropriate.

e. Copy the formula in cell D2 to cells D3:D9.

f. Format the data in cells A1:D9 as a table using any style.

g. Sort the table data by date so the most recent date is at the top.

h. In cell F3, type: Average Quantity

i. Enter a formula in cell G3 to calculate the average of cells C2:C9.

j. In cell F4, type: Total Sales

k. Enter a formula in cell G4 to calculate the sum of cells D2:D9.

l. Apply the Currency number format to cell G4.

m. Select cells A1:G9 and change the font size to 20.

n. Autofit all columns so the data are completely visible.

o. Copy cells A1:G9.

p. Save and close the Excel file.

SIMnet 2016: Word, Excel, Access, and PowerPoint Integrated Project 1

4 | Page Last Modified: 11/20/19

9. Return to the PowerPoint presentation and navigate to Slide 8.

a. Change the layout for Slide 8 to Title and Content.

b. Add a table to the slide with seven columns and nine rows.

c. Paste the data you copied from Excel into the table, and select Use Destination Style paste option.

d. Where necessary, change the text in the header row to bold, White, Background 1 (the first color in

the first row of theme colors).

10. Open the HighQuantityTruffleSales Excel file you created in step 7. e., and create a chart from the data.

a. Create a PivotTable from the data. Use the first (only) recommended PivotTable – Sum of Quantity

by State.

b. Create a 2-D pie chart from the PivotTable data.

c. Hide the chart title.

d. Apply the chart Quick Style, Style 8.

e. Copy the chart.

f. Save and close the Excel file.

11. Return to the PowerPoint presentation and navigate to Slide 9.

a. Change the layout for Slide 9 to Title Only.

b. Paste the chart you copied from Excel into the slide. Use the Keep Source Formatting and Embed

Workbook paste option.

IMPORTANT: If you receive an error message similar to "PowerPoint cannot obtain the data for the

{00020830-0000-C000-000000000046} link.", make sure that you have extracted the

HighQuantityTruffleSales Excel workbook from the resources zip folder and open the workbook from

the extracted folder, save your PowerPoint presentation, and then try again.

c. Drag the chart down so it is positioned in the middle of the slide.

12. Save and close the presentation.

13. Upload and save your project file.

14. Submit project for grading. Step 2 Upload & Save

Step 3 Grade my Project