quick access to each transaction by customer and is much easier to review than looking through all 409 records of the “2015 data” TechWear has provided. Hint: Recall that beginning AR sales – sales returns – cash receipts – bad debt write-offs = ending AR.
columns B though D. Use the following as a guide for how your pivot table should look and where it should be placed in the “P1Q1” worksheet. Note: If your pivot table begins on a row other than row 1, delete the rows so ExPrep can grade it correctly. ExPrep is expecting values in the cells referenced in the screenshot below. (1 points)
Part 1: Background: Analytics mindset TechWear Revenue Case 50 points TechWear is a privately-owned business that began operations in March 2015. Its sole business is the manufacture and sale of upper-end, high-tech sportswear. It only sells to large distribution outlets. Its primary product is a line of lightweight exercise clothes that contain a new, long-range RFID chip that captures the following information about the user based on personal data (age, weight, etc.) entered by the user: Heart rate Perspiration rate Calories burned Exercise efficiency (percent of capacity) The chip is able to continuously send this information to a host device as far away as 15 miles. The clothes are also GPS enabled and able to track routes, distances and elevations. Management prides itself on being on the cutting edge. The company expects to conduct an IPO within a year or two. TechWear recently retained your firm as its auditors, largely because of your commitment to conduct a highly efficient, technology-enabled audit. Data You are a member of the audit team completing TechWear's first audit. You are first responsible for performing a risk assessment of TechWear related to its order-to-cash function (the revenue cycle). Therefore, you know that your focus needs to be on sales, shipping and cash collection transactions. Your first task is to acquire the data for these transactions. You work with TechWear's IT group to gain access to its sales and cash receipts data for its start-up period of operations, March through December 2015. TechWear's IT group has extracted the data from the accounting system and given you an Excel file with this source data (Techwear_P1andP2.xlsx) so you can begin your analysis. The source data file includes the Customer Master table (a worksheet labeled "Customer Master File") and 2015 data (a worksheet labeled "2015 data"). The "2015 data" worksheet includes the following fields: Type: this is the type of transaction, which is either a sale (Sales) or a cash receipt (CashReceipt). TransactionNumber: this is the transaction number (beginning with 1001). AppliedToTransactionNumber: this is the sales transaction number to which a cash receipt is applied. CustNum: this is a unique customer number used to identify each customer. Analytics mindset case studies TechWear 1 © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US CustName: this is the customer's name. TransactionDate: this is the date of the sale or cash receipt. Amount: this is the amount of the sale or cash receipt. Cash receipts will show as a negative amount. InvoiceDate: this is the date the sale was invoiced (billed). ShipDate: this is the date the goods were shipped. Required Download the Techware_P1andP2.xlsx Excel file from ExPrep. Become familiar with your source data. Review the type of data included in the file before performing any analysis. Once you have completed this review, use the Excel file and these instructions to complete your work. Save your file as Techware_P1andP2_Lastname, Firstname.xlsx. Be sure to save your work often! You must upload your completed file to ExPrep by 11:30pm on March 19, 2020. You are only permitted one submission to ExPrep so make sure it is the file you wish to be graded. Any late submissions will be penalized 10% (of points) per day unless the late submission was cleared in advance by the professor. 1. You've also been told by the client that TechWear only conducts business with the approved customers in the Customer Master file (see worksheet labeled "Customer Master File"). You need to validate that there are no other customer names and that no customer names in the source data the client provided are misspelled. While there are multiple ways you could do this, follow the instructions below for the purpose of this task. a. Create a pivot table that provides a count of transaction type (Sales and CashReceipt) by CustName. Place the pivot table on a new worksheet titled "P1Q1" (no quotes). b. CustNames should show in cells A3:A17 on the "P1Q1" worksheet. The counts by transaction type and the Grand Total that automatically populates in your pivot table should appear in columns B though D. Use the following as a guide for how your pivot table should look and where it should be placed in the "P1Q1" worksheet. Note: If your pivot table begins on a row other than row 1, delete the rows so ExPrep can grade it correctly. ExPrep is expecting values in the cells referenced in the screenshot below. (1 points) Analytics mindset case studies TechWear 2 © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US c. Using VLookup in Column F, confirm that the CustNames in column A are included in the "Customer Master File" worksheet. Your VLookup formula should be in cells F3:F17 of the "P1Q1" worksheet. (1 point) 2. Next, verify that the data for all of these invoices has been captured (no missing transaction numbers) and that there are no additional invoices or duplicates included in the file. Based on transaction numbers in the source data file from TechWear, 230 sales were transacted in 2015, beginning with transaction 1001. You saw evidence of this in your Pivot Table in "P1Q1" worksheet (count of sales transactions). a. Use column C of worksheet "P1Q2" to confirm that there are no missing sales transaction numbers and no duplicates. Use an IF statement to confirm that the sales transaction numbers increment by one. Column C asks if a missing or duplicate transaction exists for each record. To answer this question, your IF statement should return "No" (without quotes) if sales transactions increment by one and "Yes" if not. Sequential transaction numbers (i.e., correctly incrementing by 1 and therefore a "No" result) indicates no missing/duplicate transaction numbers. (1 point) Note: As TechWear begins sales transactions numbers at 1001, an IF statement is not necessary for the first record and cell C2 contains an "n/a" accordingly. Begin your IF statements in cell C3. 3. You've been told by the client that the accounts receivable balance on the general ledger at December 31, 2015, is $684,491.19 and that there were no returns or write-offs in 2015. a. Using the template format on worksheet "P1Q3", confirm this $684,491.19 balance. The beginning balance has been pre-populated for you. You will need to use SUM and SUMIF formulas to complete this work in the highlighted cells B4 through B6. (3 points) b. Even though you are able to confirm this balance, your manager asks you to determine if there are any sales cutoff issues as of December 31, 2015. Based on your knowledge of the revenue cycle, you remember that invoices are not produced until goods are shipped. Analytics mindset case studies TechWear 3 © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US For all the Sales Transaction records in 2015, create a formula of your choice in column J of the 2015 data worksheet (labeled Cutoff Error) that will result in a flag/value of "Sales Cutoff" for any sales transactions incorrectly included in the December 31, 2015 accounts receivable balance and "OK" for any sales transactions correctly included in the December 31, 2015 accounts receivable balance. ExPrep will be looking for the values of Sales Cutoff (upper and lower case) and OK (upper case) in the grading. (2 points) c. Based on the results of this test, you update your calculation of the December 31, 2015 accounts receivable balance on the worksheet "P1Q3" using cells B8 and B9. (2 points) Use a formula of your choice to show the amounts needed in cells B8 and B9 to show an adjusted accounts receivable balance which removes the sales cutoff errors you identified. Note: You will complete cells B11, B13 and B15 later in this case. It is ok to leave them blank now. Format the values in column B with the Accounting format, 2 decimal places. Part 2: Required: Continue using your file Techware_P1andP2_Lastname, Firstname.xlsx to complete the following steps in Part 2. Now that you have your data and have performed steps to validate it, you are ready to perform appropriate analytics techniques to inform your risk assessment for the order-to-cash (revenue) cycle for TechWear. The analysis you complete will be used by your audit team to complete the risk assessment and help determine the audit testing necessary on this engagement. 1. Your audit manager asks you to develop an accounts receivable (AR) subsidiary ledger (by customer and by transaction number) which in total agrees to your Adjusted AR Balance of December 31, 2015 (your computed value in cell B9 on the worksheet "P1Q3"). This AR subsidiary ledger will give you quick access to each transaction by customer and is much easier to review than looking through all 409 records of the "2015 data" TechWear has provided. Hint: Recall that beginning AR + sales sales returns cash receipts bad debt write-offs = ending AR. As mentioned in Part 1, the beginning accounts receivable balance is zero and there are no returns or write-offs in 2015. a. The AR subsidiary ledger you create must be a pivot table. Create the pivot table in a new worksheet and label it "AR Sub Ledger P2Q1" (no quotes). Your pivot table should look like the screenshot below. Order the rows of the pivot table by transaction number within customer. If you expand on the "+" to the left of the CustName you can see the individual transaction # and amounts under each customer which sum to the total for that customer. If you collapse on the "-" to the left of the CustName you will just see the 12/31/15 AR subsidiary ledger balance for that customer. The image below does not contain the dollar amounts and is provided for formatting example purposes). (2 points) Analytics mindset case studies TechWear 4 © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US b. Use filters as appropriate. Format the amounts in column B of the "AR Sub Ledger P2Q1" worksheet in the Accounting format with two decimal places. You can now see the AR balance for each customer. c. Use a cell reference to pull your Grand Total of the AR Sub Ledger into cell B11 on "P1Q3" worksheet. ExPrep is looking for this value when it grades your work. (1 point) 2. While the "AR Sub Ledger P2Q1" pivot table shows you all the sales and cash receipt transaction detail for 2015 by customer and ties to your Adjusted AR Balance at 12/31/15, it is not very efficient in highlighting which invoices are still outstanding at 12/31/15. You are most interested in this information to inform your risk assessment. a. Develop a 12/31/15 Open Invoices report by CustName and Invoice Number with a new pivot table. Just like the AR Subsidiary Ledger you just created, this report should agree with your Adjusted AR Balance of December 31, 2015 (your computed value in cell B9 on the worksheet "P1Q3"). Place this pivot table in a new worksheet labeled "Open Invoices P2Q2" (no quotes). This report should display the customer name, the invoice transaction number and the adjusted invoice balance amount at 12/31/15. The report should not list any cash receipt transaction numbers. Below is an illustration showing the adjusted 12/31/15 invoice balances for just one of TechWear's customers and provides you with guidance to prepare your pivot table: Analytics mindset case studies TechWear 5 © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US b. Use filters as appropriate. Format the amounts in column B of the "Open Invoices P2Q2" worksheet in the Accounting format with two decimal places. c. Before you create this new pivot table, think about the attributes or fields available from your 2015 source data. Do you have all the values you need to create this report as specified in step 2a? No. You don't. However, you do have enough data to compute an adjusted 12/31/15 AR balance for sales transaction/invoice record. Using the data you have, populate columns K and L in the "2015 data" worksheet with excel formulas of your choice to compute the 12/31/15 balance on each sales transaction record. You must use a formula in each of the sales transaction record cells in columns K and L, do not copy or paste any values. Hint: using either VLookup or Index/Match is an effective approach but you may need to add/combine another excel function(s) to your formula as well. Do your own research as needed to find the excel function that works best for you. (4 points) d. Use a cell reference to pull your Grand Total of the Open Invoices into cell B13 on the "P1Q3" worksheet. ExPrep is looking for this value when it grades your work. (1 point) Next you perform the following analyses and visualizations relating to collectability risk, which is the risk the company won't collect money for its sales, on the adjusted December 31, 2015 accounts receivable balance. For each of these analyses you must use your adjusted December 31, 2015 AR balance. This instruction will not be repeated in the instructions below so take good care to incorporate it into the remainder of your work; it will impact your answers and therefore your grade. Analytics mindset case studies TechWear 6 © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 3. Your manager asks you create a visualization that displays the year-to-date trend in sales and cash receipts by month for 2015. a. First you review your source data in the "2015 data" worksheet to determine whether or not you have all the attributes/fields you need to create this visualization. Do you? The answer is no. You determine you need all the amount values to be positive numbers, and You need to accumulate the transaction date data by "month name", but some of your amounts are negative numbers and all the transaction date values are formatted as "daymonth-year". Because of the critical thinking skills you developed in your accounting program, you realize you can transform some of the existing data in the "2015 data" worksheet into the values you need. Review the headings for columns M and N carefully and then populate those columns excel formulas of your choice to transform the existing data into the attributes you need. Only use excel formulas and functions. (4 points) b. Once you have created those additional attributes/fields, create a pivot table to accumulate the year-to-date sales and cash receipt amounts by month name. Label the worksheet for this pivot table "P2Q3 Trend Data Pivot" (no quotes). Your pivot table should display the transaction date month name in the rows and the year-to-date sales and cash receipts amounts in the columns. (5 points) Excel will automatically include both a Grand Total row and column in your pivot table which are meaningless for this analysis. Right click on the column and row displaying a Grand Total and select the option to Remove Grand Total. Format dollar amounts as Accounting with 2 decimal places. Your pivot table must reside in cells A1:C14 of the "P2Q3 Trend Data Pivot" worksheet. Remember this trend analysis is looking at year-to-date data. Make sure your pivot table displays the cumulative or year-to-date sales and cash receipts data as the month of the year changes. Hint: there is a way to set this in the pivot table "field settings" for the sales and cash receipt amount fields. The format of your pivot table should be as follows (no dollar amounts are displayed here): Analytics mindset case studies TechWear 7 © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US c. Use your pivot table data to create a line graph visualization of the year-to-date sales and cash receipt activity. Format your graph with dollars on the y-axis and month names on the x-axis. Insert a title at the top of the visualization that reads "2015 Year-to-Date Sales and Collections" (no quotes). Include a legend on the right-hand side of your graph that distinguishes the sales trend from the cash receipt trend. Insert your visualization in a new worksheet labeled "P2Q3 Trend Graph" (no quotes). Place your graph with the cell range A1:J30 (it only needs to be within this range). Below is a formatting only example for your reference which excludes the trend lines, formal title, and legend details. (2 points) 4. Your manager has also requested a visualization that can help highlight any concerns about potential collection issues. You will use the year-to-date days-sales-outstanding ratio for this visualization. a. You realize that you will first need to compute the year-to-date days-sales-outstanding (DSO) ratio for each month. Use the template in worksheet "P2Q4 DSO" to compute values for each of the column headings provided. You will need to compute several values before you can compute the DSO ratio for each month: Column A: Month Year. You can use an excel formula to determine the number of days in a month (column G) but to do that we need a "date", not just the month name in column B. Use CONCATENATE in column A so that cells A2:A11 contain the month name followed by the year 2015. For example: "March 2015" (no quotes and a space between March and 2015. (1 point) Column B: Month has been pre-populated for you. No further action required. Column C: DSO = ending AR balance for the period / total sales for the period (year-to-date) * number of days in the period (year-to-date). Format DSO as Number with 0 decimal places. (3 points) Column D: Monthly Sales. Use an excel formula of your choice and the 2015 source data to populate cells D2:D11 with the total sales amount for the applicable month. (1 point) Column E: Monthly CashReceipt. Use an excel formula of your choice and the 2015 source data to populate cells E2:E11 with the total cash receipt amount for the applicable month. (1 point) Analytics mindset case studies TechWear 8 © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US Column F: Accounts Receivable Balance. Use an excel formula of your choice to compute the accounts receivable balance at the end of each month indicated. (1 point) Column G: Days. Use the excel functions DAY and EOMONTH in a nested formula to compute the number of days in the Month Year (column A). (1 point) Other Columns. To compute DSO in column C you will need other data values not specified in columns B:G. Some of these values you have already created and some you will need to create yourself. Use excel formulas in the columns to the right of column G to create any of the remaining values you need to compute DSO in column C. b. Now that you have calculated DSO, it is time to create a column chart (or vertical bar chart) to help your manager visualize DSO over time in 2015. (2 points) Insert this visualization below your DSO data in the worksheet "P2Q4 DSO". Place it between rows 15 and 40. Create the visualization with DSO on the y-axis and months on the x-axis. The chart title should be "2015 Days Sales Outstanding" (no quotes). Include a y-axis label of "Number of Days" (no quotes). 5. You and your manager are also interested in analyzing the age of accounts receivable in total and by customer. You decide to create the following analysis and visualization. a. First you develop an aging analysis by customer and invoice using 30-day increments (030 days, 3160 days, 6190 days and > 90 days). The aging will display at the customer level with the ability to drill down to the transaction (invoice) level. Review the attributes available to you in the "2015 data" worksheet. What data attributes do you have which can be transformed into new data attributes that you can use to create the aging analysis as described above? Consider what you can do with Excel formulas and use columns O and P on the "2015 data" worksheet to develop an "Aging Category" for any open invoice included in your 12/31/15 Adjusted AR Balance. Use the 30-day increments noted above for your "Aging Category". (4 points) b. Once you have created an "Aging Category" for the open invoices, insert a pivot table in a new worksheet called "P2Q5 Aging Analysis" (no quotes). Your pivot table should be placed in columns A:F as shown below. Only one customer, Urban Runner, is expanded in this screen shot to illustrate format for the pivot table. As noted in 5a above, the aging displays at the customer level with the ability to drill down to the transaction (invoice) level. Format all dollar amounts as Accounting, 2 decimal places. Use a cell reference to pull your Grand Total of the Aging Analysis into cell B15 on the "P1Q3" worksheet. ExPrep is looking for this value when it grades your work. (3 points) Analytics mindset case studies TechWear 9 © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US In columns J:N of the "P2Q5 Aging Analysis" worksheet, type the aging category names into cells J1:M1 and type "Total" (no quotes) into cell N1. Use an Excel formula of your choice to display the total accounts receivable dollar amount by aging category (in row 2) and to calculate the percentage of accounts receivable in each category (row 3) at the company level (all customers combined). Column N cells N2:N3 should include the SUM formula. The dollar amount in cell N2 should tie to the Grand Total dollar amount in your Aging Analysis pivot table. Dollar amounts should be formatted as Accounting, 2 decimal points. Percentages should be formatted as Percentage, 0 decimal points. This is illustrated with the following screen shot as an example only: (2 points) c. Based on the data for your analysis is 5a, you then create a visualization of the percentage of accounts receivable in each aging category at the company level using a column/vertical bar chart. (2 points) Insert this visualization on the "P2Q5 Aging Analysis" worksheet. You can place it between cells J10:N30. Format your chart with the aging category on the x-axis and percentage on the y-axis. The title of the chart should read "12/31/15 AR Aging" (no quotes). Do not include a legend. d. When you are finished, save your file and submit to ExPrep. Analytics mindset case studies TechWear 10 © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161USAcrobat Distiller 17.0 (Windows)