| powered by |  |
| powered by | |
|
| Course: CIS214DA First Term: 2021 Fall
Final Term: Current
Final Term: 9999
|
Lec + Lab 3.0 Credit(s) 4.0 Period(s) 4.0 Load
Credit(s) Period(s)
Load
Subject Type: OccupationalLoad Formula: T- Lab Load |
| MCCCD Official Course Competencies | |||
|---|---|---|---|
| 1. Explain the purpose of data analysis and Business Intelligence (BI) in business. (I)
2. Utilize conditional aggregate functions to summarize data. (II) 3. Demonstrate use of Excel Tables for data analysis. (III) 4. Use Lookup and Reference functions to find specific data for analysis. (IV) 5. Employ various Date functions to analyze data. (V) 6. Create PivotTables to perform multidimensional analysis. (VI) 7. Create advanced charts to analyze data and visualize patterns and trends. (VII) 8. Utilize conditional formatting techniques to visualize patterns, trends, and outliers. (VIII) 9. Utilize PowerPivot and Excel`s Data Model. (IX) 10. Apply a Forecasting function using historical data in Excel. (X) 11. Develop an interactive dashboard using a multi-table Excel Data Model. (I-X) | |||
| MCCCD Official Course Competencies must be coordinated with the content outline so that each major point in the outline serves one or more competencies. MCCCD faculty retains authority in determining the pedagogical approach, methodology, content sequencing, and assessment metrics for student work. Please see individual course syllabi for additional information, including specific course requirements. | |||
| MCCCD Official Course Outline | |||
|
I. Data analysis and BI
II. Conditional aggregate functions A. SUMIFS, AVERAGEIFS, COUNTIFS B. Removing duplicate values C. MIN, MAX, SMALL, LARGE D. Nested IFS III. Excel Tables in worksheets A. Benefits 1. Automatic range names for columns 2. Automatic AutoFill 3. Other B. Advanced filtering and sorting C. Calculated Column D. SUBTOTAL function E. IFS function IV. Lookup and Reference functions A. VLOOKUP B. INDEX C. MATCH D. CHOOSE E. Other V. Date Functions A. TODAY, WEEKDAY, DAY, MONTH, YEAR B. DATEDIF, WORKDAY, NETWORKDAYS C. Other VI. PivotTables A. Introduction to multidimensional analysis 1. Tie to conditional aggregates 2. Dimensions, Measures, and Key Performance Indicators (KPI) B. Calculated Fields C. Calculated Items D. Drill down capabilities E. Slicers F. Filters G. Advanced sorting techniques H. Other VII. Advanced charts A. Combo charts B. Histogram charts C. Pareto charts D. Waterfall charts E. Scatter and bubble charts F. Hierarchical charts 1. Treemaps 2. Sunburst G. Sparklines H. PivotCharts I. Other VIII. Conditional formatting techniques A. Cell rules B. Top/bottom rules C. Data bars D. Icon sets E. Cell formulas within cell rules F. Conditional formatting in PivotTables IX. PowerPivot and Excel`s Data Model A. Working with large data sets B. Multi-table Data Model 1. Import data from multiple external sources a. SQL Server Database or Access Database b. CSV file or flat file 2. Create Calendar Table in the Data Model C. Data Analysis Expressions (DAX) functions 1. Create an import query 2. Transform data 3. Establish relationships between fields in various tables 4. Create Calculated Columns 5. Create Measures a. Explicit b. Implicit 6. KPIs a. Use in Data Analysis and BI b. Create on specific measures D. Dashboards from multi-table data model 1. PivotTables 2. PivotCharts 3. Slicers and Filters 4. Conditional formatting 5. Select measures and KPIs X. Forecasting functions in Excel A. Straight line vs. moving average B. Simple vs. multiple Linear Regressions C. Create a forecast worksheet | |||
MCCCD Governing Board Approval Date: April 7, 2020 | |||