Analytics mindset case studies – Peach State University Hotel – understanding audit analytics 1 © 2017 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 04973-171US

Analytics mindset

Peach State University Hotel

Understanding audit analytics

In the case background, you learned how the audit is transforming in the world of big data and analytics. You also are better prepared to ask the right questions since you learned about basic business and accounting operations for Peach State University Hotel (PSU Hotel) and gained insight into the hotel industry. You also have become familiar with the accounting data available to you about PSU Hotel and learned that the extract, transform and load (ETL) process has been completed by your audit data capture team.

Now it is important for you to understand how data analytics is used in the audit and become more familiar with your audit analytics tool, called EY Helix General Ledger Analyzer for students (GL Analyzer).

Using data analytics for risk identification and assessment and substantive procedures

The following information will provide you with a very brief, high-level overview about how data analytics can be used in the audit process to help identify and assess risk at the account and assertion level, and also can be used in substantive procedures to obtain audit evidence. Throughout this case, you will see references made to analyses provided in the GL Analyzer workbook (in blue) that you will continue to learn more about as you progress. It is important to note that this background and the GL Analyzer workbook do not represent all of the types of analyses that might be performed, or the level of sophistication of analyses and tools that are used in practice. However, it is intended to provide an appropriate level of insight for your learning purposes.

Using data analytics to identify and assess risk at an account and assertion level

Data analytics, ranging from simple to more complex analyses, can assist in properly identifying and assessing inherent risk at an account and assertion level.

Account balance analysis:

► Often, a starting point for an auditor is to perform a high-level, year-to-year review of account balances, focusing on significant changes in a balance or variations in an account balance from the expected amount. The latter is particularly important if an auditor expects a change in a balance but does not see one (e.g., a change in accrued payroll in light of an increased number of days since the last pay date) or does not expect a change but sees a significant increase or decrease in activity. The Balance sheet analysis and Income statement analysis are particularly useful as a comparative analysis. The lead sheet functionality within each analysis also easily provides the auditor with a more granular view, which may help pinpoint the specific accounts driving the unexpected changes and, therefore, the individual accounts that represent a greater risk.

Analytics mindset case studies – Peach State University Hotel – understanding audit analytics 2 © 2017 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 04973-171US

► Relationship analysis:

Natural relationships between accounts can be observed throughout the business cycle of a company (e.g., through the Relationship analysis.) Some are fairly generic across industry and sector lines, such as the relationship of FICA taxes paid in relation to gross payroll. Others are industry specific, such as the relationship of research and development spending as a percentage of revenues for a technology company. One powerful type of relationship analysis is a Gross margin analysis, which portrays a company’s gross margin as a percentage of its sales.

► Journal entry activity and detail analysis:

Data analytics can allow the auditor to drill into the journal entries that make up an account balance or related activity to help identify and assess risks, including risks of fraud or management override, which are often addressed by risk-based journal entry testing.

– Drilling down: At a high level, the General ledger statistics dashboard can provide some insight by providing an overview of the nature and composition of journal entries used in an analysis, but a deeper analysis generally is more useful. For example, an auditor can drill down to underlying journal entries (Journal entry line items and Journal entry summary and details) to identify whether unusual activity was caused by a single material manual entry or by thousands of routine transactions.

– Dimensions: By capturing more data than just account numbers and amounts, the auditor can easily have access to multiple dimensions that help identify and assess risk. Common dimensions include the business unit, preparer, approver and source of the entry (e.g., routine systems, such sales or cash receipts, and non-routine systems or estimation, such as human- generated journal entries). These dimensions can help the auditor determine, for example, whether unusual activity came from a source unrelated to the process (e.g., revenue transactions posted within the fixed asset source).

– Activity and trends: An auditor also considers the volume of activity, as well as the size, complexity and homogeneity of transactions processed through an account when determining the inherent risk. Consider, for example, how data analysis can help an auditor detect anomalies involving depreciation. If a company’s normal depreciation process involves one entry each month, data analysis can help detect the absence of an entry in certain months and multiple entries in others. Analyzing activity, particularly trends (e.g., over time, by business unit, by geographic location), can also help identify risks and unusual activity that warrant further investigation. The Monthly activity analysis, Dimension analysis (which is a subset of the journal entry activity analysis) and Date analysis are particularly useful. Continuing with the depreciation example, if the depreciable asset balance has not changed, an auditor would not expect monthly depreciation expense to change and this easily can be seen with analytics.

► Identifying and understanding significant processes:

Data analytics often is used to identify and understand the sources of transactions materially affecting a significant account, thereby helping us identify significant processes. Analytics (e.g., Dimension analysis) can also be used to confirm our understanding of a process by tracing a transaction through the enterprise resource planning (ERP) sources affecting the process.

Analytics mindset case studies – Peach State University Hotel – understanding audit analytics 3 © 2017 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 04973-171US

Using data analytics as a substantive procedure to obtain audit evidence

Data analytics can be used to obtain substantive evidence through the performance of substantive analytical procedures or tests of detail. When performing data analytics, we typically include 100% of the population, which allows for testing entire populations and greater levels of disaggregation and may provide greater audit evidence compared with other types of audit procedures (e.g., sampling). Unlike risk identification and assessment analytics, which are exploratory in nature, substantive analytics are often confirmatory, meaning that they are used to confirm management’s assertions and an auditor’s expectations of the relationships that exist within the data.

Substantive analytical procedures involve comparisons of recorded amounts, or ratios developed from recorded amounts, to expectations developed by the auditor. The extent to which the auditor uses substantive analytical procedures as effective audit evidence is affected by several items, such as:

► Analysis of plausible relationships among both financial and nonfinancial data

► Investigation of identified fluctuations or relationships that are inconsistent with other relevant information or that differ from expectations by a significant amount

The ability to use substantive analytical procedures as effective audit evidence is affected by:

► Availability of data: The availability of reliable and relevant data will facilitate effective procedures.

► Disaggregation: The degree of disaggregation in available data can directly affect the degree of its usefulness in detecting misstatements. Data analytics is particularly useful because it enables a greater level of disaggregation. The greater the disaggregation, the more precise the auditor’s expectation can be, which can lead to higher levels of assurance from the analytics.

► Account type: Substantive analytical procedures are more useful for certain types of accounts than for others. Income statement accounts tend to be more predictable because they reflect accumulated transactions over a period, whereas balance sheet accounts represent the net effect of transactions at a point in time or they are subject to greater management judgment.

► Predictability: Substantive analytical procedures are more appropriate when an account balance or a relationship between items of data is predictable (e.g., between sales and cost of sales or between trade receivables and cash receipts). A predictable relationship is one that may reasonably be expected to exist and continue over time.

► Nature of assertion: Substantive analytical procedures may be more effective in providing evidence for some audit assertions (e.g., completeness or valuation) than for others (e.g., rights and obligations). Predictive analytical procedures using data analytics can be used to address completeness, valuation and measurement, and occurrence.

► Auditor experience and professional judgment: Effective substantive analytical procedures are based on recognizing unusual or unexpected variations. Planning and executing substantive analytical procedures should be performed by those who have a deep understanding of the entity’s business and systems.

Analytics mindset case studies – Peach State University Hotel – understanding audit analytics 4 © 2017 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 04973-171US

The design of a substantive analytical procedure is limited only by the availability of reliable data and the experience and creativity of the audit team. Substantive analytical procedures generally take one of the following forms:

► Account balance or trend analysis: A commonly used technique is the comparison of current data with the prior-period balance or with a trend in two or more prior-period balances. An auditor evaluates whether the current balance of an account moves in line with the trend established with previous balances for that account, or based on an understanding of factors that may cause the account to change. Examples would include the Balance sheet analysis, Income statement analysis, Monthly activity analysis and Date analysis.

► Ratio analysis: Ratio analysis is useful for analyzing asset and liability accounts, as well as revenue and expense accounts. An individual balance sheet account may be difficult to predict on its own, but its relationship to another account is often more predictable (e.g., the trade receivables balance related to sales). Ratios can also be compared over time or to the ratios of separate entities within the group, or with the ratios of other companies in the same industry. Examples would include the Gross margin analysis and Relationship analysis.

► Comparative nonfinancial analysis: Unlike the trend analysis, this analytical procedure does not rely on events of prior periods, but upon nonfinancial data for the audit period under consideration (e.g., occupancy rates to estimated rental income or interest rates to estimated interest income or expense). These tests are generally more applicable to income statement accounts and certain accrual or prepayment accounts.

► Structural modeling: A modeling tool constructs a statistical model from financial or nonfinancial data of prior accounting periods to predict the current account balances (e.g., linear regression).

Each technique differs in its ability to determine whether a misstatement exists and in the level of assurance it provides. For example, a trend analysis relies on data from only a single account, whereas a ratio analysis incorporates the expected relationships between two or more accounts. A comparative nonfinancial analysis and structural modeling may assist auditors in developing expectations to compare against an entity’s recorded amounts.

Understanding your audit analytic tool, GL Analyzer

You are not required to develop and perform analytics because an audit analytics tool, GL Analyzer, has already been developed for you. However, you should understand the purpose of each analysis within this tool because you will be responsible for understanding and interpreting what the analytics reveals.

The case has separate sections that correspond to the different Tableau tabs. There are several questions for each tab. If you are unfamiliar with using the GL Analyzer, consider reviewing the tab labeled User guide, which provides an explanation of what is available in each tab in the GL Analyzer. The basic purpose of each tab is described below, followed by questions specific to that tab.

Analytics mindset case studies – Peach State University Hotel – understanding audit analytics 5 © 2017 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 04973-171US

Required

The case requirements listed below align with the analytics mindset competencies to help you understand how you are developing your analytics mindset.

Apply appropriate analytics techniques

► Ensure that you have the latest version of Tableau available so that you can open the analytic GL Analyzer workbook (Analytics_mindset_case_studies_PSU_Hotel.twbx).

– See the GL Analyzer user guide file named Analytics_mindset_EY_Helix_General_Ledger_Analyzer_for_students_user_guide.pdf for information about downloading a free trial of Tableau.

► Read the GL Analyzer user guide named Analytics_mindset_EY_Helix_General_Ledger_Analyzer_for_students_user_guide.pdf. This guide will provide the purpose, navigation and instructions for each analysis within your audit analytics tool. Please note that this user guide also can be found on the second tab in your workbook for easy reference as you are navigating through the tool.

► To ensure that you understand the purpose, navigation and instructions for each analysis within the tool, use the analyses highlighted in in blue within the GL Analyzer workbook (Analytics_mindset_case_studies_PSU_Hotel.twbx) to answer each question.

– Balance sheet analysis: The balance sheet analysis provides assistance throughout the audit to understand the business and the way it accounts for its operations, to identify and assess risk, and to begin obtaining substantive evidence.

► In auditing, a lead sheet is often the starting point of an audit file. The lead sheet functions like a table of contents and summarizes, at a high level, what happened in that account.

1) Prepare a cash lead sheet:

a. What general ledger accounts are listed?

b. What other information is listed?

c. What is the amount of the beginning balance for cash?

d. What is the amount of the ending value for cash?

e. Where else can you find this information, other than in the lead sheet?

2) What are the total assets for the hotel in the current year?

3) Which liability account class had a decrease from the prior year? (Remember that liabilities are credit accounts, a decrease would result in fewer credits.)

4) How many specific general ledger accounts combine to make the total plant, property and equipment (PPE) account classification shown in this tab?

5) What is the value of the Buildings and Building Improvements account (GL Acct # 16200) in the current year?

6) What color is the bar that shows the inventory ending balance amount in the balance sheet composition view?

Analytics mindset case studies – Peach State University Hotel – understanding audit analytics 6 © 2017 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 04973-171US

– Income statement analysis: The income statement analysis provides assistance throughout the audit to understand the business and the way it accounts for its operations, to identify and assess risk, and to begin obtaining substantive evidence.

1) What was the percentage increase in travel expenses (excluding mileage)?

2) Did total revenue increase or decrease from the previous year and by how much?

3) How much did the company pay for overtime expenses in the current year?

4) What color is the bar representing Sales – Hotel?

– Monthly account activity analysis: The monthly account activity analysis provides monthly account activity trending for the selected account or account class. This information is used to understand the business; the nature of and accounting for transactions; and to investigate unusual or unexpected relationships, trends, changes or transactions to identify risks and obtain substantive evidence. This analysis provides comparisons of account activity by month across years.

1) For which month is there the largest difference in net revenue (all revenue accounts) between FY15 and FY16?

2) In FY16, which month has the highest revenue (all revenue accounts)?

3) In FY16, which month has the lowest revenue (all revenue accounts)?

4) Where did you find the answer to the previous questions?

5) Which month of each FY shows the largest amount of cash activity and how much was it?

Dimension analysis: The dimension analysis provides disaggregated account activity details and trending for the selected account class or classes by dimension, which includes source (the subledger or subsystem within the entity’s ERP system from which the entry originated), preparer or business unit, and detailed dimension (individual items within each dimension). This information is used to understand the business; identify and understand significant classes of transactions; understand the nature and relationship of accounts; investigate unusual or unexpected relationships, trends, changes or transactions; and identify and assess risks and obtain substantive evidence.

1) Create an analysis that shows the revenue related to dining and hotel sales for FY16 by source (e.g., cash, credit card, check). What is the total cash receipts amount related to dining sales for FY16?

2) What is the total cash receipts amount related to dining sales for February 2016?

3) What is the total cash receipts amount for both dining sales and hotel sales for February 2016?

4) Create an analysis that shows the revenue related to dining and hotel sales for FY16 by preparer. On the monthly activity by dimension graph, what color is assigned to transactions prepared by the property management system, GuestSYS?

5) Using the monthly activity by dimension graph, what is the total amount of hotel sales revenue from GuestSYS in Mar 2016?

– Date analysis: The date analysis provides a summary of the journal entry activity by day for each month in the period. It can assist in the identification of risks (including fraud risks) and the

Analytics mindset case studies – Peach State University Hotel – understanding audit analytics 7 © 2017 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 04973-171US

performance of substantive procedures. Note that this worksheet shows analyses using the effective date and the entry date. Refer to the background case for a refresher on the difference between these two dates.

1) For the Revenue – Sales-Dining account, what day of the week reflected the highest total credit amount entered in FY16?

2) During FY16, what specific date had the most revenue entered for the Revenue – Sales-Dining account?

3) For all revenue accounts in FY15, what day of the year has the highest amount of debit entries and what is the dollar amount?

– Gross margin analysis: The gross margin analysis can assist in the identification and assessment of risks and the performance of substantive procedures related to revenue and the cost of sales.

1) What is the average gross margin percentage for Revenue – Sales-Dining for FY15?

2) What is the average gross margin percentage for Revenue – Sales-Dining for FY16?

3) In FY16, in what month did the gross margin percentage for Revenue – Sales-Dining deviate the most from the average?

– Relationship analysis: The relationship analysis provides the trending analysis of any plausible account relationship (e.g., commission expense as a percentage of revenue). It can be used to obtain substantive evidence of the expected relationship between accounts. The same analysis also can be used to identify and assess risks related to the selected accounts.

1) For FY15, what is the relationship percentage between payroll taxes and payroll expense for April 2015?

2) In FY15, for which month is the relationship percentage between payroll taxes and payroll expense the lowest? What is the relationship percentage?

3) For FY15, what was total for the payroll tax account FICA-HI for August 2014?

– General ledger statistics: The general ledger statistics dashboard provides an overview of the nature and composition of the journal entries used in the analysis. The statistics provide some basic information to help understand the completeness and accuracy of the data, as well as to help identify changes in the IT environment (e.g., changes in the number of preparers year over year).

1) How many liability accounts does PSU Hotel have?

2) Which account class has the largest number of journal entries posted in FY16 and how many were posted?

3) How many journal entries were posted directly from the property management system (GuestSYS) in FY16?

4) Which individual preparer generated the most journal entries in FY16? What is their title?How many journal entries did they post?

5) Select the underlying data for those transactions described in the previous questions so that you could export it to another analysis tool if needed (e.g., Excel).

Analytics mindset case studies – Peach State University Hotel – understanding audit analytics 8 © 2017 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 04973-171US

– Journal entry line items: The journal entry line items worksheet displays the journal entry line items underlying the selection made by the user in the journal entry activity analysis, the source analysis, the date analysis, the gross margin analysis or the relationship analysis.

1) Show all the journal entry line items prepared by Yvonne Chapman (Catering Manager) related to credit card receipts for the Hotel Sales GL account (43180) during the last 10 days of FY16?

– Journal entry summary and details: The journal entry summary and details dashboard provide information about the journal entries related to individual line items identified in the other dashboards. This dashboard is populated by identifying journal entry line items from the journal entry line items worksheet.

1) Using the result from the analytical procedure you performed on the JE line items tab regarding credit card receipts posted by Yvonne Chapman near the end of FY16, select all of the journal entries from JE line items and filter them so that the relevant data is generated for the JE summary and details tab.

2) What is the offsetting account class for journal entry #20169203?