KO Open the READ ME worksheet.

KO Change the width of columns A and B to $20$ points.

KO Format the data in cells A$1$:A$4$ to Bold and Italics.

KO Change the font style for the entire worksheet to Arial and the font size to $12$ points.

KO Edit each entry in cells A$1$:A$4$ by adding a colon at the very end of the text.

KO Apply the Merge Across format on the range B$4$:F$4.$

KO Set the alignment in the merged cell beginning with B$4$ to Align Left.

KO Format the merged cell beginning with B$4$ to WrapText.

KO Add the current date in cell B$1.$

KO Add the current time in cell B$2.$

KO Type your last name in cell B$3.$

KO Increase the height of row $4$ to $35$ points.

KO Type the following into the merged cell beginning with B$4$: $$Do not change sort sequence in Store Data worksheet.$$ Start a new line in this cell and add the following so that it appears below the text you just typed: $$Read any alerts before using this workbook!$$

Create a PivotTable that shows each unique store from the Store Data worksheet. Your PivotTable should appear in a separate worksheet and the worksheet tab should be named Stores.

Create a second PivotTable that shows the date range from the Store Data worksheet. Your PivotTable should appear in a separate worksheet and the worksheet tab should be named Date Range $($Hint: you will have to consider the Report Layout as well as fields that may have been grouped$).$

Start a third PivotTable using the Store Data worksheet. This PivotTable should also appear in a separate worksheet and the worksheet tab should be renamed Cash Analysis.

Add the Store, Cashier Number, and Date fields to the ROWS area of the PivotTable. Make any necessary adjustments such that the field names are visible and do not display grouped data.

Add the Amount field to the VALUES area of the PivotTable. Change the name of the field to Net Cash and format the values to U$.$S$.$ Currency with zero decimal places.

Remove all subtotals from the PivotTable.

Sort the Stores based on the Net Cash $($formerly Amount$)$ field in descending order.

Filter the results so only store $540$ is showing.

Set another filter level so only cashier $5700-540$CT is showing.

Set another filter level so only the $10/27$ date is showing.

Bring the Transaction Number field into the ROWS area of the PivotTable. This field should appear to the right of the Date field. Make any necessary column width adjustments so all data and field names are visible.

Add the Cashier Report field to the VALUES area of the PivotTable. Change the name of the field to Daily Cash Report and format the values to Currency with zero decimal places.

Add the Transaction Number field to the VALUES area of the PivotTable. Change the data processing output to Count and change the field name to Transaction Count.

Sort the Transaction Numbers in descending order based on the Net Cash $($formerly Amount$)$ field.

Add a calculated field that divides the Net Cash $($formerly Amount$)$ field by the Daily Cash Report $($formerly Cashier Report$)$ field. The name of this calculated field should be: Pct$.$ of Daily Cash. Format the output of this field to a percentage, $2$ decimal places.

How can this PivotTable produce erroneous results if the row heading fields are changed? Identify one data processing risk and write your answer on the Answer Sheet worksheet.

Add a modification to the Cash Analysis worksheet to mitigate the risk you identified in question $29.$

Open the Store Data worksheet. In cell I$1,$ type the column heading $$State$.$ Then, use the RIGHT function to show the last two characters in the Cashier Number field in this column.

In cell J$1,$ type the column heading $$Week$.$ Then use the WEEKNUM function to display the week number of the calendar year in this column. Assume the week begins on a Monday.

In cell K$1,$ type the column heading $$Cashier Day$.$ Then, use the CONCATENATE function to combine the Cashier Number and Date fields in this column. Adjust the column width to accommodate the data.

In cell L$1,$ type the column heading $$Cashier Daily Report$.$ In this column, show only one instance of each repeating value in the Cashier Report column. The Cashier Report column is showing the amount of cash the cashier reported in the company system each day $($Hint: the sort order of the dataset is critical to produce an accurate result$).$ Adjust the column width to accommodate the column heading.

In cell M$1,$ type the column heading $$Price Check$.$ For this column, use the VLOOKUP function to look for the value in the Amount column in the Price List worksheet. The function should look for an exact match. Note that values can be negative in the Amount column for returns, but there are no negative values in the Price List worksheet.

In cell N$1,$ type the column heading $$Price Alert$.$ For this column, use the IF function to produce an output of $1$ if the output of the VLOOKUP function in column M is an error code. Otherwise, the function should show an output of zero.