Accounts Receivable Data Analysis Project ACCT 303 - Intermediate Accounting! Spring 2022 The objective of this project is to develop and practice data management and analyties, 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 often makes its sales on credit. Superstore began operations carly in 2016, and the excel file "Superstore Data.xls" which is available on Canvas, includes records of all of Superstore's credit sales through the end of 2019. 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, trade discounts made to the gross sales price, 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 Write-off - Account Written Off by Superstore Open - Receivable not Paid as of 12/31/2019 For "Paid", "Return", and "Writeoft", the date in the A/R Close Date column reflects the date on which those events occurred. If the account was still-Open" as of 12/31/2019, 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 you will be asked to perform a data analysis task that requires the use of multiple Excel tools. . . . Submission Requirements: Upon completion of the project, you should submit: A Word document with your answers to the first four questions in this assignment A copy of the excel file including the completed aging schedule as assigned in part "5" as well as any changes that you made to the underlying data to facilitate your generation of that schedule. When you save your excel file for submission, use "Save As" and include your last name (or last names, if you are working with a partner) in the name of the file, i.e., I might save mine as: "Hansen ACCT303DataProject2". You may use any of the tools discussed in the following tutorial, or other tools available in Excel, to complete the aging table in the final portion of the project, including creation of a supplemental worksheet to facilitate your work. All of those changes should be in the Excel workbook that you submit. You may work on the project alone, or in a group of two. The project is due at 11:00 PM on Sunday, April 24th. EXCEL TIPS AND TUTORIAL Dare teregrity and Security in all cases it is critical that you ensure the integrity and security of any data you work with the data is on a shared drive which is accessible to individuals who do not need, or should not have access to the data, you should protect it with a password. You should not need to do so for the purposes of this project. However, this can casily be done in Excel by clicking on "File", then "Protect Workbook", then "Encrypt with Password You should also save your work frequently and if possible back it up using a shared drive, flash drive, or other means. However, data integrity includes por saving the data with errors included. To that end, I suggest that in addition to downloading and saving a copy of the excel file that you will use for your final submission, you also make a duplicate copy of the excel spreadsheet you can click "File", "Save As", and then name the duplicate copy as a *ACCT303 Project WorkingFilo", or something similar). As you are working on the project, 1 recommend that you perform any task that you are not entirely confident about using the duplicate copy first. Once you feel confident that you have mastered the task and can perform the analysis correctly, then use the primary dataset. You may also find it helpful to make copies of subsets of the dataset and save then on a different workbook, or a worksheet (the tabs at the bottom of excel). 11. Data Oreanization: This section presents a number of useful tools in Excel and Exercises that can be completed to practice those tools. Sort: This tool organizes excel columns in ascending or descending order Columns can be sorted alphabetically or numerically. You can sort the data using a single column or multiple columns Sort using one column: 1. Click on the triangle in the top left corner of the worksheet. 2. Click on the Data tab at the top of the excel menu bar, then click "Sort". 3. In the pop-up window that appears, make sure the box next to "My data has headers" is checked. Then in the "Sort By" box choose the column that you would like to use to sort You will usually sort by Cell Value", though you can click on the pull down arrow next to this to see other options. Then choose either ascending or descending order, Excel will automatically recognize if the column you chose is numerie, alphabetic, or in date format and will provide the appropriate choices for orders (for example, if the column you chose is alphabetical, then it will provide you the choice to sort from A to Z or from Z to A). Practice: Sort the Superstore data based on the "Sales Dare You should see that the customer name and product name of the inventory item with the carliest sales date is: Maria Eteradi, Rogers Handheld Barrel Pencil Sharpener, the customer name, and product name of the order with the last sales date in: Juan Grom. Novimex Turbo Task Chair, with a sales date of 12/31/2019 Calculate a Column: Datasets often require the creation of columns of rows to calculate information that is not provided. Data can be created to be a function of existing cells in the data using the function Precio Gesuales competenties the stof Goods Sold 1. Create a header (title) for column"W" by typing Cost of Goods Sold" into cellW1" 2. In cell-W2".calculate the cost of one item. You can enter the formula for Sales Sales Discount - Gross Profil - Cost of Goods Sold by selecting the cell for each value. So, the formula for Cost of Goods sold is: -(R21-S2))-T2 3. Copy this formula for the entire column by clicking on cell "W2" and then hovering the mouse curser over the bottom right corner of the cell. Double-click when the cursor icon changes to "+". This will copy the formula as far down as Excel finds the necessary data in columns R. S. and T After creating the COGS column sort the data based on costs of goods sold. You should find that the product with the highest cost of goods sold is: Fellowes PBSO Electric Punch Plastic Comb Binding Machine with Manual Bind. Columns can also be created with a constant, increasing, or decreasing value or date. 1. Create a header (title) for column "X" by typing"Test Date" into cell "X1". 2. In cell-X2"type "12/31/2019 3. Click on cell X2" and then hover the mouse curser over the bottom right comer of cell X2" and double-click when the cursor icon changes to You should see the dataset increases one day at a time as you move down the column 4. Now Delete column X by right-clicking on the grey cell titled "x" at the top of column "X" (you will know you have done this correctly when the entire column is shaded) and selecting "Delete" from the pull down menu 5. Create a new header for column"X" by typing Test Date" into cell"X1". 6. In cell X2"type "12/31/2019", and type "12/31/2019" in cell"X3" 7. The highlight both cells X2" und "X3" With X2" and "X3" shaded, move the cursor to the bottom right hand corner of cell"X3" and double-click when the cursor icon changes to "+". You should then see the value "12/31/2019 in every cell in column X Create Subtotal of a Column: Entire columns can be totaled using the sum" function. To create a more useful total, use the function "subtotal". This will allow you to answer questions such as "What category of products generate the greatest amount of sales? Practice Calculate Total Sales 1. Go to the bottom of the "GrossSales (insy" Column 2. Enter the formula " SUBTOTAL(9.R2R9991)" in cell R9992 3. Because there is no filter, the value is the sum of all Gross Sales, and you should see a value of $2.275,912.45 in cell "R9992". Filter Filter your Excel data if you only want to display records that meet a certain criterion. 1. Click on the Data tab in the menu bar of Excel. 2. Then click on the "Filter" tool (you will find it right next to the "Sort" tool we used earlier). Drop down arrows in the column headers will appear. 3. Click on the drop down arrow and check the box next to the criteria that you want to use to filter the data Practice Calculate Total Gross Sales in each of 2016 and 2018 1. Click on the drop down arrow next to "Sales Date" to filter the data based on the year of Sale. Excel defaults to showing all data, so you will need to uncheck the box next to "(Select All)", and then check the box next to 2016. Then click "OK". The subtotal cell you created in the prior practice exercise will then show you the total for the year you have selected. You should see total Gross Sales for 2016 are $447.698.81. Now Filter to include only Sales for 2018 (remember to uncheck the box next to 2016), and you should find that total Sales for 2018 are $603,987.15. Calculate a Conditional using the DSUM function: DSUM is a built in function in Excel that allows to calculate the sum of a column including only those numbers in a column that meet one or more criteria supplied by you. (Remember to remove the filter you were using in the prior section. You can do this by clicking on the "Filter" tool that we began the prior exercise by clicking on.) Practice Calculate Total Gross Sales for Sales with Ouantity / Sales with Ouantity from 2-5 and Sales with Quantity of 6 or more. 1. Establish Criteria - In cell AAl enter Quantity and in cell AA2 enter"-1" 2. Calculate Gross Sales with Quantity - 1. In cell AA4 enter the following --DSUMAT:W9991.R1.AAT:AA2)". You should then see that the Total Sales with Quantity 1 is $295.821. The elements of the formula above are: ( 1DSUM" tells excel to use the preprogrammed formula DSUM: (2) "AT:W9991" tells excel that the data you wish to analyze are in cells from cell Alto cell W9991(all of columns A through W. and all of rows 1 through 9991); 5 "RI" is the title for the column that you want Excel to SUM (excel essentially reads as use column R. and alternatively, you could enter "GrossSales (ins including the Station marks in place of R1 in the formula above) finally. "AAT:AA2" identifies the criteria that you wish to apply to the data, and more In one criteria can be used as an example enter Sales Discount" in cell-AB1", and 5" in cell "AB2", then revise the formula in cell AA4 to be: DSUMAI:W9991, R1.AAT:AB2)", and you should see that the Total Sales with antity 1. and a Sales Discount greater than 5 is 55.342.80 lculate Sales with Quantity greater than 1 but less than 6 Est, delete the contents of cells "ABI" and "AB2" You can do this by either Ehlighting those cells and pressing "Delete" or "Backspace", or highlighting the entire lumn "AB" and then right clicking, and click on "Delete". Then replace the contents of "A4" with the original formula: ---DSUMAI: W9991 RIAAL:AA2)". Ext, go to cell "ABI" and type "Quantity", and in cell "AB2" type