Mod 9 Data Analysis Homework

Module 9 – Homework. You are a senior financial Analyst with ABC Company, a large transportation company. You have been asked to do some data analysis to support the yearend Accounts Receivable balance in preparation for an upcoming audit. Your IT department has downloaded the company’s open AR Subledger into a table which is found in the file called ABC Database. Using this data, you have been asked to perform the following steps and create related worksheets that will be provided to the company auditors. This data will be used for Module 9 and Module 5 Homework. Section 1 Module 9 Homework Your task in the module 9 homework is connect, clean and prepare the data to be used in the audit. Work Plan Ste p Worksheet Objective Instructions 1 NA Get Data. 1. Create a folder on your computer to put your data files in.. 2. Download the following files from Canvas into the folder you created: a. ABC AR Data.csv b. ABCMasterTables.xlsx c. ABCSiteData.xlsx 2 NA Create a Workbook 1. Create a new Excel workbook. 2. Save the workbook with the name: ‘YourName _Module_9_Homework’ in the folder you created in Step 1. 3 Get and Transform (Power Query) Import ABC AR DATA.CSV In your workbook connect to the file called ABC AR Data.csv. 1. Use Get and Transform to connect to the file. 2. This is a csv text file so find and use the appropriate tool to connect to the data. 3. Select Transform to bring the file into Get and Transform (Power Query) 4 Get and Transform Clean the ABC AR Data 1. Name the Table ‘ARData’

Module 9 – Homework. Ste p Worksheet Objective Instructions (Power Query) 2. In the applied steps remove ‘Changed Type’. 3. Remove the following Columns: a. GL Date b. Invoice Status c. INV TYPE d. Invoice Type e. Company Code f. Invoice month g. If there are columns with no title remove them as well. 4. Move the Invoice number column to the first column in the workbook. 5 Get and Transform (Power Query) Set the data type for each column. 1. All columns should be set Text except: a. Invoice Date = Date * b. Delivery Date = Date * c. DAYS OS = Whole Number d. Account Number = Whole Number e. Job Number at Header = Whole number f. Revenue Amount = Decimal Number g. Invoice Amount = Decimal number h. Invoice Length = Whole Number i. Site = Whole Number j. Department Number = Whole Number k. Commodity Code = Whole number *note, dates need to be converted from a number field. If the data field is text first convert it to Decimal number and then to a date. If a message asks you if you want to replace current, choose ‘Add new step’. 6 Get and Transform (Power Query) Close and Load as connection Close and load your query as a connection only. 7 Get and Transform Connect to excel worksheets. Use Get and transform to connect to the excel

Module 9 – Homework. Ste p Worksheet Objective Instructions 6. Close and load to a connection only. 7. The second query will merge table Merge1 with Billing Codes and name it Merge2 Repeating the steps above. 8. The third merge will merge Merge2 with the Chart of Accounts again following the steps above. 9. Continue this process for all tables except the site Table. 10 ARTable Merge the Site Table and create an Excel table called ARTable. The final merge is to merge the Site Table to the Merge6 table (or the last table you created above). 1. The Site table column primary key column name is different than the foreign key column in Merge6. The columns you will link are Site in Merge6 to Loc in the Site table. 2. Name this query ARTable. 3. In power query select to include all columns except the loc column. 4. Review each of the column titles in power query change the name of columns to remove the name of the table. For example, rename Invoice Header.Transet Days to Transit Days. 5. This query completes the build of your data warehouse. 6. Close and load to a table. 11 ARTable Transform Data While reviewing the data in the ARTable you notice that the company’s aging categories are not the industry standard. Therefore, you need to add a column to the ARtable to calculate the industry standard aging categories. This will allow you to evaluate the receivables against other companies in the same industry. The industry standard aging categories are: Not due (< 0 days outstanding) Current (0 to 30 days outstanding) 31 to 60 days

Module 9 – Homework. Ste p Worksheet Objective Instructions 61 to 90 days 91 to 180 days Greater than 180 days Steps. 1. Open the ARTable in Get and Transform (Power Query). 2. Add a new column using the Conditional Column tool. 3. Call the column Typical Aging. 4. Complete the clauses to group the data as shown above. 5. Move the Typical Aging column next to the Aging Cat column. 6. Click OK 7. Save and load changes back to the table. 12 ARTable Add a cost center Column. The company uses cost centers to aggregate revenue expenses for cost accounting. Cost centers are created by joining the site and department numbers together. Before we can create the cost centers the site and department numbers must be converted to text in new columns. The Department number must be padded with leading zeros before merging the columns together. Steps: 1. Duplicate the Site and Department Number columns. 2. Name the new site column ‘SiteText’ and the new department number column ‘DepartmentText’. 3. Open the custom column tool. 4. Name the column ‘CostCenter’. 5. Build a formula to combine the SiteText column with The DepartmentText Column using the & operator. a. The department text needs to be padded with a leading zero. b. Use the text.padStart() function in your formula around the DepartmentText field. Pad with two “0” characters. c. It should look like this: [SiteText] &