Exercise: Week $5$ Final Project Afternoon Hours

Instructions Your boss has asked you to analyze customer traffic to the store during the afternoon hours. You will need to create a pivot table, create two pivot table charts, and provide an analysis.

Objectives Understand how to clean and evaluate data, create a pivot table, pivot chart, analyze the data, and determine key findings.

Step $1$: Review the Data Number of Customer Traffic by Interval Your Notes $/$ Observations

"Instructions:

$1)$ Review the data table.

$2)$ Apply conditional formatting $($color scales$)$ to the table.

$3)$ Look for anything unusual or irregular. Look for patterns and trends in the data. The data will not be cleaned during this exercise.

$4)$ List any patterns or trends you see.

$5)$ List basic information like the min, max, average, count, and sum.

$6)$ Assess the morning, afternoon, and evening intervals using the $""$Time of Day"" Table

$7)$ Make notes in the $""$Notes $/$ Observations"" box on the right." Interval Monday Tuesday Wednesday Thursday Friday Interval "Min:

Max:

Average:

Count:

Sum:

Trends & Patterns:

Unusual Patterns: $"$

$8$:$00$ AM $2616014248137192,818$

$9$:$00$ AM $3123947994724142,391$

$10$:$00$ AM $338917983665642,652$

$11$:$00$ AM $6199345697442293,095$

$12$:$00$ PM $3866077763363762,481$

$1$:$00$ PM $7432587016527243,078$

$2$:$00$ PM $5927202616303372,540$

$3$:$00$ PM $6008634453943612,663$

$4$:$00$ PM $4417021868142662,409$

$5$:$00$ PM $706225896234882,392$

$6$:$00$ PM $2005248333997492,705$

$7$:$00$ PM $6072604454041461,862$

$8$:$00$ PM $5756963196016952,886$

$9$:$00$ PM $510658826653372,396$

$10$:$00$ PM $5826047934786983,155$

Totals $6531933487647791710339,523$

Time of Day Sum $\%$

Morning $10,95627\mathrm{.}72\%$

Afternoon $15,56339\mathrm{.}38\%$

Evening $13,00432\mathrm{.}90\%$

Total $39,523100\%$

Step $2$: Create a Pivot Table Place Your Pivot Table Below this label Your Notes $/$ Observations

"Instructions:

$1)$ Using the table in Step $1,$ create a pivot table.

$2)$ Filter the chart to show only $12$ PM $-5$ PM M$-$F

$3)$ Apply conditional formatting

$4)$ Create percentages based on the total, below the total row for each day of week.

$5)$ Create percentages based on the total, to the right of the Friday column

$6)$ List basic information: min, max, average, count, and sum

$7)$ Determine DOW of Week Ranking

$8)$ Determine Interval Ranking

$9)$ Add any additional observation on the data to the $""$Notes$/$Observations$""$ box on the right." "Min:

Max:

Average:

Count:

Sum:

Trends & Patterns:

Unusual Patterns: $"$

Step $3$: Create Pivot Charts Place Your First Pivot Table Chart Below this label Place Your Second Pivot Table Chart Below this label

"Instructions:

$1)$ Using the pivot table in step $2,$ create two pivot table charts: a column chart and a line chart.

$2)$ The chart, at minimum should include the following:

$$ Filter the chart to show only $12$ PM$-5$ PM M$-$F

$$ A chart title above the chart

$$ Hide all field buttons on the chart

$$ Add data labels above the markers for the Column Chart

$$ You can customize beyond this minimum if you would like" Place chart here Place chart here

Step $4$: Analysis and Findings Analysis Key Findings

"Instructions:

$1)$ Complete your Analysis using the pivot table, pivot chart, and notes$/$observations in the analysis box. Try to list as many patterns, trends, and anomalies as possible. What peaks and valleys do you see in the data? Co