The objective of this project is to develop and practice data management and analytics skills that will be valuable in your future careers.

For this project, you will use data on credit sales by "Superstore Inc.". Superstore is an office supply store that primarily sells its products

to small businesses $($for example, heating and air conditioning companies, law offices, doctors' offices, etc.$),$ and makes all of its sales on

credit. Superstore began operations early in $2020,$ and the excel file "Superstore Data S$24.$xls$"$ which is available on Canvas, includes

records of all of Superstore's credit sales through the end of $2023.$ Information about the Dataset: The excel file includes the following

for each customer order: the order number, the sale date, information regarding the products included in the order, information regarding

the customer, and information regarding the gross sales amount and trade discounts, and Superstore's gross profit on the order. The final

two columns of the spreadsheet provide $(1)$ the resolution of accounts receivable, and $(2)$ the date that the account receivable was closed.

There are four possible resolutions of accounts receivable, and they are coded in the spreadsheet as follows: Paid $-$ Account Paid,

Return $-$ Merchandise Returned by Customer, Writeoff $-$ Account Written Off by Superstore, Open $-$ Receivable not Paid as of

$12/31/2023$ For "Paid", "Retum", and "Writeoff", the date in the A$/$R Close Date column reflects the date on which those events

occurred. If the account is still "Open", the A$/$R Close Date column is empty. To complete the assignment, you will need to develop an

understanding of several tools available in Excel. To facilitate this understanding the project begins with a series of tasks focusing on

specific Excel tools that can be used for data management and analysis. In the final task of the project your will be asked to perform a

data analysis task that requires the use of multiple Excel tools.

AssIGNMENT:

$($It is not required. However, before starting the assignment, I recommend removing any changes you made to the data as part of the

tutorial, or redownloading the dataset so you can start the actual assignment with the original dataset.$)$

$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 $2020)$ sale in the dataset?

b$.$ What is the sale date and the name of the customer for the most recent sale $($near the end of $2023)$ in the dataset?

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?

Using the "Subtotal" function in Excel, what is the total amount of receivables generated during Superstore's existence.

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,2023?$

Complete the highlighted portions of the Accounts Receivable aging table included in the second worksheet $($tab$)$ in file "Superstore

Data.xls$"$ and calculate the necessary final balance in the Allowance for Doubtful Accounts for Superstore as of December $31,2023.$

Hint In order to complete this table, you will need to calculate the number of days that each account receivable with an "Open" status

has been outstanding as of December $31,2023.$ Excel will help with this because it actually stores dates as numbers, and just displays

them in date form. To see this, open an excel spreadsheet and type the number $"$l$"$ in any cell. Then right$-$click on the cell and select

"Format Cells". Then under the "Number" tab on the pop up box select "Date" and hit enter. You will then see $"\frac{1}{1}\frac{?}{1900}"$ displayed in

the cell. That is how Excel views day $"1".$ All other days are measured by how many days they were before or after January $1,1900.$ To

see this, enter $32$ in an empty cell in an excel spreadsheet and follow the same steps as above to convert it to date format. You will then

see $"\frac{2}{1}\frac{?}{1900}"$ in the cell. That is the $32$ nd day if you start with January $1,1900$ as day $"1".$ The result of this approach by excel is that if

you have two cells with different dates in them you can subtract the cells and the output will be the number of days between the two

dates, in order to see this, you may need to right click on the cell $($or the entire column$)$ then choose "Format Cells", then click on

"Number" then click $"$OK$".$