--:-- --
↓ Scroll for more

Deck 6

Basics of Data Analysis with Spreadsheets and Power BI

ICT 110: IT for Business

Learning Objectives

By the end of this lecture, you will be able to...
  • ✅ Understand the data analysis process for business decision-making.
  • ✅ Use core spreadsheet functions for financial, operational, and HR analysis.
  • ✅ Differentiate between the roles of spreadsheets and Business Intelligence (BI) tools.
  • ✅ Create basic data visualizations to effectively communicate business insights.

What is Data Analysis in Business?

Data analysis is the process of inspecting, cleaning, transforming, and modeling data with the goal of discovering useful information, informing conclusions, and supporting decision-making.

Why it matters for ALL business functions:

  • 🎯 Moving from gut feelings to data-driven decisions.
  • 🔍 Identifying trends and patterns across the entire business.
  • 💰 Improving efficiency, reducing costs, and increasing profitability.
  • 💼 A core skill for every modern professional in Finance, HR, Marketing, and Operations.

A Simple Framework for Business Analysis

  1. 1. Ask the Right Business Question

    Example (Operations): "Why have our delivery times increased by 15% this quarter?"

  2. 2. Gather & Clean Data

    Example (Finance): Consolidating sales data from ERP, CRM, and regional spreadsheets.

  3. 3. Analyze & Interpret

    Example (HR): Using formulas to find correlations between training hours and employee performance scores.

  4. 4. Visualize & Communicate

    Example (Marketing): Creating a chart showing campaign ROI for the management team.

Tool #1: The Spreadsheet (Excel/Google Sheets)

The Swiss Army Knife of Business 🇨🇭

Strengths

  • ✅ Ubiquitous & Accessible
  • ✅ Flexible for ad-hoc tasks
  • ✅ Great for small-to-medium datasets
  • ✅ Powerful calculation engine

Common Business Uses

  • 💰 Budgeting & Financial Modeling (Finance)
  • ⚙️ Inventory Tracking & Rosters (Operations)
  • 🤝 Employee Data & Payroll Calc. (HR)
  • 📊 Basic Sales Dashboards (Marketing)

Essential Spreadsheet Functions for Business 🔧

Logical Functions

IF, AND, OR

Use Case: Flagging inventory items that are below reorder level AND out of stock.

Lookup Functions

VLOOKUP, HLOOKUP

Use Case: Merging an employee's performance rating with their salary data from a separate HR sheet.

Aggregate Functions

SUMIF, COUNTIF

Use Case: Calculating the total sales generated by a specific sales representative.

⚡ The Most Powerful Tool: PivotTables

A PivotTable is an interactive tool that allows you to extract the significance from a large, detailed data set by quickly summarizing and reorganizing it.

Practical Example: HR Department

Scenario: An HR manager has a spreadsheet with 500 employees, including their Department, Salary, and Training Status.

Business Question: "What is the average salary for each department, and how many employees in each are 'Training Complete'?"

PivotTable Solution:
→ Rows: Department
→ Values: Average of Salary, Count of Training Status
→ Filter: Training Status = "Complete"

Result: A summary table in seconds, not hours of manual work!

Why Visualize Data? 📊

A picture is worth a thousand rows of data.

Key Benefits for Business Communication:

  • 🔍 Quickly spot trends, patterns, and outliers that are invisible in tables.
  • 🤝 Communicate complex financial or operational information clearly to stakeholders.
  • 🎯 Make your insights more memorable and impactful during presentations.
The goal of a business chart is clarity, not decoration.

Choosing the Right Chart for the Business Story

Bar Chart

Use for: Comparing categories.
e.g., Sales performance across different product lines.

Line Chart

Use for: Showing trends over time.
e.g., Monthly customer complaints over the last year.

Pie Chart

Use for: Showing parts of a whole.
e.g., Budget allocation by department. (Use with caution for >5 categories!)

Scatter Plot

Use for: Showing relationships between two variables.
e.g., Correlation between marketing spend and website traffic.

When Spreadsheets Aren't Enough...

Spreadsheets are powerful, but they have limitations in a growing business.

  • Scalability: Performance degrades significantly with very large datasets (e.g., > 100,000 rows).
  • Collaboration: Prone to version control issues ("Final_Report_v2_JohnsEdits.xlsx").
  • Automation: Manual data updates are time-consuming and can introduce errors.
  • Visualization: Interactive dashboarding capabilities are limited compared to specialized tools.

Tool #2: Power BI (Business Intelligence)

Power BI is a business analytics service by Microsoft. It provides interactive visualizations and business intelligence capabilities with an interface simple enough for end users to create their own reports and dashboards.

Key Features for Business:

  • 🔗 Connects to multiple data sources at once (Excel, databases, cloud services).
  • 🤖 Automates data refreshes, ensuring reports are always up-to-date.
  • 🎨 Creates stunning, fully interactive dashboards that users can filter and explore.
  • ☁️ Cloud-based for secure sharing and collaboration across the organization.

Spreadsheet vs. Power BI: A Business Scenario

Task: A national operations manager needs a weekly report on inventory levels across 20 warehouses.

Spreadsheet Approach

  1. Manually request 20 Excel files.
  2. Copy-paste data into a master file.
  3. Create charts and PivotTables.
  4. Email the static report.

Time Cost: ~3-4 hours every week.

Power BI Approach

  1. (One time setup) Connect to warehouse database.
  2. Build an interactive dashboard.
  3. Schedule an automatic daily refresh.
  4. Share a link to the live dashboard.

Time Cost: ~10 minutes every week.

💼 Data Analysis in Nepal: Real-World Examples

eSewa (FinTech)

Analyzes millions of transactions to understand user behavior and detect fraudulent activity.

Business Function: Finance & Risk Management

CG Foods (Manufacturing)

Uses sales data from distributors to forecast demand and optimize production schedules for Wai Wai noodles.

Business Function: Operations & Supply Chain

Daraz (E-commerce)

Analyzes customer browsing and purchase history to create personalized product recommendations.

Business Function: Marketing & Sales

Key Takeaways

  • Data analysis is a core business skill for decision-making, not just an IT task.
  • Spreadsheets are excellent for ad-hoc, small-scale analysis across all departments (Finance, HR, Operations).
  • BI tools like Power BI are built for larger, automated, and interactive reporting that saves significant time.
  • The ultimate goal is to turn raw data into actionable business insights that create value. 🎯

Thank You!

Q & A


Next Topic: Data Visualization: Charts and Graphs


Back to Start