Draft - Work in Progress

Data Driven Decision Making - Course 3

Week 4
Dashboarding
Student Workbook

757844404.xlsx 05/25/2024
PwC Cover Page Page 1 of 25
Distributor ID Distributor Name State Code Product Code Sales Channel
23263 Xerxes Smith NV dressslacks100 Online
23264 Levi Douglas TN dresstie300 Online
23265 Uriel Benton PA dresssocks300 Retail
23266 Celeste Pugh NM dressslacks200 Online
23267 Vance Campos SC dresstie100 Online
23268 Latifah Wall ND dresssocks200 Online
23269 Jane Hernandez WA dresstie100 Online
23270 Wanda Garza UT dressslacks300 Retail
23271 Athena Fitzpatrick NY dressslacks300 Retail
23272 Anjolie Hicks DC dressshirt200 Direct
23273 Isaac Cooper IN dresssocks300 Online
23274 Asher Weber VA dresstie100 Retail
23275 Ethan Gregory WV dresstie300 Retail
23276 Hayes Rollins IL dresssocks100 Online
23278 Aphrodite Brennan AK dressshirt300 Direct
23279 Angela Wise FL dresstie100 Online
23280 James Spencer HI dressslacks300 Online
23281 Adria Kaufman CT dressshirt100 Retail
23282 Amir Alexander VT dresssocks200 Retail
23283 Lani Sweet AZ dressshirt300 Online
23284 Clark Weaver MN dresstie200 Retail
23285 Leonard Cardenas AL dressshirt100 Retail
23286 Renee Padilla CO dresstie300 Online
23287 Joy Vazquez TN dresstie200 Retail
23288 Ingrid Bush HI dresssocks300 Direct
23289 Deacon Craig GA dressshirt300 Retail
23290 Rama Goodwin VT dresstie300 Online
23291 Jelani Odonnell WY dresstie300 Retail
23292 Liberty Mcbride NE dressshirt300 Online
23294 Samuel Ayala DC dresstie300 Retail
23296 India Gilbert MI dressshirt300 Retail
23297 Ursula Mcconnell OK dressslacks100 Online
23298 Ryder Conner AR dresstie200 Direct
23299 Germaine Kidd KY dressslacks200 Retail
23300 Rhona Clarke CT dresssocks300 Online
23301 Maxwell Parker MT dressslacks300 Retail
23302 Isaac Wolf NE dressslacks100 Online
23303 Guinevere Key ME dressshirt300 Retail
23304 Deanna Santana OR dressslacks200 Retail
23305 Jared Sandoval CO dresstie300 Online
23306 Ima Cummings NH dressshirt200 Online
23307 Oprah Ellis MS dresssocks200 Retail
23308 Dara Cunningham NC dresstie100 Retail
23309 Buckminster Hopkins OH dressslacks200 Online
23310 Kenyon Joyce IN dresssocks300 Online
23311 Forrest Macdonald IA dressshirt300 Retail
23312 Thomas Barnes DE dressslacks200 Online
23314 Maxine Gentry MT dresssocks300 Retail
23315 Anika Tillman GA dresstie200 Retail
23316 Robert Juarez RI dressslacks200 Retail
23317 Kay Buckley CA dresstie200 Direct
23318 Shea Cortez PA dressshirt200 Online
23320 Lance Little MD dresssocks200 Direct
23322 Arsenio Knowles DC dresstie100 Retail
23323 Colby Knapp MI dresstie100 Online
23324 Noble Warner FL dresssocks300 Retail
23325 Isadora Mcclure RI dressslacks100 Retail
23326 Katelyn Joseph OK dressshirt100 Retail
23327 Fletcher Jimenez KS dressshirt200 Retail
23328 Keaton Wolfe NJ dressshirt300 Retail
23329 Melinda Cobb AK dresstie200 Retail
23332 Yael Carter AZ dresstie200 Direct
23333 Sawyer Stokes CO dresstie200 Online
23334 Silas Battle MD dresstie100 Online
23335 Noble Gilbert WI dresstie300 Online
23336 Petra Mckenzie ID dressshirt100 Retail
23337 Bell Prince OH dressslacks300 Retail
23338 George Best CA dresstie200 Retail
23339 Basil Vang DC dresssocks200 Online
23340 Lael Gould MO dressshirt100 Online
23341 Noel Key NY dresstie100 Retail
23342 Paul Duke NM dressslacks200 Online
23343 Josiah Yates CA dresssocks300 Online
23344 Winifred Cantu SD dressshirt200 Online
23345 Devin Abbott NH dressshirt300 Online
23346 Aretha Patton DE dresssocks300 Online
23347 Nell Maddox AZ dresstie300 Online
23348 Tad Mack OR dresssocks300 Retail
23349 Amery Frazier NC dressshirt200 Retail
23350 Hiroko Acevedo ID dresstie300 Online
23351 Nyssa Quinn LA dressslacks200 Online
23352 Iliana Porter NJ dresssocks200 Online
23353 Zahir Fields IL dressshirt300 Direct
23354 Gwendolyn Mccarty WV dressslacks300 Online
23355 Victoria Solis MS dresstie200 Online
23356 Colette Sargent MA dressslacks200 Online
23357 Cyrus Whitley MN dresssocks200 Retail
23358 Joel Rivers WY dresssocks300 Retail
23360 Barrett Mckinney SD dressslacks300 Online
23361 Benedict Byrd CT dressslacks300 Online
23362 Jerry Alvarado TX dresstie100 Online
23364 Mercedes Humphrey VA dresstie300 Online
23365 Gwendolyn Walton MA dressshirt100 Retail
23367 Roary Dixon ND dresstie200 Retail
23368 Emerson Beard ME dressshirt100 Retail
23369 Ivor Mclaughlin AL dresssocks100 Retail
23370 Clark Orr SC dresstie100 Retail
23371 Doris Williams UT dressslacks300 Online
23372 Phillip Perkins LA dresssocks100 Online
23373 Maite Henson AR dressshirt200 Online
23374 Ebony Mercer IA dressslacks200 Online
23375 Brittany Burris MO dressslacks300 Retail
23376 Imogene Bradshaw KS dressslacks300 Direct
23377 Eleanor Hopper WI dresssocks100 Online
23378 Brynne Mcgowan NV dressshirt300 Online
23379 Desirae Perkins KY dressslacks200 Online
23380 Ivory Chang TX dresssocks100 Retail
Date Sold Month Sold Quantity
7/6/2012 7 73
8/18/2012 8 205
12/5/2012 12 14
8/11/2012 8 170
7/11/2012 7 129
7/12/2012 7 82
6/3/2012 6 116
6/7/2012 6 67
2/27/2012 2 125
7/31/2012 7 71
12/13/2012 12 22
8/22/2012 8 153
1/4/2012 1 141
8/1/2012 8 65
8/24/2012 8 197
4/21/2012 4 10
4/3/2012 4 30
7/13/2012 7 134
8/21/2012 8 100
6/24/2012 6 142
6/17/2012 6 135
7/24/2012 7 9
8/8/2012 8 69
6/17/2012 6 189
6/14/2012 6 141
8/2/2012 8 166
8/11/2012 8 170
8/18/2012 8 199
1/3/2012 1 73
6/22/2012 6 160
6/8/2012 6 37
8/12/2012 8 135
7/28/2012 7 12
6/27/2012 6 104
1/7/2012 1 167
7/19/2012 7 108
7/27/2012 7 105
8/17/2012 8 176
6/1/2012 6 131
8/26/2012 8 188
6/8/2012 6 93
7/4/2012 7 113
7/9/2012 7 112
6/23/2012 6 201
6/17/2012 6 41
6/12/2012 6 18
7/6/2012 7 28
8/10/2012 8 95
7/12/2012 7 109
 6/1/2012 6 137
2/17/2012 2 196
7/9/2012 7 48
6/15/2012 6 125
4/10/2012 4 20
12/29/2012 12 135
8/13/2012 8 193
6/22/2012 6 184
8/21/2012 8 126
1/31/2012 1 176
1/15/2012 1 102
1/23/2012 1 203
2/11/2012 2 203
8/5/2012 8 106
12/17/2012 12 14
8/13/2012 8 116
7/1/2012 7 7
7/7/2012 7 82
8/12/2012 8 178
7/11/2012 7 41
7/5/2012 7 85
4/27/2012 4 77
6/28/2012 6 122
8/23/2012 8 42
12/22/2012 12 64
8/29/2012 8 208
7/29/2012 7 13
6/28/2012 6 147
8/25/2012 8 163
7/22/2012 7 126
6/25/2012 6 188
8/3/2012 8 151
7/7/2012 7 89
6/10/2012 6 168
8/3/2012 8 84
4/27/2012 4 16
6/21/2012 6 80
7/17/2012 7 50
6/11/2012 6 41
6/13/2012 6 37
1/7/2012 1 184
8/18/2012 8 179
7/3/2012 7 47
7/9/2012 7 165
4/24/2012 4 10
8/25/2012 8 150
7/2/2012 7 77
4/29/2012 4 63
8/15/2012 8 204
12/12/2012 12 22
 7/24/2012 7 95
12/14/2012 12 57
4/30/2012 4 5
7/23/2012 7 85
6/15/2012 6 43
6/18/2012 6 157
12/27/2012 12 65
7/22/2012 7 95
 Tab: Products
Product Code Product Category Code Unit Price
dressshirt300 High Dress Shirt H $ 14.50
dresssocks300 High Dress Socks H $ 9.99
dresstie300 High Dress Tie H $ 7.40
dressslacks300 High Dress Slacks H $ 19.99
dressshirt200 Mid Dress Shirt M $ 8.99
dresssocks200 Mid Dress Socks M $ 6.00
dresstie200 Mid Dress Tie M $ 4.50
dressslacks200 Mid Dress Slacks M $ 13.50
dressshirt100 Basic Dress Shirt B $ 4.50
dresssocks100 Basic Dress Socks B $ 4.99
dresstie100 Basic Dress Tie B $ 3.00
dressslacks100 Basic Dress Slacks B $ 7.00

05/25/2024 07:01:13 file:///conversion/tmp/activity_task_scratch/757844404.xlsx -- Products 8/25

 Tab: Categories
Category Code Category
H Premium
M Mid-tier
B Economy

05/25/2024 07:01:13 file:///conversion/tmp/activity_task_scratch/757844404.xlsx -- Categories 9/25

 Tab: States
State Code State
AL Alabama
AK Alaska
AZ Arizona
AR Arkansas
CA California
CO Colorado
CT Connecticut
DE Delaware
DC District of Columbia
FL Florida
GA Georgia
HI Hawaii
ID Idaho
IL Illinois
IN Indiana
IA Iowa
KS Kansas
KY Kentucky
LA Louisiana
ME Maine
MD Maryland
MA Massachussets
MI Michigan
MN Minnesota
MS Mississippi
MO Missouri
MT Montana
NE Nebraska
NV Nevada
NH New Hampshire
NJ New Jersey
NM New Mexico
NY New York
NC North Carolina
ND North Dakota
OH Ohio
OK Oklahoma
OR Oregon
PA Pennsylvania
RI Rhode Island
SC South Carolina
SD South Dakota
TN Tennessee
TX Texas
UT Utah
VT Vermont
VA Virginia
WA Washington
WV West Virginia
WI Wisconsin
WY Wyoming

05/25/2024 07:01:13 file:///conversion/tmp/activity_task_scratch/757844404.xlsx -- States 10/25

Data Driven Decision Making - Course 3
Week 4
Dashboarding
Exercise 1

Exercise 1 - Form Controls and Grouping

Your employer, a retail clothing company, currently uses 3 different sales channels: Direct, Online and Ret
to create a graph showing how revenue evolves throughout the year for each of the sales channels. Instea
overlays the revenue trend for each channel, they have asked you to create an interactive chart that can b
the different sales channels.

1a) Create 3 Option Buttons to allow the user to select between the Sales Channels using Form Controls and
1b) Complete the data table below using the data from the Pivot Table and the Output from the Option Buttons
1c) Create a Column Chart showing the Revenue for the different Sales Channels using the completed data ta
Select Measure to Display

Direct
Online
Online
$25,000
Retail

Option Input 2 $20,000

Place your char

Online $15,000
Jan $ 6,405
Feb $ -
Apr $ 702 $10,000
Jun $ 14,803
Jul $ 5,429
Aug $ 21,345 $5,000
Dec $ 2,999

$-
Jan Feb Apr Ju

Sum of Revenue Column Labels

Row Labels Direct Online Retail Grand Total
Jan $6,405 $5,018 $11,423
Feb $1,796 $2,499 $4,294
Apr $702 $625 $1,327
Jun $4,595 $14,803 $11,498 $30,896
Jul $2,391 $5,429 $9,011 $16,831
Aug $2,857 $21,345 $14,039 $38,240
Dec $2,999 $140 $3,139
Grand Total $11,638 $51,682 $42,830 $106,150
hannels: Direct, Online and Retail. They have asked you
ch of the sales channels. Instead of a creating a chart that
e an interactive chart that can be used to switch between

nnels using Form Controls and link them to cell D23

Output from the Option Buttons
nels using the completed data table below

Online

Place your chart here!

Feb Apr Jun Jul Aug Dec

Data Driven Decision Making - Course 3
Week 4
Dashboarding
Exercise 2

Exercise 2 - Slicers and Power Pivot

Your employer now wants to see three different views of the data: montly sales revenue, sales revenue by
In addition, your boss would also like to be able to drilldown by state for each slice of the data. We've prov

2a) Using the pivot tables below, create the three charts as specified above.
2b) Format the charts you just created using the concept discussed in the Charting course from Week 2 by rem
2c) Add slicer and connect to each chart. Refer to the instructor-lead video if you need a quick reminder on ho

Total Total
$12,000 $12,000

$10,000 $10,000

$8,000 $8,000
Sum of Revenue
$6,000 $6,000

$4,000 $4,000

$2,000 $2,000

$0 $0
Jan Grand Total Mid-tier
Place your answer Premium
here! Grand

Total
$10,000
$8,000
$6,000
$4,000
$2,000
$0
Benedict Fletcher Keaton Liberty
Byrd Jimenez Wolfe Mcbride

Row Labels Sum of Revenue Row Labels

Jan $11,423 Mid-tier
Grand Total $11,423 Premium
Grand Total
ales revenue, sales revenue by category, and revenue by the top five distributors.
ch slice of the data. We've provided three pivot tables below to get you started.

rting course from Week 2 by removing unecessary chart elements (borders, gridlines, etc.) and format the remaining elements
ou need a quick reminder on how to find the slicer tool. Align the three charts and slicer in a 2x2 matrix and enclose in a Group

This shape represents a slicer.

Total Slicers are supported in Excel
2010 or later.

If the shape was modified in

an earlier version of Excel, or
Sum of Revenue if the workbook was saved in
Excel 2003 or earlier, the
slicer cannot be used.

Mid-tier
ce your answer Premium
here! Grand Total

Total

Sum of Revenue

Benedict Fletcher Keaton Liberty Rhona Grand

Byrd Jimenez Wolfe Mcbride Clarke Total

Sum of Revenue Row Labels Sum of Revenue

$2,496 Benedict Byrd $3,678
$8,927 Fletcher Jimenez $1,582
$11,423 Keaton Wolfe $1,479
Liberty Mcbride $1,059
Rhona Clarke $1,668
Grand Total $9,466
he remaining elements accordingly.
and enclose in a Group Box.
Data Driven Decision Making - Course 3
Week 4
Dashboarding
Exercise 3

Exercise 3 - Conditional Formatting and KPI's

Now we want to show monthly sales units by sales channel and product. The sales organization tracks it's
In this case, we've been provided goals (read below) by product for the items sold via the Online channel.
and which products are struggling. Regarding the Retail channel, while not yet measured against specific t
We'll accomplish this with an inline visualization. We've provided a table below to get you started. Online m
Less than 150 units and greater than or equal to 100 is at goal, and less than 100 is considered failing.

3a) Create a spin button using Form Controls in cell E20 that will allow you to control the month you're displayi
3b) Set Cell Link to cell B2 of 'Form Control Output', then use a VLOOKUP in cell C2 (on the 'Form Control Ou
3c) Calculate the revenue for each of the products in the table below using the SUMIFS function, the Transact
3d) Using the information provided by your boss, apply the correct conditional formatting to online revenue and

Change Month with Spin Button

Sales Month: July

Product Online Retail Retail Conditonal Formatting here

dressshirt100 0
dressshirt200
dressshirt300
dressslacks100
dressslacks200
dressslacks300
dresssocks100
dresssocks200
dresssocks300
dresstie100
dresstie200
dresstie300
 sales organization tracks it's performance against targets set by management.
sold via the Online channel. Your boss would like an interactive tool to view which products are meeting their goals,
t measured against specific targets, management would like a quick view on performance as a first step towards setting goals.
w to get you started. Online monthly sales unit volume of 150 or above is exceeding monthly targets and considered outstanding
100 is considered failing.

trol the month you're displaying. Hint: make sure to set the appropriate Min and Max values
C2 (on the 'Form Control Output' tab) to get the associated month name. Hint: use cell B2 as the lookup value and the Month T
UMIFS function, the Transaction data table and the month input from cell D21
matting to online revenue and retail revenue, with icon sets and data bars, respectively.

atting here
g their goals,
p towards setting goals.
d considered outstanding.

p value and the Month Table as the table array.

Month Selected 7 July

Month Table
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
Data Driven Decision Making - Course 3
Week 4
Dashboarding
Exercise 4

Exercise 4 - Finalizing the Dashboard

Finally, we'd like to bring the previous two exercises together and lock down our dashboard. For example,
So, we are going to hide soure sheets, the formula bar, and other workbook elements to prevent the user f

4a) Use the camera tool to bring in the completed dasboard components from Exercise 2 and Exercise 3. Res
4b) Copy slicer from Exercise 2 and overlay it on the picture below. You may need to resize Slicer.
4c) Copy the spin button from Exercise 3 and overlay it on the picture of the conditional formatting component
4d) Remove borders from each picture.
4e) Hide Sheets, Formula Bar, horizontal and vertical scrollers, and row and column headers

Place your
ashboard. For example, your boss doesn't want the users to be able to manipulate formulas and or source tabs.
ents to prevent the user from inadvertently breaking the dashboard.

e 2 and Exercise 3. Resize pictures to desired size.

resize Slicer.
al formatting component below. You may need to resize the spin button.

Place your answers here!