CAP1749 MICROSOFT EXCEL COURSE PROCEDURE

CAP1749 MICROSOFT EXCEL

3 credit hours

Student Level:

This course is open to students on the college level in either the freshman or the sophomore year and to area high school vocational students.

Catalog Description:

CAP1749- Microsoft Excel (3 hrs.)

This course provides students with an understanding and use of spreadsheet software in a realistic business environment. Topics include: creating and formatting a workbook; working with formulas and functions; creating charts, tables, and PivotTables; managing multiple workbooks; developing an Excel application; consolidating data from various sources; and collaborating on a shared workbook.  This course may be used to prepare for a MOS Excel certification exam.

Course Classification:

Lecture

Prerequisites:

None

Controlling Purpose:

This course provides students with an understanding and use of spreadsheet software in a realistic business environment. Topics include: creating and formatting a workbook; working with formulas and functions; creating charts, tables, and PivotTables; managing multiple workbooks; developing an Excel application; consolidating data from various sources; and collaborating on a shared workbook.  This course may be used to prepare for a MOS Excel certification exam.

Learner Outcomes:

Upon completion of the course, the student will be able to:

1. Demonstrate the ability to create, edit, and print workbook files.
2. Apply formatting features and styles.
3. Calculate data with formulas and functions.
4. Analyze and chart financial data.
5. Work with Excel tables, PivotTables, and PivotCharts.
6. Manage multiple worksheets and workbooks and collaborate on a shared workbook.
7. Develop an Excel application.
8. Work with advanced functions, perform What-If Analyses, and explore financial tools and functions.
9. Connect to external data.

Unit Outcomes for Criterion Based Evaluation:

The following defines the minimum core content not including the final examination period. Instructors may add other content as time allows.

UNIT 1:  Excel Basics

Outcome:  The student will properly use the basic Microsoft Excel features.

• Open, close, and navigate through a workbook and worksheet
• Select cells and ranges; enter text, dates, and numbers; resize columns and rows; insert, rename, and move worksheets
• Plan and create a workbook
• Enter formulas and basic functions and copy and paste formulas
• Insert and delete rows, columns, and ranges
• Apply various cell and range formatting
• Prepare a workbook for printing and save a workbook with a new filename

UNIT 2:  Formulas and Functions

Outcome:  The student will calculate data using various formulas and functions.

• Translate an equation into an Excel formula and understand function syntax
• Utilize the Quick Analysis tool to enter formulas and functions
• Use the AutoFill tool to enter formulas and data and complete a series
• Enter functions with the Insert Function dialog box
• Change cell references between relative and absolute
• Properly utilize the following functions:  TODAY, WORKDAY, COUNT, COUNTA, IF, VLOOKUP
• Perform what-if analysis using trial and error and Goal Seek

UNIT 3:  Analyzing and Charting Financial Data

Outcome:  The student will analyze financial data and create and format various chart types.

• Use the PMT function to calculate a loan payment
• Create the following types of charts:  embedded pie, clustered column, stacked column, line, and combination
• Add and format various chart features:  styles, data labels, legend.
• Add sparklines to a worksheet
• Modify the chart’s data source
• Format cells with data bars
• Insert a watermark

UNIT 4:  Excel Tables, PivotTables, and PivotCharts

Outcome: The student will create tables, PivotTables, and PivotCharts.

• Plan, create, rename, and format an Excel table
• Add, edit, and delete records in an Excel table and sort data
• Freeze rows and columns, and split a worksheet into two panes
• Filter data using filter buttons, and filter an Excel table with a slicer
• Insert a Total row in an Excel table, and insert subtotals into a range of data
• Use the Outline buttons to show/hide details
• Create and modify a PivotTable
• Apply PivotTable styles and formatting
• Filter a PivotTable, and insert a slicer to filter a PivotTable
• Create a PivotChart

UNIT 5:  Managing Multiple Worksheets and Workbooks

Outcome: The student will work with multiple worksheets and workbooks, using a variety of methods.

• Create a worksheet group, and format and edit multiple worksheets at once
• Create cell references to other worksheets, and consolidate information from multiple worksheets using 3-D references
• Create a link to data in another workbook, and create a workbook reference
• Insert a hyperlink in a cell
• Create a workbook based on an existing template, and create a custom workbook template

UNIT 6:  Developing an Excel Application

Outcome: The student will create an application and work with macros.

• Create an application
• Create, edit, and delete defined names for cells and ranges; paste a list of defined names as documentation; use defined names in formulas; and add defined names to existing formulas
• Create validation rules for data entry
• Protect the contents of worksheets and workbooks
• Add, edit, and delete comments
• Create and run a macro; edit a macro using the Visual Basic Editor; assign a macro to a keyboard shortcut and a button; and save and open a workbook in macro-enabled format

UNIT 7:  Advanced Functions

Outcome: The student will work with various advanced functions and use conditional formatting.

• Properly utilize the following advanced functions:  IF, AND, OR, VLOOKUP, HLOOKUP, IFERROR
• Use structured references in formulas
• Nest the IF function
• Use conditional formatting to highlight duplicate values
• Summarize data using the COUNTIF, SUMIF, and AVERAGEIF functions

UNIT 8:  Financial Tools and Functions and What-If Analyses

Outcome: The student will explore various financial tools and functions and perform what-if analyses.

• Work with financial functions to analyze loans and investments, create an amortization schedule, and calculate a conditional sum.
• Interpolate and extrapolate a series of values.
• Calculate a depreciation schedule.
• Determine a payback period, calculate a net present value and an internal rate of return, and trace a formula error to its source.
• Explore the principles of cost-volume-profit relationships.
• Create a one-variable data table and a two-variable data table.
• Create and apply different Excel scenarios with Scenario Manager, generate a scenario summary report, and generate a scenario PivotTable report.
• Explore the principles of a product mix.
• Run Solver to calculate optimal solutions, create and apply constraints to a Solver model, and save and load a Solver model.

UNIT 9:  External Data and Shared Workbooks

Outcome: The student will connect to external data and will collaborate on a shared workbook.

• Import data from a text file, work with connections and external data ranges, and define a trusted location
• Understand databases and queries, and use the Query Wizard to import data from several tables
• Import tables from Access for use with a PivotTable, and manage table relationships with PowerPivot
• Create a web query
• Retrieve financial data using an Office app
• Access data from an XML document, and work with XML data maps
• Share a workbook with multiple users, track changes made to a workbook, and accept and reject workbook edits
• Merge multiple workbooks into a single file
• Save and share workbooks on the cloud
• Set workbook properties and tags, encrypt a document file, mark a workbook as final, and save a workbook as a PDF file
• Link and embed an Office document
• Customize the Excel working environment

Projects Required:

Students will complete the required assignments.

Textbook:

Contact Bookstore for current textbook.

Materials/Equipment Required:

Computers, printers, and software

Attendance Policy:

Students should adhere to the attendance policy outlined by the instructor in the course syllabus.

Grading Policy:

The grading policy will be outlined by the instructor in the course syllabus.

Maximum class size:

Based on classroom occupancy

Course Timeframe:

The U.S. Department of Education, Higher Learning Commission and the Kansas Board of Regents define credit hour and have specific regulations that the college must follow when developing, teaching and assessing the educational aspects of the college. A credit hour is an amount of work represented in intended learning outcomes and verified by evidence of student achievement that is an institutionally-established equivalency that reasonably approximates not less than one hour of classroom or direct faculty instruction and a minimum of two hours of out-of-class student work for approximately fifteen weeks for one semester hour of credit or an equivalent amount of work over a different amount of time. The number of semester hours of credit allowed for each distance education or blended hybrid courses shall be assigned by the college based on the amount of time needed to achieve the same course outcomes in a purely face-to-face format.

Refer to the following policies:

402.00 Academic Code of Conduct

263.00 Student Appeal of Course Grades

403.00 Student Code of Conduct

Disability Services Program: 

Cowley College, in recognition of state and federal laws, will accommodate a student with a documented disability. If a student has a disability, which may impact work in this class which requires accommodations, contact the Disability Services Coordinator.

DISCLAIMER: THIS INFORMATION IS SUBJECT TO CHANGE. FOR THE OFFICIAL COURSE PROCEDURE CONTACT ACADEMIC AFFAIRS.