Home
AutoSave OFF
ABG ...
Insert Draw Page Layout Formulas Data
Alt_Lab_1_3_Data
Review
View
Tell me
Calibri (Body)
12
A A =
Required information
[The following information applies to the questions displayed below.]
Lab Insight: Apply the same steps in Lab 1.3 Excel (or Lab 1.3 Tableau or Lab 1.3 Power BI) to the Alt Lab 1.3 Data.xlsx data
set using your tool of choice.
Open Excel file Alt Lab 1.3 Data.xlsx and follow similar directions to those in Lab 1.3 Excel. ThrustMaster is a fictitious
company that sells game controllers, joysticks, and steering wheels for PCs and for Xbox, Nintendo, and PlayStation
consoles. A summary of the price list of its products by SKU is included in the Excel spreadsheet.
Required:
1. Draw a histogram of the gross margin percentages using bins of 0.025 size. Use bins 0.425, 0.45, 0.475, 0.5.
2. Get a list of SKUs in the highest 0.025 bin (from 0.475 to 0.50).
Data: Alt Lab 1.3 Data.xlsx
Analyze the Data: Refer to lab 1.3 in your text for additional instructions and steps.
7 FU-HO1
8 COM-FCS
9 PED-TFR
10 WHL-TMX
11 PED-T80
12 PED-T3P
12 PED-T3P
13 PCG-HOW
FB T3PA-P
A-Pro 3-Pedal Add-Or
Hotas Wart
Warthog PC
Ferrari Racing Wheel Red Leg
Paste
B I U v
Hv
AA
Wrap Text
General
Merge & Center v
S
%9
Share
Comments
5 v
Conditional Format
Formatting as Table
Cell
Styles
Insert
Delete Format
Find &
Sort &
Filter Select
Analyze
Data
A1
× ✓ fx SKU
A
B
C
D
E
F
G
H
I
J
K
L
Σ
N
о
P
1 SKU
Description
Sales Price
Cost
2 FLI-16H
T1600M FCS Hotas
97.3
52.53
3 FLI-TMX
TMX Force Feedback Racing
159.84
87.3
4 FLI-HOX
T-Flight Hotas X Flight Stick
39.99
20.53
5 FLI-HO4
T-Flight Hotas 4 Flight Stick
34.65
19.76
6 PED-FCS
FED-FCS
T16000M FCS
39.19
23.36
T-Flight Hotas One
59.99
30.17
30.17
T16000M FCS Flight Pack
172.35
89.15
TFRP Rudder
69.99
41.18
TMX Pro Racing Wheel
249.99
249.99
147.62
147.62
T80 Ferrari 488 GTB Edition F
94.99
94 99
129.99
462 81
462.81
52.58
66.14
66.14
266.04
52.58
14 PED-FER
79.99
42.51
43.51
15 PED-T3A
T3PA Add-On
69.99
36.4
16 FLI-16M
T16000M Space SIM Duo St
117.83
69.36
17 WHL-T30
T300RS Officially Licensed PS
319.99
170.61
18 FLI-USB
USB Joystick
21.99
12.06
19 PED-TPR
TPR Pedals - Windows
497.38
284.93
Upload one Word or PDF document containing all of your screenshots for this Lab using the button below.
Note: Please submit your file attachment response in one of the approved file formats; Word file (.doc or .docx), Excel file (.xls or .xlsx), or
PDF. To submit photo or image files please paste them into Word or PDF. Consult with your instructor as needed on their preferred file
attachment format.
1. Take a screenshot of your detailed listing of fruit products sold from the 0.4 to 0.425 gross margin percentage range, paste it into a Word
document named "Alt Lab 1.3 Submission.docx", and label the screenshot Submission 1.
21 FLI-THR
22 FLI-STX
20 WHL-T3S
VG T300RS Racing Wheel Ser
159.99
92.63
TWCS Throttle
59.99
35.18
T-Flight Stick X PS3
39.99
21.04
23 WHL-TGT
T-GT Racing Wheel (PS4/PC)
614.91
354.66
24 WHL-T3P
T300RS Racing Wheel
426.41
231.54
25
26
27
28
29
2. Take a screenshot of your histogram as a pivot chart, paste it into the same Word document, and label the screenshot Submission 2.
Sheet1
Ready
Accessibility: Good to go
凹 -1+ 125% AutoSave OFF
Home
Insert Draw
Page Layout
Formulas
Data
Review View
Tell me
Calibri (Body)
✓
11
▾ Aˆ A
|
ab Wrap Text
Paste
B
I
G
v
v
=
H5
x
fx
U ▾
B
с
D
E
G
3 FRT-APO Apricot
Required:
1. Create calculated fields for the gross margin and gross margin percentage.
2. Create a histogram to visualize the distribution of gross margin percentage across SKUs.
3. Adjust the automated bin size to 0.025.
4. Create a list of SKUs in the highest bin.
Data: Lab 1.3 Data.xlsx
Specify the Question: Which products (SKUs) are the most profitable to sell?
Obtain the Data: Fancy Fruits is a fictitious mail-order business that sells exotic fruit from around the world to customers
throughout the United States. The company keeps a price list for each product. We will work from this price list to find the
items that are most profitable per dollar of sales.
Analyze the Data: Refer to lab 1.3 in your text for additional instructions and steps.
11 FRT-CHI Chico fruit
2 FRT-APL Apple
4 FRT-AVD Avocado
FRT-BAN Banana
1.7
1.09
28
1.76
1
0.59
05
031
6 FRT-BIT Biberty
2
1.17
7 FRT-BLB Blueberry
1.07
8 FRT-BLN Blackcurrent
2
1.18
9 FRT-BLR Blackberry
2
129
10 FRT-BOR Boysenberry
1
0.62
1.75
1.03
12 FRT-CHR Cherry
25
156
13 FRT-CHY Cherimoya
1.75
1.11
14 FRT-CLR Cloudberry
1.75
1.09
15 FRT-COU Coconut
2.41
154
16 FRT-CRR Cranberry
1.01
0.61
17 FRT-CUE Cucumber
1.82
1.09
18 FRT-CUN Currant
1.04
19 FRT-DAO Damson
2.04
1.34
20 FRT-DAT Date
0.83
0.54
21 FRT-DRI Dragonfruit
163
098
22 FRT-DUA Durian
132
23 FRT-ELR Bderberry
1.52
24 FRT-FEO fejpa
198
13
25 FRT-FII F
2.41
155
26 FRT-GOR Goji berry
1.18
0.74
27 FRT-GOS Gooseberry
182
1.19
FRT-GRF Grapefruit
098
0.65
29 FRT-GRI GrapeRaisin
125
0.74
30 FRT-GUV Guava
0.94
0.56
31 FRT-HOR Honeyberry
094
0.59
32 FRT-HUR Huckleberry
0.96
0.63
33 FRT-JAB Jabuticaba
0.89
0.52
34 FRT-JAI Jackfruit
0.63
0.37
35 FRT-JAU Jambul
195
1.13
36 FRT-JUB Jujube
0.95
37 FRT-JUR Juniper berry
13
0.86
38 FRT-KII
Kiwifruit
2.32
137
39 FRT-KUA Kumquat
0.71
0.45
Answer the following questions based on the details computed.
40 FRT-LEO Lemon
2.46
1.48
41 FRT-LIM Ume
0.96
0.57
42 FRT-LOA Loquat
0.38
43 FRT-LOG Lengan
1.85
11
44 FRT-LYE Lychee
2.01
1.25
Question
Answer
45 FRT-MAG Mango
1.47
46 FRT-MAR Marionberry
0.95
0.61
47 FRT-MEO Melon
194
12
1. What is the size of each bin?
2. Is this dataset negatively (left) or positively (right) skewed?
3. Which gross margin percentage bin does the FRT-COU SKU fit in?
4. How many products (SKUS) have gross margin percentages in the 0.325 -0.35 range?
5. How many products (SKUS) have gross margin percentages in the 0.35 -0.375 range?
48 FRT-MII Miracle fruit
2.05
12
49 FRT-MUR Mulberry
0.44
0.72
51 FRT-NEN Nectarine
0.96
52 FRT-OLV Ove
0.95
53 FRT-ORG Orange
0.75
0.49
54 FRT-PAI Passionfruit
0.94
0.61
55 FRT-PAY Papaya
0.63
56 FRT-PEA Par
183
1.07
57 FRT-PEC Peach
1.15
0.71
58 FRT-PEO Parsimmon
199
123
59 FRT-PHI Physalis
1.82
1.12
60 FRT-PIL Pineapple
0.77
0.46
61 FRT-PLI Plantain
2.06
1.24
62 FRT-PLM PlumPrune (dried plum
1.31
0.77
63 FRT-PLT Plumcot (or Pluot)
2.1
1.24
64 FRT-POL Pamdo
2.37
1.54
65 FRT-POT Pomegranate
0.65
0.42
66 FRT-PUE Purple mangosteen
0.59
67 FRT-QUC Quince
0.63
68 FRT-RAA Rambutan
23
1.52
69 FRT-RAR Raspberry
1.14
0.7
70 FRT-REN Redcurrant
1.42
0.85
71 FRT-SAA Salak
081
0.5
72 FRT-SAM Satsuma
0.69
0.44
73 FRT-SAR Salal berry
1.12
0.66
74 FRT-SOO Soursop
24
1.46
75 FRT-SOS Solanum quitoense
094
0.59
76 FRT-STI Star fruit
25
1.57
77 FRT-STR Strawberry
08
0.52
78 FRT-SUL Sugr apple
101
0.62
79 FRT-TAL Tamarillo
0.77
0.51
80 FRT-TAN Tamarind
1.38
81 FRT-UGI Ugi fruit
1.12
0.69
82 FRT-YUZ Yuzu
15
093
Ready
Sheet1
+
Accessibility: Good to go
Lab_1_3_Data
Merge & Center
General
$ % 950000
✓
M
0
a
R
S
U
V
W
x
Y
2
AA
AB
AC
AD
AE Required information
[The following information applies to the questions displayed below.]
Lab Insight: Apply the same steps in Lab 1.3 Excel (or Lab 1.3 Tableau or Lab 1.3 Power BI) to the Alt Lab 1.3 Data.xlsx data
set using your tool of choice.
Open Excel file Alt Lab 1.3 Data.xlsx and follow similar directions to those in Lab 1.3 Excel. ThrustMaster is a fictitious
company that sells game controllers, joysticks, and steering wheels for PCs and for Xbox, Nintendo, and PlayStation
consoles. A summary of the price list of its products by SKU is included in the Excel spreadsheet.
Required:
1. Draw a histogram of the gross margin percentages using bins of 0.025 size. Use bins 0.425, 0.45, 0.475, 0.5.
2. Get a list of SKUs in the highest 0.025 bin (from 0.475 to 0.50).
Data: Alt Lab 1.3 Data.xlsx
Analyze the Data: Refer to lab 1.3 in your text for additional instructions and steps.
Answer the following questions based on the details computed.
Q
Share
Comment
General
m
Συ
Merge & Center
S
% 9
898
Conditional Format Cell
Formatting as Table Styles
Insert Delete Format
Sort &
Filter
Find &
Select
N
Analyze
J
K
L
M
N
o
P
a
R
$
T
AutoSave OFF ABAD C...
Alt_Lab_1_3_Data
Home
Insert Draw Page Layout Formulas Data Review View
Tell me
X
Calibri (Body)
12
B I U ▾
AA
A
Wrap Text
A1
× ✓ fx SKU
A
B
C
D
E
F
H
1 SKU
2 FLI-16H
Description
T1600M FCS Hotas
Sales Price Cost
97.3
52.53
3 FLI-TMX
TMX Force Feedback Racing
159.84
87.3
4 FLI-HOX
T-Flight Hotas X Flight Stick
39.99
20.53
5 FU-HO4
T-Flight Hotas 4 Flight Stick
34.65
19.76
6 PED-FCS
T16000M FCS
39.19
23.36
7 FLI-HO1
T-Flight Hotas One
59.99
30.17
8 COM-FCS
T16000M FCS Flight Pack
172.35
89.15
9 PED-TER
TFRP Rudder
69.99
41.18
TMX Pro Racing Wheel
249.99
147.62
11 PED-T80
T80 Ferrari 488 GTB Edition
94.99
52.58
12 PED-T3P
FB T3PA-Pro 3-Pedal Add-On
129.99
66.14
13 PCG-HOW
Hotas Warthog PC
462.81
266.04
14 PED-FER
Ferrari Racing Wheel Red Le
79.99
43.51
15 PED-T3A
T3PA Add-On
69.99
36.4
16 FLI-16M
T 16000M Space SIM Duo St
117.83
69.36
17 WHL-T30
T300RS Officially Licensed P
319.99
170.61
18 FL-USB
USB Joystick
21.99
12.06
19 PED-TPR
TPR Pedals Windows
497.38
20 WHL-T3S
VG T300RS Racing Wheel Si
159.99
92.63
21 FL-THR
TWCS Throttle
59.99
35.18
22 FUI-STX
T-Flight Stick X PS3
39.99
21.04
23 WHL-TGT
T-GT Racing Wheel (PS4/PC
614.91
354.66
24 WHL-T3P
T300RS Racing Wheel
426.41
231.54
25
26
27
28
29
Answer
30
31
$
42.41
32
33
42.410
34
35
36
27
Sheet1
+
Note: Round gross margin to 2 decimal places and gross margin percentage to 3 decimal places.
Question
1. What is the gross margin (dollars) for SKU PED-T80?
2. What is the gross margin percentage for SKU PED-T80?
3. What was the highest gross margin percentage among all the SKU's?
4. How many total SKU's are there?
5. How many SKU's are in the 0.45-0.475 bin?
23
7 Required information
[The following information applies to the questions displayed below.]
Lab Insight: Apply the same steps in Lab 1.1 to the Lab 1.1 Data.xlsx data set with two modifications. The same scenarios
exist as before, with labor costing either $13/lawn or $25/lawn.
Assume that your mowing business will mow 350 lawns per month rather than 200 lawns per month. Also assume that the
cost for advertising increases from $50 to $300 per month. Label these two new scenarios Scenario 3 (with labor costing
$13/lawn) and Scenario 4 (with labor costing $25/lawn).
Specify the Question: What is the profit on each lawn if the mowing business mows 350 lawns per month? What is the
business's total profit per month?
Required:
1. Assess whether this mowing business will be profitable if you mow 350 lawns per month.
2. Show a visualization of the change in monthly margin based on a range of labor costs per lawn.
Data: Lab 1.1 Data.xlsx
Analyze the Data: Refer to lab 1.1 in your text for additional instructions and steps.
Upload one Word or PDF document containing all of your screenshots for this Lab using the button below.
Note: Please submit your file attachment response in one of the approved file formats; Word file (.doc or .docx), Excel file (.xls or .xlsx), or PDF. To
submit photo or image files please paste them into Word or PDF. Consult with your instructor as needed on their preferred file attachment format.
1. Take a screenshot of the completed spreadsheet, paste it into a Word document named "Alt Lab 1.1 Excel Submission.docx", and label the screenshot
Submission 1.
2. Take a screenshot of the scatterplot showing labor cost per lawn and monthly margin, paste it into the same Word document, and label the screenshot
Submission 2.
AutoSave OFF
Home Insert Draw Page Layout
Lab_1_1_Data
Formulas
Data
Review
View
Tell me
X
Calibri (Body)
✓
11
✓ A A
ab v
ab Wrap Text v
General
ˇ
Paste
B
I
U v
Hv
A
✓
Merge
Center v
$ % 9
8.00
Conditional Form
Formatting as Ta
L17
x
✓ fx
A
1 Base Scenario
2
B
C
D
E
F
Scenario 1 Scenario 2
Per Lawn
Per Lawn
G
Enhanced Scenario
H
J
K
10
22
23
24
25
27
28
29
Assumption: Total Lawns Serviced Each Month.
Revenue
Costs
Monthly Required:
To run this business, you'd have to leave your day job, where you earn $4,000/month.
1. Assess whether this mowing business will be profitable if you mow 200 lawns per month.
2. Show a visualization of the change in monthly margin based on a range of labor costs per lawn.
Data: Lab 1.1 Data.xlsx
AutoSave OFF
Lab_1_1_Data
Home
Insert
Draw Page Layout
Formulas
Data
Review
View
Tell me
v
Calibri (Body)
11
✓ Aˆ A
ab Wrap Text v
General
Paste
P
BI U ▾ H
✓ A ✓
Merge & Center
$ % 9
800
Condi
Forma
L17
×
✓ fx
A
B
C
D
E
F
Scenario 1
Per Lawn
Scenario 2
Per Lawn
G
Enhanced Scenario
H
1
J
1
Base Scenario
2
3
Specify the Question: What is the profit on each lawn if the mowing business mows 200 lawns per month? What is the
business's total profit per month?
4
Assumption: Total Lawns Serviced Each Month
5
6
Revenue
7
Obtain the Data: Open Lab 1.1 Data.xlsx and browse its contents. The data will come from the various assumptions given.
Analyze the Data: Refer to lab 1.1 in your text for additional instructions and steps.
8
Costs
9
10
11
12
13
14
15
16
Answer the following questions based on the details computed.
Note: Round the margin and cost per lawn to 2 decimal places.
17
18
19
20
21
Question
Answer
22
23
1. What is the total monthly margin for scenario 1?
2. In scenario 1, what is the expected number of lawns that will be mowed each week? (assuming 4
weeks in a month)
24
25
26
27
3. What is the total margin per lawn for scenario 1?
28
4. What is the total monthly margin for scenario 2?
5. In scenario 1, what is the cost of the monthly truck payment per lawn?
29
30
31
32
33
34
35
36
37
38
39
Monthly
