🟢 Your task is to complete a comprehensive business analysis for a fictional company, XYZ Services, which operates in multiple regions.
🟢 You have been provided with raw data that includes client orders, customer satisfaction scores, and product/region categories.
🟢 Your goal is to clean, analyze, and visualize this data to support business decision-making. This project will help you showcase your skills in advanced formulas, data organization, and visualization—perfect for including in your portfolio.

Note📝: "Data (To Be Completed)" contains entry data to perform this assignment

1. ▶ Cleaningand Preparation

2. ▶ Advanced Data Analysis with Formulas

3. ▶ Interactive Dashboard Creation

<< PROJECT TASKS & STEPS >>

TASK 1: DATA CLEANING AND PREPARATION

1.1 Standardize Date Formats

Objective: Ensure that all dates are in MM/DD/YYYY format.

Example:
🟢 Add a clolumn "Date Standardized" and use ARRAYFORMULA with TEXT to standardize dates in bulk.
🟢 Apply TEXT formula only to NOT empty rows by using IF.

1.2 Identify and Handle Missing Data

Objective: Use conditional formulas like IF and ISBLANK to flag and replace missing data.

Example:
🟢 Identify any missing values in the Satisfaction Score column.
🟢 Create a column "Satisfaction Score Adjusted": if a score is missing, input an estimated score of “N/A” for further analysis.
🟢 Use ARRAYFORMULA
🟢 Apply “N/A” only to table data: rows outside entry data should be empty. For this purpose, use additional IF.

1.3 Create Helper Columns for Further Analysis

Objective: Add calculated columns such as Revenue per Unit (using Amount/Quantity) and Profit (Amount * Profit Margin).

Example:
🟢 Use an ARRAYFORMULA for calculation.
🟢 Apply IFERROR for division.
🟢 Apply multiplication only to NOT empty Amount cells (use IF).

TASK 2: ADVANCED DATA ANALYSIS WITH FORMULAS

2.1 Segmentation Analysis Using IF, IFS, and ARRAYFORMULA

Instruction:
🟢 Use an ARRAYFORMULA with IFS for calculation.
🟢 Apply segmentation only to NOT empty Profit cells

2.2 Region-Based Sales Aggregation with QUERY

Objective: Use the QUERY function to summarize total sales (column Amount) per Region.

Goal: Highlight which regions generate the highest sales.

2.3 Monthly Trends Analysis with INDEX and MATCH

Objective: Build a dynamic table to track monthly sales growth using QUERY, INDEX, MATCH, and ARRAYFORMULA.

Instruction: Calculate month-over-month growth by comparing Amount for each Product across months.

Steps:
🟢 Use the QUERY function to summarize total sales (column Amount) per Product by month, year. Use MONTH(), YEAR() inside QUERY; label Amount column as Total Sales.
🟢 Add column MoM, % for month-over-month growth calculation.
🟢 For MoM, % calculation use INDEX and MATCH: you need to divide Product Total Sales for current month by Total Sales for previous month.
🟢 Apply conditional formatting to MoM results (if value < 0 then RED; if value > 0 then GREEN )

TASK 3: INTERACTIVE DASHBOARD CREATION

3.1 Create a Regional Sales Dashboard

Objective: Design an interactive dashboard to visualize Total Sales by Region with a pie chart. Use table from Task 2.2

Steps:
🟢 Add visualisation.
🟢 Use slicer to allow for filtering by Product.

3.2 Customer Satisfaction Score Analysis

Objective: Create a bar (or combo) chart to display Average Satisfaction Scores by Product.

Steps:
🟢 Use QUERY to calculate average scores.
🟢 Add visualisation.