SPREADSHEET FUNDAMENTALS FOR DATA ANALYSIS (Skill Based)

Paper Code: 
SCAI 601
Credits: 
4
Periods/week: 
4
Max. Marks: 
100.00
Objective: 

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
  3. Develop data management skills for industry.

 

Course Outcomes (COs).

Course Outcome (at course level)

Learning and teaching strategies

Assessment Strategies

On completion of this course, the students will:

  1. Organise complex data using spreadsheet software.
  2. Perform conditional operations on data.
  3. Manage data using named ranges, filters and Tables
  4. Solve business problems using appropriate formulas and functions.
  5. Analyse complex information by creating charts and graphs

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)

 

ESSENTIAL READINGS: 

Suggested Text Books

  • Winston,”Microsoft Excel 2013: Data Analysis and Business Modeling”, PHI
  • Denise Etheridge, “Excel Data Analysis”,WileyPublication, Third Edition

 

Suggested Reference Books 

  • Hector Guerrero,“Excel Data Analysis - Modeling and Simulation”, Springer
  • Financial Analysis and Modeling using Excel and VBA: Chandan Sengupta, Wiley

 

 

REFERENCES: 

Reference Journals

E-Resources including links

Academic Year: 
2023-24
  • Printer-friendly version