t test , ANOVA , correlation and regression analysis using Excel

Many researchers suffer from their institute not having statistical packages for data analysis. Don't worry. One can do most of the statistical analysis using Excel only. You will get a similar output as in the SPSS.
The procedure for testing a hypothesis is given below:

Step 1: Check your Excel have a data analysis option in Data for MS excel

Step 2: If you dont get data analysis in the data view go to top of the excel window button and press it.

Step 3: Click excel options and window will appear like this

Step 4: Now click on add Ins on the left side and select analysis tool pack

Step 5: Down you have manage and click on the Go and you will get window like this

Click ok the first screen of this post will appear. Now,click on data analysis and proceed with any hypothesis testing.

Next post I will discussion how to do hypothesis testing using Excel in detail
Thank you

Sample Size calculation

Sample size calculation:

Scenario 1: I don't know neither population mean, population standard deviation nor sample mean and sample standard deviation.

Use the Slovins formula


Sample size = N/ ( 1+N(e)2)

Example:

Say, There are six lakh households in city X and you would like to find monthly grocery purchased by these households. You would like to find the sample size for your research. You would like to allow 5% error in the research. Then sample size should be calculated as below

Sample Size = 600000/ 1+ 600000( 0.05)2
                    = 399.73
                   =  400 samples are required for the research.

For further details contact ; prasadkh90@gmail.com

Sample Size calculation using Excel

Can we calculate sample size using Excel? Yes.

Sample size can be calculated using a confidence  function in excel and followed by Goalseek function   with What if analysis of Excel.

Please do mail me for further clarification at

prasadkh90@gmail.com

Reliability tests Using SPSS

Reliability tests using SPSS.
Reliability of scale can be tested in SPSS using
1. Cronbach alpha
2. Test retest and
3. Guttman
For further details of calculations contact Prof. Prasad Kulkarni  prasadkh90@gmail.com.

Normality test using SPSS

How do we check normality in the SPSS?
There are many techniques to check normality in the SPSS. I will be discussing three here-
1. Through Histograms
2. Through Q- Q Plots.
3. Through explore option and conducting wilki's test or K- S test.
For details contact prasadkh90@gmail.com

Research Gap Analysis

Sl.No	Content description	content validity
1	Findings from various stakeholders (tourism professionals, scholars and IT vendors) and sources (observations, documents, job descriptions) produced a thorough list of social CRM capabilities and revealed the social CRM readiness of Greek tourism firms. The following capabilities are discussed: organisational culture and management, information resource management, information technology infrastructure, business strategy, customer-centric processes, communication, performance measurement.	Sigala, M. (2016). Social CRM Capabilities and Readiness: Findings from Greek Tourism Firms. In Information and Communication Technologies in Tourism 2016 (pp. 309-322). Springer International Publishing.
		Gaps identified: Customer engagement and relationship is not defined.
2	The social media ecosystem offers enormous value to advertisers as a piece of Programmatic Advertising; it provides an even more contextually targeted advertising solution where the influence of a friend or other trusted source, guides the user along a path to consuming new forms of content.	Dawson, P., & Lamb, M. (2016). Enhanced Success with Programmatic Social Advertising. In Programmatic Advertising (pp. 103-110). Springer International Publishing.
		Gaps identified: How peer generated content can be used as promotion tool
3	The findings suggest that consumers will use social media if the sector creates and clearly articulates consumer value from using social media. The sector also needs to address technology security perceptions to increase usage of social media.	Dootson, P., Beatson, A., & Drennan, J. (2016). Financial institutions using social media–do consumers perceive value?. International Journal of Bank Marketing, 34(1).
		Gaps Identified: Security is a bigger concern for customers
4	Brand social responsibility image and emotional brand attachment positively moderated the relationship between consumer moral identity centrality and intention to purchase CRM sponsor brand.	He, H., Zhu, W., Gouran, D., & Kolo, O. (2016). Moral identity centrality and cause-related marketing: the moderating effects of brand social responsibility image and emotional brand attachment. European Journal of Marketing,50(1/2).
		Gap Identified: Do brands matter in price sensitive markets.
5	A relationship is also shown between engagement and relational information processes, which is viewed as a performance outcome of social CRM.	Harrigan, P., Soutar, G., Choudhury, M. M., & Lowe, M. (2015). Modelling CRM in a social media age. Australasian Marketing Journal (AMJ), 23(1), 27-37.
		Gap identfied: How Indian e- tailers are using relational information processes to have better customer engagement.
6	positive and significant relationships among the personality variables and perceptions of usefulness and user satisfaction with a service provider's social CRM efforts.	Bailey, A. A. (2015). Factors Promoting Social CRM: A Conceptual Model of the Impact of Personality and Social Media Characteristics. International Journal of Customer Relationship Marketing and Management (IJCRMM),6(3), 48-69.
		Gaps identified: How Indian e- tailers are giving importance to user satisfaction for their social CRM efforts.
7	people are exchanging information across different countries, that people are conversing about a product on social media and people are sharing information about a product on social media and thus, proving the hypothesis. Further, the results indicate that the users in USA, Canada, and UK tweet more than the other countries, USA and UK being the highest in tweets followed by the Canada. On the other hand, the number of tweets in Australia, India, South Africa are low with New Zealand being the lowest of all the countries. This indicates that different countries’ users have different social media behaviour.	Hodeghatta, U. R., & Sahney, S. (2016). Understanding Twitter as an e-WOM. Journal of Systems and Information Technology, 18(1). Hudson, S., Huang, L., Roth, M. S., & Madden, T. J. (2015). The influence of social media interactions on consumer–brand relationships: A three-country study of brand perceptions and marketing behaviors. International Journal of Research in Marketing. VanMeter, R. A., Grisaffe, D. B., & Chonko, L. B. (2015). Of “Likes” and “Pins”: The Effects of Consumers' Attachment to Social Media. Journal of Interactive Marketing, 32, 70-88.
		Gap Identified: whether Indian consumers are sharing their information on social media? Do social media differs from one city to another city in India.
8	the way for future confirmatory empirical research of organizational activities, top management team support and effective internal communication in the rapid-response environment of social media. Findings also provide implications for marketing practitioners for the use and measurement of social media to achieve marketing objectives.	Choi, Y., & Thoeni, A. (2016). Social media: is this the new organizational stepchild?. European Business Review, 28(1), 21-38.
		Gap Identified: What is the role of top management in implementing Social CRM among e- tail companies.
9	Organizations are losing their once complete control over their messages and processes of value-creation to consumers, who are becoming active participants and co-creators of value in the relationship.	Martínez-López, F. J., Anaya-Sánchez, R., Aguilar-Illescas, R., & Molinillo, S. (2016). Evolution of the Marketing Mind-Set and the Value-Creation Process. In Online Brand Communities (pp. 65-85). Springer International Publishing.
		Gap Identified: Do e- commerce companies encourage message and idea creation among their customers.
10	social media plays an important role in communicating information to customers, but as an antecedent enhancing salesperson behaviors to increase customer satisfaction rather than a direct factor	Agnihotri, R., Dingus, R., Hu, M. Y., & Krush, M. T. (2015). Social media: Influencing customer satisfaction in B2B sales. Industrial Marketing Management.
		Gaps identified: Do social policy of organization bring cooperation among employees and customers
11	Traditionally, firms have tried to listen to primary stakeholders (e.g., customers, suppliers, creditors, employees) but have paid little attention to the concerns of secondary stakeholders (e.g., the general public, communities, activist groups). This is because primary stakeholders were perceived to have power, legitimacy, and urgency behind their requests, while secondary stakeholders had little or no leverage. With the coming of the Internet and social media this asymmetry of influence has changed. Today, secondary stakeholders have to be managed as adroitly as primary stakeholders.	Jurgens, M., Berthon, P., Edelman, L., & Pitt, L. (2016). Social media revolutions: The influence of secondary stakeholders. Business Horizons.
		Gaps identified: Importance of Public in the social CRM applications.
12	This study confirmed the importance of frequent updates and incentives for participation. In addition, several creative strategies were associated with customer engagement, specifically experiential, image, and exclusivity messages.	Ashley, C., & Tuten, T. (2015). Creative strategies in social media marketing: An exploratory study of branded social content and consumer engagement.Psychology & Marketing, 32(1), 15-27.
		Gap identified; Need of exclusive messages by Indian online retailers.
13	This research suggest that company's social media activities affect the company's value positively through raising the brand awareness and influencing the decision of buying.	Ioanid, A., Militaru, G., Negoita, O. D., & Dumitriu, D. (2015, October). MANAGING BUSINESS USING SOCIAL NETWORKS: THE RELATION BETWEEN A COMPANY'S SOCIAL MEDIA ACTIVITIES AND THE RESULTS OBTAINED. In International Conference on Management and Industrial Engineering (No. 7, p. 211). Niculescu Publishing House.
		Gap Identified: Do social media influence customer to buy brands.
14	Findings confirm that website service quality and consumers’ predispositions to use Facebook for online shopping directly and positively affect consumer trust toward an e-tailer. In contrast, peer recommendations affect attitude directly rather than indirectly via trust. The results further indicate that peer recommendations have a significantly stronger influence on attitudes of females than they do on attitudes of males.	Nadeem, W., Andreini, D., Salo, J., & Laukkanen, T. (2015). Engaging consumers online through websites and social media: A gender study of Italian Generation Y clothing consumers. International Journal of Information Management, 35(4), 432-442.
		Gap Identified: Do peer recommendations help consumer decision making? Does thei any impact of gender on social influence.
15	online reviews in social media, specifically overall rating and response to negative comments, should be managed as a critical part of hotel marketing.	Kumar, A., Bezawada, R., Rishika, R., Janakiraman, R., & Kannan, P. K. (2016). From Social to Sale: The Effects of Firm-Generated Content in Social Media on Customer Behavior. Journal of Marketing, 80(1), 7-25.
		Gap Identified: How fast Indian online retailers in replying the order information, queries of customer and delivery management.
16	The study also examines the relationship between the four mediators—trust, commitment, relationship quality, and relationship satisfaction— and the antecedents and consequences of relationship marketing.	Verma, V., Sharma, D., & Sheth, J. (2015). Does relationship marketing matter in online retailing? A meta-analytic approach. Journal of the Academy of Marketing Science, 1-12.
		Gap Identified: In the big country like India, delivery plays a significant role in customer satisfaction.
17	The results suggest that different dimensions of user reviews have significantly different effects in forming user evaluation and driving content generation.	Duan, W., Yu, Y., Cao, Q., & Levy, S. (2015). Exploring the Impact of Social Media on Hotel Service Performance A Sentimental Analysis Approach.Cornell Hospitality Quarterly, 1938965515620483.
		Gap identified: Do Indian online retailers have higher satisfaction from the users after their reviews/ Does company change their content after user reviews.
18	The expected outcome of this research paper is an insight on the power of the social medium “Twitter” and the way retailers are making use of this medium to reach out to their target customer base and enhance their sales to maintain a competitive edge over their competitors in the market	Priyanka, P. V., & Srinivasan, P. (2015). “Tweet” to sales–focus on the" Gen Y" in the Indian retail industry. ACADEMICIA: An International Multidisciplinary Research Journal, 5(6), 259-267.
		Gap identified: The article focused on purchase of the products but not highlighted isthere any repeated purchase by the customer due to online media.
19	The outcome of the study is that social media marketing had created a synchronized platform for doctors, pharmaceutical companies and patients (consumers) in more meaningful and coherent way. It not only creates the awareness about the disease but connects the patients of similar disease profile	Agrawal, S., & Kaur, N. (2015). Influence of Social Media Marketing in Indian Pharmaceutical Industry. International Journal, 3(4), 735-738.
		Gap identified: Impact of social media on pharma online retailers like medplus, healthkart etc..
20	Our study contributes to our knowledge body of social media marketing by demonstrating that social media activities for a brand can foster the consumer base of the brand, but that effort is not necessarily sales-oriented.	Xie, K., & Lee, Y. J. (2015). Social Media and Brand Purchase: Quantifying the Effects of Exposures to Earned and Owned Social Media Activities in a Two-Stage Decision Making Model. Journal of Management Information Systems, 32(2), 204-238.
		Gap identified: Will the lead generation or consumer awareness leads to sales.
21	The paper finds that the emergence of social media has empowered the users because they were able to comment and recommend the firm products more effectively than before. Starbucks closely follows the principle of ‘design with customers’ in defining the role of customers, allowing them to play the role of creators and evaluators of ideas. Taken together, social media has been an effective platform for Starbucks to better understand consumer needs and preferences that eventually bring forth much improvement in Starbucks’ operational performance.	Sam, Y., & Cai, Y. (2015). A Study on the Use of Social Media to Understand Consumer Preference: The Case of Starbucks. International Journal of Management and Business Research, 5(3), 207-214.
		Gap identified: How indian online retailers are encouraging comments and recommendation on their products. How customers are involved in product idea development.
22	This paper concludes with the likely impact of digital media including both the latest trends like electronic shopping and mobile marketing on an ongoing transformation of Indian retail industry. Changing lifestyles, time constraints, worsening traffic, easy availability of broadband, and growing aspirations of non-metro youth, coupled with cash-on-delivery option, are jostling factors for online shopping and bargain hunting in India. The sudden growth of e-tailing in India in the last couple of years has attracted large investments and new entrepreneurs into the industry.	Agarwal, S., & Agarwal, N. (2015). E-TAILING–HARNESSING THE POWER OF DIGITAL MEDIA. Journal of Management Value and Ethics, 5(2).
		Gap identified: What is the role of price and availability influence on consumers of e- tailers in India via social networking sites?

Non Parametric tests

NONPARAMETRIC  TESTS

Why NP Test?

When the sample distribution is unknown.

When the population distribution is abnormal i.e., data involves too many variables.

Make minimal assumptions about the underlying distribution of the data.

Broad categories of NP Tests

The NP tests can be grouped into three Broad categories based on how the data are organized:

A one-sample test - analyzes one field.

A test for related samples -  compares two or more fields for the same set of cases.

An independent-samples test  - analyzes one field that is grouped by categories of another field.

Various NP tests

There are a number of nonparametric tests. The important one are;

The Chi-Square Test

The Binomial Test

The Runs Test

The One-Sample Kolmogorov-Smirnov Test

Two-Independent-Samples Tests

Tests for Several Independent Samples

Two-Related-Samples Tests

Tests for Several Related Samples

Chi-Square Test

Tests the hypothesis that the observed frequencies do not differ from their expected values.

Example;

A large hospital schedules discharge support staff assuming that patients leave the hospital at a fairly constant rate throughout the week. However, because of increasing complaints of staff shortages, the hospital administration wants to determine whether the number of discharges varies by the day oftheweek. C:\ProgramFiles\SPSSInc\PASWStatistics18\Samples\English\dischargedata.sav

Data

Hypothesis

Use Chi-Square Test to test the assumption that patients leave the hospital at a constant rate.

Computations

Test Results

Discussion

chi-square statistic equals 29.389. This is computed by squaring the residual for each day, dividing by its expected value, and summing across all days.

Number of expected values that can vary before the rest are completely determined.

For a one-sample chi-square test, df is equal to the number of rows minus 1.

Asymp. Sig. is the estimated probability of obtaining a chi-square value greater than or equal to 29.389 if patients are discharged evenly across the week.

The low significance value (.000) suggests that the average rate of patient discharges really does differ by day of the week.

One-Sample Kolmogorov-Smirnov

The One-Sample Kolmogorov-Smirnov procedure is used to test the null hypothesis that a sample comes from a particular distribution. (Diagnostic test).

Computational Procedure

It involves finding the largest difference (in absolute value) between two cumulative distribution functions (CDFs)--one computed directly from the data; the other, from mathematical theory.

Example

An insurance analyst wants to model the number of automobile accidents per driver. She has randomly sampled data on drivers in a certain region. She wants to test to confirm that the number of accidents (X) follows a Poisson distribution.

This example uses the file autoaccidents.sav.

Results and discussion

The Poisson distribution is indexed by only one parameter--the mean. This sample of drivers averaged about 1.72 accidents over the past five years.

The next three rows fall under the general category Most Extreme Differences. The differences referred to are the largest positive and negative points of divergence between the empirical and theoretical CDFs.

The first difference value, labeled Absolute, is the absolute value of the larger of the two difference values .

This value will be required to calculate the test statistic.

The Positive difference is the point at which the empirical CDF exceeds the theoretical CDF by the greatest amount.

At the opposite end of the continuum, the Negative difference is the point at which the theoretical CDF exceeds the empirical CDF by the greatest amount.

The Z test statistic is the product of the square root of the sample size and the largest absolute difference between the empirical and theoretical CDFs.

Unlike much statistical testing, a significant result here is bad news. The probability of the Z statistic is below 0.05, meaning that the Poisson distribution with a parameter of 1.72 is not a good fit for the number of accidents within the past five years in this sample of drivers.

Generally, a significant Kolmogorov-Smirnov test means one of two things--either the theoretical distribution is not appropriate, or an incorrect parameter was used to generate that distribution.

Looking at the previous results, it is hard for the analyst to believe that the Poisson distribution is not the appropriate one to use for modeling automobile accidents.

Poisson is often used to model rare events and, fortunately, automobile accidents are relatively rare.

The analyst wonders if gender may be confounding the test. The total sample average assumes that males and females have equal numbers of accidents, but this is probably not true. She will split the sample by gender, using each gender's average as the Poisson parameter in separate tests.

The analyst wonders if gender may be confounding the test. The total sample average assumes that males and females have equal numbers of accidents, but this is probably not true.

She will split the sample by gender, using each gender's average as the Poisson parameter in separate tests.

The statistics table provides evidence that a single Poisson parameter for both genders may not be correct.

Males in this sample averaged about two accidents over the past five years, while females tended to have fewer accidents.

When assessing goodness of fit, remember that a statistically significant Z statistic means that the chosen distribution does not fit the data well.

Unlike the previous test, however, we see a much better fit when splitting the file by gender.

Increasing the Poisson parameter from 1.72 to 1.98 clearly provides a better fit to the accident data for men.

Similarly, decreasing the Poisson parameter from 1.72 to 1.47 provides a better fit to the accident data for women

Summary

Using the One-Sample Kolmogorov-Smirnov Test procedure, we found that, overall, the number of automobile accidents per driver do not follow a Poisson distribution.

However, once we split the file on gender, the distributions of accidents for males and females can individually be considered Poisson.

Conclusion

These results demonstrate that the one-sample Kolmogorov-Smirnov test requires not only that we choose the appropriate distribution but the appropriate parameter(s) for it as well.

If we want to compare the distributions of two variables, the two-sample Kolmogorov-Smirnov test in the Two-Independent-Samples Tests procedure is to be used.

The Runs Test Procedure

Many statistical tests assume that the observations in a sample are independent; in other words, that the order in which the data were collected is irrelevant.

If the order does matter, then the sample is not random, and we cannot draw accurate conclusions about the population from which the sample was drawn.

Therefore, it is prudent to check the data for a violation of this important assumption.

We can use the Runs Test procedure to test whether the order of values of a variable is random.

The procedure first classifies each value of the variable as falling above or below a cut point and then tests to ensure that there is no order to the resulting sequence.

The cut point is based either on a measure of central tendency ( mean, median, or mode) or a custom value.

We can obtain descriptive statistics and/or quartiles of the test variable.

Example

An e-commerce firm enlisted beta testers to browse and then rate their new Web site. Ratings were recorded as soon as each tester finished browsing. The team is concerned that ratings may be related to the amount of time spent browsing.

The ratings are collected in the file siteratings.sav. Test  the hypothesis that time spent in browsing is correlated with site rating.

Nonparametric Tests for Two Independent Samples

The nonparametric tests for two independent samples are useful for determining whether or not the values of a particular variable differ between two groups.

This is especially useful when the assumptions of the t test are not met.

When we want to test for differences between two groups, the independent-samples t test comes naturally to mind.

However, despite its simplicity, power, and robustness, the independent-samples t test is invalid when certain critical assumptions are not met.

These assumptions center around the parameters of the test variable (in this case, the mean and variance) and the distribution of the variable itself.

Most important, the t test assumes that the sample mean is a valid measure of center. While the mean is valid when the distance between all scale values is equal, it's a problem when our test variable is ordinal because in ordinal scales the distances between the values are arbitrary.

Furthermore, because the variance is calculated using squared distances from the mean, it too is invalid if those distances are arbitrary.

Finally, even if the mean is a valid measure of center, the distribution of the test variable may be so non-normal that it makes us suspicious of any test that assumes normality.

If any of these circumstances is true for our analysis, we should consider using the nonparametric procedures designed to test for the significance of the difference between two groups.

 They are called nonparametric because they make no assumptions about the parameters of a distribution, nor do they assume that any particular distribution is being used.

Two popular nonparametric tests of location (or central tendency)--the Mann-Whitney and Wilcoxon tests--and a test of location and shape--the two-sample Kolmogorov-Smirnov test.

From the above two tests, Mann-Whitney and Wilcoxon tests is commonly used test.

Mann-Whitney and Wilcoxon tests

We can use the Mann-Whitney and Wilcoxon statistics to test the null hypothesis that two independent samples come from the same population.

Their advantage over the independent-samples t test is that Mann-Whitney and Wilcoxon do not assume normality and can be used to test ordinal variables.

Physicians randomly assigned female stroke patients to receive only physical therapy or physical therapy combined with emotional therapy. Three months after the treatments, the Mann-Whitney test is used to compare each group's ability to perform common activities of daily life.

Data File

The results are in the file adl.sav. Test to determine whether the two groups' abilities differ.

The U statistic is simple (but tedious) to calculate. For each case in group 1, the number of cases in group 2 with higher ranks is counted. Tied ranks count as 1/2. This process is repeated for group 2. The Mann-Whitney U statistic displayed in the table is the smaller of these two values.

References

Siegel, S., and N. J. Castellan. 1988. Nonparametric statistics for the behavioral sciences. New York: McGraw-Hill, Inc..

Conover, W. J. 1980. Practical Nonparametric Statistics, 2nd ed. New York: John Wiley and Sons.

Daniel, W. W. 1995. Biostatistics, 6th ed. New York: John Wiley and Sons.

Norusis, M. 2004. SPSS 13.0 Guide to Data Analysis. Upper Saddle-River, N.J.: Prentice Hall, Inc..

Norusis, M. 2004. SPSS 13.0 Statistical Procedures Companion. Upper Saddle-River, N.J.: Prentice Hall, Inc..