ITCS 2200 - Data Analysis

Credit Hours: 3.00

OBJECTIVES:

1. Subtotal, Group, and Ungroup data
2. Create, modify, filter and slice PivotTables and Pivot Charts
3. Create a Data Model

OUTCOME 2: Upon completion of this course, students will be able to demonstrate how to use decision-making tools in Excel.

OBJECTIVES:

1. Create one and two variable Data Tables
2. Use Scenario Manager
3. Create an optimization model using Solver

OUTCOME 3: Upon completion of this course, students will be able to manipulate data using date, logical, lookup, database, and financial functions.

OBJECTIVES:

1. Use Date, Nested Logical, Financial, and Advanced Lookup functions
2. Create a Loan Amortization Table

OUTCOME 4: Upon completion of this course, students will be able to employ statistical functions to analyze data for decision making.

 OBJECTIVES:

1. Use Conditional Math Functions
2. Calculate Relative Standings with Statistical Functions
3. Measure Central Tendency
4. Use the Analysis Toolpack
5. Create a Forecast sheet to identify trends based on historical data
6. Perform Analysis of Variance

OUTCOME 5: Upon completion of this course, students will be able to demonstrate data management within workbooks.

OBJECTIVES:

1. Manage Multiple Worksheets
2. Validate Data
3. Audit Worksheets

OUTCOME 6: Upon completion of this course, students will be able to manage data from external sources.

OBJECTIVES:

1. Import data from external sources
2. Import XML Data into Excel
3. Manipulate Text with functions

OUTCOME 7: Upon completion of this course, students will be able to create macros to simplify data analysis in Excel.

OBJECTIVES:

1. Record a macro
2. Create a Sub Procedure
3. Edit a Macro in the Visual Basic Editor
4. Use visual representation techniques that increase the understanding of complex data and models
5. Analyze data findings and identify appropriate visualization approach
6. Provide insight by creating charts for various datasets

OUTCOME 8: Upon completion of this course, students will be able to use data validation features to improve data entry in databases.

OBJECTIVES:

1. Establish Data Validation rules
2. Create input masks
3. Create Lookup Fields

OUTCOME 9:  Upon completion of this course, students will be able to perform data analysis using advanced database queries.

OBJECTIVES:

1. Create parameter queries and reports
2. Use advanced functions to query a database table

OUTCOME 10: Upon completion of this course, students will be able to use action queries to update, add, and delete data and create queries for specialized purposes.

OBJECTIVES:

1. Create queries to Update, Append and Delete records in a table
2. Summarize data with a Crosstab query
3. Find duplicate and unmatched records using queries

OUTCOME 11: Upon completion of this course, students will be able to use SQL to manage data within a database.

OBJECTIVES:

1. Create basic macros
2. Create simple SQL queries

OUTCOME 12: Upon completion of this course, students will be able to create appropriately formatted business documents to present analysis findings.

OBJECTIVES:

1. Use Word to compose business documents
2. Use PowerPoint to present and defend findings

1. Summarizing and Analyzing Data
   1. PivotTables
   2. Data Models
   3. PivotCharts
2. Using Decision Making Tools
   1. One-Variable Data Tables
   2. Two-Variable Data Tables
   3. Goal Seek
   4. Scenario Manager
   5. Solver
3. Specialized Functions
   1. Logical and Lookup functions
   2. Database Functions
   3. Financial Functions
4. Analyzing data using Statistics
   1. Math and Statistical Functions
   2. Descriptive Statistical Functions
   3. Inferential Statistics
5. Managing Data
   1. Import data from external sources
   2. Manipulate Text with functions
   3. Use Power Pivot, Power Query
6. Macros and VBA
   1. Macros
   2. Procedures in VBA
   3. Custom Functions
7. Visual Representation Techniques
   1. Visualization
   2. Advanced Charting
   3. Sparklines
8. Validation and Data Analysis using Databases
   1. Data Validation
   2. Advanced Select Queries
9. Action Queries and Specialized Queries - DML
   1. Action Queries
   2. Specialized Queries
10. SQL and Macro Design
    1. SQL
    2. Macro Design
    3. Data Macros