Excel Question

User Generated

rinatg

Business Finance

Organizational Behavior Analysis

UCDAVIS

Description

Overview and Submission Instructions

  • Analyze the data collected for the following question:
    Do people (1) donate more and/or (2) express more longing to help when a fundraiser is for a child vs. a dog?
  • Submit two files: One Excel file with the analyzed data, and one Word Doc with the output and interpretation. Each of the files will be named “Data Analysis - First and Last Name”

Step 1: Analyze Data (Excel File)

Download the data file from the experiment that was conducted. Name the file “Data Analysis - First and Last Name” because you will do the work in this file and turn it in.

PREPPING DATA: (1) The “Data Map” sheet tells you what the codes in the “Responses” sheet mean. On the demographics tab, use the “find and replace” option to replace all codes for gender, pets, and children (e.g., 1’s and 2’s for gender) with what they mean (e.g., male, female, other); this will make pivot tables easier to understand later. (2) Use vlookup to bring all demographic info from the demographic sheet to the responses sheet so all the data is in one sheet.

ANALYZING DATA: Create the below pivot tables, charts, and regression outputs within the same file, organized into the six sheets indicated below (the name of each sheet should be what is underlined). Make sure your charts are configured in a way that is most easy to understand and that they’ve been titled, labeled, and so on in a professional manner.

  1. Donation by Cond: Pivot table and column chart of average donation by condition. (Hint: You might have to change “sum” to “average” in the calculation so you know on average how people responded in each condition
  2. Donations by Cond Reg: Output of running a regression between condition and donation amount.
    Because Excel doesn’t process text data, you should create a new column that has condition in numerical form (0’s and 1’s) before running the regression. You can do this by duplicating the condition column (call the new column “ConditionNum”) and then using “find and replace” to replace text with 0’s and 1’s in the new column.

  3. Longing to Help by Cond: Pivot table and column chart of average longing to help by condition.

  4. Longing by Cond Reg: Output of running a regression between condition and longing to help.

  5. Longing by Cond and Pets: Pivot table and column chart of average longing by condition and whether participant has had a pet. Hint: Your main question is whether the effect is the same for pet owners and non-pet owners. Play with the “switch row column” option of your chart to see what format makes it easier to answer that question at a glance.

  6. Percent Pet Owners: Pivot table and pie chart of percent pet owners and non-pet owners in the experiment. Hint: You will have the same thing in the row field as you will have in the value field. Also, you need to use “count” not “average” and insert a pie chart to display the info as a percentage (you may have to click “design” and choose a design that shows the percentages on the pie chart).

Step 2: Interpret Data (Word Document)

Download this document and name it “Data Analysis - First and Last Name” because you will do the work in this file and turn it in to me. Fill it in according to the structure provided. Here is the stimuli used in the study: Child, Dog.

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached. Please let me know if you have any questions or need revisions.

Average of Donation Amount
4.058461538
4.051515152
4.054961832

Average of Donation Amount

Donation by condition
Average donation amount

Row Labels
Child
Dog
Grand Total

4.06
4.058
4.056
4.054
4.052
4.05
4.048
Child

condition
Condition

by condition

Dog

SUMMARY OUTPUT
Regression Statistics
Multiple R
0.001280661
R Square
1.64009E-06
Adjusted R Square
-0.007750285
Standard Error
2.732892801
Observations
131
ANOVA
df
Regression
Residual
Total

Intercept
ConditionNum

SS
MS
F
Significance F
1 0.00158 0.00158 0.000212 0.988417
129 963.4627 7.468703
130 963.4643

Coefficients Standard Error t Stat
P-value Lower 95% Upper 95%Lower 95.0%
4.051515152 0.336396 12.04389 7.47E-23 3.385948 4.717083 3.385948
0.006946387 0.477562 0.014546 0.988417 -0.93792 0.951814 -0.93792

Upper 95.0%
4.717083
0.951814

Average of Longing to Help
55.359375
64.29850746
59.93129771

Average of Longing to Help

Longing to help by condition
Average longing to help

Row Labels
Child
Dog
Grand Total

65
60
55
50
Child

condition
Condition

help by condition

Dog

condition

SUMMARY OUTPUT
Regression Statistics
Multiple R
0.153894
R Square
0.023683
Adjusted R Square
0.016115
Standard Error
29.88551
Observations
131
ANOVA
df
Regression
Residual
Total

Intercept
ConditionNum

SS
MS
F
Significance F
1 2794.881 2794.881 3.129264 0.079262
129 115215.5 893.1434
130 118010.4

Coefficients
Standard Error t Stat
P-value Lower 95% Upper 95%Lower 95.0%
64.51515 3.678651 17.53772 5.89E-36 57.23685 71.79345 57.23685
-9.23823 5.22237 -1.76897 0.079262 -19.5708 1.094358 -19.5708

Upper 95.0%
71.79345
1.094358

Average of Longing to Help
Row Labels
Child
Dog
Grand Total

Column Labels
No Pet
60.11111111
64
61.66666667

Yes Pet
Grand Total
54.58181818
55.359375
64.32786885 64.29850746
59.70689655 59.93129771

average longing to help

Average of Longing to Help
66
64
62
60
58
56
54
52
50
48

Pets
No Pet
Yes Pet

Child

Dog

condition
Condition

Row Labels
No Pet
Yes Pet
Grand Total

Count of Pets
15
116
131

Count of Pets

Percent Pet Owners
No Pet
11%

Yes Pet
89%

Owners

StartDate
EndDate
1/21/2021 15:01 1/21/2021 15:02
1/21/2021 15:11 1/21/2021 15:17
1/21/2021 15:13 1/21/2021 15:14
1/21/2021 15:15 1/21/2021 15:15
1/21/2021 15:15 1/21/2021 15:16
1/21/2021 15:22 1/21/2021 15:23
1/21/2021 15:32 1/21/2021 16:12
1/21/2021 15:40 1/21/2021 15:41
1/21/2021 15:43 1/21/2021 15:44
1/21/2021 15:48 1/21/2021 15:50
1/21/2021 15:48 1/21/2021 15:49
1/21/2021 17:29 1/21/2021 17:33
1/21/2021 18:56 1/21/2021 18:58
1/21/2021 19:03 1/21/2021 19:04
1/21/2021 19:43 1/21/2021 19:44
1/21/2021 20:02 1/21/2021 20:04
1/21/2021 20:05 1/21/2021 20:06
1/21/2021 20:09 1/21/2021 20:10
1/21/2021 20:27 1/21/2021 20:29
1/21/2021 21:17 1/21/2021 21:17
1/22/2021 0:27
1/22/2021 0:28
1/22/2021 0:38
1/22/2021 0:40
1/22/2021 10:20 1/22/2021 10:21
1/22/2021 11:32 1/22/2021 11:33
1/22/2021 11:37 1/22/2021 11:39
1/22/2021 12:00 1/22/2021 12:02
1/22/2021 15:02 1/22/2021 15:04
1/22/2021 15:14 1/22/2021 15:18
1/22/2021 16:40 1/22/2021 16:41
1/22/2021 17:09 1/22/2021 17:10
1/22/2021 17:35 1/22/2021 17:37
1/22/2021 22:51 1/22/2021 22:54
1/23/2021 13:18 1/23/2021 13:19
1/23/2021 13:33 1/23/2021 13:34
1/23/2021 14:23 1/23/2021 14:26
1/23/2021 14:29 1/23/2021 14:30
1/23/2021 14:30 1/23/2021 14:31
1/23/2021 16:13 1/23/2021 16:15
1/23/2021 18:35 1/23/2021 18:36
1/23/2021 18:39 1/23/2021 18:40
1/24/2021 1:24
1/24/2021 1:25
1/24/2021 2:58
1/24/2021 2:59
1/24/2021 13:17 1/24/2021 13:20
1/24/2021 14:32 1/24/2021 14:33
1/24/2021 16:41 1/24/2021 16:43

ResponseId
R_1CDwXjUGoeRJqC8
R_3ha622uRFgH33Dn
R_1OTyUdOEN9730YT
R_1QtunCrPnMBku6p
R_vDHWkzY1r2UvMd3
R_bDvJuE2M6VmlsJ3
R_3rTn57RQiOEwuhr
R_3Gg56j7tqr6NqrU
R_2wTXUvBX8fQqmXy
R_2rWUxztTnKfLvFf
R_2zNHrskrpyERwFe
R_3D1JDqwiRKcfJQH
R_2bV4HAZSNTkGYqm
R_ULOFQaUonK4ZD21
R_3q7lkL6NzpuDnRb
R_3jeq4lUaA2PmtB9
R_1ihc8DC3CdG6bKU
R_1q7E5HXbyjTG6rM
R_1FQ8lUXs1LERrOA
R_R2GXHqqmB8KRbIB
R_2VIRQWbqpIW8qYD
R_1ilEcGpKXmiYroC
R_1mIummzfCezteoa
R_eII0DWFEVenKrXX
R_3EMchgMVA6zrlyA
R_vTPzmCzfRgkpUyt
R_1H2G95eIn9yjLnb
R_eKT1u6URVtxyhBn
R_2318l7XA2uARh6L
R_1BSo94Xiz30Yt3S
R_11XwrrIeIgsPJHP
R_1pSRku8OrrPd8xX
R_27P4x5zjtx1M6zX
R_3GcS7uRNsYFryqL
R_2zubiLEfOHytngz
R_PtcDaXju9ikH8yJ
R_1j8PtfO0Tu0LPGa
R_2xzUHcYi4Udg4hJ
R_3KB1pbBeU7xkj4g
R_2uw8lv7C0Knhb6c
R_1FhdAJtQAEXjWDV
R_3iD9zA3xMInbDcv
R_3qpnmyMrB6BFR8o
R_2YAhgGAHMKTqXTB
R_3nvBcgSKSMrvae8

Condition
Child
Dog
Child
Dog
Dog
...


Anonymous
Really great stuff, couldn't ask for more.

Studypool
4.7
Indeed
4.5
Sitejabber
4.4

Similar Content

Related Tags