Data Analytics with Excel (BCS358A)

Data Analytics with Excel

Course Code BCS358A 
CIE Marks 50
Teaching Hours/Week (L:T:P: S) 0:0:2:0 
SEE Marks 50
Credits 01 
Exam Hours 100
Examination type (SEE) Practical

Experiments

1 Getting Started with Excel: Creation of spread sheets, Insertion of rows and columns, Drag & Fill, use of Aggregate functions.

2 Working with Data : Importing data, Data Entry & Manipulation, Sorting & Filtering.

3 Working with Data: Data Validation, Pivot Tables & Pivot Charts.

4 Data Analysis Process: Conditional Formatting, What-If Analysis, Data Tables, Charts & Graphs.

5 Cleaning Data with Text Functions: use of UPPER and LOWER, TRIM function, Concatenate.

6 Cleaning Data Containing Date and Time Values: use of DATEVALUE function, DATEADD and DATEDIF, TIMEVALUE functions. 

7 Conditional Formatting: formatting, parsing, and highlighting data in spreadsheets during data analysis. 

8 Working with Multiple Sheets: work with multiple sheets within a workbook is crucial for organizing and managing data, perform complex calculations and create comprehensive reports.

9 Create worksheet with following fields: Empno, Ename, Basic Pay(BP), Travelling Allowance(TA), Dearness Allowance(DA), House Rent Allowance(HRA), Income Tax(IT), Provident Fund(PF), Net Pay(NP). Use appropriate formulas to calculate the above scenario. Analyse the data using appropriate chart and report the data. 

10 Create worksheet on Inventory Management: Sheet should contain Product code, Product name, Product type, MRP, Cost after % of discount, Date of purchase. Use appropriate formulas to calculate the above scenario. Analyse the data using appropriate chart and report the data. Template for Practical Course and if AEC is a practical Course Annexure-V 

11 Create worksheet on Sales analysis of Merchandise Store: data consisting of Order ID, Customer ID, Gender, age, date of order, month, online platform, Category of product, size, quantity, amount, shipping city and other details. Use of formula to segregate different categories and perform a comparative study using pivot tables and different sort of charts.

12 Generation of report & presentation using Autofilter &macro.