CO5124: Data Analysis and Business Intelligence Case Study Report

This case study analyzes accommodation data from Cunningham Holdings Limited (CHL Hospitality) to address key business questions regarding pricing. The analysis, conducted using Microsoft Excel and statistical tests like ANOVA, examines the prices of various accommodation brands (Resort, Cottage, Classic, Comfort) across different states (NSW, QLD, VIC) and locations (Metropolitan, Regional). The report addresses five specific issues, including price differences across brands, states, and locations, as well as the impact of competition and comfort on pricing. The student utilizes pivot tables and ANOVA tests to derive insights and test hypotheses, concluding that brand prices differ, but not consistently across states or locations. The study provides a detailed breakdown of the methodology, including null and alternative hypotheses, significance levels, and the interpretation of results from the ANOVA tests, with appendices containing supporting data and test outputs. The report aims to provide actionable insights for CHL Hospitality's pricing strategies.

Running head: DATA ANALYSIS AND BUSINESS INTELLIGENCE
Data Analysis and Business Intelligence
Name of the Student:
Name of the University:
Author Note:

1DATA ANALYSIS AND BUSINESS INTELLIGENCE
Table of Contents
Introduction....................................................................................................................2
Action Plan and Hypothesis Testing..............................................................................2
Action Plan for 1st Issue.............................................................................................2
Action Plan for 2nd Issue and Hypothesis Testing......................................................3
Action Plan for 3rd Issue and Hypothesis Testing......................................................3
Action Plan for 4th Issue and Hypothesis Testing......................................................4
Action Plan for 5th Issue and Hypothesis Testing......................................................4
Conclusion......................................................................................................................5
Reference and Bibliography...........................................................................................6
Appendices.....................................................................................................................7
Appendix 1: Price of accommodation brands................................................................7
Appendix 2: Price of accommodation brands by State..................................................7
Appendix 3: Price of accommodation brands by location.............................................7
Appendix 4: One-way ANOVA test for difference in prices across brands..................8
Appendix 5: Two-way ANOVA test for difference in brand prices across state...........9
Appendix 6: Two-way ANOVA test for difference in brand prices across location...10
Appendix 7: Two-way ANOVA test for difference in brand prices while there is
comfort if there is competition.....................................................................................11

2DATA ANALYSIS AND BUSINESS INTELLIGENCE
Introduction
The aim of the report is to answer the question that has been requested by Mr
Oscar on 5 issues. The 5 issues are as mentioned below:
1. The current price of the accommodation brands by state and by location.
2. Is there any difference in prices across brands (Resort, Cottage and Classic)?
3. Is there any difference in prices across brands by state (NSW, QLD, VIC)?
4. Is there any difference in prices across brands by location (Metropolitan Cities
and Regional Cities)?
5. Is there any difference in prices across brands while there is comfort if there is
competition?
Action Plan and Hypothesis Testing
Action Plan for 1st Issue
A table is created using pivot table option of MS Excel for the price of
accommodation across brands by state and location. These tables present the average
price and the standard deviation of price.
The average price of Resort, Cottage and Classic is 201.46, 202.66 and 200.27
respectively.
The average price of Resorts in NSW, QLD and VIC are 199.27, 201.23 and
200.31 respectively. The average price of Cottage in NSW, QLD and VIC are 202.09,
203.78 and 202.10 respectively. The average price of Classic in NSW, QLD and VIC
are 201.71, 201.58 and 201.10 respectively.
The average price of Classic in Metropolitan and Regional cities are 201.46
and 202.31 respectively. The average price of Cottage in Metropolitan and Regional

3DATA ANALYSIS AND BUSINESS INTELLIGENCE
cities are 202.25 and 203.06 respectively. The average price of Resort in Metropolitan
and Regional cities are 200.11 and 200.43 respectively.
Action Plan for 2nd Issue and Hypothesis Testing
The data on price is sorted by brand and reorganized into separate columns for
each brand. To find out the result using statistical tool, the hypothesis is developed
which is presented below:
Null Hypothesis, H0 : The mean price of all brands are equal.
Alternate Hypothesis, H1 :At least one mean brand price is different.
The level of significance is set at 0.05. The one-way ANOVA test is
performed (Jaggia et al., 2016). The result shows that the p-value of the test statistic is
less than 0.05. This indicates that the alternative hypothesis is accepted at 0.05
significance level.
Action Plan for 3rd Issue and Hypothesis Testing
The data on price is sorted by brand first and reorganized into separate
columns for each brand. Then these columns are sorted by state. To find out the result
using statistical tool, the hypothesis is developed which is presented below:
Null Hypothesis, H0 1 : The mean price across all states are equal.
H0 2 :The mean price of all brands are equal.
H0 3 :The mean price of all brands across all states are equal.
Alternate Hypothesis, H1 :At least one mean brand price is different.
The level of significance is set at 0.05. The two-way ANOVA test is
performed (George & Mallery, 2016). The result shows that the p-value of the test

4DATA ANALYSIS AND BUSINESS INTELLIGENCE
statistic is less than 0.05 only for H02. Hence, null hypothesis is accepted at 0.05
significance level for H01 and H03. The alternative hypothesis is accepted for H03.
Action Plan for 4th Issue and Hypothesis Testing
The data on price is sorted by brand first and reorganized into separate
columns for each brand. Then these columns are sorted by state. To find out the result
using statistical tool, the hypothesis is developed which is presented below:
Null Hypothesis, H01 : The mean price across all states are equal.
H02 :The mean price of all brands are equal.
H03 :The mean price of all brands across all states are equal.
Alternate Hypothesis, H1 :At least one mean brand price is different.
The level of significance is set at 0.05. The two-way ANOVA test is
performed. The result shows that the p-value of the test statistic is less than 0.05 only
for H02. Hence, null hypothesis is accepted at 0.05 significance level for H01 and H03.
The alternative hypothesis is accepted for H03.
Action Plan for 5th Issue and Hypothesis Testing
The data on price is sorted by brand first and reorganized into separate
columns for each brand. Then these columns are sorted by state. To find out the result
using statistical tool, the hypothesis is developed which is presented below:
Null Hypothesis, H01 : The mean price across all states are equal.
H02 :The mean price of all brands are equal.
H03 :The mean price of all brands across all states are equal.
Alternate Hypothesis, H1 :At least one mean brand price is different.

5DATA ANALYSIS AND BUSINESS INTELLIGENCE
The level of significance is set at 0.05. The two-way ANOVA test is
performed (Baker & Hart, 2016). The result shows that the p-value of the test statistic
is less than 0.05 for all the hypothesis of the test. Hence, all of the alternative
hypothesis is accepted.
Conclusion
The analysis has found that the brand prices are different. However, the brand
prices does not differ across state and location. Now, if there exist completion then
there will be a difference in brand prices depending on the comfort.

6DATA ANALYSIS AND BUSINESS INTELLIGENCE
Reference and Bibliography
Anderson, D. R., Sweeney, D. J., Williams, T. A., Camm, J. D., & Cochran, J. J.
(2020). Modern business statistics with Microsoft Excel. Cengage Learning.
Baker, M. J., & Hart, S. (Eds.). (2016). The marketing book. Routledge.
George, D., & Mallery, P. (2016). General Linear Models: Two-Way ANOVA. In
IBM SPSS Statistics 23 Step by Step (pp. 183-190). Routledge.
Jaggia, S., Kelly, A., Salzman, S., Olaru, D., Sriananthakumar, S., Beg, R., &
Leighton, C. (2016). Essentials of Business Statistics: communicating with
numbers. McGrawhill Education.
Klasson, K. T. (2019). Two-way ANOVA for Unbalanced Data: The Spreadsheet
Way. USDA-ARS Research Notes.
Pyrczak, F. (2016). Success at statistics: A worktext with humor. Routledge.
Verma, S., & Patel, K. (2017, April). Association between shopping habit and
demographics of m-commerce user's in India using two way ANOVA. In
2017 2nd International Conference for Convergence in Technology (I2CT)
(pp. 38-43). IEEE.

7DATA ANALYSIS AND BUSINESS INTELLIGENCE
Appendices
Appendix 1: Price of accommodation brands
Row Labels Count of Price Average of Price StdDev of Price
Classic 48 201.46 3.39
Cottage 48 202.66 3.09
Resort 48 200.27 2.73
Grand Total 144 201.46 3.21
Price by Brand
Appendix 2: Price of accommodation brands by State
Row Labels Count of Price Average of Price StdDev of Price
Classic 48 201.46 3.39
NSW 16 201.71 4.20
QLD 16 201.58 2.94
VIC 16 201.10 3.09
Cottage 48 202.66 3.09
NSW 16 202.09 1.85
QLD 16 203.78 4.28
VIC 16 202.10 2.48
Resort 48 200.27 2.73
NSW 16 199.27 2.08
QLD 16 201.23 2.55
VIC 16 200.31 3.23
Grand Total 144 201.46 3.21
Appendix 3: Price of accommodation brands by location
Row Labels Count of Price Average of Price StdDev of Price
Classic 48 201.46 3.39
Metropolitan Cities 24 202.31 3.50
Regional Cities 24 200.61 3.13
Cottage 48 202.66 3.09
Metropolitan Cities 24 202.25 3.42
Regional Cities 24 203.06 2.74
Resort 48 200.27 2.73
Metropolitan Cities 24 200.11 2.75
Regional Cities 24 200.43 2.76
Grand Total 144 201.46 3.21
Price Brand by Location

8DATA ANALYSIS AND BUSINESS INTELLIGENCE
Appendix 4: One-way ANOVA test for difference in prices across brands
Anova: Single Factor
SUMMARY
Groups Count Sum Average Variance
Classic 48 9670.21 201.46 11.52
Cottage 48 9727.50 202.66 9.55
Resort 48 9612.98 200.27 7.43
ANOVA
Source of Variation SS df MS F P-value F crit
Between Groups 136.61 2 68.31 7.19 0.00 3.06
Within Groups 1339.46 141 9.50
Total 1476.08 143

9DATA ANALYSIS AND BUSINESS INTELLIGENCE
Appendix 5: Two-way ANOVA test for difference in brand prices across state
SUMMARY Resort Cottage Classic Total
NSW
Count 16 16 16 48
Sum 3188.34 3233.45 3227.35 9649.14
Average 199.27 202.09 201.71 201.02
Variance 4.33 3.43 17.67 9.71
QLD
Count 16 16 16 48
Sum 3219.69 3260.43 3225.33 9705.45
Average 201.23 203.78 201.58 202.20
Variance 6.49 18.32 8.62 11.96
VIC
Count 16 16 16 48
Sum 3204.95 3233.62 3217.53 9656.10
Average 200.31 202.10 201.10 201.17
Variance 10.42 6.16 9.58 8.90
Total
Count 48 48 48
Sum 9612.98 9727.50 9670.21
Average 200.27 202.66 201.46
Variance 7.43 9.55 11.52
ANOVA
Source of Variation SS df MS F P-value F crit
Sample 39.27 2 19.63 2.08 0.13 3.06
Columns 136.61 2 68.31 7.23 0.00 3.06
Interaction 24.98 4 6.25 0.66 0.62 2.44
Within 1275.21 135 9.45
Total 1476.08 143
Anova: Two-Factor With Replication

10DATA ANALYSIS AND BUSINESS INTELLIGENCE
Appendix 6: Two-way ANOVA test for difference in brand prices across location
SUMMARY Resort Cottage Classic Total
Metropolitan Cities
Count 24 24 24 72
Sum 4802.71 4854.02 4855.53 14512.26
Average 200.11 202.25 202.31 201.56
Variance 7.54 11.68 12.25 11.26
Regional Cities
Count 24 24 24 72
Sum 4810.27 4873.48 4814.68 14498.43
Average 200.43 203.06 200.61 201.37
Variance 7.60 7.48 9.78 9.51
Total
Count 48 48 48
Sum 9612.98 9727.50 9670.21
Average 200.27 202.66 201.46
Variance 7.43 9.55 11.52
ANOVA
Source of Variation SS df MS F P-value F crit
Sample 1.33 1 1.33 0.14 0.71 3.91
Columns 136.61 2 68.31 7.28 0.00 3.06
Interaction 42.52 2 21.26 2.26 0.11 3.06
Within 1295.62 138 9.39
Total 1476.08 143
Anova: Two-Factor With Replication

11DATA ANALYSIS AND BUSINESS INTELLIGENCE
Appendix 7: Two-way ANOVA test for difference in brand prices while there is
comfort if there is competition.
SUMMARY Classic Cottaage Resort Total
No
Count 16 16 16 48
Sum 3251.93 3239.95 3216.15 9708.03
Average 203.25 202.50 201.01 202.25
Variance 10.68 2.72 9.59 8.22
Yes
Count 16 16 16 48
Sum 3192.95 3227.12 3177.14 9597.21
Average 199.56 201.70 198.57 199.94
Variance 9.52 6.52 2.57 7.68
Total
Count 32 32 32
Sum 6444.88 6467.07 6393.29
Average 201.40 202.10 199.79
Variance 13.28 4.64 7.42
ANOVA
Source of Variation SS df MS F P-value F crit
Sample 127.93 1 127.93 18.45 0.00 3.95
Columns 89.56 2 44.78 6.46 0.00 3.10
Interaction 33.48 2 16.74 2.41 0.10 3.10
Within 624.04 90 6.93
Total 875.00 95
Anova: Two-Factor With Replication