JAWAHARLAL NEHRU TECHNOLOGICAL UNIVERSITY HYDERABAD

Course Objectives:

• To understand the application of Statistical tools in Excel /Spread sheets.
• To enable the students to work with data using templates.
• To enable the students to work with data using formulae and functions.
• To discuss the results of data analysis for better decision-making.
• To enable the students to work in a macro’s environment and present data in a visually appealing manner.

Course Outcomes

Students will be able to

• Familiarize with the features of the spread sheet.
• Create and use templates to analyze the data in an efficient manner.
• Apply appropriate formulae and perform various functions on the data for better decision making.
• Apply statistical tools and techniques to the data and analyze the results.
• Work in macros environment and create visual representation in the form of charts/pivots/dashboards .

UNIT - I: Introduction to Statistical Packages - MS - EXCEL

Introduction, uses, functions and features of Statistical Packages, Getting started with excel, Highlights and main functions: Home, Insert, page layout, formulas, Data, review, view, add-inns, using help function, Customizing the Quick Access Toolbar.

UNIT - II: Creating and Using Templates

Working with Data: Entering, Editing, Copy, Cut, Paste, Paste Special, Formatting Data and Using the Right Mouse Click, Saving, Page Setup, and Printing, Using Headers and Footers, Manipulating Data, using Data Names and Ranges, Filters and Sort and Validation Lists.

UNIT - III: Data from External Sources

Using and Formatting Tables, Basic Formulas and Use of Functions, Managing, Inserting, and Copying Worksheets, Securing the Document, Advanced Formulae and Function – VLook Up, HLook Up, Conditional Logic, Financial formulae, Pivot Tables and Pivot Charts.

UNIT - IV: Data Analysis

Tabulation, bar diagram, Multiple Bar diagram, Pie diagram, Measure of central tendency: mean, median, mode, Measure of dispersion: Variance, Standard deviation, Coefficient of Variation. Correlation, Regression lines.

UNIT - V: Macros Operation & Interactive Excel Dashboard

Introduction to Macro Security, Recording a Macro, Assigning a macro to a button; Running a macro upon opening a workbook; Inspect and modify a macro; Creating an interactive excel dashboard – Pivot Chart Analyze

Suggested Readings:

• D P Apte : Statistical Tools for Managers USING MS EXCEL, Excel, 2012.
• Excel Functions and Formulas, Bernd Held, 2015
• Bruce Bowerman, Business Statistics in Practice, TMH, 5/e 2012.
• Getting Started with Excel Macros, Wiley India
• Excel Dashboards and Reports for Dummies, A Wiley Brand
• Business Data Analysis using Excel, David Wigham, Oxford. BBA SEMESTER – III