Data Analysis Final
Examples of Prescriptive Analytics
What-If Sensitivity Analysis Evaluating Future Cash Flows Using Various Analysis Techniques Marginal (or Incremental) Analysis Goal Seek Analysis What-If Scenario Analysis
Analysis of the Highest Owing Customer:
Which customers included in the total accounts receivable owing have the highest balances outstanding? Do some accounts need to be written off due to uncollectibility?
Sequence Checks and Sequence Analytics
Why are some check numbers missing documentation? Fraud or error?
Duplicate Transactions
Why are there duplicates of some transactions in the financial reporting records? Are they fraud or just errors?
Accounting Data Sources
Balance Sheet Income Statement Statement of Cash Flows Statement of Stockholders' Equity Footnotes 10/K Filing
Separating Into Groups or Classes
Bankrupt/Not Bankrupt Fraud/No Fraud Misstatement/No Misstatement Audit Client Acceptance/Client Rejection Extend Loan/Do Not Extend Loan Continue as Going Concern/Do Not Continue as a Going Concern
Loan Extension Classification
Banks and other lenders evaluate borrowers based on their credit worthiness to decide whether they will extend a loan to them. Lenders use various borrower characteristics for individuals including: credit scores, work history, existing levels of total debt, debt-to-income ratios, if they own or rent their home, as well as other factors to determine whether to extend a loan.
X4: Market value of stockholders' equity / Book value of total debt owed
Measures long-term solvency of the company, or whether the company will have sufficient funds to pay its debt as it comes due.
X3: Earnings before interest and taxes / Total assets
Measures recent, or short-term profitability of the company.
Profit Margin
Net profit/sales
Return on Equity (ROE)
Profit Margin x Asset Turnover x Financial Leverage
Asset Turnover
Sales/Total Assets
Testing/Tools Used in Diagnostic Analytics
Sequence Checks and Sequence Analytics Fuzzy Matching Duplicate Transactions Variance Analytics Bank Reconciliation Benford's Law
Variance Analytics
Typically performed in management accounting. Looks at differences from expectations - Why is the labor rate and labor use variance for direct labor unfavorable?
Benford's Law
Used to identify fraud or irregular transactions - Why does the first digit of some refunds offered by Verizon customer service representatives depart from the distribution expected by Benford's Law? Are they associated with fraud?
Vertical Analyses
Vertical Analyses expresses financial information in relation to some relevant figure, or base. For example, on an income statement, the relevant base would be net sales revenue. On a balance sheet, the relevant base for a balance sheet line item would be total assets.
Overhead Cost Drivers
What drives overhead costs for a company that makes deliveries? DV: Daily Overhead Costs. IVs: Deliveries made, Miles driven, Delivery time, Weight of all deliveries.
Interest Rates
What interest rate do we give to a borrower? DV: Interest Rate Given to Borrower IVs: Borrower Income, Level of Debt Compared to Income, Credit History, Rent/Own, Credit Score.
Totals, Sums, Subtotals
What is net income each year for the past four years?
Mean, Median, Mode
What is the average employee salary? What is the average stock market return over the past five years?
Horizontal Analytics
What is the percentage increase in accounts receivable from the prior balance to the current balance sheet?
Percentage Change
What is the percentage increase in sales, general and administrative expenses from last year to this year?
Risk and Return
What is the relationship between investment risk taken and expected investment returns? DVs: Different measures of expected investment return [e.g., stock prices (or returns), bond yields, or returns on real estate investments, etc.] IVs: Different measures of risk [e.g., stock price volatility, volatility of earnings, beta, bankruptcy risk (also known as default risk), level of debt (leverage), etc.]
Standard Deviation
What is the standard deviation of the company's stock price over the last quarter?
Histogram
What is the total aged receivables in each 30-day bucket?
PivotTables
What is the total profitability of each customer or each inventory item?
Quartiles/Deciles
What quartile is Ford in based on its leverage compared to the automobile industry as a whole?
Minimums, Maximums
What was the largest sales refund given last month?
Price and Rate Variance Analytics
What's the benchmark? What's the outlier? What's the anomaly?
probabilistic
there is always an element of chance of whether or not it will happen.
Diagnostic Analyses
"Why it Happened?" and "What are the Reasons for Past Results?" analytics performed to investigate the underlying reasons for past results that cannot be answered by simply looking at the descriptive data.
Management by Exception
A managerial style that allows management to spend its time addressing issues/problems.
Accountants Expectation on a Specific Issue
Accountants expect certain levels of performance or outcomes. When that doesn't happen, it usually triggers additional investigation to figure out why.
Hypothesis testing using a difference in means:
Are Nordstrom's sales returns as a percentage of sales higher during the holiday season (e.g., Christmas, New Year's, Hanukkah, etc.) as compared to the non-holiday season? Is the average female executive salary lower than the average male executive salary?
Hypothesis testing using regression:
Are advertising expenses related to sales revenue outcomes?
Analysis of the Most Profitable Customer
By learning which customers are most profitable, the company can better work and cater to these customers.
Analysis of the Most Profitable Product:
By learning which products are most profitable, the company can better promote them to their customers. At the same time, they will be learning which products are not profitable and either work to make them more profitable (raise sales price, get product from another cheaper source), or just cut them from their offering altogether.
If Z >=1.8 and Z < 3
Classify as at risk of bankruptcy, or "gray zone"
If Z >= 3
Classify as not currently at risk of bankruptcy, or "safe zone"
IF Z < 1.8
Classify as significant risk of bankruptcy, or in the "distress zone"
What type of changing conditions might a company face?
Consumer preferences, tax laws, interest rates, levels of risk, exchange rates, demand for employees and their expertise, etc.
DuPont Analysis
Disaggregation of Return on Equity (ROE) into three component parts, including profit margin, asset turnover and financial leverage.
Social media sentiment and stock returns
Does sentiment predict returns? DVs: Different measures of stock market return IVs: Different measures of social media sentiment (positive vs. negative words; number of positive posts vs. negative posts, etc.)
Drill-Down Testing
Drill into the analytics to help explain why or how something has occurred
Bank Reconciliation
Examines differences between transactions recorded by the bank and the GL.
What type of constraints might a company face?
Firms don't receive unlimited capital resources, so they must optimize based on the resources they have available.
Beneish (1999) conducted a predictive analytics from 1982-1992, comparing companies that were found to have committed fraud by the Securities and Exchange Commission (SEC) (in Accounting and Auditing Enforcement Releases) with a sample of companies that did not commit fraud.
He found eight factors to be predictive of financial statement fraud. Using these eight factors, he derived an index called an M-score (similar to Altman's Z-score presented earlier), which is used to predict and classify which firms have likely committed financial statement fraud.
Firm Cost Behavior
How are firm costs dependent on the level of production for a company that makes composite decking? Dependent variable (DV): Total production costs Independent variable (IV): Board feet produced
Uncertainty
How likely or unlikely are we to receive this cash flow? What is the risk that the actual cash flow we receive is greater than what was forecasted?
Counts
How many sales transactions did we have last year?
Amounts
How much cash will be given up (invested) or received from a company initiative?
Diagnostic Analysis involves:
Identifying Anomalies/Outliers Finding Previously Unknown Linkages, Patterns, or Relationships between and Among Variables
Timing
In what time period (month, quarter or year) will each cash flow be given up (invested) or received?
Beneish's Eight Factors
Increase in receivables as compared to last period. Decline in gross margin as a percent of sales. Decline in asset quality index (noncurrent assets as compared to current assets). Increase in sales growth in current period. Decrease in depreciation expense. Decrease in selling, general and administrative costs. Increase in debt (leverage). Higher total accruals to total assets.
Tools and Techniques for Descriptive Analytics
Mean, Median, Mode Minimums, Maximums Standard Deviation Quartiles/Deciles Counts Totals, Sums, Subtotals Graphs (Bar Charts) Percentage Change PivotTables Histogram Ratio Analytics Vertical Analytics Horizontal Analytics
X5: Sales / Total assets
Measures asset efficiency, or how well assets are utilized.
X1: Working capital / Total assets
Measures how liquid, cash-like assets (or liquidity level in relation to the size of the company).
Measures how liquid, cash-like assets (or liquidity level in relation to the size of the company).
Measures long-term profitability over the life of the company.
Identifying Anomalies/Outliers
Often a first step in diagnostic analytics is to look for and identify unusual, unexpected results or transactions. Once we have done an initial attempt trying to understand what might have occurred, we can investigate further to understand exactly why they occurred and if they identify an error or fraud, or just an extreme observation.
Time Series
One of the most important things accountants do is forecast future sales, earnings, and other measures of performance. is a tool/technique used to predict future values based on past values of the same variable.
Dechow and Schrand (2004) looked at the persistence of various financial statement variables over the 1987-2002 time period. Their basic model used current financial performance (for period t) to predict financial performance for the next period (time t+1):
Performancet+1 = β0 + β 1 (Performance t) Where β 1 is our measure of persistence. Note accrual-basis measures of performance exhibits higher persistence than cash-basis measures of performance.
Examples of Probabilistic Models
Predicting which customers will be able to pay back their loan. This might help decide whether to extend credit to potential borrowers. Predicting whether a company will need to restate its financial statements. Predicting which companies would be more likely to commit financial statement fraud. Predicting future performance, including sales, net income, and cash flows.
Why are some check numbers missing documentation? Fraud or error?
Some vendor addresses are similar to employee addresses.
Graphs (Bar Charts)
The change in revenue from one period to the next might be best shown with a
Vertical Analytics
The comparison of interest expense in relation to net sales revenue from one period to the next.
Ratio Analytics
The computation of the debt-to-equity ratio as a measure of solvency.
Objective of Financial Accounting
to help investors, creditors, and other users assess the amounts, timing, and uncertainty of prospective cash receipts.
Moneyball!
The movie starring Brad Pitt tells the true story of finding patterns to identify undervalued talent for the Oakland Athletics in Major League Baseball. This is an example of using analytics to find previously unknown patterns.
Goals of Descriptive Analytics
To be useful to decision makers, accounting needs to be both relevant and faithfully represent the substance of what actually occurred.
Financial Leverage
Total Assets/Equity
Debt-to-Income Ratio:
Would you guess if the greater the debt-to-annual income ratio of the prospective borrower, the lesser or greater the chance of Lending Club granting a loan? We would expect the bigger the debt relative to income, the less likely the loan be accepted, so expect a negative (-) relationship between Loan Acceptance and Debt-to-Income Ratio.
Amount of the Requested Loan:
Would you guess if the greater the requested loan by the prospective borrower, the lesser or greater the chance of Lending Club granting a loan? We would expect the bigger the loan, the less likely the loan be accepted, so expect a negative (-) relationship between Loan Acceptance and Loan Amount.
Employment Length:
Would you guess if the longer the employment length, the lesser or greater the chance of Lending Club granting a loan? We would guess the longer the employment length, the more certain that the loan will be accepted due to stability of the job. So we would expect a positive (+) relationship between Loan Acceptance and Employment Length.
Altman's Z found five factors that helps predict bankruptcy.
X1: Working capital / Total assets X2: Retained earnings / Total assets X3: Earnings before interest and taxes / Total assets X4: Market value of stockholders' equity / Book value of total debt owed X5: Sales / Total assets
Original Z-score formula:
Z = 1.2X1 + 1.4X2 + 3.3X3 + 0.6X4 + 1.0X5.
financial modeling
a representation of the financial outcomes resulting from a decision or future event
Predictive analytics
addresses the question of whether something or some event will happen in the future. To the extent that there is a strong correlation between the past and future, predictive analytics can be particularly useful. analytics performed to provide foresight by identifying patterns in historical data by judging likelihood or probability. provides less deterministic output and more probabilistic models, judging the likelihood and probability of a future event or outcome occurring.
Descriptive Analytics
addresses the questions of "What happened?" or "What is happening?". analytics performed that characterizes, summarizes, and organizes features and properties of the data to facilitate understanding.
deterministic models
all events are known before hand,
Make-or-buy analysis
analysis is a technique used to analyze and address the question whether to manufacture a product in house or to purchase it (or outsource it externally).
prescriptive analytics
analysis performed which identifies the best possible options given constraints or changing conditions. Some would call this an optimization.
Base Rates
are defined as probability of an event occurring based on a related historical average.
sensitivity analysis
as the evaluation the outcomes based on uncertainty regarding the inputs
While both predictive and prescriptive analytics forecast future outcomes, prescriptive analytics
goes a step further and helps to make specific recommendations for management to consider based on what they expect will happen.
Goal seek analysis
is a form of what-if analysis that tells us what input will be needed or what will need to be done (or assumed) in order to reach a desired outcome, output or result. is the ability to calculate backwards to understand the input needed to achieve a certain output.
Regression
is a predictive analytics technique that allows the accountant to estimate a specific dependent variable outcome value based on independent variable inputs.
Classification
is a predictive analytics technique used to separate or classify a sample (or population) into two or more groups or classes.
Marginal analysis
is an examination of the associated cost or benefit of very specific business decisions. typically refers to the cost or benefit of the next (or the marginal) unit.
Hypothesis Testing
is sometimes used in conjunction with predictive analytics. Using hypotheses, accountants can understand the impact of different decisions.
Machine learning
is the ability of a computer (or machine) to automatically learn on its own without being explicitly programmed to do so.
Internal Rate of Return (IRR)
is the discount rate that makes the project's net present value equal to zero, or the rate of return where the present value of cash outflows is exactly equal to the present value of the cash inflows.
payback period
is the length of time required to earn back the amount of the initial investment.
The accounting rate of return (ARR)
is the percentage rate of return expected on an asset. It compares profit to the initial investment cost.
is the length of time required to earn back the amount of the initial investment.
is the present value of the cash inflows less the present value of the cash outflows
What-if scenario analysis
is the process of analyzing future events by considering potential outcomes. does not try to predict one possible outcome, but rather it presents a range of alternative outcomes. computes the expected overall impact on the company based on the joint probability of multiple events on the company.
Persistence
is the repeatability (or continuity) and durability of the financial statement variables (e.g., earnings, sales, cash flows) over time. Simply, it estimates if the trend of the past financial performance continue will into the future.
Finding Previously Unknown Linkages, Patterns, or Relationships between and Among Variables
look for patterns in the underlying data set by summarizing data at different levels and uncovering additional details to understand why something happened.
Base Rate Fallacy
occurs when the prediction places too little weight on the base rates of the past and instead uses different or new information.
Horizontal Analyses
provides comparative increases about various line items of each financial statement over time. uses percentage change using this formula: (New-Old)/Old
A form of machine learning in accounting is
robotic process automation (RPA) referring to software programs that automate certain repeatable tasks.
