Q1) Customer Orders Analysis using Power Query
Dataset 1: Customer Master
Customer ID Customer Name Segment
C001 Neha Sharma Retail
C002 Amit Gupta Wholesale
C003 Riya Mehta Online
C004 Rajat Verma Retail
C005 Alok Joshi Online
Dataset 2: Orders Table
Order ID Customer ID Product Quantity Unit Price
O101 C001 Pen 50 10
O102 C002 Book 40 100
O103 C003 Laptop 1 50000
O104 C004 Mouse 10 500
O105 C005 Chair 5 2000
Questions:
1. Merge the datasets using Customer ID.
2. Add a calculated column to compute Order Value = Quantity × Unit Price.
3. Identify the highest and lowest order value.
4. Find the average Quantity purchased per segment.
Q2) TechMart Pvt. Ltd. - Power Pivot Analysis
Dataset 1: Transaction Data
Product ID Product Name Quantity Revenue Region Month
T001 Tablet 30 300000 East Jan
T002 Smartphone 60 600000 West Feb
T003 Charger 150 75000 North Mar
T004 Headphone 100 120000 South Apr
T001 Tablet 35 350000 North May
Dataset 2: Product Info
Product ID Category Profit Margin (%)
T001 Electronics 12%
T002 Electronics 15%
T003 Accessories 8%
Product ID Category Profit Margin (%)
T004 Accessories 10%
Tasks: (a) Create a relationship via Product ID using Power Pivot.
(b) Add a new column to calculate Total Profit.
(c) Create a Pivot Table showing Revenue by Region and Category.
(d) Prepare a monthly summary of Quantity sold by product.
Q3) Sales Data – Conditional Formatting (MS Excel)
Product Region Salesperson Units Sold Unit Price Discount % Revenue Profit %
Watch East Arjun 10 2000 5% 19000 12%
Phone South Bhavna 5 10000 15% 42500 20%
Tablet West Chirag 8 8000 20% 51200 18%
Mouse North Divya 15 700 10% 9450 10%
Speaker East Esha 12 3000 5% 34200 14%
Instructions:
1. Highlight rows with Discount > 10% using light red fill.
2. Format names with Profit % ≥ 15% in green font.
3. Apply blue fill to Revenue > ₹40,000.
Q4) R Programming – Matrix & Conditions
a) Create Matrix:
Matrix (2, 4, 6, 8, 10, 12, 14, 16, 18), nrow=3, byrow=TRUE
1. Multiply the matrix by 3
2. Transpose the matrix
3. Multiply original matrix with transposed matrix
b) Conditional Execution in R:
x <- -5
1. If x > 0 → print “Positive Number”
2. If x < 0 → print “Negative Number”
3. If x == 0 → print “Zero”
Q5) Employee Dataset save in in Excel and import into R and generate R
coding
Emp_ID Age Exp_Years Salary (₹) Rating
A01 22 0 20000 6.0
A02 24 1 24000 6.5
A03 27 3 28000 7.0
A04 30 5 32000 7.5
A05 34 7 37000 8.0
A06 38 10 45000 8.5
Tasks: a) Plot two lines in R using ggplot2:
Y1 = Salary
Y2 = Rating (rescaled)
b) Plot a histogram for Age column using binwidth = 4, color = "lightblue", border =
"darkblue"
Q6) Inventory and Orders Analysis using Power Query
Dataset 1: Product Inventory
Product Code Product Name Category
PR001 Air Conditioner Home Appliance
PR002 Dishwasher Kitchenware
PR003 Hair Dryer Personal Care
PR004 Iron Home Appliance
PR005 Toaster Oven Kitchenware
Dataset 2: Order Records
Order ID Product Code Region Quantity Ordered Unit Price
O101 PR001 East 50 28000
O102 PR002 North 70 12000
O103 PR003 South 90 3500
O104 PR004 West 60 4200
O105 PR005 East 40 8000
Questions:
1. Merge both datasets using Product Code.
2. Create a new column for Total Order Value = Quantity Ordered × Unit Price.
3. Identify the product with maximum and minimum total order value.
4. Compute average quantity ordered per category.
Q7) Retail Store Profitability Dashboard using Power Pivot
Dataset 1: Monthly Sales
Item Code Product Name Qty Sold Sales Value Branch Month
IT001 Tablet 35 210000 North Jan
IT002 Printer 30 75000 West Feb
IT003 Scanner 25 62500 South Mar
IT004 Router 45 54000 East Apr
IT001 Tablet 28 168000 South May
Dataset 2: Product Profit Info
Item Code Product Name Product Type Profit Margin (%)
IT001 Tablet Gadgets 12%
IT002 Printer Accessories 15%
IT003 Scanner Accessories 10%
IT004 Router Networking 8%
Tasks: a) Establish a relationship using Item Code.
b) Calculate total profit using the profit margin.
c) Prepare a Pivot showing Sales by Branch and Product Type.
d) Create a Month-wise Quantity Sold chart.
Q8) Conditional Formatting in Excel - Furniture Sales Performance
Dataset:
Product Units Discount Total Profit
Product Region Salesperson Price
Code Sold % Sales Margin %
Sneha
F001 Chair North 40 1500 10% 54000 18%
Sharma
F002 Table East Kunal Bansal 20 5000 12% 88000 25%
F003 Sofa West Maya Kapoor 15 20000 15% 255000 30%
Tasks:
1. Highlight rows where Discount > 10% with orange fill.
2. Highlight Salespersons with Profit Margin ≥ 25% in dark green font.
3. Highlight Total Sales above ₹100,000 with yellow fill.
Q9) Matrix Operations and Conditional Logic in R
Part a: Create a matrix in R:
matrix(c(2,4,6,8,10,12,1,3,5), nrow=3, byrow=TRUE)
Perform:
1. Multiply the matrix by 3
2. Transpose the matrix
3. Multiply original matrix with its transpose
Part b:
x <- 8
Write an if-else condition:
If x > 5 → print “x is greater than 5”
Else → print “x is 5 or less”
Q9) Employee Dataset Analysis in R
Dataset:
ID Age Experience Salary Rating
A01 22 1 21000 6.0
A02 24 2 24000 6.8
A03 26 3 27000 7.2
A04 28 4 29000 7.5
A05 30 5 32000 7.9
Tasks in R: a) Plot line graphs for Salary (Y1) and Rating (Y2) using different colors
b) Plot histogram of Age with bin width = 4, color = pink, border = black
Q10) Regression with built-in dataset trees
Instructions:
1. Load dataset: trees
2. Build regression:
Y = Volume
X1 = Girth, X2 = Height
3. Print model summary
4. Interpret coefficients, R-squared, significance
5. Plot diagnostic graphs
Q11) Descriptive Stats and Vector Operations in R
Part a – iris dataset:
Range of [Link]
Variance of [Link]
SD of [Link]
IQR of [Link]
Part b – vectors:
x <- c(3,6,9,12,15,18)
y <- c(18,15,12,9,6,3)
z <- c(5,10,15,20,25,30)
Find:
Covariance and correlation between x and y
Covariance and correlation between x and z
Interpret direction and strength
Q12) Experience vs Salary (Simple Regression)
Experience (Years) Salary ('000 ₹)
0.5 18
1.0 21
1.5 23
2.0 26
2.5 28
3.0 30
Tasks: a) Build regression model
b) Get equation and R²
c) Predict salary for 2.3 years experience
d) Create plot of actual vs predicted
e) Comment on significance
Q13) Share Price Analysis (Matrix Data)
Matrix of Prices
Day A B C D
1 120 140 130 135
2 125 145 135 138
Day A B C D
3 130 150 140 141
Tasks: a) Calculate mean, median, mode, SD, range
b) Create class intervals of size 20 (start from 100)
c) Plot histogram
Q14) House Price Prediction using Multiple Regression
Vectors:
price <- c(180, 220, 250, 270, 300, 330, 350)
area <- c(900, 1100, 1300, 1500, 1700, 1900, 2100)
floor <- c(1, 2, 3, 4, 5, 6, 7)
Tasks: a) Build model: Y = price, X = area + floor
b) Print summary and interpret
c) Predict price for area=1600, floor=3
d) Write complete R code
Q15) Vector Statistics – Employee Ages
ages <- c(26, 30, 32, 29, 28, 35, 31, 30, 34, 37, 33, 36, 28, 31, 30)
Find:
Mean
Median
Mode
Standard Deviation
Range
Q16) Nested if-else Condition in R
x <- 0
Use if-else-if: (print)
If x > 0 → “x is positive”
If x < 0 → “x is negative”
If x == 0 → “x is zero”
Q17) HR Dataset: Box Plot & Regression in R
Sample Excel Dataset:
Emp ID Dept Salary Experience Score
E01 Sales 25000 2 6.5
E02 Finance 30000 3 7.2
E03 IT 35000 4 7.8
E04 HR 28000 2.5 7.0
Tasks:
1. Create boxplot: Salary by Dept (fill = "skyblue")
2. Run regression:
Y = Salary
X1 = Experience, X2 = Score
3. Interpret R² and p-values