LECTURE NOTES

ON

MBA I semester (Autonomous-R25)

STATISTICAL DATA ANALYSIS LAB

EXPERIMENTS

Mr. M Naveen Kumar Reddy

Assistant Professor

Master of Business Administration

St. Peters Engineering College (Autonomous)
HYDERABAD

SYLLABUS
Unit – I: Introduction to Statistical Packages: MS – EXCEL
or SPSS: Introduction, Uses, Functions and Features of Statistical
Packages, getting started with Excel/SPSS, Highlights and Main
Functions: Home, Insert, Page Layout, Formulae, 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
Formulae and Use of Functions, Data Analysis Using Charts and Graphs,
Managing, Inserting, and Copying Worksheets, Securing the Document, Advanced
Formulae and Functions, Worksheet Features, Data Analysis using Pivot Tables
and Pivot Charts.

Unit – IV: Data Analysis – I: Tabulation, Bar Diagram, Multiple Bar Diagram,
Pie Diagram, Measures of Central Tendency: Mean, Median, Mode. Measures of
Dispersion: Variance, Standard Deviation, Coefficient of Variation, Correlation
and Regression Lines.

Unit –V: Data Analysis – II: t-test, F-test, ANOVA One-way

classification, Chi-square Test, Independence of attributes.

Time series: Forecasting Method of Least Squares, Moving Average Method,

Inference and Discussion of Results.

Suggested Readings:
▪ R. Panneerselvam, Business Statistics Using MS Excel, Sage
Publications, 2022.
▪ Glyn Davis, Branko Pecar, Business Statistics Using Excel, Oxford
University Press, 2e, s2014.
▪ D P Apte: Statistical Tools for Managers USING MS EXCEL, Excel,
2012.
▪ David M Levine, David. F. Stephan & Kathryn A. Szabat,
Statistics for Managers – Using MS Excel, PHI, 2015.
▪ Bruce Bowerman, Business Statistics in Practice, TMH, 5e, 2012.
▪ Ajai .S Gaur, Sanjaya S. Gaur, Statistical Methods for
Practice and Research, Response, 2009.
Unit – I: Introduction to Statistical Packages: MS Excel

1. Experiment 1: Exploring MS Excel Interface and Basic Functions

 Navigate through the Ribbon tabs: Home, Insert, Page Layout,

Formulas, Data, Review, View.

 Enter sample data and use basic formulas like SUM, AVERAGE.

 Customize the Quick Access Toolbar.

 Use the Help function to find support topics.

2. Experiment 2: Data Entry and Basic Analysis in Excel

 Practice entering, editing data.

 Use Paste Special (values, formats, formulas).

 Format cells and data using Font, Alignment, and Number

formats.

 Save and print worksheet using Page Setup and Print Preview.

Unit – II: Creating and Using Templates; Working with Data

1. Experiment 1: Creating and Using Excel Templates

 Design a formatted workbook with headers, formulas.

 Save as a template and reuse by creating new workbooks

from the template.

2. Experiment 2: Data Manipulation and Validation

 Enter and edit data, use Copy, Cut, Paste commands.

 Apply filters and sort data.

 Create drop-down validation lists.

 Define named ranges and use them in formulas.

Unit – III: Data from External Sources; Advanced Excel Features

1. Experiment 1: Importing and Formatting External Data

 Import data from CSV or text file.

 Format data as a table, apply filters and table styles.

  Perform basic calculations using functions like SUM, IF,
VLOOKUP.

2. Experiment 2: Data Analysis using Pivot Tables and Charts

 Create pivot tables for summarizing data.

 Generate Pivot Charts.

 Manage worksheets (insert, rename, copy, protect).

 Use advanced formulas like INDEX and MATCH.

Unit – IV: Data Analysis – I: Descriptive Statistics and Visualization

1. Experiment 1: Data Tabulation and Visualization

 Create frequency and cross-tabulation tables.

 Draw Bar Diagrams, Multiple Bar Diagrams, and Pie Charts.

2. Experiment 2: Measures of Central Tendency and Dispersion

 Calculate mean, median, mode.

 Compute variance, standard deviation, coefficient of variation.

 Perform correlation analysis and draw regression line with

Excel trendline.

Unit – V: Data Analysis – II: Inferential Statistics and Time Series

1. Experiment 1: Hypothesis Testing using Excel

 Perform t-tests (independent and paired).

 Conduct F-test for variances.

 Carry out One-Way ANOVA.

2. Experiment 2: Time Series Analysis and Chi-square Test

 Apply Chi-square test for categorical data.

 Forecast using Least Squares method (fit and use trend line).

 Smooth data using Moving Average.

 Discuss inference based on results.

 Unit – I: Introduction to Statistical Packages: MS Excel
Experiment 1

Exploring MS Excel Interface and Basic Functions

Aim:

To familiarize with the Microsoft Excel interface and learn basic functions
for data entry, editing, and calculations.

Apparatus:

 Computer system with Microsoft Excel installed

Theory:

Microsoft Excel is a spreadsheet program used to organize, format, and

calculate data in tabular form. It offers a user-friendly interface with
various key components:

 Ribbon: The top toolbar containing tabs like Home, Insert, Page
Layout, Formulas, Data, Review, and View. Each tab groups relevant
commands for tasks such as data formatting, formula input, chart
creation, and data analysis.

 Quick Access Toolbar: A small customizable toolbar located above

the Ribbon providing shortcuts to frequently used commands like
Save, Undo, and Redo.

 Worksheet: The grid consisting of rows (numbered) and columns

(lettered). The intersection is called a cell where data is entered,
referenced by cell address (e.g., A1, B2).

 Formula Bar: Displays the content of the active cell, including data
or formulas.

 Name Box: Shows the address of the active cell and can be used for
quick navigation.

 Status Bar: At the bottom, displays quick calculations like sum or

average for selected cells.

Basic functions in Excel begin with an equal sign (=) followed by a

function name and arguments. For example, =SUM(A1:A5) adds values
from A1 to A5.
Procedure:

1. Open Microsoft Excel.

2. Explore the Ribbon and note different tabs and groups.

3. Identify and use the Quick Access Toolbar.

4. Navigate the worksheet by clicking cells and using the Name Box.

5. Enter sample data into cells, e.g., numbers or text.

6. Use the Formula Bar to edit cell content.

7. Create simple formulas, such as addition (=A1+B1), or use functions

like SUM and AVERAGE.

8. Save the Excel workbook.

Observations:

 The Ribbon contains options grouped logically under tabs, such as

Home for formatting, Insert for adding tables and charts, and
Formulas for calculations.

 Data entered in cells is reflected in the Formula Bar.

 Functions ease calculations and data summarization within the

worksheet.

Conclusion:

This experiment helps in understanding the Excel interface and basic

functions essential for performing data entry, editing, and calculation.
Mastery of these fundamentals is crucial for further data analysis and
spreadsheet manipulation tasks.

Experiment 2

Data Entry and Basic Analysis in Excel

Aim:

To learn how to enter data in Excel and perform basic data analysis
functions such as sums, averages, and simple statistical calculations.
Apparatus:

 Computer with Microsoft Excel installed

Theory:

Microsoft Excel is a powerful spreadsheet tool used for data organization

and analysis. Basic data entry involves inputting numbers, texts, or dates
into designated cells. Basic analysis can be done using built-in functions
such as SUM, AVERAGE, COUNT, MAX, MIN, and simple formulas. Functions
are entered beginning with an equal (=) sign, followed by the function
name and arguments in parentheses.

Basic functions useful for analysis:

 SUM(range) - Adds values in the specified range.

 AVERAGE(range) - Calculates the average of a range.

 COUNT(range) - Counts numeric entries in a range.

 MAX(range) and MIN(range) - Find the highest and lowest values in a

range respectively.

Procedure:

1. Open a new Excel workbook.

2. Enter sample data such as student marks, sales figures, or any

numerical data in the cells.

3. Select a cell where you want the result of a calculation.

4. Use the formula bar to enter basic functions:

 Calculate total using =SUM(A1:A10)

 Calculate average using =AVERAGE(A1:A10)

 Find maximum =MAX(A1:A10) and minimum

using =MIN(A1:A10)

5. Use the fill handle (drag corner of cell) to copy formulas to adjacent
cells.

6. Explore autofill and basic cell formatting options like text wrap,
alignment, and number formatting.

7. Save the workbook.

Observations:
  Data can be efficiently organized in rows and columns.

 Formulas instantly calculate results based on cell data.

 Excel functions simplify statistical summaries and data insights.

Conclusion:

This experiment demonstrates basic data entry and elementary data

analysis in Excel, providing foundational skills for handling larger datasets
and more advanced analytical functions in the future.

Unit – II: Creating and Using Templates; Working with

Data

Experiment 1

Creating and Using Excel Templates

Objective:

 To design a formatted Excel workbook as a reusable template.

Procedure:

1. Open Excel and create a new blank workbook.

2. Add headers such as report title, date, and column names.

3. Enter sample data and add formulas to calculate totals or averages

as required.

4. Apply cell formatting: font styles, colors, borders, and alignment.

5. Save workbook as a template:

 Go to File > Save As.

 Choose Excel Template (*.xltx) as file type.

 Name and save it in the templates folder.

 6. To reuse template, open File > New, select Personal to access saved
templates.

7. Create a new workbook based on the saved template for consistent

formatting and formulas.

Observation:

 Templates provide efficiency by reusing styles and formulas without

recreating them each time.

 Promotes standardization across reports and workbooks.

Experiment 2

Data Manipulation and Validation

Objective:

 To practice data entry, editing, copying, sorting, filtering, creating

validation lists, and defining named ranges.

Procedure:

1. Enter sample tabular data in the worksheet.

2. Edit cell values by double-clicking or direct overwrite.

3. Use Copy (Ctrl+C), Cut (Ctrl+X), and Paste (Ctrl+V) commands to

move or duplicate data.

4. Use Paste Special to selectively paste values, formulas, or formats.

5. Apply filters:

 Select data range with headers.

 Go to Data > Filter.

 Use dropdown arrows to filter rows based on criteria.

6. Sort data:

 Use Data > Sort, choose columns and ascending/descending

order.

7. Create drop-down lists for validation:

 Select target cells.

 Go to Data > Data Validation > Settings.

 Choose List in Allow box.

  Input list of valid entries or reference a range.

8. Define named ranges:

 Select a range.

 Go to Formulas > Define Name.

 Enter a meaningful name.

 Use these names in formulas for clarity,

e.g., =SUM(SalesData).

Observation:

 Filters and sorting help analyze subsets of data quickly.

 Data validation restricts erroneous inputs.

 Named ranges simplify formula readability and reduce errors.

These detailed steps guide users in handling Excel templates, mastering

data manipulation, and ensuring data integrity through validation and
range naming, foundational skills for data analytics labs.

Unit – III
Data from External Sources; Advanced Excel Features

Experiment 1

Importing and Formatting External Data

Objective:

 To import external data from CSV/text files into Excel and prepare it
for analysis.

Procedure:

1. Open Excel.

2. Go to Data > Get & Transform Data > From Text/CSV.

3. Browse and select the desired CSV or text file.

4. Use the Text Import Wizard if prompted:

 Choose the delimiter (comma for CSV, tab for text).

 Specify if data has headers.

  Define data types for columns as needed.

5. Import data into a new worksheet or table.

6. Format the imported data as an Excel Table:

 Select data range, press Ctrl + T.

 Apply table style and enable filters.

7. Use filters to sort data or display specific rows.

8. Perform basic calculations:

 Use =SUM(range) to sum a column.

 Use =IF(condition, true_value, false_value) to apply logic.

 Use =VLOOKUP(lookup_value, table_array, col_index,

range_lookup) for data retrieval.

Observations:

 Data import options allow control over how external data is brought
in.

 Formatting as a table helps organize data and provides

sorting/filtering capabilities.

 Basic functions facilitate quick data analysis.

Experiment 2

Data Analysis using Pivot Tables and Charts

Objective:

 To summarize and visualize large datasets effectively using Pivot

Tables and Charts.

Procedure:

1. Select the data range.

2. Go to Insert > PivotTable.

3. In the dialog, choose the data source location and where to place
the pivot table.

4. Drag fields into the Rows, Columns, Values, and Filters areas to
create summaries.

5. Insert a Pivot Chart:

  With Pivot Table selected, go to Analyze > PivotChart.

 Choose chart type and customize.

6. Manage worksheets:

 Insert new sheets using the "+" icon.

 Rename by double-clicking the sheet tab.

 Copy sheets with right-click > Move or Copy.

 Protect sheets via Review > Protect Sheet with optional

password.

7. Use advanced formulas for dynamic data lookups:

 =INDEX(array, row_num, [col_num]) to find value at a position.

 =MATCH(lookup_value, lookup_array, [match_type]) to find

position of a value.

Observations:

 Pivot Tables simplify data summarization and uncover trends.

 Pivot Charts provide visual insights based on pivot data.

 Worksheet management features help organize workbooks.

 INDEX and MATCH functions enable powerful dynamic referencing

beyond VLOOKUP.

These detailed steps and observations will guide you through efficient
import, formatting, analysis, and management of data in Excel for
advanced statistical data analytics labs.

Unit – IV
Data Analysis – I: Descriptive Statistics and Visualization

Experiment 1

Data Tabulation and Visualization

Objective:

 To create organized frequency tables and visualize data using

charts.
Procedure:

1. Enter raw data in Excel worksheet.

2. Create frequency table:

 Use Pivot Table or COUNTIF formula to tabulate frequencies.

3. Generate cross-tabulation (two-way)

tables using Pivot Table with two variables.

4. Draw Bar Diagrams:

 Select frequency data,

 Go to Insert > Bar Chart or Column Chart.

5. Create Multiple Bar Diagrams:

 Arrange data with categories and groups,

 Insert clustered bar chart for comparison.

6. Create Pie Chart:

 Select data with category and values,

 Insert Pie Chart for proportion visualization.

7. Customize charts with titles, axis labels, data labels, and legends.

Observations:

 Tabulation simplifies data interpretation.

 Different chart types convey data insights visually.

 Pie charts show parts of a whole clearly.

Experiment 2

Measures of Central Tendency and Dispersion

Objective:

 To compute statistical measures summarizing dataset distribution.

 Explore relationships using correlation and regression.

Procedure:

1. Calculate measures of central tendency:

 Mean: =AVERAGE(range)
  Median: =MEDIAN(range)

 Mode: =[Link](range)

2. Compute measures of dispersion:

 Variance: =VAR.S(range)

 Standard Deviation: =STDEV.S(range)

 Coefficient of
Variation: (STDEV.S(range)/AVERAGE(range))*100

3. Analyze correlation:

 Use =CORREL(array1, array2) for correlation coefficient.

4. Draw regression line:

 Create scatter plot for two variables.

 Add trendline: Right-click data points > Add Trendline.

 Display equation on chart to interpret regression line Y =a+bX .

Observations:

 Central tendency and dispersion describe the data structure and

spread.

 Correlation quantifies linear relationships.

 Regression line models dependency and aids prediction.

These lab notes guide precise calculation and visualization of

statistical measures using Excel’s built-in features, supporting
analysis comprehension and effective communication

Unit – V
Data Analysis – II: Inferential Statistics and Time Series

Experiment 1

Hypothesis Testing using Excel

Objective:

 To perform hypothesis tests for comparing means and variances.

Procedure:

1. Perform Independent t-test and Paired t-test:

 Go to Data > Data Analysis > t-Test: Two-Sample Assuming

Unequal Variances or Paired Two Sample for Means.

 Select input ranges for two samples.

 Specify output range and significance level (usually 0.05).

2. Conduct F-test for equality of variances:

 Use Data > Data Analysis > F-Test Two-Sample for Variances.

 Select ranges and generate output.

3. Carry out One-Way ANOVA:

 Use Data > Data Analysis > ANOVA: Single Factor.

 Select multiple ranges representing groups.

 Analyze output for between-group and within-group variance

and p-value.

Observations:

 Low p-values (<0.05) indicate rejecting the null hypothesis.

 Tests help compare group means and variance assumptions.

Experiment 2

Time Series Analysis and Chi-square Test

Objective:

 Check independence in categorical data and analyze time series

trends.

Procedure:

1. Apply Chi-square test:

 Prepare contingency table of observed frequencies.

 Compute expected frequencies.

 Use formula χ 2=∑ ¿ ¿ or Excel

function [Link](range_observed, range_expected).

2. Forecast using Least Squares method:

  Plot time series data in scatter plot.

 Add trendline and display equation for Y =a+bX trend.

 Use equation for forecasting future data points.

3. Smooth data with Moving Averages:

 Use Data > Data Analysis > Moving Average.

 Enter interval (e.g., 3 periods) to smooth fluctuations.

4. Discuss statistical significance, trend, and model fit based on

outputs.

Observations:

 Chi-square test detects dependence between variables.

 Trendline assists in forecasting with a least squares model.

 Moving average reduces noise, highlighting patterns.

These notes outline steps and interpretations for using Excel’s statistical
tools to perform essential inferential analyses and time series forecasting
in lab settings.