FREE Trial
Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

4.0OnlineCoffee is a coffee shop chain providing coffee, hot chocolate, and tea for customers who can enjoy their drinks by placing orders online and wait

4.0OnlineCoffee is a coffee shop chain providing coffee, hot chocolate, and tea for customers who can enjoy their drinks by placing orders online and wait for the delivery. The shop is successful and currently has a plan to expanding its business. To make a good business strategy, the shop hires you as a data scientist to analyse their current data to extract valuable insights into their sales.

You are given an Excel file with data (DataExplorationAssignment.xlsx). Your work is to develop an analytic dashboard analytic dashboard for the managing team. The template of the dashboard is provided in the Excel file which consists of 4 tasks.

  1. Product Statistics [25 marks]
  2. You will need to find all products/drinks (with their sizes) the shop is selling and present the statistics of their price (mean, median, max, min). [10 marks] Example: If we have 3 products, each with 3 different sizes as follows then the max price will be 5.3 and the min price is 3.4.
Product Small Standard Large
Cappuccino 3.9 4.6 5.3
Chai Latte 3.9 4.6 5.3
Choc Duo 3.4 4.1 4.8
  1. You will need create a chart to show the price of every product (with different sizes) [15 marks]Example: For the Cappuccino, we show the price of its Small, Standard, and Large sizes. Similar for other product.

  1. Sale Statistics [25 marks]
  2. You will present the sale statistics of each product's sale (the number of each product sold in the data) [10 marks]
  3. You will present a chart to show the number of each product sold and the its sale values. [15 marks]

For example, there were 3 large size Cappuccinos (5.3$ each) and 2 small size Cappuccinos hav been sold (3.9$ each), therefore the number of sale of Cappuccino is 5 and the sale value of Cappuchino is 3x5.3 +2x3.9= 23.7$. Note: You may not need to use multiplication.

  1. Sale Trend [25 marks]
  2. You will find the number of sales (how many products have been sold) each day from Monday to Sunday. This is the sale amount. [10 marks].
  3. You will present a chart to show the trend of the sale in each day [15 marks]

  1. Analysis Report [25 marks]
  2. From Product Statistics, Sale Statistics, Sale Trend, write a short analysis from the information extracted. [15 marks]
  3. What important information is missing from this dashboard? Explain why it is important. [10 marks]
<

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Geometry A Comprehensive Course A Comprehensive Course

Geometry A Comprehensive Course A Comprehensive Course

Authors: Dan Pedoe

1st Edition

0486131734, 9780486131733

More Books

Students also viewed these Mathematics questions

Question

★★★★★
On January 1, 2019, Lesley Benjamin signed an agreement, covering 5 years, to operate as a franchisee of Campbell Inc. for an initial franchise fee of $50,000. The amount of $10,000 was paid when the...

Answered: 1 week ago

Question

★★★★★
The Wilhoit Company has observed that there is a linear relationship between indirect labor expense (ILE) and direct labor hours (DLH). Data for direct labor hours and indirect labor expense for 18...

Answered: 1 week ago

Question

★★★★★
3. Allow choices (stories in language arts or projects in science) based on students interests.

Answered: 1 week ago

Question

★★★★★
Sixty-three percent of U.S. adults oppose special taxes on junk food and soda. You randomly select 10 U.S. adults. Find the probability that the number of U.S. adults who oppose special taxes on junk...

Answered: 1 week ago

Question

★★★★★
on uestion 1 of 41 -11 lew Policies Current Attempt in Progress Red Grass Company produces high definition television sets and uses total cost-plus pricing. The following information is availa for...

Answered: 1 week ago

Question

★★★★★
12. Now, the landlords problem is maxe. H (e. h + (1 e) l) h.1 such that e h-l C 1 =lLL - where the first constraint is the tenant's ICC and the second is the limited liability con- straint (LLC)....

Answered: 1 week ago

Question

★★★★★
QS 21-6 (Algo) Contribution margin ratio LO A1 Compute the contribution margin ratio and fixed costs using the following data. Sales $ 4,800 Variable costs $ 2,976 Income $ 520 Contribution margin...

Answered: 1 week ago

Question

★★★★★
B. Credit Card Volume: The total credit card volume for Visa, MasterCard, American Express, and Discover has increased dramatically in recent years, as shown in the table below. (Source, CardWeb...

Answered: 1 week ago

Question

★★★★★
Two desks are setup parallel (customer may choose any of these two desks). Each desk takes 26 minutes with each customer. What is the throughput of this parallel system? Provide your answer in...

Answered: 1 week ago

Question

★★★★★
Entrepreneur and firm founder Oscar Farinetti has said that Eataly's "informal style of communication shows how direct and approachable we are, just the way people like." A focus on customer...

Answered: 1 week ago

Question

★★★★★
Bank Organizer Printers Inc, produces luxury checkbooks with three checks and stubs per page. Each checkbook is designed for an individual customer and is ordered through the customers bank. The...

Answered: 1 week ago

Question

★★★★★
PA1 LO 10.1 Artisan Metalworks has a bottleneck in their production that occurs within the engraving department. Jamal Moore, the COO is considering hiring an extra worker, whose salary will be...

Answered: 1 week ago

Question

★★★★★
Name and discuss each type of promissory note. Choose one type of promissory note and discuss why you would prefer this note? What is the purpose of a mortgage note and discuss whether it is a...

Answered: 1 week ago

Question

★★★★★
Cost of goods manufactured. Consider the following account balances (in thousands) for the Canseco Company: REQUIRED 1. Prepare a schedule of cost of goods manufactured for 2009. 2. Revenues in 2009...

Answered: 1 week ago

Question

★★★★★
Finding unknown balances. An auditor for Canada Revenue Agency is trying to reconstruct some partially destroyed records of two taxpayers. For each case in the accompanying list, find the unknowns...

Answered: 1 week ago

Question

★★★★★
Income statement and schedule of cost of goods manufactured. The Powell Corporation Operating income $50 has the following account balances (in millions): REQUIRED Prepare an income statement and a...

Answered: 1 week ago

Previous Question Next Question
Z ID Coffee Size Unit cost Day Dashboard
0 1652 Hot Chocolate Standard 4.8 Friday Author: _____________. ID: _________________
1 1146 Short Macchiato Small 3.7 Sunday
2 1706 Long Black Standard 4.5 Tuesday Product Statistics CHART 01: Product price
3 1938 Marble Mocha Small 4.4 Sunday Mean Price ?
4 1628 Tea Small 3.5 Wednesday Median Price ?
5 1630 Cappuccino Large 5.3 Tuesday
6 1604 Marble Mocha Standard 5.1 Friday Min Price ?
7 1628 Choc Duo Large 5.8 Thursday Max Price ?
8 1218 Cappuccino Small 3.9 Wednesday
9 1316 Flat White Small 3.9 Saturday
10 1628 Chai Latte Large 5.3 Friday
11 1668 Hot Chocolate Standard 4.8 Friday
12 1253 Vienna Small 4.2 Thursday
13 1628 Espresso Small 3.5 Wednesday
14 1557 Espresso Small 3.5 Thursday
15 1822 Marble Mocha Small 4.4 Friday
16 1178 Dark Chocolate Large 5.5 Wednesday
17 1604 Latte Small 3.9 Friday
18 1148 Espresso Small 3.5 Thursday Sale Statistics CHART 02: Sale Quantity and Sale Values
19 1176 Short Macchiato Small 3.7 Monday Mean Sale Number ?
20 1253 Piccolo Latte Small 3.7 Sunday Median Sale Number ?
21 1668 Tea Small 3.5 Saturday
22 1668 Chai Latte Small 3.9 Friday Min Sale ?
23 1146 Latte Small 3.9 Tuesday Max Sale ?
24 1923 Cappuccino Small 3.9 Friday
25 1604 Piccolo Latte Small 3.7 Thursday
26 1301 Chai Latte Small 3.9 Thursday
27 1923 Latte Standard 4.6 Monday
28 1975 Piccolo Latte Small 3.7 Friday
29 1121 Marble Mocha Large 5.8 Friday
30 1019 Flat White Standard 4.6 Tuesday
31 1315 Long Macchiato Standard 4.5 Tuesday
32 1131 Long Macchiato Standard 4.5 Sunday
33 1666 Espresso Small 3.5 Saturday
34 1947 Tea Large 3.7 Saturday
35 1556 Choc Duo Large 5.8 Wednesday
36 1167 Latte Standard 4.6 Friday Sale Trend
37 1746 Choc Duo Small 4.4 Friday Day Sale Amount CHART 03: Sales per days
38 1121 Latte Small 3.9 Tuesday Monday ?
39 1557 Long Macchiato Large 5.2 Saturday Tuesday ?
40 1829 Vienna Large 5.6 Wednesday Wednesday ?
41 1630 Tea Large 3.7 Sunday Thursday ?
42 1131 Vienna Small 4.2 Saturday Friday ?
43 1198 Flat White Large 5.3 Tuesday Saturday ?
44 1146 Espresso Small 3.5 Saturday Sunday ?
45 1267 Espresso Small 3.5 Sunday
46 1743 Long Macchiato Large 5.2 Friday
47 1668 Marble Mocha Small 4.4 Monday
48 1027 Hot Chocolate Standard 4.8 Monday
49 1743 Hot Chocolate Standard 4.8 Monday
50 1176 Piccolo Latte Small 3.7 Wednesday
51 1569 Tea Large 3.7 Sunday
52 1829 Choc Mocha Large 5.8 Friday
53 1746 Dark Chocolate Standard 4.8 Saturday
54 1923 White Chocolate Small 4.1 Thursday Please write down your analysis by summarising the information from the statistical and visualised information above. Also let us know what we are still missing in this dashboard?
55 1072 Long Black Large 5.2 Monday :
56 1292 Vienna Standard 4.9 Thursday Analysis point 1 (example, delete it when you write your analysis): Saturday is the best selling day.
57 1253 Flat White Standard 4.6 Friday
58 1938 Espresso Small 3.5 Tuesday
59 1652 Long Macchiato Small 3.8 Monday
60 1131 Espresso Small 3.5 Thursday
61 1975 Dark Chocolate Small 4.1 Tuesday
62 1146 Vienna Standard 4.9 Wednesday
63 1666 Espresso Small 3.5 Wednesday
64 1829 Tea Small 3.5 Tuesday
65 1569 Chai Latte Large 5.3 Tuesday
66 1553 Piccolo Latte Small 3.7 Friday
67 1292 Piccolo Latte Small 3.7 Monday
68 1975 Latte Large 5.3 Tuesday
69 1569 Tea Small 3.5 Friday
70 1628 Tea Large 3.7 Wednesday
71 1292 Espresso Small 3.5 Saturday
72 1668 Long Macchiato Small 3.8 Thursday
73 1666 Flat White Small 3.9 Saturday
74 1628 Chai Latte Large 5.3 Saturday
75 1743 Long Black Small 3.8 Thursday
76 1176 Flat White Large 5.3 Monday
77 1604 Dark Chocolate Standard 4.8 Thursday
78 1301 Choc Duo Large 5.8 Saturday
79 1628 Piccolo Latte Small 3.7 Sunday
80 1316 Choc Duo Standard 5.1 Thursday
81 1820 Long Macchiato Standard 4.5 Friday
82 1316 White Chocolate Standard 4.8 Monday
83 1027 Long Black Standard 4.5 Wednesday
84 1630 Vienna Large 5.6 Monday
85 1630 Latte Small 3.9 Friday
86 1405 Cappuccino Standard 4.6 Saturday
87 1652 Chai Latte Small 3.9 Sunday
88 1216 Dark Chocolate Large 5.5 Friday
89 1668 Espresso Small 3.5 Tuesday
90 1131 Long Black Large 5.2 Monday
91 1405 Piccolo Latte Small 3.7 Saturday
92 1927 Cappuccino Small 3.9 Friday
93 1131 Short Macchiato Small 3.7 Wednesday
94 1975 Short Macchiato Small 3.7 Tuesday
95 1628 Latte Large 5.3 Thursday
96 1975 Choc Duo Small 4.4 Wednesday
97 1101 Hot Chocolate Large 5.5 Saturday
98 1146 Long Macchiato Large 5.2 Saturday
99 1167 Piccolo Latte Small 3.7 Tuesday
100 1628 Choc Duo Standard 5.1 Thursday
101 1628 Vienna Small 4.2 Thursday
102 1292 Dark Chocolate Small 4.1 Sunday
103 1628 Dark Chocolate Standard 4.8 Sunday
104 1938 Short Macchiato Small 3.7 Sunday
105 1146 White Chocolate Large 5.5 Sunday
106 1315 Latte Standard 4.6 Thursday
107 1301 Choc Mocha Small 4.4 Saturday
108 1706 Long Macchiato Standard 4.5 Monday
109 1743 Chai Latte Large 5.3 Monday
110 1253 White Chocolate Small 4.1 Tuesday
111 1072 Latte Large 5.3 Friday
112 1927 Choc Duo Large 5.8 Thursday
113 1027 Long Black Small 3.8 Wednesday
114 1326 Dark Chocolate Large 5.5 Sunday
115 1013 Chai Latte Small 3.9 Wednesday
116 1326 White Chocolate Small 4.1 Saturday
117 1556 Short Macchiato Small 3.7 Monday
118 1019 Espresso Small 3.5 Sunday
119 1860 Espresso Small 3.5 Wednesday
120 1628 Espresso Small 3.5 Sunday
121 1668 Espresso Small 3.5 Saturday
122 1822 Flat White Large 5.3 Tuesday
123 1604 Hot Chocolate Standard 4.8 Friday
124 1628 Espresso Small 3.5 Sunday
125 1178 Marble Mocha Small 4.4 Friday
126 1628 Piccolo Latte Small 3.7 Monday
127 1148 Tea Large 3.7 Wednesday
128 1315 Latte Standard 4.6 Friday
129 1253 Espresso Small 3.5 Sunday
130 1628 Piccolo Latte Small 3.7 Sunday
131 1101 Choc Duo Large 5.8 Saturday
132 1923 Long Black Large 5.2 Tuesday
133 1666 Espresso Small 3.5 Monday
134 1057 Chai Latte Standard 4.6 Sunday
135 1553 Vienna Standard 4.9 Sunday
136 1630 Latte Small 3.9 Friday
137 1315 Marble Mocha Standard 5.1 Friday
138 1178 Chai Latte Large 5.3 Thursday
139 1131 Vienna Standard 4.9 Wednesday
140 1198 Latte Small 3.9 Monday
141 1131 Long Macchiato Small 3.8 Wednesday
142 1628 Cappuccino Large 5.3 Saturday
143 1218 Marble Mocha Small 4.4 Sunday
144 1121 Choc Duo Large 5.8 Thursday
145 1121 Short Macchiato Small 3.7 Monday
146 1019 Cappuccino Small 3.9 Saturday
147 1604 Marble Mocha Standard 5.1 Friday
148 1553 Choc Mocha Large 5.8 Wednesday
149 1267 Latte Standard 4.6 Saturday
150 1218 Hot Chocolate Large 5.5 Thursday
151 1938 Marble Mocha Large 5.8 Tuesday
152 1326 Tea Standard 3.6 Tuesday
153 1316 Latte Large 5.3 Sunday
154 1822 Dark Chocolate Large 5.5 Wednesday
155 1860 Chai Latte Large 5.3 Sunday
156 1743 Chai Latte Small 3.9 Monday
157 1628 Cappuccino Standard 4.6 Sunday
158 1746 Choc Duo Small 4.4 Saturday
159 1975 Cappuccino Standard 4.6 Thursday
160 1072 Long Black Small 3.8 Saturday
161 1860 Hot Chocolate Standard 4.8 Tuesday
162 1148 Long Macchiato Small 3.8 Sunday
163 1668 Choc Mocha Standard 5.1 Saturday
164 1301 Choc Mocha Small 4.4 Sunday
165 1176 Vienna Small 4.2 Monday
166 1121 Hot Chocolate Small 4.1 Friday
167 1927 Flat White Standard 4.6 Wednesday
168 1975 Marble Mocha Small 4.4 Wednesday
169 1253 Choc Duo Large 5.8 Friday
170 1569 Cappuccino Standard 4.6 Tuesday
171 1938 Piccolo Latte Small 3.7 Saturday
172 1746 Latte Large 5.3 Sunday
173 1553 Flat White Standard 4.6 Tuesday
174 1553 Long Black Large 5.2 Tuesday
175 1292 White Chocolate Small 4.1 Friday
176 1148 Short Macchiato Small 3.7 Thursday
177 1146 Choc Mocha Small 4.4 Thursday
178 1176 Cappuccino Large 5.3 Thursday
179 1405 Vienna Standard 4.9 Thursday
180 1553 Cappuccino Small 3.9 Sunday
181 1131 Choc Mocha Small 4.4 Friday
182 1548 Dark Chocolate Small 4.1 Tuesday
183 1829 Piccolo Latte Small 3.7 Monday
184 1548 Vienna Large 5.6 Saturday
185 1975 Flat White Standard 4.6 Saturday
186 1101 Espresso Small 3.5 Saturday
187 1652 Choc Duo Large 5.8 Tuesday
188 1706 Dark Chocolate Small 4.1 Thursday
189 1668 Tea Large 3.7 Tuesday
190 1131 Hot Chocolate Standard 4.8 Tuesday
191 1557 Tea Large 3.7 Thursday
192 1057 Cappuccino Large 5.3 Friday
193 1326 Dark Chocolate Large 5.5 Monday
194 1405 Long Black Small 3.8 Wednesday
195 1019 Hot Chocolate Small 4.1 Wednesday
196 1057 Hot Chocolate Small 4.1 Friday
197 1938 Chai Latte Large 5.3 Monday
198 1947 White Chocolate Large 5.5 Friday
199 1628 Cappuccino Large 5.3 Tuesday
200 1292 Tea Large 3.7 Tuesday
201 1101 Dark Chocolate Standard 4.8 Saturday
202 1178 Chai Latte Small 3.9 Thursday
203 1743 Choc Mocha Standard 5.1 Sunday
204 1860 Piccolo Latte Small 3.7 Tuesday
205 1146 Espresso Small 3.5 Saturday
206 1668 Choc Duo Small 4.4 Tuesday
207 1148 Hot Chocolate Large 5.5 Thursday
208 1148 Hot Chocolate Standard 4.8 Friday
209 1628 Vienna Standard 4.9 Thursday
210 1315 Dark Chocolate Standard 4.8 Wednesday
211 1267 White Chocolate Standard 4.8 Thursday
212 1822 Choc Duo Large 5.8 Sunday
213 1027 Long Black Small 3.8 Friday
214 1975 Cappuccino Small 3.9 Monday
215 1548 Vienna Standard 4.9 Friday
216 1743 Choc Mocha Large 5.8 Wednesday
217 1822 Vienna Standard 4.9 Thursday
218 1548 Choc Mocha Large 5.8 Thursday
219 1315 Piccolo Latte Small 3.7 Sunday
220 1553 Vienna Standard 4.9 Tuesday
221 1743 Dark Chocolate Small 4.1 Saturday
222 1628 White Chocolate Small 4.1 Wednesday
223 1405 Hot Chocolate Large 5.5 Friday
224 1027 Cappuccino Small 3.9 Thursday
225 1216 White Chocolate Standard 4.8 Tuesday
226 1706 Choc Mocha Standard 5.1 Wednesday
227 1628 White Chocolate Small 4.1 Sunday
228 1176 Hot Chocolate Large 5.5 Sunday
229 1975 Hot Chocolate Large 5.5 Sunday
230 1553 Hot Chocolate Small 4.1 Thursday
231 1569 Flat White Standard 4.6 Monday
232 1548 Choc Mocha Standard 5.1 Friday
233 1216 Marble Mocha Standard 5.1 Thursday
234 1057 Espresso Small 3.5 Wednesday
235 1316 Short Macchiato Small 3.7 Tuesday
236 1743 Dark Chocolate Small 4.1 Thursday
237 1292 White Chocolate Small 4.1 Sunday
238 1630 Choc Mocha Standard 5.1 Saturday
239 1822 Marble Mocha Small 4.4 Thursday
240 1548 Marble Mocha Small 4.4 Tuesday
241 1326 Latte Large 5.3 Saturday
242 1267 Choc Duo Standard 5.1 Wednesday