Power BI
1. Overview of Power BI
● Key Components and Their Roles:
o Power BI Desktop: A desktop application for building reports and dashboards.
o Power BI Service: A cloud-based service for sharing, collaborating, and managing reports.
o Power BI Mobile: An app for viewing reports and dashboards on mobile devices.
o Power BI Gateway: Facilitates secure data transfer between on-premises data sources and
Power BI Service.
o Power BI Report Server: An on-premises solution for hosting and managing Power BI reports.
● Installing and Setting Up Power BI Desktop:
o Download from the official Microsoft Power BI website.
o Follow installation prompts and sign in with a Microsoft account if required.
2. Power BI Desktop Basics
● Connecting to Data Sources:
o Supports various sources: Excel, SQL Server, SharePoint, web services, and more.
o Use the "Get Data" option to connect to data sources.
● Transforming and Cleaning Data with Power Query:
o Use Power Query Editor to shape and clean data before loading.
o Perform operations like removing duplicates, filtering, and renaming columns.
● Data Loading and Data Model Basics:
o Load cleaned data into the Power BI model for reporting.
o Understand the basics of data modeling, including relationships and data types.
3. Data Modeling in Power BI
● Understanding Normalized and Denormalized Data:
o Normalized Data: Data is structured to reduce redundancy, typically across multiple related
tables.
o Denormalized Data: Combines related data into a single table for simplicity in reporting.
● Creating Relationships:
o Define relationships between fact tables (transactional data) and dimension tables (contextual
data).
● Creating Calculated Columns and Measures with DAX:
o DAX (Data Analysis Expressions): A formula language used to create custom calculations.
o Calculated Columns: New columns added to a table based on existing data.
o Measures: Aggregated calculations that respond to filters in reports.
4. Building Reports in Power BI Desktop
● Creating Visuals:
o Use built-in visuals such as tables, charts, and maps to represent data visually.
● Formatting and Customizing Visuals:
o Apply formatting options for better readability and aesthetics.
o Customize visual properties such as colors, labels, and legends.
● Working with Slicers and Filters:
o Slicers allow users to filter data interactively.
o Filters can be applied at the visual, page, or report level.
● Introduction to Themes and Report Design Best Practices:
o Apply consistent themes for visual coherence.
o Follow best practices for layout and design to enhance user experience.
5. Power BI Service Overview
● Introduction to Power BI Service:
o A cloud platform for sharing and collaborating on reports and dashboards.
● Uploading and Sharing Reports:
o Publish reports from Power BI Desktop to the Power BI Service.
o Share reports with colleagues or stakeholders via email or direct links.
● Creating Dashboards:
o Combine visuals from multiple reports into a single dashboard for an overview of key
metrics.
6. Advanced Data Transformations with Power Query
● Importing, Summarizing, Cleaning Data:
o Use Power Query to import data and summarize it for analysis.
● Removing Unwanted Rows/Columns:
o Filter out unnecessary data during the transformation process.
● Dates, Numbers, Text Transformations:
o Transform data types and formats to fit analysis needs.
● Merging Queries and Appending Queries:
o Combine multiple queries to create a unified data set.
● Unpivot/Pivot Columns:
o Reshape data by converting columns into rows and vice versa.
● Group By, Conditional Columns, Custom Columns:
o Summarize data using the Group By feature.
o Create conditional columns based on rules.
7. Advanced Data Modeling and DAX
● Time Intelligence Functions:
o Use functions like MTD (Month-To-Date), QTD (Quarter-To-Date), and YTD (Year-To-Date)
for time-based calculations.
● Advanced DAX Calculations:
o Utilize various functions such as CALCULATE, FILTER, and iterators for complex analyses.
● Handling Errors and Exceptions in DAX:
o Implement error handling to manage potential issues in calculations.
8. Advanced Visualizations and Other Tools
● Custom Visuals and Marketplace:
o Explore and use custom visuals available in the Power BI marketplace.
● Advanced Formatting and Design Techniques:
o Apply advanced formatting options for enhanced visual appeal.
● Bookmarks:
o Create bookmarks to save specific views or selections for easy access.
● Field Parameters and Conditional Formatting:
o Use field parameters for dynamic reporting and conditional formatting for visual cues.
● Drill Through and Tooltips:
o Enable drill-through features to explore data in more detail.
o Use tooltips to display additional information on visuals.
9. Power BI Service Advanced Features
● Publish a Report on Power BI Service:
o Upload reports for broader access and sharing capabilities.
● Mobile Layout:
o Optimize reports for mobile viewing to ensure accessibility.
● Setting Up Row Level Security (RLS):
o Implement RLS to restrict data access based on user roles.
● Adding Comments to a Power BI Report:
o Facilitate collaboration by allowing comments directly on reports.
● Use Q&A on a Dashboard/Report:
o Utilize the Q&A feature to interactively query data using natural language.
PYTHON
1. Python and Its Libraries / Packages
● Python: A versatile programming language widely used in data analysis, machine learning, and
automation due to its readability and ease of use.
● Key Libraries:
o NumPy: A library for numerical computations. It provides support for arrays, matrices,
and a range of mathematical functions.
▪ Key Features: Fast array processing, mathematical functions, linear algebra
operations.
o Pandas: A library for data manipulation and analysis, particularly suited for structured
data (like tables).
▪ Key Features: DataFrames for handling data, easy data manipulation, powerful
data aggregation and group operations.
2. Python for Descriptive and Statistical Analysis of Business Data
● Mean (Average): The sum of all data points divided by the number of points.
● Median: The middle value in a sorted dataset.
● Quartiles: Values that divide the data into four equal parts.
o Q1 (First Quartile): 25th percentile.
o Q2 (Second Quartile): Median (50th percentile).
o Q3 (Third Quartile): 75th percentile.
● Maximum and Minimum: The highest and lowest values in the dataset.
● Range: Difference between maximum and minimum values.
● Variance: Measure of data dispersion; how far data points are from the mean.
● Standard Deviation: Square root of variance, indicating the amount of variation in a dataset.
3. Plots, Graphs, Tables Using Python Libraries
● Matplotlib: A library for creating static, animated, and interactive visualizations in Python.
o Key Plots: Line graphs, bar charts, histograms, scatter plots.
● Seaborn: Built on Matplotlib, it provides a high-level interface for drawing attractive statistical
graphics.
● Pandas Visualization: Direct plotting methods in Pandas DataFrames for quick data visualization.
4. Correlation and Hypothesis Testing
● Correlation: A statistical measure that describes the strength and direction of a relationship
between two variables (e.g., Pearson correlation coefficient).
● Hypothesis Testing: A method to determine if there is enough statistical evidence in a sample to
infer that a certain condition holds true for the entire population.
o Common tests include t-tests and chi-squared tests.
5. Gartner’s Model of Analytics
● Descriptive Analytics: Analyzes past data to understand what happened.
● Diagnostic Analytics: Examines past performance to determine why something happened.
● Predictive Analytics: Uses statistical models and machine learning techniques to forecast future
outcomes.
● Prescriptive Analytics: Provides recommendations for actions based on data analysis.
6. Predictive Modeling to Analyze Financial Statements
● Regression Analysis: A statistical method for estimating the relationships among variables.
● Clustering: Groups similar data points together based on characteristics, useful for market
segmentation.
● Classification Analysis: Assigns data points to predefined categories (e.g., classifying loan
applications as approved or denied).
7. Python in Audit, Assurance, and Related Services
● Evaluating Internal Controls: Using Python scripts to analyze transaction data for compliance
and anomalies.
● Gathering Evidence: Automating data collection processes for audits, ensuring comprehensive
data analysis
8. Manipulate and Visualize Data with Python Libraries
● Data Manipulation: Using Pandas for cleaning, transforming, and reshaping data.
● Data Visualization: Creating informative graphics using Matplotlib and Seaborn to communicate
findings effectively.
9. Data Extract, Transform, and Load (ETL) Using Python
● ETL Process:
o Extract: Pulling data from various sources (databases, APIs).
o Transform: Cleaning and structuring data for analysis.
o Load: Storing the transformed data in a target database or data warehouse.
● Libraries: pandas, sqlalchemy, pyodbc.
10. Market Basket Analysis (MBA)
● A technique used to understand the purchase behavior of customers by identifying products that
are frequently bought together.
● Association Rules: Techniques like the Apriori algorithm to find associations between items.
11. Data Analytics Cases
● Consumer Demand: Analyzing trends and patterns in consumer purchases to forecast demand.
● Pricing and Revenues: Using historical data to determine optimal pricing strategies.
● Customer Value, Segmentation, and Share of Wallet: Understanding different customer
segments and their purchasing behaviors to maximize revenue.
● Product Profitability and Channel Optimization: Analyzing the profitability of different products
across various sales channels.
● Supply Chain Optimization: Using data analytics to improve supply chain efficiency and reduce
costs.
● Predictive Maintenance: Forecasting when equipment will fail to minimize downtime and repair
costs.
● Operational Efficiency: Analyzing processes to identify areas for improvement and cost savings.
● Fraud and Risk Assessment: Detecting anomalies and fraudulent activities in financial
transactions.
● Business Portfolio Assessment: Evaluating the performance of different business units or
products.
● Business Valuation Metrics: Using financial data to determine the value of a business.
Artificial Intelligence for Predictive and Prescriptive Analytics
1. Understand Artificial Intelligence (AI) and Common AI Algorithms
● Artificial Intelligence (AI): The simulation of human intelligence processes by machines,
especially computer systems, to perform tasks such as learning, reasoning, and self-correction.
● Machine Learning (ML): A subset of AI that focuses on building systems that learn from and
make decisions based on data without being explicitly programmed for specific tasks.
● Key Categories: * Predictive Analytics: Uses historical data and ML algorithms to identify the
likelihood of future outcomes (e.g., forecasting stock prices).
○ Prescriptive Analytics: Suggests specific courses of action to reach a desired goal,
utilizing optimization and simulation algorithms.
2. Supervised Machine Learning: Forecasting and Classification
Supervised learning uses labeled datasets to train algorithms to classify data or predict outcomes
accurately.
● Linear Regression: * Concept: Models the relationship between a dependent variable and one
or more independent variables using a linear equation.
○ Financial Use Case: Forecasting future revenues based on historical sales and marketing
spend.
● Logistic Regression:
○ Concept: Used for binary classification; it predicts the probability that an instance
belongs to a specific category (0 or 1).
○ Financial Use Case: Credit scoring—classifying a loan applicant as "Default" or
"Non-Default."
● Decision Trees:
○ Concept: A flowchart-like structure where internal nodes represent "tests" on attributes
and branches represent the outcomes.
○ Financial Use Case: Insurance underwriting—determining policy approval based on a
series of risk factors.
3. Unsupervised Machine Learning: Clustering and Behavior Analysis
Unsupervised learning analyzes and clusters unlabeled datasets to discover hidden patterns or data
groupings.
● K-Means Clustering:
○ Concept: Partitions data into K distinct clusters based on feature similarity.
○ Use Case: Market Segmentation—grouping customers with similar spending habits to
design targeted marketing campaigns.
● Hierarchical Clustering:
○ Concept: Builds a tree-like hierarchy of clusters (Dendrogram) either by merging small
clusters or splitting large ones.
○ Use Case: Fraud Detection—identifying "outlier" clusters of transactions that deviate
significantly from normal customer behavior.
4. Generative AI Tools and Python Code Generation
Generative AI refers to models that can create new content, including text, images, and code, based on
their training data.
● Key Tools: ChatGPT (OpenAI), Gemini (Google), and Copilot (Microsoft/GitHub).
● Financial Analysis: Automating the summarization of quarterly earnings reports or performing
sentiment analysis on financial news.
● Content Creation: Drafted management discussion and analysis (MD&A) sections for financial
statements.
● Code Generation: Using AI to write, debug, and optimize Python scripts for ETL processes, data
cleaning, or complex DAX measures.
5. Introduction to Deep Learning and Advanced Algorithms
Deep Learning is a subset of ML based on Artificial Neural Networks with multiple layers (hence "deep")
that mimic the human brain.
● Neurons: The basic unit of a neural network that receives inputs, processes them with a
mathematical function (activation function), and passes the output to the next layer.
● Key Deep Learning Algorithms:
○ Convolutional Neural Networks (CNNs): Primarily used for image and pattern
recognition.
■ Use Case: Automating document verification (KYC) by analyzing IDs and
signatures in audit and compliance.
○ Long Short-Term Memory (LSTM): A type of Recurrent Neural Network (RNN) designed
to recognize patterns in sequences of data (time-series).
■ Use Case: High-frequency trading and complex financial time-series forecasting
where long-term dependencies are crucial.
○ Generative Adversarial Networks (GANs): Consist of two networks (Generator and
Discriminator) that compete to create realistic synthetic data.
■ Use Case: Generating synthetic financial data to train models without
compromising sensitive client privacy (Data Privacy & Risk).
6. AI Use Cases in Audit, Finance, and Risk
● Audit & Assurance: AI algorithms can scan 100% of transactions for anomalies, replacing
traditional sample-based auditing.
● Compliance: Natural Language Processing (NLP) models monitor communication for "red flags"
or insider trading indicators.
● Risk Management: Predictive models evaluate geopolitical and market volatility to adjust
business portfolios in real-time.
● Operational Efficiency: AI-driven robotic process automation (RPA) handles repetitive data entry
and reconciliation tasks, reducing human error.
Power Automate
Power Automate is a tool from Microsoft that helps people automate repetitive tasks and processes
without needing to write any code. Think of it as a digital assistant that can perform tasks for you based
on rules you set up. It’s part of the Microsoft Power Platform, which also includes tools for data analysis
(Power BI) and app creation (Power Apps).
Why Use Power Automate?
1. Save Time: By automating repetitive tasks, you can free up time to focus on more important
work, like analyzing financial reports or developing strategies.
2. Reduce Errors: Manual tasks can lead to mistakes. Automation helps ensure that processes are
carried out consistently and accurately.
3. Increase Efficiency: Automation can speed up processes, allowing for quicker responses to
changes and improved productivity.
How Does Power Automate Work?
Power Automate works by creating workflows. A workflow is a sequence of steps that you set up to
perform a specific task. Here’s a simple breakdown of how workflows are structured:
1. Triggers: A trigger is an event that starts the workflow. For example, a trigger could be receiving
an email or a new file being added to a folder. When the trigger happens, the workflow begins.
2. Actions: After the trigger, you define the actions that the workflow will take. For example, if the
trigger is receiving an email, the actions could include:
o Saving the email to a specific folder.
o Sending a notification to your phone.
o Creating a task in a project management tool.
Common Use Cases for Finance Students
1. Expense Tracking: Automatically collect expenses from emails or forms and store them in a
spreadsheet. This makes it easier to manage and analyze spending.
2. Data Updates: If you receive financial reports regularly, you can set up a workflow to
automatically save these reports to a specific folder or update a database.
3. Notifications for Approvals: If your team needs to approve budgets or expenses, you can
automate notifications to remind the approvers when a new request is submitted.
4. Monthly Reporting: Set up a monthly report that compiles data from various sources (like Excel
sheets or databases) and sends it to stakeholders automatically.
Getting Started with Power Automate
1. Sign Up: You need a Microsoft account (like an Outlook or Office 365 account) to access Power
Automate.
2. Explore Templates: Power Automate provides many pre-built templates that you can use. For
example, there might be templates for automatically saving email attachments to OneDrive or
notifying you of new sales leads.
3. Create a Workflow:
o Choose a Trigger: Start by selecting what will kick off your workflow (e.g., receiving an
email).
o Define Actions: Decide what you want to happen when the trigger occurs (e.g., save the
email to a folder).
o Test Your Workflow: Once set up, you can test your workflow to ensure it works as
expected.
4. Monitor and Adjust: After using a workflow, you can monitor its performance and make
adjustments if necessary.
1. Flows
Flows are the core components of Power Automate. They automate tasks and processes. Here’s how
flows can be set up for specific scenarios:
● Sending an Email:
o You can create a flow that automatically sends an email when a specific event occurs,
like receiving a new document or a report being generated.
● Responding to an Email:
o You can set up a flow to automatically reply to emails based on certain keywords or
criteria. For example, if you receive an email about budget approval, the flow could send
a standard response acknowledging receipt.
● Creating MS Excel Files from Documents Saved in MS Word:
o You can create a flow that triggers when a Word document is saved in a specific location,
and then it extracts data from that document to create an Excel file automatically. This is
useful for organizing financial reports or data entries.
2. Troubleshooting Flows
If a flow doesn’t work as intended, Power Automate provides tools for troubleshooting:
● Error Messages: When a flow fails, Power Automate gives specific error messages that can help
you understand what went wrong.
● Run History: You can check the run history of your flows to see the details of each execution,
which helps identify where the issue occurred.
● Testing: You can manually run a flow to test it and see if it behaves as expected.
3. Recurring Flows
Recurring flows are scheduled to run at specific intervals. For example:
● You can set a flow to run every month to generate financial reports, send reminders for budget
reviews, or collect weekly expense data.
4. Incoming Emails
You can create flows that act upon incoming emails:
● For example, a flow can be set up to trigger whenever you receive a specific type of email (like
invoices), which then processes the email and extracts relevant data for tracking expenses or
creating records.
5. Variables
Variables in Power Automate are used to store information that you can use throughout your flow:
● Types of Variables:
o String: For text data (e.g., names, email addresses).
o Number: For numerical data (e.g., amounts, quantities).
o Boolean: For true/false values (e.g., approval status).
● Variable Manipulation: You can create, update, and use variables within your flows to hold and
manipulate data as needed.
6. Data Types, Input and Output Variables, Variable Manipulation, and Sensitive Variables
● Data Types: As mentioned, variables can be strings, numbers, booleans, etc. Each type serves a
different purpose depending on the data you are working with.
● Input and Output Variables:
o Input Variables are used to accept data when a flow starts.
o Output Variables can be used to send data out of a flow after it runs.
● Sensitive Variables: You can mark certain variables as sensitive (like passwords or confidential
information) to protect them within your flow.
7. Connectors
Connectors are pre-built integrations that allow Power Automate to communicate with other apps and
services:
● Types of Connectors:
o Standard Connectors: Available to all users (e.g., Office 365, Twitter).
o Premium Connectors: Require a subscription (e.g., Salesforce, certain databases).
● Connectors make it easy to integrate and automate tasks across various applications, such as
saving email attachments to OneDrive or updating records in a CRM.
8. UI Flows
UI Flows are a feature in Power Automate that allows you to automate tasks on your computer:
● Desktop Automation: UI Flows can interact with software applications on your desktop,
mimicking user actions like clicking buttons, entering data, and navigating interfaces.
● This is particularly useful for automating processes in applications that don’t have APIs
(Application Programming Interfaces) for integration.
Conclusion
Power Automate is a powerful tool that can help finance professionals streamline their workflows,
automate repetitive tasks, and enhance efficiency. By understanding these concepts—flows,
troubleshooting, recurring tasks, handling incoming emails, using variables, connectors, and UI
flows—you can leverage automation to improve your daily operations significantly.