Report Analytics Assignment | Essay Help Services

report using data provided in excel

Individual Report
This assignment requires you to build and estimate a model using Microsoft Excel or specialist
software to address policy/business/management related questions. Please upload both your report
file on Moodle.
Specifics
You are required to find a management decision problem in any company. Using Excel (or specialised
software), conduct an analytics for the problem, and interpret the results. You are required to submit a
technical report (1,500 words) that discusses the managerial problem, the results, their implications and
makes recommendations for improvement.
Your report will be marked according to the following criteria: problem description;
modelling and solution; discussion on the result; managerial recommendations.
The grade components will be approximately distributed as follows:
Introduction (5%)
Descriptive analysis (20%)
Regression analytics (25%)
Managerial interpretations and implications (40%)
Conclusion (10%)
Introduction
In this section, you will identify your research question – based on the Excel Data you are
assigned. It is important to give reasons for why you think it’s interesting to explore such
question.
Descriptive analysis:
Import the raw data to the data platform of your chosen software (e.g., MS excel), make sure it
imported correctly, identify the main tables and relationships between, produce relevant
visualizations.
Regression analysis:
Using appropriate regression models to address the managerial problem that you have identified in the
Introduction.
II. Steps to be taken
1. Please go to the module webpage in Unihub;
2. Go to the section named “ Individual Assignment”;
3. Upload the zip file to your laptop;
4. Open the file name “Assignment of Excel file” to individual student;
5. Identify the data file that is associated with your student ID.
6. Open the Excel file that you find in step 5. For example, if your student ID is
associated with Data 2, then you will work on Data 2.
III. Description of the EXCEL data files:
Data 1:
The file P02_03.xlsx contains data from a survey about consumer behavior.
Data 2:
The file P02_07.xlsx includes data on 204 employees at the (fictional) company Beta Technologies.
Data 3:
The data Lasagna Triers.xlsx is related to the buying behavior of customers.
Data 4:
Catalog Marketing.xlsx contains recent data on 1000 HyTex customers.
Data 5:
The file P03_63.xlsx contains financial data on 85 U.S. companies in the Computer and Electronic
Product Manufacturing sector (NAICS code 334) with 2009 earnings before taxes of at least $10,000.
Each of these companies listed R&D (research and development) expenses on its income statement.
Data 6:
The file P10_05.xlsx contains information about the human resources.
Data 7:
P11_14.xlsx This is data for Business Week’s top U.S. MBA programs in the MBA Data sheet of the file.
Data 8: The file P11_44.xlsx lists the test scores and performance ratings for a randomly selected group
of employees. It also lists their seniority (months with the company) at the time of the performance
rating.
IV. Some suggestions on how to proceed with the assignment
The best way to think about this assignment is to consider it a simplified version of your master
dissertation which you are going to do soon! As such, you can apply knowledge acquired from the
Dissertation Module, and any other modules to work on the assignment. In what follows, I would like to
provide you with some suggestions on how to explore the EXCEL data to write the report.
Let’s assume you are given a data that have information about HR. In this data you have variables such
as age, gender, work experience, education, salary, tenure and so on.
1. How to define a problem or research question?
It is sufficient to define just one research question. In this example, you can propose a question such as “
Does there exist a link between gender and wage differential?”. In other words, you want to study
whether female employees and male employees have the same salary level if they have the same level of
education, work experience….
It is also important to elaborate on why you think this is an interesting and relevant question. To do so,
it’s a good idea to incorporate some existing academic references that address the link between gender
and salary.
2. How to do the data analysis
2.1. Descriptive analysis
You can report some measure of central tendency such as mean, median, standard deviation, frequency
of the main variables in the Excel file;
2.2. Regression analysis
In this example, salary would be the dependent variable. You then can run the regression using the
techniques we talk in class.
3. Interpretation of the results
It’s important to provide some discussion on your findings. For example, if you find that the coefficient
for men is positive i.e., men have higher salary than women. You can explain why this might be the case.
Also, it’s good idea to make references to academic studies that may or may not support your results.
4. Recommendation
What would be the managerial implications for your results? For example, if men have higher salary
then women, what would you like to recommend either from the government’s policy perspective, and/or
from the firm’s HR practices perspective.

Don't use plagiarized sources. Get Your Custom Essay on
Report Analytics Assignment | Essay Help Services
For $10/Page 0nly
Order Essay

1.0 Introduction
This report analyses the opinion of respondents from 10 states in the USA in respect to
environmental policy. The data was collected from 399 respondents, and the analyses is
carried out using Microsoft Excel tools. The analysis is based on a number of research in
the past. First, study by Sundstrom and McCright (2014) based on multivariate order
logistic regression indicates that women are more likely to be concerned with
environmental issues than men in Sweden. Torgler et al. (2008) in-depth study on the
‘Differences in preferences towards the environment’ found out that age and
environmental preferences have negative correlation. Moreover, Wynes and Nicholas
(2017) argued that having fewer children can help fight environmental changes. While
Mair et al. (2019) argue that high salary represents a good but not sufficient step towards
dealing with environmental issues. Thus, based on these, this study sought to find out the
following:
a) Are women more likely to support environmental policy in the USA than men?
b) Is there a negative correlation between Age differences and environmental policy
opinion in the USA?
c) Is there a correlation between number of Children and environmental policy
opinion in the USA?
d) Is there a correlation between Salary differences and environment policy opinion
in the USA?
2.0 Descriptive analysis
According to Trochim and Donnelly (2001), descriptive statistics entails explanation of
data in terms what it is and what it shows. It is about describing or summarizing large
[Date] 4
volume of data into meaningful form for decision making purposes (Van Der Aalst, 2016).
However, this statistics is not useful in making conclusions past the data analysed
(Statistics.laerd.com, 2018). But, it is important to perform a descriptive analysis so as to
understand the data gathered in this research.
2.1 Gender
Analysis of Gender is carried out in this report based on the available data consisting of
399 respondents. As show in Table 1 below, Excel COUNTIF function was used to
summarize this data in terms of Gender, and then the data was converted into percentage
using Excel division function. The findings shows that out of the 399 respondents, 59%
of them are Female (assuming 2 represented Female) and the rest (41%) are Male
(assuming that 1 represented Male) as shown in Figure 1 below;
Figure 1: Illustration of respondents’ Gender by Number and Percentage (Source;
Author).
165
234
41%
59%
0%
10%
20%
30%
40%
50%
60%
70%
0
50
100
150
200
250
1=Male 2=Female
Respondents by Gender
Count Percentage
[Date] 5
Table 1: Computation of number and percentage of respondents’ Gender (Source;
Author).
2.2 Age
The Age is summarized in terms of Young, Middle-aged, and Elderly. As shown in Figure
2 below, 55% of the respondents were Middle-aged, 24% of the respondents were Elderly
and rest (22%) of them were Young.
Figure 2: Illustration of the number and percentage of respondents by Age (Source;
Author).
87
218
94
22%
55%
24%
0%
10%
20%
30%
40%
50%
60%
0
50
100
150
200
250
Young Middle-aged Elderly
Respondents by Age
Count Percentage
[Date] 6
Table 2: Computation of the number and percentage of respondents by Age (Source;
Author).
2.3 Children
Analysis using Excel COUNTIF function shows that the majority (42%) of the respondents
had 2 children. This is followed by 31% who said they had no child. 17% of them said
they had 1 child and only 10% of the respondents had 3 children as shown in Figure 3
below;
Figure 3: Respondents by number of children (Source; Author).
124
67
167
41
31%
17%
42%
10%
0%
5%
10%
15%
20%
25%
30%
35%
40%
45%
0
20
40
60
80
100
120
140
160
180
0 1 2 3
Respondents by children
Count Percentage
[Date] 7
Table 3: Computation of respondents in terms of number of children (Source; Author).
2.4 State
Analysing of the State of the respondents using the COUNTIF function seems to indicate
that the respondents were relative balanced across the 10 States of the USA as shown
in Figure 4 below. The top 4 State by number of respondents were Texas (12%), New
York and Michigan with 11.5% respectively, and then Florida at number 4 with 10.3% of
the respondents. The State with least number of respondents is Minnesota with only 7.8%
of them.
Figure 4: Illustration of Respondents by State (Source; Author).
48
33
38 41
46 46
33
44
39
31
12.0%
8.3%
9.5% 10.3%
11.5% 11.5%
8.3%
11.0%
9.8%
7.8%
0.0%
2.0%
4.0%
6.0%
8.0%
10.0%
12.0%
14.0%
0
10
20
30
40
50
60
Texas
California
Illinois
Florida
New York
Michigan
Arizona
Ohio
Virginia
Minnesota
Respondents by State
Count Percentage
[Date] 8
Table 4: Computation of respondents by State (source; author).
2.5 Salary
Analysis of Salary is done using Microsoft Excel Pivot Tables as shown in Figure 5, 6 and
7 below. Figure 5 indicates the average Salary of respondents in respect to their Age
group. The findings indicate that Middle-aged respondents are the most paid, with an
average Salary of $94,398.46, followed by Elderly respondents with an average salary of
$74,110.15. Young people are the least paid, with only an average salary of $45,434.79,
[Date] 9
Figure 5: Illustration of respondents’ average salary by age (Source; Author).
Figure 6 below illustrates the average salary by gender among the 399 respondents. The
insights indicates that Male (represented by 1) are the most paid, with an average salary
of $80,733.20 compared to Female who are paid have an average salary of $77,679.78.
Figure 6: Illustration of average salary by gender (Source; Author).
Also, Figure 7 illustrates the average salary by State among the respondents. The
findings clearly shows that respondents from Texas are the most paid, with an average
[Date] 10
of $84,970.65. They are followed by New York and Illinois, with $83,897.46 and
$82,723.76 respectively. The least paid respondents come from Virginia State with only
an average salary of $70,860.49.
Figure 7: Illustration of average salary by State (Source; Author).
Moreover, Figure is shows an Histogram of the Salary of the respondents, which was
developed based on the ‘Histogram’ tool under the ‘Data Analysis’ tool box of Microsoft
Excel. The Figure shows that salary of the respondents is skewed to the left and majority
of respondents earn between $72,062 and $86,741.
Figure 8: Illustration of respondents’ salary using Histogram (Source; Author).
54 48
40 38 31 26 26 25 24 23 21
10 8 6 5 4 4 4 1 1
0.00%
20.00%
40.00%
60.00%
80.00%
100.00%
120.00%
0
10
20
30
40
50
60
86740.84211
79401.52632
50044.26316
94080.15789
72062.21053
42704.94737
101419.4737
108758.7895
64722.89474
57383.57895
116098.1053
130776.7368
138116.0526
123437.4211
145455.3684
28026.31579
35365.63158
152794.6842
20687
More
Frequency
Bin
Histogram
Frequency Cumulative %
[Date] 11
Table 5: Data used in the creation of the Histogram (Source; Author).
Lastly, the salary of the respondents was subjected to ‘Descriptive Statistics’ under the
‘Data Analysis’ tool box of Microsoft Excel. The summary of the finding is shown in Table
6 below. The data confirms the Histogram finding that the salary is skewed to the left
since it has a positive Skewness of 0.30602321 (Kim, 2013). However, the negative
Kurtosis of 0.218380955 indicates that the respondents’ salary is less extreme than
[Date] 12
anticipated under normal distribution (Bhatt, 2015). Moreover, the summary shows that
the smallest salary is $20,687 and the highest is $169,134.
Table 6: Summary of Descriptive Statistics (Source; Author).
2.6 Respondents Opinion
Figure 9 indicates the respondents Opinion in respect to the environmental policy. The
findings indicate that majority (22%) of the respondents Strongly agree to it, followed by
21% who also agree to it. However, 21% of the respondent disagree about the policy and
20% strongly disagree about it.
[Date] 13
Figure 9: Illustration of respondents by Opinion (Source; Author).
Table 7: Computation of respondents Opinion (Source; Author).
3.0 Regression analytics
Regression analytics entails inferential statistics that is carried out with the view of finding
solutions to research questions (Trochim and Donnelly, 2001). They establish the
correlation between variables, and unlike descriptive statistics, they are capable of
providing conclusions beyond the data (Statistics.laerd.com, 2018). That is, they can be
used to predict future outcomes. The following regression analysis were carried out to
find answers to the questions developed in this study.
78
85
68
82 86
20%
21%
17%
21% 22%
0%
5%
10%
15%
20%
25%
0
10
20
30
40
50
60
70
80
90
100
Strongly
disagree
Disagree Neutral Agree Strongly agree
Respondents Opionion
Count Percentage
[Date] 14
3.1 Correlation between Gender and environmental policy opinion
Findings summarized in Figure 10 below done using Excel ‘Regression’ tool under the
‘Data Analysis’ tool box shows there is a negative correlation between Gender and
Environmental Policy opinion. R Squared shows that 78.15% of the variation in
environmental policy opinion can be explained by Gender.
Figure 10: Illustration Gender Line of fit plot (Source; Author)
3.2 Correlation between age differences and environmental policy opinions
Figure 11 below shows also that there is insignificant positive correlation between age
differences and environmental policy opinion. R Squared shows that less than 1% of the
changes in environmental policy opinions that can be explained by Age differences.
y = -0.1615x + 3.2887
R² = 0.0031
y = -0.1615x + 3.2887
R² = 0.7815
0
1
2
3
4
5
6
0 0.5 1 1.5 2 2.5
Opinion
Gender
Gender Line Fit Plot
Opinion
Predicted Opinion
Linear (Opinion)
Linear (Predicted Opinion)
[Date] 15
Figure 11: Illustration of Age Line Fit plot (Source; Author).
3.3 Correlation between number of children and environmental policy opinion
The findings summarized in Figure 12 below indicates that there is relatively significant
negative correlation between number of children and the environmental policy opinion in
the USA. R Squared shows 27.1% of the variation in environmental policy opinion is
affected by number of children.
Figure 12: Illustration of Children line fit plot (Source; Author).
y = 0.0004x + 3.0187
R² = 2E-05
y = 0.0004x + 3.0187
R² = 0.0054
0
1
2
3
4
5
6
0 10 20 30 40 50 60 70
Opinion
Age
Age Line Fit Plot
Opinion
Predicted Opinion
Linear (Opinion)
Linear (Predicted Opinion)
y = -0.0459x + 3.0928
R² = 0.0011
y = -0.0459x + 3.0928
R² = 0.271
0
1
2
3
4
5
6
0 0.5 1 1.5 2 2.5 3 3.5
Opinion
Children
Children Line Fit Plot
Opinion
Predicted Opinion
Linear (Opinion)
Linear (Predicted Opinion)
[Date] 16
3.4 Correlation between salary and environmental policy opinion
Figure 13 shows that there is insignificant positive correlation between salary and
environmental policy opinion in the USA. R Squared indicates less than 1% of changes
in environmental policy opinion can be explained by changes in salary.
Figure 13: Illustration of Salary line fit plot (Source; Author).
y = 2E-07x + 3.0138
R² = 2E-05
y = 2E-07x + 3.0138
R² = 0.0052
0
1
2
3
4
5
6
$0 $50,000 $100,000 $150,000 $200,000
Opinion
Salary
Salary Line Fit Plot
Opinion
Predicted Opinion
Linear (Opinion)
Linear (Predicted Opinion)
[Date] 17
Overall summary of the regression output discussed above is shown below;
Figure 14: Summary of Regression output (Source; Author).
4.0 Managerial interpretation and implications
The finding of this report has establish that there is a disparity between the existing
literature and the current situation in the USA. First, it is interesting to note that there is
insignificant correlation between age differences and environmental policy opinion in the
USA, contrary to the findings of Torgler et al. (2008), who had argued that a negative
correlation existed between these two variables. Secondly, it is also important to note that
there is no correlation between salary and environmental policy opinion in the USA,
contrary to Mair et al. (2019) findings that suggested high salary could influence
environmental concerns. The implication of these two findings is that increasing the salary
of people in organisation cannot lead to improved support for environment policies.
[Date] 18
Secondly, age is not important in making policy about environment and therefore no
attention is needed as far as this is concerned.
However, the findings establish that Gender has a negative correlation with environmental
policies, with an R Squared of 78.15%. Moreover, descriptive statistics indicated that
majority (59%) of the respondents were Female. Thus, this suggests that Male (41%) are
more likely to be concerned with environment policy than women, contrary to the findings
of Sundstrom and McCright (2014), who claimed a positive correlation existed between
women and environmental concerns. The implications is that women should be educated
more on the need to engage in environmental policy so as to enhance environment
protection and conservations.
Moreover, the findings indicated that a negative correlation exists between number of
children and environmental policy opinion. This is consistent with findings of Wynes and
Nicholas (2017) that suggested fewer children can enhance environmental protection.
The implication of this is that USA and organisations can promote environment policies if
they encourage people to have fewer children.
5.0 Conclusion
This report has analysed how Gender, Age, Number of children and Salary differences
affects environmental policies in the USA. The findings indicate that both Age and Salary
did not have any effect on environmental policy opinion. However, the findings indicated
that Gender affected negatively environmental concerns, especially women. A negative
correlation is also found in respect to Number of children. It is therefore concluded that
USA and organisations in this country can promote environmental policy by encouraging
[Date] 19
people to have fewer children and educating women more about environment and how it
affects them and people across the globe.
[Date] 20
References
Bhatt, R. (2015) What is the meaning of negative coefficient of kurtosis obtained in my
specific AFM sample? [online]. Available at:
https://www.researchgate.net/post/What_is_the_meaning_of_negative_coefficient_of
_kurtosis_obtained_in_my_specific_AFM_sample (Accessed 11th July 2019).
Kim, H. Y. (2013). Statistical notes for clinical researchers: assessing normal distribution
(2) using skewness and kurtosis. Restorative dentistry & endodontics, 38(1), 52-54.
Mair, S., Druckman, A., & Jackson, T. (2019) Higher Wages for Sustainable
Development? Employment and Carbon Effects of Paying a Living Wage in Global
Apparel Supply Chains. Ecological economics, 159, 11-23.
Statistics.laerd.com, (2018) Descriptive and inferential statistics. [online]. Available at:
https://statistics.laerd.com/statistical-guides/descriptive-inferential-statistics.php
(Accessed 11th July 2019).
Sundström, A., & McCright, A. M. (2014) Gender differences in environmental concern
among Swedish citizens and politicians. Environmental Politics, 23(6), 1082-1095.
Torgler, B., Garcia-Valiñas, M. A., & Macintyre, A. (2008) Differences in preferences
towards the environment: The impact of a gender, age and parental effect.
Trochim, W. M., & Donnelly, J. P. (2001). Research methods knowledge base (Vol. 2).
Cincinnati, OH: Atomic Dog Publishing.
Van Der Aalst, W. (2016). Data science in action. In Process Mining (pp. 3-23). Springer,
Berlin, Heidelberg.

 

Calculator

Calculate the price of your paper

Total price:$26

Need a better grade?
We've got you covered.

Order your paper