For this module’s assignment we will focus on the integration of Microsoft Word and Microsoft Excel. To continue our travel project theme, we will first create a PivotTable to provide us with the travelers that have a balance due greater than or equal to $2000. Then, we will create a memo to show the management team our results.
Part 1: Adding Formulas and Creating the Pivot Table
Open the Module 11 Data file linked below and follow these steps (click the Enable Editing button, if necessary):
Module 11 Data File
- Add your name and your travel destination.
- Add first and last names for all travelers.
- Create formulas for theTotalandAmount Duecolumns. (The information is given on the sheet).
- Create a pivot table for the information.
- Filter the pivot table to show only those travelers with a balance of 2000 or more. HINT: you should have four (4) travelers after you filter.
- Save the Excel document using the naming convention given at the end of this assignment.
Part 2: Integrating the Pivot Table into a Word Memo
Using the??Professional Design Memo template in Microsoft Word, create a memo to your employer which provides them with the travelers that have a balance due greater than or equal to $2000. In doing so, be sure to address the following requirements:
- Open a new Word document. In the search box, run a search for the Professional Design Memo Template.
- Update the Company Name to International Travel Company.
- Update the values in the To, CC, From, and Date fields.
- Update the Re: to be Statistics from Recent Balance Due Inquiry
- The title of your template should be Travelers with >=$2000 Balance Due
- In the body of the memo, include a short explanation of the details that you are providing to your employer.
- Integrate the PivotTable you created in part 1 into the Memo as an Excel object. (donotcreate a link).HINT: when you double click the pivot table, you should get a mini copy of Excel.
- Save the Word document using the naming convention given below.