B23-CAP-304 Basics of Data Science using Excel
Part A – Introduction | |||
Subject | BCA | ||
Semester | III | ||
Name of the Course | Basics of Data Science using Excel | ||
Course Code | B23-CAP-304 | ||
Course Type: (CC/MCC/MDC/CC- M/DSEC/VOC/DSE/PC/AEC/ VAC) | CC-M3 | ||
Level of the course (As per Annexure-I | 200-299 | ||
Pre-requisite for the course (if any) | Basic knowledge of mathematics and computer | ||
Course Learning Outcomes(CLO): | After completing this course, the learner will be able to:
1. understand the fundamental concepts of data science and the role of Excel in data analysis. 2. learn data cleaning, preparation, and visualization techniques using Excel. 3. apply statistical analysis and predictive modeling using Excel. 4. To explore advanced Excel functions and data analysis tools.
5*. Implement the various functions in Excel |
||
Credits | Theory | Practical | Total |
3 | 1 | 4 | |
Contact Hours | 3 | 2 | 5 |
Max. Marks:100(70(T)+30(P))
Internal Assessment Marks:30(20(T)+10(P)) End Term Exam Marks: 70(50(T)+20(P)) |
Time: 3 Hrs.(T), 3Hrs.(P) | ||
Part B- Contents of the Course |
Instructions for Paper-Setter
The examiner will set a total of nine questions. Out of which the first question will be compulsory. The remaining eight questions will be set from four units selecting two questions from each unit. The examination will be of three-hour duration. All questions will carry equal marks. The first question will comprise short answer-type questions covering the entire syllabus. The candidate must attempt five questions, selecting one from each unit. The first question will be compulsory. The practicum will be evaluated by an external and an internal examiner. The examination will be of three-hour duration. |
||
Unit | Topics | Contact Hours |
I | Introduction to Data Science: Definition, importance, and applications. Overview of Excel: Interface, basic functions, and features. Data Types and Formats in Excel: Text, numbers, dates, and custom formats. Basic Data Manipulation: Sorting, filtering, and basic formulas (SUM, AVERAGE, COUNT). | 11 |
II | Data Import and Export: CSV, TXT, and Excel files. Data Cleaning Techniques: Handling missing values, duplicates, and errors. Data Transformation: Text-to-columns, concatenation, and data validation. Data Visualization: Creating and customizing charts (bar, line, pie). | 11 |
III | Descriptive Statistics: Mean, median, mode, standard deviation, and variance. Inferential Statistics: Hypothesis testing, t-tests, and chi-square tests. Regression Analysis: Simple linear regression and multiple regression. Predictive Modeling: Introduction to basic predictive models and their implementation in Excel. | 11 |
IV | Advanced Excel Functions: VLOOKUP, HLOOKUP, INDEX- MATCH, and PivotTables. Data Analysis ToolPak: Using Excel’s built-in data analysis tools such as Descriptive Statistics, Histograms, Correlation, and Regression. What-If Analysis Tools: Scenario Manager, Goal Seek, and Data Tables. | 12 |
V* | The following activities will be carried out/ discussed in the lab during the semester.
Familiarize with Excel interface and basic operations. · Explore Excel ribbons, toolbars, and interface. · Practice data entry, formatting, and basic calculations. · Create a simple spreadsheet and perform basic functions. Import data and perform basic cleaning tasks. |
30 |
· Import datasets from CSV and text files.
· Identify and handle missing values. · Remove duplicates and correct data errors. Manipulate data through sorting and filtering. · Apply sorting to datasets based on different criteria. · Use filters to analyze subsets of data. · Create custom filters to extract specific data points. Utilize formulas for data transformation. · Practice text functions: LEFT, RIGHT, MID, CONCATENATE. · Use date functions: TODAY, DATE, DATEDIF. · Implement basic mathematical formulas: SUM, AVERAGE, COUNT. Calculate and interpret descriptive statistics. · Calculate measures of central tendency: mean, median, mode. · Compute measures of dispersion: range, variance, standard deviation. · Use built-in Excel functions for statistical analysis. Apply conditional formulas and formatting. · Use IF, SUMIF, and COUNTIF functions for conditional analysis. · Apply conditional formatting to highlight data trends and anomalies. · Create data-based rules for formatting. Create and customize basic charts and graphs. · Generate line charts, bar charts, and pie charts. · Customize chart elements: titles, labels, and legends. · Analyse data visually through chart types. Summarize data using PivotTables. · Create PivotTables to aggregate data. · Group data and create custom summaries. · Utilize slicers to filter and analyze PivotTable data interactively. Apply lookup and reference functions. · Use VLOOKUP and HLOOKUP for data retrieval. · Implement INDEX and MATCH functions for advanced lookups. · Practice using the OFFSET function for dynamic data ranges. Perform statistical analysis using the Data Analysis Toolpak. · Install and activate the Data Analysis Toolpak. · Conduct regression analysis and ANOVA. · Explore other statistical tests available in the Toolpak. |
Suggested Evaluation Methods | |
Internal Assessment:
➢ Theory · Class Participation: 5 · Seminar/presentation/assignment/quiz/class test etc.: 5 · Mid-Term Exam: 10 ➢ Practicum · Class Participation: NA · Seminar/Demonstration/Viva-voce/Lab records etc.: 10 · Mid-Term Exam: NA |
End Term Examination:
A three-hour exam for both theory and practicum. |
Part C-Learning Resources | |
Recommended Books/e-resources/LMS:
· “Data Science for Business: What You Need to Know about Data Mining and Data- Analytic Thinking” by Foster Provost and Tom Fawcett. · “Excel Data Analysis: Modeling and Simulation” by Hector Guerrero. · “Data Analysis Using Microsoft Excel” by Michael R. Middleton. · “Excel 2019 Bible” by Michael Alexander, Richard Kusleika, and John Walkenbach. · “Practical Statistics for Data Scientists: 50 Essential Concepts” by Peter Bruce and Andrew Bruce. |