Description:

Upon successful completion of this course, the student should be able to demonstrate competencies in using advanced formatting techniques, advanced features and advanced functions of Microsoft Excel. The following topics will be covered: working with templates, workbooks and lists; using Excel's analysis tools; managing and auditing worksheets; collaborating with workgroups; creating and editing macros; and importing and exporting data. 1 hr. lecture/wk.

Supplies: Refer to the instructor's course syllabus for details about any supplies that may be required.

Textbook(s): NONE

Course Fees: NONE

Course Objectives:

Upon successful completion of this course the student should be able to:

1. Create a projected budget worksheet.
2. Use a loan amortization report to compare various loan strengths and weaknesses.
3. Create a payroll worksheet, insert formulas and functions as required, format the worksheet and print it.
4. Create, format, and print a monthly travel expense report.
5. Create, format, develop formulas and functions, and print a worksheet designed to track student grades.
6. Create a regional sales analysis that summarizes sales data using the mapping features of the program.
7. Create a worksheet to track inventory, and establish methods for identifying and ordering parts in low supply.
8. Create comparison reports on the advantages and disadvantages of using specific service providers and include graphics and charts as necessary.
9. Create a sales forecast, including the best, worst, and average scenarios.
10. Create a sales report that also calculates varying commissions.
11. Link multiple worksheets and workbooks using formulas and functions.
12. Confirm the accuracy of the formulas and functions used.
13. Troubleshoot a worksheet to determine solutions to existing problems.
14. Identify multiple options for acquiring additional help.

Content Outline & Competencies:

I. Using Data Entry Shortcuts
   A. Cut, copy, and paste text.
   B. Customize AutoFill options.

II. Understanding Formulas and Functions
   A. Create formulas and functions using various methods.
   B. Use formula and function syntax.
   C. Control order of operation.
   D. Specify absolute versus relative references.

III. Creating Charts
   A. Pick the right chart for the job.
   B. Use the Chart Wizard.
   C. Edit chart contents.
   D. Add additional data to an existing chart.
   E. Add graphics as indicators.

IV. Accessing Advanced Printing Techniques
   A. Identify print areas.
   B. Print nonconsecutive areas.
   C. Use the View Manager.
   D. Create and save reports.
   E. Create, save, and print ranges.

V. Confirming the Accuracy of the Worksheet Results
   A. Review the order of operation in formulas and functions.
   B. Key results of formulas and functions to confirm the operations.
   C. Use the auditing toolbar.
   D. Use multiple sets of sample data to confirm accuracy.
   E. Understand the three dangerous spreadsheet assumptions: data entry
is complete and correct, spreadsheet designer checked for flaws, and
reports are accurate.
 
VI. Working with Multiple Worksheets and Workbooks
   A. Evaluate the needs of multiple users.
   B. Control data entry access in worksheets and workbooks.
   C. Work with grouped sheets.
   D. Create formulas and functions that access other worksheets and
workbooks.
   E. Create and update summary sheets.
   F. Save the workspace as a file.
   G. Link workbooks.

VII. Using Macros, Styles, and Templates to Increase Efficiency,
Effectiveness, and Accuracy
   A. Record, edit, play, and delete macros.
   B. Access existing templates to decrease development time.
   C. Save modified templates.
   D. Create, assign, edit, and delete styles.
   E. Add macros to menus and toolbars.

VIII. Using Excel Database Features
   A. Input raw data.
   B. Create and use a data input form.
   C. Sort information.
   D. Create filters to select specific information.
   E. Create queries to extract data.

IX. Troubleshooting Worksheets
   A. Access the auditing toolbar.
   B. Utilize validity tools.
   C. Correct circular references.
   D. Troubleshoot error messages.
   E. Check for common errors.

X. Getting Additional Help
   A. Find answers to frequently asked questions.
   B. Find spreadsheet related publications.
   C. Access the Internet.

XI. Previewing Advanced Features
   A. Create automatic totals.
   B. Group data.
   C. Add outlines to worksheets.
   D. Use maps in a worksheet.
   E. Annotate cells.
   F. Use the IF function to control data properties.
   G. Create and use binders.
   H. Use pivot tables to analyze data from multiple points of view.
   I. Find answers using Goal Seeking.
   J. Use Solver to create multiple possible conditions.

Methods of Evaluation of Competencies:

Evaluation of student mastery of course competencies will be accomplished using the following methods:

Tests                    30% of grade
In-Class Exercises       20% of grade
Projects                 50%  of grade
  TOTAL                 100%

   Grade Criteria:
   90 - 100% = A
   80 -  89% = B
   70 -  79% = C
   60 -  69% = D
    0 -  59% = F

Caveats: NONE

Disabilities:

If you are a student with a disability, and if you will be requesting accommodations, it is your responsibility to contact Access Services. Access Services will recommend any appropriate accommodations to your professor and his/her director. The professor and director will identify for you which accommodations will be arranged.

JCCC provides a range of services to allow persons with disabilities to participate in educational programs and activities. If you desire support services, contact the office of Access Services for Students With Disabilities (913) 469-8500, ext. 3521 or TDD (913) 469-3885. The Access Services office is located in the Success Center on the second floor of the Student Center.