Instructions: Complete the following problems using $$Assignment $1-$Data.xlsx$.$ All

work should be performed in a single Excel workbook named as

$$YourFullName$\_$HW$1.$xlsx$$ with multiple tabs$.$ Make a separate tab for each part of

each question and clearly name the tab accordingly. Upload the file to Canvas under

$$Assignment $1$ when you are finished.

Note: You have five full days to complete this assignment at your pace. You may use

your textbook, lecture notes, and other resources from the course to complete this

assignment. Any student who attempts to communicate with anyone for the completion

of the assignment via email, text messaging, or other means will receive an automatic F

on the course. Read each question carefully and make sure that you answer each question

completely.

$1.$ The Dish database contains the transaction history of detergent purchases at a number

of stores in a grocery chain over a period of several weeks.

a$.(2\mathrm{.}5$ points$)$ In a separate tab called $1.$a$,$ construct a pivot table that tabulates

the sales $($in cases$)$ by brand, store, and week. Include a pivot chart as well.

b$.(4$ points$)$ In a separate tab called $1.$b$,$ construct a pivot table that tabulates

the sales $($in ounces$)$ by brand and size. Include a pivot chart as well. $($Hint:

you need to convert the sales to OZ units using the three fields $$SIZE$,$

$$CASE$,$ and $$MOVE$.)$

$2.$ Use the sample data from the Executives database to answer the following questions.

a$.(2$ points$)$ In a separate tab called $2.$a$,$ compute the descriptive statistics for

total executive compensation. $($Note: total compensation is composed of

Salary, Bonus, and Other.$)$

b$.(4$ points$)$ In a separate tab called $2.$b$,$ construct both a dynamic histogram

$($using the FREQUENCY$()$ function$)$ and a static histogram $($using the Data

Analysis Toolpack$)$ for the total executive compensation.

$3.$ The YellowCabTrips database contains the travel data from $1000$ passenger trips on

the yellow taxis in NYC on the evening of the last day of $2022.$

a$.(3$ points$)$ The column $$tip$\_$amount$$ contains some non$-$numeric values

which do not make sense. $(1)$ Load the file in OpenRefine, $(2)$ find these rows,

$(3)$ remove them from the data, $(4)$ and then using the $$Export$$ tab on the topright of the software window, export the cleaned data as an Excel file $(.$xls

extension$).$ Copy this clean data into a separate tab called $3.$a in your Excel

workbook.

b$.(4\mathrm{.}5$ points$)$ The company wants to know if there is a relationship between

$$fare$\_$amount$$ and $$tip$\_$amount$.$ Using your cleaned data from part $($a$),$

construct a $$Scatterplot facet$$ in OpenRefine, choose the plot corresponding

to the two variables from the grid of generated plots, and pasted it in a

separate tab called $3.$b$.$ Looking at the scatterplot you generate, do you think

there is a relationship between the two? Explain shortly.