Accounting 3303 Exam 2 Prep

Audit Data Analytics

art and science of identifying and analyzing pattern, trends, and anomalies through an examination of audit-related data. Typically entails modeling, visualization, and analysis, and informs the planning and execution of an assurance engagement. PwC and Ernst & Young have rolled out data analytics software. PwC's Halo and EY's Helix both use audit dashboards which are fully interactive.

Online analytical processing (OLAP)

involves using ETL tools to obtain complex info that describes what happened and why it happened. Examples include, Essbase (Oracle) and SAS OLAP Services. Future is uncertain because many do not allow enough ad hoc flexibility to deal with big data.

Is every collection of data a database?

not every collection of data is a database. For example, a payroll system might store the time-card data in one or more Excel files, but this is generally too simplistic to be called a "database."

Database

collection of organized data that multiple users often access and that help accounting systems store data

Data Modeling

design the database. This can be the most challenging step in the process of creating a database because the designer must collect a considerable amount of information through investigation and interviews and must then determine the needs of all stakeholders as accurately and completely as possible. This process is both an art and a science.

Control Source Property

Of particular importance is the Control Source property of an object, which you will find among the settings in the Data tab portion of the Property Sheet window and which binds the control to an underlying data field.

Database Administrator

Supervises the design, development, and installation of a large database system and is also the person responsible for maintaining, securing, and changing the database. As a result of the administrator's many duties and powers, it is essential that the administrator be both skilled and trustworthy. Example: Oil and Gas production firm sabotaged by database administrator.

Irreplaceable data

The information contained in most accounting databases is unique to the organization that created it and is typically priceless. Many organizations would fail shortly after losing the information contained in their accounting databases. For this reason, the security of databases is critical to the organization.

Critical information

The information stored in an organization's databases is oftentimes its most valuable asset. Equifax, for example, is one of the nation's largest credit bureaus, maintaining credit information about millions of Americans. Its credit files are its business.

Calculated Fields

A common task when creating reports is to include calculated fields. For example, a report of employee information might also include a field entitled "Years of Service," which the system can calculate from the employee's date of hire. Sometimes, you want a calculated field to appear in the detail section of a report while at other times, you want group or grand totals to appear in the group footer or the report footer sections of your report.

Database-as-a-Service (DAAS)

Allows firms to outsource their databases to cloud service providers, and use of DAAS has grown rapidly. Many investors are making large investments into cloud databases. Removes the need for firms to hire their own database administrators.

Data analysis

Art and science of identifying and analyzing patterns, trends, and anomalies through an examination of data.

Bound and Unbound Controls

Bound controls are textboxes, drop-down boxes, and similar form objects that depend upon the underlying data and therefore change from record to record. In contrast, unbound controls are labels, report headings, and similar items that are the same from record to record in a form and do not display underlying database information.

Database management systems (DBMS's)

Data in databases manipulated by specialized software packages. Example, inventory systems, general ledger systems, and production-scheduling systems. Most accounting systems involve complex combinations of data stored in databases, processing software, and hardware that interact with one another to support specific storage and retrieval tasks.

Velocity

Data originating from everywhere. Facebook's estimated 250 billion photographs is understated. Velocity is the speed at which data is created. Water hose example, too much water coming at you makes it hard to to drink. FSU Seminole football team uses tech to prevent injuries.

Step 3) Identify Relationships

Entities are related to other entities. For instance, a sale involves the exchange of merchandise inventory to a customer for cash, and the relationship between them is a direct relationship. Cardinality: describes how entities are related, and we often abbreviate the description of the cardinality between two entities as one-to-one (1:1), one-to-many (1:N), or many-to-many (N:N)

authenticity controls

Example, require passwords that limit access to approved users and protect databases from external hacks.

Extract

First part of ETL process. Involves identifying and copying relevant data from a source data store to make it accessable for further processing. Most critical step is identifying which data to extract from the source system. Care should be taken when doing this so there won't be negative performance hits.

Assurance and compliance

Focus on obtaining reasonable assurance that management's assertions are complete, accurate, and truthful, and that the organization is complying with applicable laws, rules, and regulations.

Managerial Accounting

Historically management accountants have focused their efforts in 3 areas: cost management and reporting, performance measurement and analysis, and supporting managements planning and decision-making efforts. Proliferation of internal and external data has been positive for management accountants.

Tax Accounting

In most organizations tax department is one of the largest consumers of data. Every organizational event has some tax implication and every financial transaction has a tax consequence, tax accountants must pour over and consume volumes of financial and transactional data. Taxes play a critical role in maximizing shareholders value within organization. US company's paid highest tax rate in the world (41%). "No Free Lunch" Free online tax website likely use your financial data for other purposes.

Datasheet Screen

It's better to use form instead of Datasheet Screen because one advantage is that a datasheet displays many records at once, making it possible to accidentally type over existing information instead of creating a new record. Another advantage is that developers can design a form to display all the data-entry textboxes for an entire record in one screen, whereas a datasheet typically requires users to keep scrolling to the right to enter data for off-screen items.

Input Masks

Limit user inputs to specific formats—for example, "123-45-6789" for a Social Security number, "(987) 456-7890" for a telephone number, or "7/9/18" for a date. Although system designers use special symbols for the mask, the DBMS interprets these symbols as input requirements and acts accordingly.

Volume (databases)

Many databases are truly enormous. For example, the United States maintains a database of visitors, each of whom must complete a standard online entry form (ESTA) that the U.S. Customs and Border Protection office uses. In 2015, there were over 77 million visitors to the United States.

Variety

Most challenging aspect. Deals with the diversity of data that organizations create or collect. Today organizations collect unstructured data such as media files, Twitter feeds, scanned documents, web pages, blogs, and emails. Usually incompatable with earlier data analysis tools. Example, wearable tech for livestock fends off predators with e-pill or collar.

Hadoop

Open-source infrastructure for storing and processing large sets of unstructured, semistructured, and structured data. Hadoop is part of Apache open-source project. (http://hadoop.apache.org) named after stuffed elephant belonging to son of developer. Stores data and processes data in an interesting way. Utilizes a name code and cluster of data nodes. Uses program called MapReduce to process large data sets, loads processing program on nodes where data resides. Only results are transmitted back across network, virtually eliminating bottlenecks.

Data relevant for analysis from accounting perspective originates from 3 sources

Operational, Mechanical, and social

Transform

Second part of ETL process. Involves cleaning and converting the data from the source systems so that they can be loaded into the target system. Good chance that field format may be different from data source to data source. Example, gender may be M/F in one data source but MALE/FEMALE in another, so a standard format must be set.

Tax data hub

Specialized data mart designed to provide a single version of truth for tac related data. Automatically extracts data from source systems and loads it in a standard format that lends its self to the need of the tax department. Save time and increase transparency.

Need for Accuracy

The data stored in databases must be complete, comprehensive, and accurate—and there can be severe consequences for small data errors. For example, consider the tax payer who forgot to tell his accountant he had moved. His tax return was completed on time, but sent to his old address, compromising his privacy and costing him several sleepless nights.

Distribution

The databases of some organizations are centralized (i.e., the system stores the database in a single location). Many other databases, however, are distributed (i.e., duplicated in local or regional computers as processing needs dictate). Distributing data can make it difficult to (1) ensure data accuracy, consistency, and completeness and (2) secure information from unauthorized access.

Step 5) Identify Attributes of Entities

The tables consist of records, each containing data fields that describe the entity's attributes.

Schema

The totality of the information in a database and the relationships between its tables are called the database schema. Thus, the schema is a map or plan of the entire database.

Queries

What these individuals have in common is the need for specific information from one or more database tables. Queries allow database developers to create customized subschemas. For example, using the car registration database, you might want to (1) find out who owns a certain car parked on campus because the car's lights are on, (2) change the information in a specific record (e.g., update a student's phone number), (3) delete a record (e.g., because the person sells his or her car), or (4) list file information selectively (e.g., prepare a list of all students with California license plates)

Dynaset

dynamic subset of a database that you create with such queries, and the purpose of a data manipulation language (DML) is to help you create such dynasets.

Step 2) Identify Entities

each resource, event, or agent represents an entity in a relational database. REA model does not recognize "receivables" as resources.

Data integrity Controls

edit tests that protect databases from erroneous data entries. Examples include tests for data completeness, conformance to the data type required for the data field, valid code tests (e.g., a state code such as "CA"), and reasonableness tests (e.g., regular payroll hours worked must be between "0" and "40")

Subform

form within a form. It displays data that are related to main form

Relational databases

groups of related, two-dimensional tables.

Data Definition Language (DDL)

of a DBMS enables users to define the record structure of any particular database table (i.e., the individual fields that each record will contain). Thus, DDL is the language that DBMSs use to create the data dictionaries that we described in Chapter 14.

Field Properties

settings for such items as the "field size" (e.g., a maximum length of 7 characters), "format" (e.g., a number with a percent sign for a number data field), and "input mask" (e.g., an input template for entering a phone number in a specific format)

Resources

"what" that databases store—that is, things of economic value. For example, a merchandise sale will transfer an inventory resource to a customer and generate a cash resource for the firm. Common examples of resources are cash, raw materials, and inventory.

Agents

"who" associated with events. Internal agents work within the firm for which a database is designed (e.g., salespeople), while external agents such as customers are outside the firm. Most events involve both internal and external agents. For example, both a salesperson and a customer participate in a merchandise sale.

REA Model Steps

(1) identify business and economic events, (2) identify entities, (3) identify relationships among entities, (4) create entity-relationship diagrams, (5) identify the attributes of data entities, and (6) create database tables and records.

Key Goals for satisfying challenges in creating useful databases

(1) identifying the reports desired by users of the system; (2) finding hardware and software solutions that can adequately perform the data-gathering, storage, and reporting tasks involved; (3) keeping the databases from becoming too large, complex, and unwieldy; (4) protecting the privacy of sensitive information; and (5) avoiding data redundancy (i.e., avoiding the storage of the same data repeatedly in different tables). To accomplish these and other goals, databases must be carefully designed to serve their intended uses.

Accountants have historically interfaced with a organizations AIS in a limited number of ways:

1) As users enter data or extract information from these systems 2) as objective evaluators to assess integrity of data and information 3) as investigators assessing the nature and implications of fraud.

CEO's top ranked most important technological advancements

1) mobile apps and computing 2) Data Analytics

Default Value

A third control over the accuracy of data entry is to specify a default value for the data fields of new records. For example, a weekly payroll table might use the number "40" as the default value for the hours-worked data field. These default values help guard against input errors as well as speed data entry.

Simple Report

A typical report has seven major components: (1) report heading, (2) page heading, (3) group heading, (4) detail or body, (5) group footer, (6) page footer, and (7) report footer.

Legacy system

Catch-all phrase that refers to aging applications and/or hardware that is either outdated or in need of replacement. Usually installed a decade or more ago, interfacing with new AIS's is difficult. Can be a treasure trove of historical data, may be useful inputs into a data analysis exercise. Example, US federal government spends 80% of its $90 billion on operating and maintaining its legacy systems. Study suggests that governments that spend more on legacy systems have higher incidence of security breaches.

Enterprise Resource Planning systems (ERP)

Comprehensive information systems that integrate front and back office business processing functionality and are supported by a single centralized database. In addition to AIS, systems mights also support customer relationship management, sales force automation, supply chain and logistics, and partner relationship management. Used as primary source of data for analytics.

Internet Uses

Databases are critical components of both internal and external corporate Web systems. Databases store information related to product information for online catalog sales, product registration data, employment opportunities, stock prices, and so on. Internet applications often store customer-entered data such as online product orders, credit card numbers, subscription information, airline reservations, and university-student registration data.

Privacy

Databases often contain sensitive information—for example, employee pay rates or customer credit card numbers. This information must be protected from those unauthorized to access it. Experts suggest that the procedures for protecting databases from unwarranted access are among the most critical controls in an organization.

3 broad categories of analytics

Descriptive, Predictive, and Prescriptive

Step 1) Identify Business and Economic Events

Economic events: typically affect an organization's financial statements—for example, a sale on account. This event increases an entity's accounts receivable (balance sheet) and increases a sales revenue account (income statement). Business Events: may not affect financial statements but can affect important aspects of an organization. One example is the discovery that a construction project will cost far more than originally budgeted. While there is no journal entry for this event, the fact would certainly be important information to managers. Other examples are (1) hiring a new CEO or (2) making a valuable discovery during research and development.

Load

Final part of the ETL process. Involves importing transformed data into the target system. The target system is the final data store and will be where the actual data analysis occurs. Initial load is typically a full load bringing all data into target data store. Subsequent loads will be incremental. Target system may be data warehouse, data mart, or a database.

Property Sheet Window

Finally, to customize a control on a form, use that object's Property Sheet window (Figure 16-6b) to make individual settings for control objects. In effect, each form object has separate settings and its own Property Sheet window.

Tab Order

If you rearrange objects in your form in design mode, there is a good chance that you will also want to reset the tab order of your form controls—that is, the order in which each control becomes active in run mode

Referential integrity

In the context of this example, referential integrity is a control that prohibits users from creating employee records with references to nonexistent departments. (However, it does not affect your ability to create a department with no employees.)

Mechanical sources

Include sensors that may be worn, embedded in devices, or ingested. They gather different types and quantities of data, which can be stored internally or uploaded via wireless connections. As mentioned before sensors can monitor health and livestock and much more. Example, BP issued fitbits for healthier employees to lower costs.

Data Warehouse

Pooling data from separate applications into a large, common body of information. Data is rarely current. Should have these 4 characteristics: 1) be error free 2) be defined uniformly 3) span a longer time horizon than the company's transaction systems 4) allow users to answer complex questions (ex: queries requiring info from several diverse sources.). Primary advantage to data warehouse is to make organizational information available on a corporate-wide basis.

Concurrency Controls (lockout controls)

Prevents users from accessing the same records from the same table at the same time, which would cause an issue.

Extract-Transform-Load (ETL)

Process of extracting data from similar or disparate data sources, transforming the data into a common format, and loading the harmonized and cleansed data into a final target data store.

Presentation

Report is a way of presenting and summarizing a view of data. Visualizations (dataviz) use graphical representations of the data and results from the analysis. Dashboards are type of visualization.

Association to Advance Collegiate Schools of Business (AACSB)

Separately accredits accounting programs. Newest accounting standard requires separately accredited accounting programs include "data analytics" in accounting curriculum.

Volume

Sheer quantity and scale of data. Would take 10 million Blu-Ray disks to record new data created on any given day. Facebook has more users than China's population. Experts estimate sum of all data in 2020 would be 44 zetabytes or 44 trillion gigabytes, in 2015 it was 4.4 zetabytes. Volume of data creates problems for accountants because excel and access are now ill equipped to handle the complex analysis's.

Data Mart

Smaller than data warehouses and usually focus on one application area (ex: marketing). Acts as interface between data warehouse and its end users. Usually designed for a specific subset of users in organizations.

Descriptive analytics

Useful in telling us what happened and are historical in nature. Most common form of analysis (some think it represents more than 80% of business analytics). Examples, descriptive statistics and key performance indicators (KPI's). Continuous monitoring systems utilize descriptive analytics to evaluate transactions.

Predictive analytics

Useful in telling us what might happen in the future are forward-looking. Describes set of tools and techniques for analyzing historical data to predict events that may occur in the future. Example, predictive analytics can be used to identify cellular customers at risk of not renewing contracts. Regression models, decision trees, and machine learning are 3 of the most used methods in predictive analytics.

Prescriptive analytics

Useful in telling us what should be done by recommending course of action based on set of scenarios or inputs. Offers a suggested solution vs. Predictive analytics which identifies trends based on historical data. Guides decision maker to take the "best" course of action.

Decision maker ask accountants

What happened? What might happen? What should we do?

Navigation Bar

You can use this navigation bar for both the input and output tasks

Database Form

a custom-designed screen for entering the data for a new record or for displaying the data for existing records. 3 Major sections: (1) a heading section, which appears at the top of the form; (2) a detail section, which usually occupies the majority of the form and which displays the record information; and (3) a footer section, which appears at the bottom of the form (and which is often not used).

REA Model

approach for designing the databases in accounting systems. REA is an acronym for resources (R), events (E), and agents (A). The basic assumption of this model is that business events affect firm resources and involve agents (i.e., people) who participate in the event. Many describe the REA model as event-driven, meaning that it focuses on the important business events that managers must understand to make decisions.

Operational Sources

collect data regarding business events of an organization and support day-to-day business data requirements. Incorporates stand alone transaction processing systems, enterprise systems, and legacy systems.

Select Query

creates a dynaset of database information based on two types of user-specified criteria: (1) criteria that determine which records to include and (2) criteria that determine which data fields to include from those records.

Entities

data about objects of interest. Include business and economic events plus information about "who" and "what" were involved in those activities

Data analytics software performs 3 core functions

enabling data collection, processing data, and communicating insights.

Transaction controls

ensure that the system performs each transaction accurately and completely.

Veracity

extent to which data can be trusted for insights. In order for data to be and/or feedback value it has to be objective and representative relative to question. Confirmation bias is only getting biased advice. Example, Dell ran into this issue when trying to get feedback from its user community website.

Step 4) Create Entity- Relationship Diagrams

graphical documentation technique called the entity-relationship (E-R) diagram, sometimes called an ERD, to depict entities and their relationships. The diagram consists of three items: rectangles, connecting lines, and cardinality notations. Rectangles represent entities and connecting lines depict relationships. E-R diagrams depict all of the entities and the relationships graphically.

Transaction Processing Systems

handle collection, modification, and retrieval of data associated with business events such as making a sale, billing a customer, processing payroll, or depositing cash. Usually folded into organizations enterprise system implementation.

Proliferation (increase) of data

has potential to fundamentally change practice of accounting.

Social sources

include vast quantities of unstructured data from social media sites, such as Facebook, Twitter, Pinterest, LinkedIn, YouTube, and Instagram. Also shown promise in monitoring trend setters and influencers to more quickly move a product to market. Example, Starbucks monitors Twitter feeds and other social media when releasing new products using sentiment analysis in order to quickly respond by altering price, coffee blend, or other factors.

Data Dictionary

is a critical component of database documentation that describes the data fields in a specific type of database record. Thus, a data dictionary is metadata—that is, data about other data. Example, a data dictionary can help establish an audit trail because it identifies the input sources of data items, the potential computer programs that use or modify particular data items, and the management reports that use the data.

View Controls

limit each user's access to information on a need-to-know basis. For example, a defense contractor will limit its employees' access to many files that contain sensitive information.

Form Controls

objects such as text boxes and labels that appear on a form

Reports

provide custom information to database users. Reports can be simple documents that only display the contents of a table or complex outputs that combine the information from several tables and show selected subsets of database information. If you are using Access to print something to paper, the chances are high that you are using a report for this task.

Big Data

refers to data that are of such great volume that they cannot be captured, stored, and analyzed by traditional databases and existing hardware. Big data create new opportunities for accountants and auditors because analyzing such data can reveal meaningful patterns and produce information from data that were previously unknown.

Subschema

subset of the information in the database. This limited access is a subschema or view. For example, one subschema for our parking database might be the information required by the university registrar—the student's name, number, and outstanding parking tickets. Subschemas are important design elements of a database because they dictate what data each user needs and also because they protect sensitive data from unauthorized access

Data Type

tells Access how to store the data—for example, as a date. Several examples of data types that might be used in a payroll record are: (1) "short text" for an employee's First Name, (2) "long text" for memos, explanations, and other lengthy entries, (3) "number" for an employee's hours worked, (4) "currency" for an employee's pay rate, (5) "date" for an employee's date of hire, (6) "Yes/No" for an employee's qualifications to earn overtime pay, and (7) "attachment" for attaching pictures, word documents, and spreadsheet files.

Primary Key

uniquely identifies each record. After you have defined the data fields in your table, you should designate a primary key—for example, the Employee Number here