1	Open the ClothingTech Pre-Project Analysis - Start workbook.
2	Cutt-Off Test: In steps 3 6 below, you are going to determine if there were any 2016 sales that were not shipped until 2017. While these sales took place in 2016, title to the inventory did not pass to the seller at 12/31/2016. As a result, these transactions, if any, have already been recorded but should be corrected via and adjusting entry.
3	Make a copy of the 2016 AR-Original sheet named 2016 AR Data placed to the right of the 2016 Inventory Relief-Original sheet. In the 2016 AR Data sheet, enable the Table features for this data with a table name of ARData. Also, format all dates with a short date format. Duplicate the 2016 Inventory Relief-Original sheet and name the new sheet 2016 Sales Shipped 2017 and position it to the right of the 2016 AR Data sheet. In this new sheet, enable the Table features with a table name of SalesShipped2017. Also, format all dates with a short date format.
4	In the 2016 Sales Shipped 2017 sheet, create a label in cell H1 named SalesAmount to extend the table to include this column. This amount, along with the InvCostReliefAmount which is already part of the data in the sheet, will be needed to create an adjusting entry to correct sales and cost of goods sold if some transactions are found in this analysis. Create a formula in H2 using a function from the lookup and reference category of functions to return the sales amount from an existing table in another worksheet. If there is no matching sale, then the formula should return the null or empty string. When constructing this formula, you should use structured cell references. Also, format the amounts in both columns with the Accounting format. Autofit both columns that have dollar amounts. Hint: Hint: The lookup table used in this formula will be a subset of the ARData table. The null or empty string is simply the use of two double quotes right next to one another (i.e., "") within a formula.
5	Using the table in the 2016 Sales Shipped 2017 sheet, insert a PivotTable named 2016SalesShipped2017 in a new sheet named 2016 Sales Shipped 2017 PT located to the right of the 2016 Sales Shipped 2017 sheet. Include in the PivotTable the total sales and cost of goods sold numbers (values) for each customer name and shipment number (rows). Filter the data by ShipDate to see if there are any 2017 transactions. Collapse the data so that a list of customer names appear with the sum of the sales and cost of goods sold amounts. Change the three column headings to Customer Name, Sales Amount, and Cost of Sales. Format the amounts with the Accounting number format.
6	Based on the results of your analysis, were there any customers that had 2016 sales but shipments were made in 2017? In the Misc sheet, use a Fill Color of Yellow to answer this question in either B2 (i.e., Yes) or C2 (i.e., No). If there are, by how much are sales and cost of sales overstated? Enter the sales amount in B3 and the cost of sales amount in B4.
7	Gross Margin Test: In next two steps below, you are going to review the gross margin for each customer by month to see if there are any unusual amounts that may warrant further investigation. You will also take into consideration the findings in the first test done in this project, as you dont want those transactions, if any, included within this analysis.
8	In the 2016 Sales Shipped 2017 sheet extend the data by inserting a new heading in I1 named Gross Margin %. Create a formula using structured references and no functions to calculate the gross margin percentage and format these amounts with the Percentage format and no decimal places. Autofit all of the columns in the table.
9	Using the SalesShipped2017 table, insert a PivotTable named GrossMargin% in a new sheet named Gross Margin Analysis PT located to the right of the 2016 Sales Shipped 2017 PT sheet. Show for each customer name (row) and transaction month based on the invoice date (column) the average gross margin percentage. Format the gross margin percentages with a Percentage format and zero decimal places. Use the shipping date to filter out 2016 sales that shipped in 2017. Remove all grand totals and change the label in A3 to Gross Margin % Analysis, the label in A4 to Customer Name, and the label in B3 to Months. In cell A17 insert the text Min and in cell A18 insert the text Max. Compute the minimum and maximum gross margin percentages in cells B17:B18 using the appropriate Excel functions and relative references for these calculations. Hint: When creating the PivotTable, you may need to click on the Refresh button in order for the Gross Margin % field to appear. Also, when using the InvoiceDate field in the columns quadrant, Excel will likely interpret what you are trying to do and group the dates by month. This will likely create two fields in the columns quadrant (i.e., one for the month and one for the invoice date). If this occurs, remove the InvoiceDate field and leave the Month.
10	Based on the results of your analysis, are there any gross margins that appear to be out of the norm? Return to the Misc sheet and highlight in a Yellow fill your answer in B7:C7. If Yes, list the customer name(s) alphabetically in column B starting with B9.
11	AR Test: In next four steps below, you are going to create an updated accounts receivable aging schedule that excludes any issues previously discovered. This is done to determine a true amount in each of the aging categories so that a reasonable estimate of the amount collectible can be determined.
12	Make a copy of the 2016 AR-Original sheet and name it AR Aging Data. Place this new sheet to the right of the Gross Margin Analysis PT sheet. Place the text Applied Amount in cell I1. In cell I2 create a formula that uses an appropriate function from the lookup and reference category of functions that will return the amount of cash paid on a Sale or Open Balance based on the data in the AppliedToTransactionNumber column of all Cash Receipt transactions. If there is no payment, then the formula should return an empty string. Copy this formula down to the last Sale row. Hint: Hint: The table_array in the lookup function will be a subset of the data in this worksheet (i.e., just the needed cash receipt data at the bottom of the worksheet).
13	Place the text Balance in cell J1. Create a formula that uses relative references and a logical function in cell J2 to calculate the balance if there is an amount to apply or just return the invoice amount if there is not. Format the balance and applied amounts with the Accounting format. Create a defined name called ARAgingDate and assign the date 12/31/2016 to the name in the Name Manager as a constant not a reference to a cell. Place the text Age in cell K1 and create a formula in cell K2 that uses a logical function to calculate the age of the invoice based on the invoice date and the ARAgingDate. The formula should compute the age if there is a balance due and return the number 0 if there is not. Format these numbers with the Accounting format with zero decimal places and no currency symbol. Copy the formulas in I2:K2 down to the other rows of Open Balance and Sales data. Autofit columns I K.
14	Place the text Min Days Outstanding and Category into cells P3 and Q3, respectively. In cells P4:P7, enter the numbers 0, 31, 61, and 91. In cells Q4:Q7, enter the text 0-30, 31-60, 61-90, and >90. Name the range P4:Q7 ARAgingCategories. Place the text Category in cell L1, and create a formula in cell L2 that uses appropriate functions from the lookup and reference group of functions and the logical group of functions that will return the correct aging category for each sale or opening balance with a balance that is not equal to zero. Specifically, if the balance is not equal to zero, then it should determine the correct aging category from ARAgingCategories; otherwise, it should return the empty string. Copy the formula down for each Opening Balance and Sales transactions. Copy the formula in J2 to the cells in the column J that relate to transactions identified as UnappliedReceipts. Also, for these transactions, place the text Unapplied Cash in column L.
15	Place the text Shipped Date into cell M1. In cell M2 create a formula that will return the date shipped from an existing table in the workbook. Specifically, the formula should test to see if the balance due is not equal to zero and then use an appropriate function from the lookup and reference group of functions that will return the shipping date; otherwise, the formula should return the empty string. Format the dates with a short date format. This value is necessary to filter out shipments made in 2017 for 2016 sales, if there are any. Format this date with the short date format.
16	Based on the data in the AR Aging Data sheet, insert a new PivotTable named ARAging in a new sheet named AR Aging PT located to the right of the AR Aging Data sheet. In this PivotTable, show the balance for customer name and transaction number (rows) for each aging category (column). Limit the results to omit the problem previously encountered (i.e., the cutoff problem found in an earlier step). Reorder the category columns to 0-30, 31-60, 61-90, >90, and Unapplied Cash. Filter out any blank data or transactions with zero amounts. Format all the amounts with the Accounting format. Collapse all the results so that a list of customers is presented with the aging data to the right of their names. Change the text in A3, A4, and B3 to Outstanding Balances, Customers, and Aging Categories.
17	Based on the results of your analysis, return to the Misc sheet and prepare the journal entry to properly account for uncollectible accounts assuming that the allowance for bad debts account had a credit balance of $11,256 and that ClothingTech estimates that 1% of accounts receivable will become uncollectible. Enter the accounts for your entry in column A starting with A18 with the appropriate debit and credit amounts to the right of each account. Use the account titles in the partial chart of accounts given in the sheet.
18	This is the end of the project. Submit your work to be graded. Based on the number of attempts provided, make changes and re-submit this part of the project until you have achieved the highest score or your number of attempts has been exhausted.

Please let me know how to upload the excel doc or how to send it to you. Thanks