You are hired by a ChattCoffee, a small online coffee company which operates in Chattanooga, Tennessee. You will perform several data processing tasks with Microsoft Excel. ChattCoffee provides you with its sales records since $2009($see Excel file attached$).$ The file contains sale transactions records about:

$$ Date of Transaction

$$ Transaction Number

$$ Business Name

$$ Business Address

$$ City

$$ State

$$ Total Sales of the Transaction

Perform the following functions $($apply each function in a separate worksheet$)$:

$1.$ Apply SUMIF, AVERAGEIF and COUNTIF functions to calculate the total, average and the number of sales in each city. On the same worksheet, write$/$note in the same worksheet any conclusion or recommendation for the business.

$2.$ Apply the AVERAGEIFS function to calculate the sales to each city during the year $2015.$ There will be a few cities that did not purchase in $2015.$ Use the IFERROR function to state $$No Sales in $2015$ for such cities. Any conclusion or recommendation? Note them in the same worksheet.

$3.$ Apply transpose function to the result from question $1$ so you can list the cities as table heads across the first raw. Below them, list total sales $($you calculated with SUMIF$),$ average sales $($you calculated with AVERAGEIF$),$ and number of sales $($you calculated with COUNTIF$).$ Then use SUMPRODUCT function to calculate the total sales as the sum of products of average sales and number of sales for each city. Also, use a simple SUM function to add the total sales for each city. Make sure that the results of SUMPRODUCT and SUM are exactly the same.

$4.$ Apply a PIVOT Table to the source data and place the results in a new worksheet. Display the business name, latest date the business has made a transaction, the number of transactions for each business, and the average purchases from $($average sales to$)$ the business. Rename the labels accordingly as Business Name, Latest Date, Number of Transactions, and Average of Sales.