This course is introduction to data analysis and research in business. We working on basic excel questions please read carefully the questions

 

 

MGMT203 Homework Assignment 1
Due: 16th February 4pm IMPORTANT:
For grading to be done correctly make sure you follow the instructions precisely.
- Write your answers in the yellow cells that are specified.
- In order for your charts to be graded you must save them as a separate chart sheet.
- Do not modify the formulas that are already given.
- Do not insert/delete worksheets, columns, rows unless you are told to do so.
- Do not submit ZIPPED files to SUCourse. Question 1
There are 3 types of tickets sold at a movie theater: Full price, membership price, student price.
You are given the ticket prices and monthly revenue of each type. You are also given the fixed
cost of operating the movie theater (you assume there is no variable cost) and tax rate. a.
Calculate total monthly revenue (B15:M15) and total annual revenue of each type
(N12:N14) in the designated cells. (5 points)
b.
Calculate the number of tickets sold as organized in the given table. The monthly
number of tickets sold of each type should be calculated such that when you enter one formula in
cell B19 and copy it to range B19: M121, all the calculations are done correctly (Hint: need to
use dollar signs for cell references in a certain way to get this done.) You should also have row
and column totals (10 points)
c.
Calculate the monthly profit by subtracting monthly fixed cost from the monthly total
revenue. Also calculate the total annual profit. (5 points)
d. Calculate monthly profit as percent of total annual profit. (5 points) e. Calculate annual net income after taxes in cell B29. (7 points) Question 2
You are given data on monthly utility expenses of a set of households. For each household
calculate: a. average daily utility expense (8 points) b. average daily utility expense per person at the household (8 points) Then, calculate
c. total daily utility expense for rural households using SUMPRODUCT. (10 points) d. Number of rural households. (7 points) e. Average daily utility expense for rural households (10 points) f. Overall average daily utility expense (for rural and urban). (10 points) Question 3 (15 points)
You are given the final exam, midterm exam, in-class exercises, homework assignments and
project grades.
Plot a scatter plot of midterm grades (along X-axis) versus final exam grades (along Y-axis).
Hint: To select two columns that are not adjacent to each other, first select the midterm column
and then press the CTRL key and select the final column while holding down the CTRL key.
Make sure:
• chart is saved as a separate chart sheet with an appropriate name, • there is an appropriate vertical axis title, • there is an appropriate horizontal axis title, • there is no legend, and • there are no gridlines. Additionally, comment on the graph you draw in few sentences like what can you interpret from
the drawn graph in the yellow box on the excel sheet.

 

Ticket type
Full
Member
Student Price
$18
$12
$9 Monthly fixed cost
Tax percentage $5,600
0.25 Revenue
Full
Member
Student
Total $
$
$ Jan
Feb
Mar
Apr
May
Jun
5,094 $
5,652 $
5,292 $
5,346 $
5,868 $
3,708
6,372 $
4,488 $
5,172 $
4,380 $
3,924 $
1,824
1,548 $
1,737 $
1,152 $
1,674 $
1,782 $
756 Tickets sold
Jan Feb Mar Apr May Jun Feb Mar Apr May Jun Full
Member
Student
Total
Total Profit Calculations (before tax)
Jan
Total Profit
% annual total
Total Profit after taxes $
$
$ Jul
Aug
Sep
Oct
Nov
1,980 $
2,214 $
5,580 $
5,940 $
5,886 $
3,096 $
2,652 $
4,740 $
4,980 $
5,244 $
1,017 $
693 $
1,800 $
1,665 $
1,602 $ Dec
5,940
4,968
2,088 Total Jul Aug Sep Oct Nov Dec Total Jul Aug Sep Oct Nov Dec Total Days/month 30 Household # Urban/Rural
Household Size (1/0)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 2
1
1
1
3
1
1
4
3
4
2
4
1
1
2
4
1
2
4
3
3
3
4 Monthly Utilities
1
1
0
0
1
1
1
1
1
1
1
1
1
0
1
1
1
1
1
1
1
1
1 $252
$256
$243
$242
$256
$251
$250
$254
$272
$321
$253
$259
$248
$245
$251
$355
$250
$287
$264
$254
$264
$264
$261 24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59 3
4
2
3
3
7
3
5
1
5
2
1
1
4
2
5
2
2
3
2
3
1
1
3
4
2
4
2
2
4
4
3
2
4
1
5 1
1
1
0
1
0
1
1
0
0
1
1
0
1
1
0
1
1
0
0
1
1
1
0
1
1
0
1
1
1
1
0
0
1
0
1 $249
$301
$245
$290
$259
$303
$261
$321
$253
$277
$151
$199
$247
$260
$253
$279
$244
$254
$260
$247
$257
$244
$250
$259
$260
$247
$249
$259
$246
$254
$261
$272
$249
$251
$244
$278 60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95 2
2
5
3
2
1
3
3
2
2
6
4
6
2
1
3
2
4
4
1
3
3
3
1
3
2
2
3
2
1
5
1
2
2
4
1 1
1
0
1
1
1
1
1
1
1
0
1
1
1
1
1
1
1
0
1
1
1
1
1
1
1
0
1
1
1
1
1
0
1
1
0 $254
$245
$265
$254
$255
$254
$259
$265
$259
$241
$278
$265
$275
$245
$239
$254
$242
$255
$259
$250
$264
$261
$257
$246
$270
$247
$254
$261
$255
$248
$263
$239
$250
$252
$253
$253 96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131 6
3
2
3
2
1
1
3
3
3
3
5
4
2
3
5
2
2
1
5
1
3
3
3
4
4
2
1
1
3
4
6
3
1
3
4 0
1
1
1
1
1
1
1
1
1
1
0
1
1
1
1
1
1
1
1
0
1
1
1
1
1
0
1
0
1
1
0
1
1
1
1 $268
$257
$245
$257
$243
$246
$255
$262
$261
$256
$253
$271
$257
$249
$260
$278
$251
$252
$255
$275
$246
$252
$255
$271
$254
$257
$254
$247
$248
$263
$254
$267
$256
$254
$254
$253 132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167 3
5
1
9
6
3
1
2
2
1
4
3
4
3
2
3
5
6
4
2
2
1
2
3
2
4
3
2
3
3
2
5
1
2
2
3 0
1
1
0
0
1
1
1
1
1
1
0
1
1
1
1
1
0
1
1
0
1
1
1
1
0
1
0
0
1
1
1
1
1
1
1 $252
$262
$246
$287
$276
$255
$254
$249
$245
$244
$266
$254
$263
$254
$253
$260
$271
$266
$261
$259
$247
$242
$256
$255
$244
$260
$248
$245
$268
$264
$249
$261
$252
$249
$254
$257 168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203 2
4
5
1
4
3
2
2
2
5
1
3
3
2
3
4
2
4
3
4
3
3
6
3
2
8
2
4
5
6
5
3
5
5
3
1 1
1
1
1
1
1
1
1
1
1
0
1
1
1
0
0
1
1
1
1
0
0
0
1
1
0
0
1
1
0
1
1
0
1
1
1 $246
$258
$269
$244
$258
$259
$247
$246
$239
$272
$260
$267
$266
$241
$258
$260
$248
$264
$264
$268
$256
$259
$266
$258
$254
$279
$245
$266
$268
$272
$279
$257
$267
$264
$268
$248 204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239 4
5
3
2
2
1
6
4
3
1
5
4
2
2
1
4
3
5
3
5
1
2
4
3
2
6
1
5
2
3
2
2
4
1
2
6 0
1
0
0
1
1
1
1
1
1
1
1
1
1
1
1
1
0
1
1
1
0
1
1
0
0
1
1
1
1
1
0
1
0
0
0 $264
$262
$266
$248
$252
$249
$271
$256
$251
$242
$264
$250
$257
$253
$252
$270
$267
$267
$247
$267
$250
$249
$253
$260
$252
$265
$249
$279
$257
$270
$248
$252
$255
$258
$251
$276 240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275 2
5
1
3
3
4
4
2
1
5
4
5
5
2
2
3
3
4
4
8
3
1
3
1
2
4
3
1
3
2
3
2
3
3
5
2 1
0
1
1
1
1
1
1
1
1
1
1
0
1
1
0
1
1
1
0
0
1
0
1
0
1
1
1
1
1
1
1
1
0
1
1 $245
$262
$248
$257
$256
$259
$250
$261
$252
$274
$252
$273
$267
$247
$249
$264
$262
$265
$251
$286
$259
$259
$254
$249
$255
$260
$254
$253
$258
$246
$259
$248
$259
$261
$266
$253 276
277
278
279
280
281
282 5
1
2
5
2
4
1 1
1
1
0
1
1
1 $275
$248
$246
$274
$253
$266
$251 Daily Utility Expense Per Person Statistics
Rural total
Nbr rural
Rural average
Overall average Daily Utility
Expense Daily Utility
Expense Per
Person MGMT203 Grades - Spring 2015 Midterm Inclass
0.565
0.087
0.250
0.000
0.000
0.146
0.254
0.000
0.000
0.880
0.620
0.609
0.076
0.891
0.359
0.457
0.185
0.285
0.641
0.761
0.276
0.000
0.087
0.163
0.570
0.576
0.385
0.211
0.333
0.189
0.250
0.211
0.120
0.059
0.000
0.522
0.285
0.120
0.622
0.417
0.000
0.339
0.293
0.065
0.370
0.124
0.230
0.578
0.222
0.685 0.914
0.150
0.592
0.339
0.211
0.917
1.000
0.906
0.815
1.000
1.000
1.000
0.933
1.000
1.000
0.963
0.250
0.761
1.000
1.000
1.000
1.000
0.625
0.969
0.688
0.991
1.000
0.357
1.000
0.898
0.842
0.792
0.794
0.066
0.332
1.000
1.000
0.406
0.891
1.000
0.315
0.313
0.930
0.297
0.698
0.978
0.989
1.000
1.000
1.000 Project
0.963
0.735
0.507
0.000
0.520
0.667
0.667
0.760
0.704
0.809
0.702
0.809
0.624
0.548
0.716
0.704
0.000
0.488
0.848
0.963
0.716
0.898
0.507
0.735
0.898
0.963
0.870
0.312
0.624
0.688
0.488
0.735
0.624
0.760
0.683
0.898
0.548
0.527
0.653
0.704
0.683
0.199
0.621
0.316
0.760
0.667
0.653
0.870
0.809
0.688 HomeworkFinal
0.944
0.000
0.742
0.507
0.000
0.872
0.891
0.253
0.555
0.913
0.946
0.942
0.942
0.989
0.895
0.737
0.225
0.884
0.960
0.965
0.923
0.946
0.479
0.854
0.869
0.947
0.956
0.453
0.961
0.908
0.879
0.559
0.966
0.000
0.551
0.956
0.842
0.435
0.899
0.771
0.603
0.616
0.901
0.000
0.801
0.888
0.956
0.943
0.868
0.923 0.512
0.417
0.298
0.000
0.262
0.143
0.405
0.202
0.488
0.762
0.667
0.738
0.083
0.652
0.429
0.381
0.000
0.417
0.536
0.667
0.464
0.619
0.333
0.250
0.703
0.702
0.560
0.179
0.476
0.321
0.524
0.536
0.179
0.000
0.512
0.560
0.464
0.357
0.488
0.512
0.417
0.298
0.500
0.000
0.643
0.405
0.690
0.655
0.202
0.536 0.109
0.304
0.000
0.513
0.185
0.265
0.178
0.109
0.304
0.120
0.000
0.000
0.914
0.704 0.869
0.763
0.305
1.000
1.000
0.966
0.867
0.597
0.842
0.742
0.148
0.162
0.791
0.536 0.488
0.507
0.000
0.870
0.848
0.621
0.621
0.688
0.653
0.312
0.000
0.064
1.000
0.978 0.863
0.880
0.157
0.925
0.937
0.867
0.904
0.452
0.894
0.011
0.000
0.046
0.842
0.897 0.333
0.536
0.000
0.631
0.667
0.357
0.393
0.357
0.488
0.000
0.000
0.000
0.540
0.420

Attachments:

• 1493797603-mgmt203_hw1.xlsx
• 1493797604-HomeworkAssignment1.docx