1. Sort the Superstore data based on Sales Date. a. What is the sale date and the name of the customer for the oldest (near the beginning of 2018) sale in the dataset? b. What is the name of the customer and the order ID for the most recent sale (near the end of 2021) in the dataset? 2. Using the Superstore data create a new column to present the amount of accounts receivable for each product sold. Title the new column Receivable. The receivable will be reduced by the trade discount, so this amount should be: Sales x (1-Trade Discount). Then sort the data based on Receivable a. What is the name of the product associated with the largest account receivable? b. What is the name of the product associated with the smallest account receivable? 3. Using the Subtotal function in Excel, what is the total amount of receivables generated during Superstores existence. 4. Use the filter function to present only data on sales with an A/R Status of Open. What is the total amount of receivables outstanding as of December 31, 2021? 5. Complete the highlighted portions of the Accounts Receivable turnover ratio table included in the second worksheet (tab) in file Superstore Data.xls including calculating the SuperStores A/R turnover ratios for 2019, 2020, and 2021. The accounts receivable turnover ratio is calculated as follows: Net Sales (Beginning Net A/R + Ending Net A/R)/2 Therefore, in order to calculate the A/R turnover ratio you will need to calculate net sales for the years 2019 2021 and net A/R as of December 31st for the years 2019 2021. Net sales is the gross sales (after reducing the Sales (in$) amount in column Q by the trade discount) less an allowance for sales returns. Based on industry experience, SuperStore estimates an allowance for returns each year that is 4% of their gross sales. Superstore does not grant sales allowances to customers so you will not need to make any adjustment for sales allowances. Net A/R is gross A/R outstanding on December 31st of each year less the Allowance for Doubtful Accounts. Superstore applies a single percentage-of-receivables rate to establish the balance in the allowance for doubtful accounts, and has used an estimate of 1.5% since inception. Therefore, the balance in Allowance for Doubtful Accounts should be 1.5% of the gross A/R outstanding on December 31st of each year.