Business Intelligence Foundations Bundle

Level 1:

1. Introduction to Excel
   • Navigational Elements: Tabs, sheets, ribbon, cells, columns, rows,
   • Basic Interactions: Auto-adjusting row/column size, hiding & unhiding items, adding new columns/rows
   • Cell Selection: Select single cells, multiple cells, ranges, columns/rows, entire sheets
   • Basic Keyboard Shortcuts: For Mac and PC

Gain a strong understanding of how to navigate Excel, including key terminology, keyboard shortcuts, and sheet interactions.

2. Formatting Basics
   • Text Formatting: Bold, italicize, underline, alignment, decimal places
   • Cell Formatting: Merge, fill
   • Clear tool: Clear formatting, contents, or all

Learn formatting best practices and how to apply them in Excel to create presentable worksheets.

3. All About Data
   • Power Query: Import and modify data
   • Types of Data
   • Data Formatting: Number, currency, accounting, dates, etc.
   • Filters
   • Data Types feature: Adding related information

Learn how to use Power Query to import and modify data from external files, the different types of data in Excel, and how to use filters and Data Types to handle data sets.

4. Introduction to Formulas
   • Formula Syntax: Starting formulas, formula rules
   • Cell Referencing: Absolute & relative referencing
   • Formula Operators: Basic operators, manual concatenation, order of operations
   • Function Syntax: Arguments & rules
   • Fill: Fill handle, auto-fill
   • Basic Functions: SUM, PRODUCT, SUMPRODUCT

Gain a fundamental understanding of Excel’s formula and function syntax, including absolute referencing, operators, basic functions such as SUM, PRODUCT, and SUMPRODUCT, as well as how to reuse formulas efficiently with the fill handle.

5. Introduction to Indexes
   • Indexes: Ranges/tables vs text
   • Indexing Functions: INDEX, MATCH, & combining the pair
   • Text Indexing Functions: LEFT, RIGHT, MID, FIND

Develop a strong understanding of indexing in Excel, learning how to use INDEX and MATCH to create a powerful lookup formula and how to apply text indexing functions such as LEFT, MID, RIGHT, and FIND to extract text values.

Level 2:

1. Tables and Named Ranges
   • Managing Tables: Creating, naming, and referencing tables
   • Managing Named Ranges: Creating and referencing named ranges, the Name Manager menu
   • Table Functionalities: Formatting, filtering, deleting duplicates, calculated columns, adding total rows

   Understand the differences and benefits of using tables and named ranges in Excel and learn how to manage them.

2. Statistical Functions
   • Central Tendency Functions: AVERAGE, MEDIAN, MODE
   • Range Functions: MAX, MIN
   • Rounding Functions: ROUND, ROUNDUP, ROUNDDOWN, MROUND

   Learn basic statistical functions to analyze the central tendency and range of your data sets, as well as rounding functions to refine numerical outputs.

3. Data Manipulation
   • Text to Columns
   • Range Management: TRANSPOSE, SORT, UNIQUE
   • Text Management: TEXT, CONCAT, TEXTJOIN, VALUETOTEXT
   • Text Cleanup: LOWER, UPPER, PROPER, TRIM
   • Date Management: DAY, MONTH, YEAR, DATE, EOMONTH

   Develop a strong understanding of Excel’s data manipulation and cleanup tools to prep any workbook for data handling, including text-to-columns, range and text management functions such as UNIQUE and TEXTJOIN, text cleanup functions such as PROPER and TRIM, and date management functions such as EOMONTH and YEAR.

4. Conditionals
   • Recap on Boolean Values
   • Conditional Basics: IF, IFS, AND, OR, NOT
   • Conditional Function Variants: SUMIF(S), COUNTIF(S), AVERAGEIF(S)
   • Count Functions: COUNT, COUNTA, COUNTBLANK
   • Data Validation
   • Conditional Formatting

Learn how to build logic in Excel using basic conditional functions such as IF and AND, conditional aggregation functions such as SUMIF and COUNTIF, how to use data validation to restrict cell values, and how to apply conditional formatting for efficient highlighting.

Level 3:

1. Advanced Searches
   • XLOOKUP
   • VLOOKUP
   • FILTER

Learn how to use advanced search functions such as XLOOKUP, VLOOKUP, and FILTER for data extraction and understand their differences and use cases.

2. Excel Visualization
   • Sparklines: Adding and formatting sparklines
   • Charts in Excel: Insert different chart types, edit elements, apply designs, edit series
   • Gantt Charts: Build automatic custom Gantt charts using:
     • SEQUENCE
     • Date functions
     • Custom conditional formatting
   • Developer Tools: Option buttons

Gain a strong understanding of data visualization in Excel, including adding charts and sparklines, learning how to build custom Gantt charts, and getting familiar with developer tools to insert option buttons.

3. Pivot Tables and Charts
   • PivotTables: Create & design PivotTables
   • PivotCharts: Add & format PivotCharts, including combo charts
   • Dashboards: Move Pivot items to form a cohesive dashboard
   • Slicers: Add and format slicers, report connections

Learn how to use powerful PivotTables and PivotCharts in Excel to derive further insights from your data, including adding slicers and reporting connections to create dynamic dashboards.

Level 1:

1. Introduction to Excel
   • Navigational Elements: Tabs, sheets, ribbon, cells, columns, rows,
   • Basic Interactions: Auto-adjusting row/column size, hiding & unhiding items, adding new columns/rows
   • Cell Selection: Select single cells, multiple cells, ranges, columns/rows, entire sheets
   • Basic Keyboard Shortcuts: For Mac and PC

Gain a strong understanding of how to navigate Excel, including key terminology, keyboard shortcuts, and sheet interactions.

2. Formatting Basics
   • Text Formatting: Bold, italicize, underline, alignment, decimal places
   • Cell Formatting: Merge, fill
   • Clear tool: Clear formatting, contents, or all
   • Format Painter Tool

Learn formatting best practices and how to apply them in Excel to create presentable worksheets, as well as useful tools such as the Format Painter.

3. All About Data
   • Power Query: Import and modify data
   • Types of Data
   • Data Formatting: Number, currency, accounting, dates, etc.
   • Filters
   • Data Types feature: Adding related information

   Learn how to use Power Query to import and modify data from external files, the different types of data in Excel, and how to use filters and Data Types to handle data sets.

4. Introduction to Formulas
   • Formula Syntax: Starting formulas, formula rules
   • Cell Referencing: Absolute & relative referencing
   • Formula Operators: Basic operators, manual concatenation, order of operations
   • Function Syntax: Arguments & rules
   • Fill: Fill handle, auto-fill
   • Basic Functions: SUM, PRODUCT, SUMPRODUCT, TRANSPOSE
   • Financial Topics: Financial statements, pro-forma statements, common size analysis, income measures, stock portfolios

Develop a strong understanding of formula and function syntax in Excel, including absolute referencing, operators, arguments, through preparing financial statements with functions such as SUMPRODUCT and TRANSPOSE.

Level 2:

1. Tables & Indexes
   • Managing Tables: Creating, naming, and referencing tables
   • Managing Named Ranges: Creating and referencing named ranges, the Name Manager menu
   • Table Functionalities: Formatting, filtering, deleting duplicates, calculated columns, adding total rows
   • Indexes: Ranges/tables vs text
   • Indexing Functions: INDEX, MATCH, XMATCH,
   • Text Indexing Functions: LEFT, RIGHT, MID, FIND, LEN

   Gain a strong understanding of tables and named ranges, including their management, use cases, features, and learn how indexing works for both ranges and strings through functions such as INDEX, MATCH, and LEFT.

2. Interest and Amortization
   • Interest Concepts: Simple vs compounding interest, APR, EAR, EPR
   • Interest Functions: EFFECT, NOMINAL
   • Time Value Functions: PV, FV, PMT, RATE, NPER, IPMT, PPMT
   • Amortization Schedules: Create dynamic amortization schedules using:
     • SEQUENCE
     • Time value functions
   • What-If Analysis: Data Table, Goal Seek, Scenario Manager

   Learn how to calculate rates and apply them to time-value calculations by building a dynamic amortization schedule with functions such as SEQUENCE, PV, and EFFECT.

3. Stocks & Statistics
   • Calculating stock returns
   • Central Tendency Functions: AVERAGE, GEOMEAN, STDEV.S,
   • Relationship Functions: COVARIANCE, CORREL
   • Range Functions: MIN, MAX

   Gain an understanding of different statistical measures and functions, applying them to stock returns to analyze performance.

4. Inventory & Optimization
   • Lookup Functions: VLOOKUP, XLOOKUP
   • Optimization Calculations - EOQ: SQRT, ROUNDDOWN
   • Manufacturing COGS: Materials available for use, goods manufactured, cost of goods manufactured, goods available for sale, cost of goods sold
   • Custom Formatting: Applying string formatting to numerical values

Learn the differences between VLOOKUP and XLOOKUP through an inventory management application, additionally calculating optimization metrics and COGS.

Level 3:

1. Data Manipulation:
   • Text to Columns
   • Range Management: TRANSPOSE, SORT, UNIQUE
   • Text Management: TEXT, CONCAT, TEXTJOIN, VALUETOTEXT
   • Text Cleanup: LOWER, UPPER, PROPER, TRIM
   • Date Management: DAY, MONTH, YEAR, DATE, EOMONTH

Gain a strong understanding of data management and manipulation in Excel, learning how to use tools such as Text-to-Columns, date functions, and text and range management functions to prepare data for use.

2. Conditionals:
   • Recap on Boolean Values
   • Conditional Basics: IF, IFS, AND, OR, NOT
   • Receivables Modelling: Build an accounts receivable management system using:
     • OFFSET
     • MATCH
     • Logical functions
     • IFERROR
     • TODAY
   • Conditional Function Variants: SUMIF(S), COUNTIF(S), AVERAGEIF(S)
   • Count Functions: COUNT, COUNTA, COUNTBLANK
   • Data Validation
   • Conditional Formatting

Learn how to build logic in Excel using functions such as IF, SUMIFS, and more–applying complex logic to automate an accounts-receivable management system.

3. Pivot Tables and Charts
   • PivotTables: Create & design PivotTables
   • PivotCharts: Add & format PivotCharts, including combo charts
   • Slicers: Add and format slicers, report connections

Gain a strong understanding of how to build PivotTables, PivotCharts, and connect them all with slicers to create powerful, dynamic dashboards.

Level 1:

1. Understanding the Basics of Power BI
   • Download Guide
   • Understanding Power BI: What is Power BI, use cases in the workplace, differences between Power BI and Tableau
   • Importing Data - Power Query
   • Basic Functions: Ribbon, Power Map, Power Q&A
   • Views: Report, table, model

Get an introduction to Power BI to gain an understanding of its powerful use cases in the workplace–then get started by learning how to import data using Power Query and gain familiarity with Power BI’s user interface.

2. Managing the Relationship
   • Understanding Relationships: What are relationships, use cases and opportunities
   • Managing Relationships: Creating, deleting, editing
   • Cardinality: Many-to-one, one-to-many, one-to-one, many-to-many
   • Types of Tables: Dimensions, facts
   • Cross-Filter Direction: Both, single
   • Relationship Activity: Active, Inactive

Gain a strong understanding of relationships in Power BI, including how to create and manage them, cardinality, cross-filter direction, and toggling activity.

3. Data Formatting and DAX Language
   • Understanding DAX: What is it, how to use it, differences from Excel, DAX behaviour, basic operators
   • Managing Tables: Creating columns, measures
   • Scalar/Aggregation Functions: SUM, COUNTROWS, IF
   • Context/Relationship Management Functions: FILTER, CALCULATE, EARLIER, RELATED
   • Table Manipulation Functions: SELECTCOLUMNS, UNION
   • Context in DAX Formulas: Row, multiple row, query, filter
   • Data formatting
   • Merges: Inline, intermediate

Learn about how to use DAX, a powerful language in Power BI, including differences from Excel, its behaviour, basic operators, and various functions that can be applied to data such as CALCULATE, COUNTROWS, and UNION.

4. Data Visualization
   • Dashboard Basics: Tiles, adding visuals, bookmarks, buttons, slicers
   • Types of Visuals: Filled map, clustered bar chart, card, doughnut chart, waterfall chart, gauge
   • Disconnecting Cards

Learn how to transform data into insightful dashboards in Power BI using a variety of different visuals such as waterfall charts, gauges, and filled maps.

Level 2:

1. Advanced Measures & Calculated Columns
   • Understanding KPIs
   • Date Tables: Create a date table using:
     • GENERATESERIES
     • DATE
     • MONTH
     • YEAR
     • QUARTER
     • FORMAT
   • Text Indexing Functions: LEFT, MID, RIGHT
   • Measures Tables: Create various KPIs stored in a measures table using:
     • CALCULATE
     • IF
     • SAMEPERIODLASTYEAR
     • DIVIDE
     • SUM

Gain a strong understanding of various KPIs used in the workplace and learn how to calculate them with date and measures tables.

2. Visualizing Sales Performance & Insights
   • Custom Canvas Sizes
   • Additional Visual Types: New cards, decomposition trees, line charts
   • Forecasting
   • Top N Filtering
   • Dashboard Best Practices

Learn advanced dashboard methods including additional visual types, forecasting, top N filtering, and overall best practices.

3. Advanced Interactive Dashboards
   • Drillthrough Pages
     • SELECTEDVALUE
   • Report Page Tooltips
   • Sparklines

Learn how to add drillthrough pages and report page tooltips to create layered dashboards that can showcase data at different levels of granularity.

Level 1:

1. Introduction to Tableau
   • Understanding Tableau: What is Tableau, use cases, differences between Tableau and Power BI
   • Tableau Products: Prep Builder, Cloud, Server, Desktop, Desktop Public
   • Installation Guide
   • Basic Navigation: Opening files, workbook types, workbook name, toolbar, side bar & panes, cards & shelves, view, data source tab, sheet tabs & types, status bar

Get an introduction to Tableau, including its use cases, products, file types, and user interface.

2. All About Views
   • Data Types in Tableau
   • Roles in Tableau: Discrete, continuous, measures, dimensions
   • Changing Field Properties
   • Creating Views: Naming sheets, using Show Me, using drag & drop
   • Tab Management: Creating, deleting, duplicating tabs
   • Understanding Aggregation: What is aggregation, changing aggregation type
   • Quick Table Calculations
   • Populating Cards: Marks, pages, filters

Learn the various data types and roles in Tableau and learn how to create basic views using different aggregation types, including Quick Table Calculations.

3. Formatting Basics
   • Toolbar Options: Swap, sort, highlight, mark labels, fix axes, fit
   • Marks Card Formatting
   • Viz Formatting: Titles, field labels & headers
   • Dashboards: Creating dashboards, populating, removing items, adding objects, connecting filters, filtering by view, floating items, layout pane
   • Stories: Creating stories, story points, navigator styles, populating, annotations

Learn the various viz types in Tableau and how to format them through both the toolbar and cards.

4. Creating Calculations
   • Calculation Components: Different types, usage, colour coding
   • Calculation Syntax: Function syntax, basic operators, best practices
   • Calculated Fields: Creating calculated field, calculated field editor, using calculated fields in vizzes
   • Basic Functions: SUM, AVG, COUNT, COUNTD
   • Logical Operators: AND, OR, NOT, IF, THEN, ELSE, END, ELSEIF, CASE, WHEN

Gain an understanding of Tableau’s powerful calculated fields, learning basic syntax and applying aggregation and logical functions and operators.

Level 2:

1. Importing Data
   • Compatible Data Sources
   • Different Import Methods
   • Data Source Tab: Viewing data, managing relationships
   • Cardinality: Many-to-one, one-to-many, one-to-one, many-to-many
   • Referential Integrity
   • Sets: Dynamic vs fixed, creating sets, filtering using sets

Gain an understanding of how to import data into Tableau and how to properly use relationships with correct cardinality and referential integrity for optimized performance.

2. Advanced Charts
   • Dual Axis Charts
   • Synchronized Axis Charts
   • Building Donut Charts
   • Tree Map Charts & Alternative Chart Types
   • Shape Tables

Learn about advanced chart types in Tableau, including how to use dual or synchronized axes and build your own donut charts and shape tables.

3. Creating Maps
   • Creating Maps
   • Simple Maps
   • Symbol Maps
   • Map Formatting

Understand the types of map visuals in Tableau, simple and symbol, and learn how to format them.

Level 3:

1. Advanced Calculated Fields
   • Growth Calculations
   • DATE functions: YEAR, QUARTER, MONTH, DAY, DATE, DATEPART, DATEADD, DATEDIFF, DATETRUNC
   • Parameters: Creating parameters, combining with CASE WHEN statements
   • LOD Expressions: INCLUDE, EXCLUDE, FIXED
   • Range Functions: MIN, MAX

Expand on your calculated field knowledge with date functions, creating parameters, and LOD expressions.

2. Clustering Models
   • Creating Matrices
   • Clusters: Creating clusters, interacting with clusters, cluster groups,
   • Describe Clusters: Between-group sum of squares, within-group sum of squares, total sum of squares, F-statistic, p-values, model sum of squares

Learn how to use the clustering tool with matrices to generate powerful insights on groups, including how to interpret statistical results.

3. Data Analytics
   • What-If Analysis
   • Parameter Controls
   • Advanced Sets
   • Trend Lines
   • Average Lines
   • Forecasting

Gain an understanding of advanced data analytics in Tableau, learning how to conduct What-If Analyses, use parameter controls, forecasting, and how to add trend lines.

4. Set Actions
   • Creating Set Actions
   • Actions Menu
   • Asymmetric Drill-Downs
   • Proportional Brushing

Learn how to use sets through set actions to create advanced, dynamic views and dashboards, including how to apply asymmetric drill-downs and proportional brushing.