Assignment 1: Financial Performance Dashboard Analysis

BUS220 — Business Intelligence and Analytics

Author

Oleh Omelchenko

Published

January 1, 2026

Overview

You have a financial dataset with key performance ratios and one base metric (Gross Sales). Reconstruct the missing financial columns, analyze the data with pivot tables, and build an executive dashboard.

Total: 8 points + 2 bonus points

Deadline: See Moodle. Late policy: 1–7 days = 50% credit, >7 days = 0.

Dataset

The Financial Sample dataset (available on Moodle) contains 700 sales transactions.

Dimensions: Segment, Country, Product, Date (monthly)

Base metric: Gross Sales

Ratios provided: Profit Margin (%), Discount Rate (%), Gross Margin (%), Price Realization Rate (%)

Part 1: Data Preparation (3 points)

Calculated Columns

# Column Formula
1 Sales Gross Sales × Price Realization Rate %
2 Discounts Gross Sales × Discount Rate %
3 Profit Sales × Profit Margin %
4 COGS Sales × (1 − Gross Margin %)

Format currency columns as currency, percentage columns as percentages.

Discount Bracket

Create a Discount Bracket column:

Discount Rate Bracket
0% None
> 0% and < 5% Low
≥ 5% and < 10% Medium
≥ 10% High

Verification Columns

  • Sales Check: Gross Sales − Discounts (should equal Sales)
  • Profit Check: Sales − COGS (should equal Profit)

Conditional Formatting

  • Color scale on Profit Margin: red (low/negative) → green (high)
  • Highlight rows where Discount Rate ≥ 10%

Part 2: Pivot Tables & Charts (3 points)

Create three pivot tables, each on its own named sheet.

Warning

Do not average ratios directly. Compute from summed components: e.g., Profit Margin = SUM(Profit) / SUM(Sales). Averaging a pre-calculated ratio gives incorrect results when rows have different sales volumes.

Pivot Table 1: “Segment vs Country”

  • Rows: Segment | Columns: Country
  • Values: SUM of Sales, Profit Margin (properly calculated)
  • Sort rows and columns by Grand Total of Sales, descending

Pivot Table 2: “Product & Segment”

  • Rows: Product | Columns: Segment
  • Values: Discount Rate (properly calculated)
  • Sort rows and columns by Grand Total, descending
  • Add a chart + one-sentence justification for your chart type choice

Pivot Table 3: “Monthly Metrics”

  • Rows: Date (ascending)
  • Values: SUM of Gross Sales, SUM of Sales, SUM of Profit, ROI % (Profit/COGS), Profit Margin % (Profit/Sales)
  • Add a chart + one-sentence justification for your chart type choice

Findings

Report these in your Moodle submission text with exact values:

  • Segment with the highest overall Sales
  • Product × Segment combination with the highest and lowest Discount Rate
  • Month with the highest Gross Sales, lowest ROI, highest Profit Margin

Part 3: Executive Dashboard (2 points)

Create a sheet called “Executive Dashboard” with:

  • Slicers for Date, Segment, Country, Product
  • Scorecards showing Total Sales, Total Profit, Profit Margin %, Discount Rate %, ROI %
  • Stacked Column Chart: Profit by Country over Date
  • Multi-Line Chart: Profit Margin by Segment over Date (one line per Segment + a Grand Total line; you will likely need an intermediate pivot table)

Slicers in Google Sheets only filter charts built directly from the data sheet — they will not affect charts built from pivot tables.

Part 4: Month-over-Month Growth (2 bonus points)

Calculate and visualize period-over-period growth — a concept not covered in practice sessions.

Task

Add two columns to your Monthly Metrics analysis:

  • Sales MoM Growth % — percentage change vs. the previous month
  • Profit MoM Growth % — percentage change vs. the previous month

\[\text{MoM Growth} = \frac{\text{Current Month} - \text{Previous Month}}{\text{Previous Month}}\]

The first month has no previous period — handle this so no errors are visible.

Tip

Hint: Pivot table calculated fields cannot reference “the cell above.” You will need to work outside the pivot table (e.g., reference pivot cells from adjacent columns, or copy values to a separate area).

Add a line chart of Sales MoM Growth % to your dashboard.

In your Moodle submission, write 2–4 sentences: Are there months with unusually large growth swings? Is the business accelerating or decelerating?

Submission

  1. Google Sheets link — named BUS220 – Assignment 1 – <Name Surname>, shared with commenting access to o_omelchenko@kse.org.ua, asidliarenko@kse.org.ua
  2. Exported .xlsx file with the same name
  3. Online submission text in Moodle: findings from Part 2 (and Part 4 insight if attempting the bonus)

Your workbook should contain sheets: Data, Segment vs Country, Product & Segment, Monthly Metrics, Executive Dashboard.

Grading

Part Points
1. Data Preparation 3
2. Pivot Tables & Charts 3
3. Executive Dashboard 2
4. MoM Growth (bonus) +2

No formula errors (#DIV/0!, #VALUE!). Consistent formatting. Clean chart titles and labels.

AI Usage

You may use AI tools for formula syntax, troubleshooting, or understanding concepts — mention it briefly in your submission. The spreadsheet work must be done by hand.