SHRI RAM COLLEGE OF COMMERCE
UNIVERSITY OF DELHI
END TERM PRACTICAL EXAM, MAY 2025
Name of Paper: DSC-6.1: Business Analytics
Unique Paper Code (UPC): 2412083601
Type: Discipline Specific Core
Semester VI
Programme [Link]. (Hons.)
SET: B
Maximum Marks: 40 (Practical Exam) + 20 (Viva Voice)
Duration: 3 hours+ 2 hours
This paper has 6 pages.
Read each Instruction very carefully:
1. There are total six (06) questions. Question No. 1 is compulsory. Out of the
remaining five (05) questions, any three (03) questions can be attempted. In
total, four (4) questions are to be attempted.
2. All questions have equal marks.
3. Use of Internet is strictly restricted to downloading of packages in R or for
downloading files. If you are found using internet/AI/Whatsapp/any other medium
which is not needed for this paper, you would be treated as a case of Unfair
Means (UFM), and you risk cancellation of Practical Exams.
4. Where R is mentioned, it is understood to include R-Studio as well.
PROCEDURE OF SUBMISSION
Step 1. First, create a folder on the desktop of the system allocated to you. The
name of the folder should be your complete EXAM Roll number. (Do
not put your College Roll Number as the name of the folder)
Step 2. In that folder, create sub-folders representing questions. Each sub-folder is
to be named according to the question you attempt. Let’s say you attempt
Q.1, Q.2, Q.3 and Q.5. Accordingly, your sub-folders should be Q. 1, Q.2,
Q.3 and Q.5.
Step 3. For each question, you will be asked to save a file in the corresponding
sub-folder. Save this file in the format of Q.2._Sub part_lastfour digits of
the roll no. Let’s say you are attempting Q.2. (a) and your exam roll no is
278145867. Then you must save this as Q2_a_5867
Step 4. In each question, submission is to be made in a particular format. You
must follow the same.
-----------------------------------------------------------------------------------------------------------------------
----------
-:P.T.O:-
Question 1:
Page 1 of 6
Create the above dataset in Excel. Clean the data in a separate sheet in the same
excel file. While data cleaning, please also ensure consistency in format, outliers
and missing values in the data.
After cleaning the data, you are required to visualize the data set using suitable
software (Excel, PowerBi, or R). Prepare a suitable Dashboard/ Visualization for the
cleaned dataset.
a) Save the Excel file
b) Take screenshots of the visualization and save it in a word document.
Question 2:
ABC Company is trying various social media channels as advertising to improve its
sales. It hires you as a consultant. You collect the below data from its branches
towards expenditure made on various social media channels as well as their sales.
Analyse the data for anomalies of regression using Sales as the explained variable
in R. Explain your workings and interpret the results as comments in the R-Script.
Are the results meaningful? If yes, which channel do you think ABC company should
increase the advertising spends to increase sales in different branches? Predict the
value of Sales if advertising spend on that medium is increased by 10% of its
average.
DATA is on Page 3
Facebo Instagra Twitter Sales
ok m
230.1 37.8 69.2 22.1
Page 2 of 6
44.5 39.3 45.1 10.4
17.2 45.9 69.3 12
151.5 41.3 58.5 16.5
180.8 10.8 58.4 17.9
8.7 48.9 75 7.2
57.5 32.8 23.5 11.8
120.2 19.6 11.6 13.2
8.6 2.1 1 15.5
199.8 2.6 21.2 15.6
66.1 5.8 24.2 12.6
214.7 24 4 17.4
23.8 35.1 65.9 9.2
97.5 7.6 7.2 13.7
204.1 32.9 46 19
195.4 47.7 52.9 22.4
67.8 36.6 114 12.5
281.4 39.6 55.8 24.4
69.2 20.5 18.3 11.3
147.3 23.9 19.1 14.6
218.4 27.7 53.4 18
237.4 5.1 23.5 17.5
13.2 15.9 49.6 5.6
228.3 16.9 26.2 20.5
62.3 12.6 18.3 9.7
Question 3:
Do the following in R:
Create a data frame titled “db” of the following variables with any five
datapoints (“elements”) for the following variables:
o Employee ID (empid), class must be character
o Department (dept), class must be factor
o Employee name (emname), class must be character
o Experience Year (ey), class must be integer
o Weekly Hours (wh), class must be numeric
o Monthly Sales (ms), class must be numeric
o Net salary (ns), class must be numeric
Determine Mean, Standard deviation, and Quartiles for the numeric values in
the dataframe.
Create a histogram of net salary.
Create a properly titled box plot of experience in years.
Create a properly titled scatter plot of weekly hours.
Compute covariance and correlation between Experience in Years and net
salary. Interpret the covariance and correlation as comments in R.
Compute coefficient of determination between monthly sales and net salary.
Interpret the same as comments in R.
Save the R-script in the sub-folder.
Question 4
Page 3 of 6
Create two files:
File 1: Customer Details
Customer ID, Customer-Name, State
D101, Alok, Delhi
D102, Tanya, Bengaluru
D103, Kunal, Hyderabad
D104, Ritika, Mumbai
D105, Arjun, Kolkata
File 2: Excel File: Transactions
Transaction Customer Sales Cost
Product
No ID (₹) (₹)
T01 D101 Smartwatch 12000 8500
T02 D102 Laptop 60000 50000
T03 D103 Shoes 4000 2500
Mobile
T04 D101 25000 20000
Phone
T05 D104 Backpack 2500 2000
T06 D105 Desk Chair 8000 6000
T07 D102 Printer 7000 5000
T08 D103 Tablet 22000 18000
T09 D104 Trolley Bag 4500 3500
T10 D105 Headphones 3500 2500
2.1. Create text and excel files.
2.2. Combine them using Power Pivot.
2.3. Calculate Profit = Sales – Cost.
2.4. Find Average Sales for each Product.
2.5. Find Average Sales per State.
2.6. Create Pivot Charts and Slicers for Product and State
Save your Excel file.
Question 5:
From the Text in italics only, generate a wordcloud (minimum frequency>3) and a
sentiment barplot in R.
Interpret the wordcloud and the sentiment chart as comments in the R-Script itself.
Your R-script for the same must have codes for processing including removing
numbers, stopwords, and punctuations amongst others.
TEXT:
“Hon’ble Members,
It gives me immense pleasure to address this session of Parliament.
Page 4 of 6
Just two months ago, we celebrated the 75th anniversary of adoption of our
Constitution, and only a few days ago, the Indian Republic completed 75 years of its
journey. This occasion will elevate India pride as the mother of democracy to new
heights. On behalf of all the citizens of the country, I pay tribute to Babasaheb
Ambedkar and all the framers of the Constitution.
2. The historic festival of Mahakumbh is also underway in the country. Mahakumbh
is a festival of India cultural tradition and social consciousness. Millions of devotees
from across the country and the world have taken the holy dip at Prayagraj. I
express my sorrow over the unfortunate incident that occurred on Mauni Amavasya
and wish for the speedy recovery of the injured.
A few days ago, we lost the former Prime Minister of the country, Dr. Manmohan
Singhji. He served the nation as Prime Minister for ten years and was a long-time
member of Parliament. I offer my heartfelt tribute to Manmohan Singhji.”
Ref: Address by the Hon’ble President of India, Smt. Droupadi Murmu to
Parliament on 31.01.2025
Question 6:
Process the below data set in Excel.
After performing the processing steps, load this data set into R as file. In R,
Calculate descriptive statistics i.e. measures of central tendency and
dispersion.
Visualise the data using proper titled scatter plots, histograms or any other
appropriate visualization method
Create a linear model using Median House Value as the explained variable
and remaining variables as explanatory variables.
Explain as comments in R if the regression is useful for prediction?
Check for homoskedasticity and multi-collinearity. Interpret the results as
comments in R-Script.
housing median median
Longitu total_ro total_be populat househ
latitude _media _incom _house_
de oms drooms ion olds
n_age e value
-122.23 37.88 41 880 129 322 126 8.3252 452600
-122.22 37.86 21 7099 1106 2401 1138 8.3014 358500
-122.24 37.85 52 1467 190 496 177 7.2574 352100
-122.25 37.85 52 1274 235 558 219 5.6431 341300
-122.25 37.85 52 1627 280 565 259 3.8462 342200
-122.25 37.85 52 919 213 413 193 4.0368 269700
-122.25 37.84 52 2535 489 1094 514 3.6591 299200
-122.25 37.84 52 3104 687 1157 647 3.12 241400
-122.26 37.84 42 2555 665 1206 595 2.0804 226700
-122.25 37.84 52 3549 707 1551 714 3.6912 261100
-122.26 37.85 52 2202 434 910 402 3.2031 281500
-122.26 37.85 52 3503 752 1504 734 3.2705 241800
-122.26 37.85 52 2491 474 1098 468 3.075 213500
Page 5 of 6
-122.26 37.84 52 696 191 345 174 2.6736 191300
-122.26 37.85 52 2643 626 1212 620 1.9167 159200
-122.26 37.85 50 1120 283 697 264 2.125 140000
-122.27 37.85 52 1966 347 793 331 2.775 152500
-122.27 37.85 52 1228 293 648 303 2.1202 155500
-122.26 37.84 50 2239 455 990 419 1.9911 158700
-122.27 37.84 52 1503 298 690 275 2.6033 162900
-122.27 37.85 40 751 184 409 166 1.3578 147500
-122.27 37.85 42 1639 367 929 366 1.7135 159800
-122.27 37.84 52 2436 541 1015 478 1.725 113900
-122.27 37.84 52 1688 337 853 325 2.1806 99700
a) Save the Excel file
b) Save the R-Script
****
Page 6 of 6