Department Events
SPREADSHEETS FOR DATA ANALYSIS
Course Objectives:
The course will enable the students to
1. Study the concept of working with spreadsheets right from basics to Tables,
2. Understand the working on advanced data manipulation with spreadsheets
Develop data management skills for industry.
Course | Learning Outcomes (at course level) | Learning and teaching strategies | Assessment Strategies | |
Course Code | Course title | |||
24SCAI 601 | SPREADSHEET FOR DATA ANALYSIS (Practical)
| CO1. Construct complex data using spreadsheet software. CO2. Apply conditional operations on data. CO3. Design data using named ranges, filters and Tables CO4. Solve business problems using appropriate formulas and functions. CO5. Analyse complex information by creating charts and graphs CO6. Contribute effectively in course-specific interaction | Approach in teaching: Interactive Lectures, Discussion, Demonstrations, Group activities, Teaching using advanced IT audio-video tools. Learning activities for the students: Effective assignments, Giving tasks.
| Assessment Strategies Class test, Semester end examinations, Quiz, Practical Assignments, Individual and group projects.
|
Introduction to Spreadsheets
Understanding and preparing data for analysis
Performing calculations, Formatting Features, Format Painter, Cell referencing (Relative, Absolute, Mixed). Arranging data for a situation. Paste Special function.
Series, auto fill series. Freeze Panes & Split windows.
Conditional Formatting, Sparklines and Number Formats.
Name Ranges, concept & their advantages, methods for creating Named Ranges, Modify workbooks to use Named Ranges.
Data Tabulation in Excel
Creating a Table, Adding, Deleting new rows or columns, moving a Table, Removing duplicate rows from a table. Sorting and filtering a table, auto filter, advanced filter, formatting of table.
Functions and its parts, some useful mathematical and statistical Functions in spreadsheet (eg. SUM, COUNT, MAX, MIN, IF, COUNTIF, CEILING, FLOOR, TRUNC, ABS, INT, LOG, MOD, POWER, ROUND), logical functions (IF, AND, OR). Concept and use of nested IF(), COUNTIF(), SUMIF() and AVERAGEIF().
Text Functions (Len, Mid, Find, Proper, Rept , Trim, Upper, Substitute, Concatenate
Date & Time functions (NOW, DATE, TIME, DAY, MONTH, YEAR, HOUR, MINUTE, SECOND).
Hlookup & Vlookup
Graphing and Charting: line graph, bar graph, pie chart, histogram, scatter plot.
What if analysis (Data tables, Scenario, Goal seek, Sub-totals, Pivot Table)
Suggested Text Books:
- Winston,”Microsoft Excel 2013: Data Analysis and Business Modeling”, PHI
- Denise Etheridge, “Excel Data Analysis”,WileyPublication, Third Edition
Suggested Reference Books:
1. Hector Guerrero,“Excel Data Analysis - Modeling and Simulation”, Springer
2. Financial Analysis and Modeling using Excel and VBA: Chandan Sengupta, Wiley
Reference Journals:
1. https://vciba.springeropen.com/
e-Resources including links
1. https://www.w3schools.com/EXCEL/index.php
2. https://support.microsoft.com/en-us/office/excel-video-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb
