Business Analytics Project
/Workbook
Last date of Submission: 27/04/2026
1. You are given the sales data of XYZ Ltd. for the period Jan-April 2024, as follows:
Date Product SoldBy ListPrice ActualPrice Quantity Region
02-Jan-24 Laptops Abhinav 45,000 36,000 4 East
08-Jan-24 Watches Deepak 35,000 28,000 5 North
18-Jan-24 Ipads Dushyant 56,250 45,000 2 West
05-Feb-24 Watches Deepak 28,125 22,500 6 East
16-Feb-24 Iphones Ananya 65,000 52,000 4 Noth
27-Feb-24 Laptops Sukant 60,000 48,000 6 East
LG Window
28-Feb-24 AC Abhinav 40,000 32,000 7 South
09-Mar-24 Watches Deepak 31,875 25,500 3 West
10-Mar-24 Iphones Sukant 78,750 63,000 2 East
17-Mar-24 IPads Deepak 61,250 49,000 6 South
22-Mar-24 IPads Ananya 58,958 47,166 6 West
03-Apr-24 Laptops Sukant 47,500 38,000 2 North
10-Apr-24 Desktops Dushyant 37,500 30,000 4 West
15-Apr-24 Ipads Sukant 63,125 50,500 8 South
22-Apr-24 Iphones Dushyant 68,750 55,000 4 South
27-Apr-24 Desktops Ananya 40,000 32,000 6 North
Create a Pivot Table and a Pivot Chart to compare and depict the sales at list price
and sales at actual price by taking (a) Product (b) Sold By and (C) both product and
Soldby as the basis of comparison.
2. The following dataset shows the monthly sales (in units) of Refrigerators and Washing
Machines. Construct a scatter plot to visualize the relationship between the sales of
these two products.
Months Jan Feb March April May June July August Sep Oct Nov Dec
Refrigerators 120 135 128 142 150 138 145 155 148 140 132 137
Washing Machines 98 105 100 110 115 108 112 120 118 109 104 107
3. The profits earned by the 100 firms during the financial year 2021-22 have been given
below. Prepare the Histogram of the data.
231 150 250 244 252 287 242 226 204 184
190 207 242 211 249 180 163 229 212 170
226 225 249 161 289 291 167 227 233 248
181 261 214 153 249 273 184 196 219 206
180 219 190 216 174 220 155 200 299 284
283 226 204 259 185 235 184 225 195 288
185 168 267 212 262 280 186 175 292 168
171 157 207 297 172 261 247 266 227 253
208 212 160 298 220 224 185 182 151 178
175 282 150 202 262 285 194 193 195 236
4. The following data shows the relationship between Number of Branches and Annual
Revenue (₹ Lakhs) of a company. Develop a simple linear regression model to
predict revenue based on the number of branches. Also, estimate the revenue when
the company operates 120 branches.
Number of Branches 5 10 15 20 25 30 35 40 45 50
Annual Revenue (₹ Lakhs) 40 48 55 63 70 78 85 93 100 108
5. The following dataset contains feedback comments from students on an online learning
platform. Extract the top 10 most frequently occurring words after removing stop
words. Visualize the results using a Bar Chart and a Word Cloud. Also, briefly interpret
the findings.
a. "The course content was engaging and easy to understand."
b. "Frequent buffering issues affected the learning experience."
c. "Excellent instructor and clear explanations."
d. "Assignments were helpful but a bit lengthy."
e. "More practical examples would improve the course quality."
f. "The platform interface is user-friendly and smooth."
6. The following dataset represents the monthly sales (in ₹ thousands) of a retail store.
Calculate the descriptive statistics including Mean, Median, Mode, Standard
Deviation, Variance, Minimum, Maximum, and Range.
Month Jan Feb March April May June July August Sep Oct
Sales (₹ '000) 45 50 55 60 52 58 62 65 59 57
7. You are provided with the following HR data and Pay details of employees working in
your company. Using Power Query in Excel, you are required to merge both data
sources and compute the Net Salary payable to all employees.
(a) Merge both data sources using suitable transformations in Power Query
(b) Compute Net Salary for each employee
HR Data
Emp_ID Employee Name Date of Birth Designation
F001 Emp544 Name 12-11-1994 Supervisor
F002 Emp682 Name 26-01-1987 Manager
F003 Emp387 Name 04-05-1974 Senior Manager
F004 Emp315 Name 27-07-1986 Supervisor
F005 Emp669 Name 27-07-1967 Senior Manager
Pay Details
Emp_ID Basic Salary DA (%) HRA (%) TDS Rate PF Contribution
F001 101227 40% 16% 30% 20%
F002 49757 20% 17% 10% 15%
F003 42080 40% 17% 30% 17%
F004 37236 40% 17% 20% 15%
F005 115609 20% 17% 15% 17%