401K Calculator

Document Sample
401K Calculator Powered By Docstoc
					         Project 10
Sharing Excel 2000 Data with Other
 Microsoft Office 2000 Applications



    Jason C. H. Chen, Ph.D.
           Professor of
Management Information Systems
School of Business Administration
       Gonzaga University
   Spokane, WA 99258, USA
      Chen@gonzaga.edu
Excel Skills
   Change Workbook Properties.
   Complete A Microsoft Word 2000 Mail Merge Using An Excel List
    As The Data Source.
   Create A Web Query.
   Create An Excel Workbook From A Microsoft Access 2000
    Database Using Microsoft Query.
   Embed An Excel Workbook In A Word 2000 Document.
   Link An Excel Worksheet To A PowerPoint Presentation.
   Modify A Word Mail Merge Main Document.
   Update A Web Query.



                                                              2
Key Terms
   Microsoft Query
       An Office program for bringing data from external sources
        into Microsoft Excel. By using Query to retrieve data from
        existing databases and files, you do not have to retype the
        data you want to enter into a worksheet.
   Object Linking and Embedding
       A program-integration technology you can use to share
        information between Windows programs. All of the Office
        programs support OLE, so you can share information
        through linked and embedded objects."




                                                                  3
Key Terms
   Query
       In Microsoft Query or Microsoft Access, a means of finding
        the records that meet the specific criteria you define about
        the data that is stored in a data source."
   serial value
       A sequential number representing dates and times as
        fractions, since time is considered a portion of a day. Dates
        and times are values and, therefore, can be added,
        subtracted, and included in other calculations.
   Web Query
       A query you define to retrieve text or data that is in tables or
        preformatted areas on a Web page.


                                                                     4
Objectives
   Use Microsoft Query to create an Excel workbook
    from a Microsoft Access database
   Change the properties of an Excel workbook
   Embed an Excel worksheet in a Microsoft Word
    document
   Use an Excel list as a Microsoft Word Mail Merge
    source
   Link an Excel worksheet to a Microsoft PowerPoint
    presentation
   Create and Update Web Queries

                                                        5
Running Case
   You will assist the Selections, Inc. Finance
    Department in using Excel and other Office
    2000 applications to distribute and update
    information.




                                                   6
Project Challenge
   Mr. Traylor’s department has decided to send
    a letter explaining the new employee
    retirement plan to all employees. This letter
    will contain an Excel workbook employees
    can use to estimate their future retirement
    earnings. He has a sample Access database
    file containing employee data. You will need
    to import this data form Access into Excel,
    and then add data listing each employee’s
    current age.

                                               7
Project Challenge
   Then Mr. Traylor wants you to help him build a
    PowerPoint presentation explaining the plan that he
    will eventually send to the manager of each
    Selections, Inc. store. This presentation will also
    include the workbook for calculating future
    retirement earnings, so that any given presentation
    of the plan can be interactive, where the presenter
    can explore different investment scenarios.
    Finally, Mr. Traylor He wants you to develop a
    simple worksheet that will list the major market
    indices, and can be updated daily on the Web.


                                                      8
Sharing Excel 2000 Data with
other MS/2000 Applications
   Use Query to retrieve data from your
    corporate databases and files such as
    MS/Access databases.
   Files needed
       Task 1,2
            Selections.mdb (Access database)
       Tasks 3-5
            401K Calculator.xls and 401K Letter.doc
       Task 6
            401K Calculator.xls and Selections 401K Plan.ppt


                                                           9
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)

                                  1. Click Data, Get External
                                  Data, and choose New
                                  Database Query.




                                   2. The Choose Data Source
                                   dialog box will appear.
                                   Choose MS Access 97
                                   Database* (or other
                                   Access) and click OK.



                                                         10
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)

                   3. MS Query will attempt to connect to a
                   database, so the Select Database dialog box
                   will appear. Locate the Selections.mdb
                   database file on the floppy diskette (or
                   other appropriate location) and click OK.




                   4. The Query Wizard – Choose Columns
                   dialog box will appear. Click the button to
                   expand the list.

                                                                 11
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
                        4 (cont.). The Query Wizard – Choose
                        Columns dialog box will appear. Click the
                        button to expand the list.

                        Then add all columns except HomePhone
                        and PayRate from the Available Tables and
                        Columns: list to the Columns in your
                        Query:list. Click Next when you done.



                                   You may move to step 5 for a
                                   normal process, however, you
                                   may try the following steps:

                                   4.a) If you press [Esc], you
                                   will be asked …


                                                                  12
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)
                             4.a) If you press [Esc], you
                             will be asked “Do you want to
                             continue ..”
                             4.b) Click Yes, the Microsoft
                             Query window (from
                             MS/Access) will appear.




                                                             13
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)

                                                  4.c) Click [X]




                             4 d. Microsoft Query will have you
                             verify the location for the imported
                             data. Select the defaults shown in
                             Figure, and click OK.

                             Notice that the result from this step
                             will not include the sorting criteria.



                                                              14
         Task 1: To Create an Excel Workbook from a MS/Access
         2000 Database using MS Query (cont.)




The result from step 4 d).
Notice that the result from this step will not include the sorting criteria.

                                                                               15
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)

                            5. In the next step of the Query
                            Wizard you can choose to filter the
                            database by one or more criteria.
                            Click Status in the Column to Filter:
                            list. Click the [Down arrow] key on
                            the right-side of the options.




                              Then set two criteria: for the first
                              criteria filter records where Status
                              is equal to Three-Quarter Time,
                              and for the second criteria, filter
                              records where Status is equal to
                              Full Time. When the setting
                              matches those shown in the Figure,
                              click Next.

                                                            16
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)

                              6. In the next step of the Query
                              Wizard you can specify a sort order
                              for you list. Select Lastname and
                              click Next.




                            7. You are almost finished! This last
                            step of Wizard allows you to save
                            the query and return data to Excel.
                            Since you are merely working with a
                            sample of records from the
                            Selections database, there is no
                            reason to save the query. When your
                            options are the same as those shown
                            in the Figure, click Finish.


                                                            17
Task 1: To Create an Excel Workbook from a MS/Access
2000 Database using MS Query (cont.)

                            8. Microsoft Query will have you
                            verify the location for the imported
                            data. Select the defaults shown in
                            Figure, and click OK.


                            The data is imported into the first
                            worksheet of the workbook.




                                                              18
        Task 1: To Create an Excel Workbook from a MS/Access
        2000 Database using MS Query (cont.)




9. Type Current Age in cell J1 and press [Enter]. Format the cell as Bold, Align
right, and adjust the column width as necessary.
10. Type =TRUNC((NOW()-H2)/365) as the formula in cell J2.

                                                                                   19
       Task 1: To Create an Excel Workbook from a MS/Access
       2000 Database using MS Query (cont.)




11. Use the Fill handle to copy the formula down the column through row 10.
12. Click cell A1. Save the workbook as Employee List.xls.




                                                                              20
     Goals:
     1) To specify the purpose of the document in the File
        Properties
     2) To add a comment in cell A1.

Task 2: To Change Workbook Properties
                              1. Click File, Properties
                              2. Type Employee Address List in the Title:
                                   text box.
                              3. Press [Tab] twice. Type your name in the
                                   Author: text box.
                              4. Press [Tab] twice. Type Selections, Inc. in
                                   the Company:text box.
                              5. Press [Tab] three times. Type File created
                                   from the Selections.mdb database.
                              6. Click the checkbox to save a preview
                                   picture. The settings should match those
                                   shown in the Figure.
                              7. Click OK.

                                                                          21
Task 2: To Change Workbook Properties (cont.)

                       8. Click Insert, Comment.
                       (cell A1 should be the active cell)
                       9. Type Workbook created for use as a
                             mail merge source document as the
                             comment for cell A1.




                      10. Click cell A1 and the click Save (or save
                           as).
                      11. Place the insertion point over cell A1. A
                           comment similar to the one shown in
                           Figure is displayed.




                                                                 22
Embedding an Excel Workbook in a
MS/Word Document
   MS/Office supports Object Linking and
    Embedding (OLE), a technology for sharing
    data among Office applications.
       When you embed an object, such as an Excel
        Workbook in a Word document, information in the
        destination file (Word document) does not change
        if you modify the source file (Excel workbook).




                                                     23
Task 3: To Embed an Excel Workbook in a Word
Document

                         1. Click File, Open and open the 401K
                              Calculator.xls workbook from the
                              floppy diskette (or other
                              appropriate folder).
                         2. Highlight the range A1:D13 and
                              click Copy as shown in the
                              Figure.



                          3. Launch MS/Word.
                          4. Click File, Open, and locate the
                               401K Letter.doc document, select
                               it and click Open, as shown in the
                               Figure.




                                                             24
        Task 3: To Embed an Excel Workbook in a Word
        Document (cont.)




4 (cont.). The letter contains placeholders for each of the merge fields you
     will insert as well as the Excel workbook.

                                                                               25
    Task 3: To Embed an Excel Workbook in a Word
    Document (cont.)




5. Navigate to the second page of the letter and highlight the placeholder
     titled [Embed the Worksheet Object Here], as shown in the Figure.

                                                                             26
        Task 3: To Embed an Excel Workbook in a Word
        Document (cont.)




6. Press [Del] to delete the placeholder.
7. Press [Enter] twice and move the insertion point to immediately before the second
     Paragraph Mark.
8. Click Edit, Past Special (you may need to expand the selections from the Edit
     command)                                                                          27
Task 3: To Embed an Excel Workbook in a Word
Document (cont.)

                             9. Select Microsoft Excel
                                  Workbook Object in the
                                  Paste Special dialog box and
                                  click OK.
                             The workbook is embedded in
                                  the document, as shown in
                                  the Figure. You may resize
                                  the embedded worksheet if
                                  necessary.
                             10. Save your changes to the
                                  401K Letter.doc document.

                                   11. Close the 401K
                                        Calculator.xls
                                        workbook. When
                                        Excel asks if you want
                                        to save the
                                        information on the
                                        Clipboard, click NO.
                                                          28
Word Mail Merge
   You are now ready to prepare a Word Mail
    Merge.
   You have three tasks you must first complete:
       Specify the kind of merge document,
       Specify the data source,
       Edit the main document by adding field codes.




                                                        29
Task 4: To Modify a Word Mail Merge Main Document

                     1. Click the 401K Letter.doc button on the
                          Windows Taskbar to activate Word.
                     2. Navigate to the beginning of the document.
                     3. Click Tools, Mail Merge
                     4. Click the Create button in the Mail Merge
                          helper and choose Form Letters.




                                                              30
Task 4: To Modify a Word Mail Merge Main Document
(cont.)

                             5. Click the button to use the
                                  Active Window,




                              6. Click the Get Data button
                                   and select open Data
                                   Source.




                                                              31
Task 4: To Modify a Word Mail Merge Main Document
(cont.)

                            7. Navigate to your floppy
                                 diskette (or other
                                 appropriate drive), and
                                 change the Files of type
                                 selection in the Open
                                 Data Source dialog box to
                                 MS Excel worksheets.
                                 When your settings match
                                 those shown in the
                                 Figure, click Open.


                           Word will locate the data source.
                           When the dialog box shown in
                           the Figure appears, select Entire
                           Worksheet and click OK.


                                                               32
Task 4: To Modify a Word Mail Merge Main Document
(cont.)

                                   8. Word will inform
                                   you that you need to
                                   edit the main
                                   document. Click Edit
                                   Main Document, as
                                   shown in the Figure.




                                                     33
Task 4: To Modify a Word Mail Merge Main Document
(cont.)
                               9. For each placeholder in
                               the letter, replace the text
                               for the placeholder with
                               the appropriate merge
                               fields by clicking the Merge
                               field button on the Mail
                               Merge toolbar.




                                                        34
Task 4: To Modify a Word Mail Merge Main Document
(cont.)
                               When you are finished, the
                               letter will resemble the one
                               shown in the Figure.

                               10. Save the letter.




                                                        35
Task 5: To Complete a MS/Word Mail Merge using an
Excel List as the Data Source
                                  1. Launch MS/Word if it
                                  is not currently running,
                                  and open the 401K
                                  Letter.doc document
                                  from the floppy diskette.

                                  2. Click the Merge
                                  button        on the
                                  mail Merge toolbar.




                                 3. The Merge dialog box
                                 will appear. Click Merge.




                                                         36
Task 5: To Complete a MS/Word Mail Merge using an
Excel List as the Data Source (cont.)
                          Word merges the main document
                          with the data source as a new
                          document with the default name
                          Form Letters1.
                          You will notice that this document
                          consists of 18 pages.

                          4. Click Save, and name the
                          document containing the merged
                          letters as 401K Letters-
                          Merged.doc




                                                          37
       Task 5: To Complete a MS/Word Mail Merge using an
       Excel List as the Data Source (cont.)

                                         Move to the third page to view the
                                         next new document created.

                                         5. Click the Print Preview button on
                                         Word’s Standard toolbar, and
                                         change the preview zoom to 10%.
                                         The nine merged letters will appear
                                         in the Preview window (see next
                                         slide)
                                         6. Close the preview.
                                         7. Close the two open Word
                                         documents. Save any changes if
                                         you are prompted to do so.


Tip: When you close the main document, Word will also close the Excel
workbook containing the data source.
                                                                         38
Task 5: To Complete a MS/Word Mail Merge using an
Excel List as the Data Source (cont.)




                                                39
Linking an Excel Worksheet to a
MS/Powerpoint Slide
   Link the 401K Calculator workbook to the
    PowerPoint presentation
       Therefore, during the presentation the presenter
        only changes the data in the workbook and then
        refresh the slide.
       With the a linked object, the original information
        remains stored in the source file.
       The destination file displays a representation of
        the linked information but stores only the location
        of the original data.
       When you link an object, the size of the
        destination file remains virtually unchanged.
                                                        40
Task 6: To Link an Excel Worksheet to a Powerpoint
Presentation
                            1. Open the 401K
                            Calculator.xls workbook from
                            your floppy diskette.
                            2. Highlight cells A1:D13,
                            right-click, and select Copy.




                            3. Open the Selections 401K
                            Plan.ppt presentation from
                            your floppy diskette.
                            4. Use the vertical scroll bar to
                            navigate to the third slide in
                            the presentation.



                                                            41
Task 6: To Link an Excel Worksheet to a Powerpoint
Presentation (cont.)

                               5. Click Edit, Paste Special.
                               6. In the Paste Special dialog
                               box, choose MS/Excel
                               Worksheet Objects in the AS:
                               list, and check the Past Link
                               option button. When the
                               settings match those shown in
                               the Figure, click OK.
                               7. Resize and center the linked
                               worksheet. When you are
                               finished, your slide should
                               look similar to the one shown
                               in the next Figure.
                               8. Save the presentation.
                               9. You may press [F5] to run
                               the slide show.



                                                         42
Task 6: To Link an Excel Worksheet to a Powerpoint
Presentation (cont.)




                                                     43
Task 6: To Link an Excel Worksheet to a Powerpoint
Presentation (cont.)

                                  Change the age from 21 to
                                  30 on the worksheet. You
                                  should notice that the
                                  associated information are
                                  updated automatically on
                                  the powerpoint
                                  presentation.
                                  10 When you done, close
                                  Powerpoint and the
                                  workbook.




                                                       44
Task 6: To Link an Excel Worksheet to a Powerpoint
Presentation (cont.)

                               TIP: When you open the
                               presentation, Office will also
                               open Excel and the linked
                               worksheet object.

                               If you change data in the
                               linked workbook and switch
                               back to Powerpoint, you can
                               right-click the linked
                               worksheet and choose Update
                               Link.




                                                           45
Creating and Updating Web
Queries
   To analyze data on a Web page, you can use
    a Web query
       to retrieve a single table,
       multiple tables, or
       all of the text on the a Web page




                                            46
Task 7: To Create a Web Query

                                1. Click the New button
                                to create a blank workbook.
                                2. Click Data, Get External
                                Data, and select New Web
                                Query.




                                                       47
Task 7: To Create a Web Query

                     3. The New Web Query dialog box will
                     appear. Type
                     http://www.djfpc.com/markets.html
                     as the Web address in Step 1, check the
                     option to view the entire page in Step2, and
                     select the option to display full HTML
                     formatting in Step 3. When the settings
                     match the Figure, click OK.




                                                            48
Task 7: To Create a Web Query




                                49
Task 7: To Create a Web Query




                                50
Task 4: To Modify a Word Mail Merge Main Document
(cont.)


                           Once the Word file is saved …
                           its associated merged file must
                           be placed on the right drive
                           properly, otherwise, a warning
                           message will be displayed.




                                                         51
Solution- MS Query and OLE




                             52
Solution – OLE in PowerPoint




                           53
Solution – Web Queries




                         54
Project 10

  Sharing Excel 2000 Data with Other
   Microsoft Office 2000 Applications

				
DOCUMENT INFO
Description: 401K Calculator document sample