Module $2$ Data AssignmentUsing Pivot Tables to Evaluate Catalog MarketingIn this assignment you will be working with the Catalog Marketing file that contains $1000$ customers. You will continue this analysis in the following module.Objective: An important analytics task is to summarize complex data quickly and effectively for the target audience. Additionally, this is often the first step an analyst takes to become familiar with the data before more complex analytics tasks are completed. In this exercise you will use a variety of Excel tools to summarize both qualitative and quantitative variables.What you need:$1.$You must have a computer with a working version of Microsoft Excel$\mathrm{.}2.$You will use the data set Catalog Marketing Ch $2$ posted in the Canvas.Assignment$/$Activity:Complete each of the following tasks. You should create new tabs and name those tabs as appropriate.Preliminary Work:$1.$Data Preparation variables called Age, Gender, Own Home, Married, and Close are all variables that have been entered into the data set as numeric codes. Create new variables with appropriate names that use text for each category using the IF or & functions. Hint: Read the variable descriptions by putting your cursor on the red corners in the top row labels for these variables. Use the descriptions to create text variables. You may name the new variables Age Text, Gender Text, etc.For example, to recode the variable Own Home to Own Home Text you could use a function like $=$IF$($D$2=1,$Homeowner,Renter$)2.$Re$-$order your file to put the variables in the following order. The easiest way to do this is to left$-$click the column header to select the whole column, then grab the box outlining the selection to drag it to the intended location. You may have to insert a column or two to give yourself room. Use the variables you created in Step $1,$ not the original coded versions.Person IDQualitative, nominal variablesQualitative, ordinal variablesQuantitative, discrete variablesQuantitative, continuous variablesLocation variablesDate variablesFor the following steps you should use Pivot Tables. To do this just click on any cell in the full data set, click the Insert ribbon, then select Pivot Table. Excel will automatically select the entire data set. For each required task please select New Worksheet so that each task is on a separate sheet. Please rename each worksheet as appropriate. Use the text variables you created for these tasks, not the existing coded variables.Evaluating Individual Variables:$3.$Create a Pie Chart summarizing the percentages of Married and Single customers:Select Married as the Rows field and Customer ID as the Values field.Click Customer ID in the Values field and change the Value Field setting from SUM to COUNTSelect any cell in the Pivot Table, click the Insert Ribbon, then select a Pie ChartRight$-$Click the pie chart youve created and select Add Data LabelsRight$-$Click a value in the Count of Customer ID column. Select Show Values As $>\%$ Column Total.Feel free to explore any other nominal$-$qualitative variables using this technique$\mathrm{.}4.$Create a Column Chart summarizing the numbers of catalogs sent:Select Catalogs as the Rows field and Customer ID as the Values field.Click Customer ID in the Values field and change the Value Field setting from SUM to COUNTSelect any cell in the Pivot Table, click the Insert Ribbon, then Select a Column ChartCheck the Vertical Axis labels to make sure the values begin at zeroFeel free to explore any other qualitative$-$nominal, qualitative$-$ordinal, or quantitative discrete variable using this technique$5.$Create a Bar Chart summarizing customers by state:Select State as the Rows field and Customer ID as the Values field.Click Customer ID in the Values field and change the Value Field setting from SUM to COUNTSelect any cell in the Pivot Table, click the Insert Ribbon, then Select a Bar ChartResize the graph so you can see all the state labelsRight$-$Click a value in the Count of Customer ID column. Select Sort $>$ Smallest to Largest.Feel free to explore any other qualitative$-$nominal variable using this technique$6.$Create a Histogram summarizing the distribution of the Amount spent by customers:Select Amount Spent as the Rows field and Customer ID as the Values field.Click Customer ID in the Values field and change the Value Field setting from SUM to COUNTSelect any cell in the Pivot Table, click the Insert Ribbon, then Select a Column ChartRight$-$Click a value in the Row Labels column. Select Group, then type $0$ for the Start At value. Try a few different values for By until you are satisfied with the HistogramFeel free to explore any other quantitative variables using this technique$7.$Calculate Summary StatisticsWith the main Data worksheet open, Click the Data ribbon, then Data Analysis $>$ Descriptive Statistics.For the input range select all continuous quantitative variable columns. Click New Worksheet for Output Range and click Labels in first row and Summary statisticsRename the new spreadsheet $7-$ Summary Stats Create a text box and write a short summary about the distribution of each quantitative variable. Be sure to examine the mean, median or mode as appropriate, Look at the range at skewness of the distribution.