Advanced Analytics — Winter 2025

Use Excel to create a simple analytics dashboard that summarizes key performance metrics for a retail store (e.g., total sales, average order value, and customer count).

Apply Excel formulas to calculate range, variance, and standard deviation for employee performance scores and interpret the result.

Write down Steps to Use INDEX-MATCH to retrieve sales figures for a specific region and product category more efficiently than VLOOKUP.

Write down steps to import textual survey data (from Google Forms or CSV) and clean it using LOWER(), TRIM(), and SUBSTITUTE() to prepare it for analysis.

Create a sentiment dictionary using Excel that categorizes words as positive or negative. Then use COUNTIF() and SEARCH() to evaluate sentiment in customer feedback.

Write down different steps to Connect Tableau to a sample Excel dataset and apply data visualization principles to design a dashboard showing sales by region and profit margin.

Write down steps to Combine multiple chart types in a Tableau dashboard to provide an interactive summary view. Page 1 of

Import a CSV file into a Pandas DataFrame and apply filtering and sorting to analyze high-performing products.

Write a Python Code to use Matplotlib and Seaborn to visualize sales trends with histograms and boxplots.

Write a Python Code to use lists to store monthly revenue data and compute total and average using a Python program.

Write a Python Code for Apply SciPy to generate a normal distribution curve for product demand data.

Case Study: Analyzing Sales and Customer Sentiment for “FreshMart Retail Pvt. Ltd.” Background:FreshMart is a mid-sized grocery retail chain operating across Gujarat. The company sells a variety of products—fruits, vegetables, packaged foods, and household items—through both offline stores and online delivery. The management has noticed fluctuating profits in recent months and an increase in negative online customer feedback. The company’s CEO has asked the Business Analytics team to conduct a data-driven analysis using Excel, Tableau, and Python to identify patterns, predict outcomes, and suggest data-backed strategies. You are part of the analytics team responsible for analyzing the following datasets: Sales_Data.xlsx → Contains Date, Store_ID, Product_Category, Sales_Amount, Quantity, Discount, Region. Customer_Feedback.csv → Contains Review_ID, Customer_ID, Feedback_Text, Rating. Product_Pricing.csv → Contains Product_ID, Product_Name, Category, Cost_Price, Selling_Price.

Import the Sales_Data.xlsx and Product_Pricing.csv files, and use VLOOKUP or INDEX-MATCH to combine product pricing with sales details.

Import a subset of Customer_Feedback.csv and clean text data using LOWER(), TRIM(), and SUBSTITUTE() functions to prepare it for sentiment analysis.

Create two lists — positive_words and negative_words — and apply a word matching technique using Python’s count() or re module to classify each feedback as positive or negative.

Import the Sales_Data.xlsx, calculate the moving average of sales, and forecast the next 7 days’ sales using basic time series logic. Page 2 of