ACCT 4710: Analytics, Excel, Relational Databases, and Statistical Programming in R

Comprehensive vocabulary flashcards covering data analytics processes, Excel functionality, relational database schemas, SQL syntax, ERP systems, R statistical programming, and predictive machine learning models.

Data Analytics Process

A structured five-step cycle used to extract value from data, consisting of: 1) Ask a question, 2) Acquire data, 3) Transform data, 4) Analyze data, and 5) Present findings.

Workbook

An entire spreadsheet document file in Microsoft Excel, typically saved with the file extension .xlsx or .xls.

Worksheet

A single page or tab within an Excel workbook where data is organized into rows and columns.

Cell Range

A contiguous block of cells in a worksheet identified by the top-left cell reference and bottom-right cell reference, separated by a colon (e.g., $A1:D23$).

Relative Cell Reference

A cell reference in an Excel formula (e.g., $B4$) that automatically adjusts its row and column coordinates when the formula is copied or filled across other cells.

Absolute Cell Reference

A cell reference containing dollar signs (e.g., $B$4) that locks the column and/or row so that the reference remains fixed to a specific cell when copied.

VLOOKUP

An Excel function that searches vertically down the leftmost column of a specified table array for a lookup value and retrieves a corresponding value from a designated column in the same row.

Completeness

A data quality dimension that evaluates whether any essential information is missing or if null values exist within the dataset.

Validity

A data quality dimension assessing whether values strictly conform to established business rules, allowable value lists, and required syntax or formats.

Timeliness

A data quality dimension that measures whether the frequency of data updates is sufficient to satisfy business operational requirements.

Accuracy

A data quality dimension evaluating whether recorded data correctly represents true real-world values or matches the authoritative source content.

Integrity

A data quality dimension verifying that logical links and relational references (such as primary key–foreign key pairs) between related tables remain intact and unbroken.

Consistency

A data quality dimension that evaluates whether data values agree across disparate internal systems and whether duplicate records exist.

Pivot Table

An interactive data summarization tool in Excel that aggregates source records across custom row and column categories using metrics such as sum, count, average, or min/max.

Analysis ToolPak

An Excel add-in providing advanced statistical and engineering analysis capabilities, such as generating descriptive statistics, histograms, correlation matrices, and linear regression models.

Conditional Formatting

An Excel tool that dynamically formats cell backgrounds, fonts, or icons according to cell values or rules, commonly used for Red/Amber/Green (RAG) performance reports.

Freeze Panes

An Excel viewing utility that keeps designated rows and/or columns stationary on screen while the rest of the worksheet is scrolled.

Relational Database Management System (RDBMS)

Software that enables users to create, modify, administer, and query relational databases by storing data in structured tables linked by keys (e.g., SQLite, PostgreSQL, Oracle).

Primary Key

A column or combination of columns in a database table whose values uniquely identify each record, ensuring no duplicate rows exist.

Foreign Key

A field in a child database table that points to the primary key in a parent table, establishing and enforcing a referential link between them.

Entity Relationship Diagram (ERD)

A graphical diagram that maps the structure of a database, displaying entities (tables), attributes (columns), primary keys, and relationships connecting them.

Enterprise Resource Planning (ERP)

An integrated enterprise software platform that unifies core business processes across an organization, including finance, accounting, supply chain, manufacturing, and HR.

General Ledger

The central accounting master record in an ERP system into which automated subledger journal entries and manual journal entries feed.

Manual Journal Entry

An accounting journal entry posted directly to the general ledger by a user rather than generated automatically by an ERP operational subledger, posing a higher inherent control risk.

Data Definition Language (DDL)

The subset of SQL statements (such as CREATE TABLE) used to build, alter, or drop database objects and table schemas.

Data Manipulation Language (DML)

The subset of SQL statements (such as SELECT, INSERT, UPDATE, and DELETE) used to retrieve, insert, and modify stored data records.

INNER JOIN

A SQL join operation that returns only records that have matching key values in both the left and right participating tables.

LEFT JOIN

A SQL join operation that returns all records from the left table and the matched records from the right table, filling in NULL for right-side fields when no match occurs.

HAVING Clause

A SQL clause used to filter aggregated group records produced by a GROUP BY clause, based on aggregate conditions (e.g., HAVING COUNT(*) > 50).

Data Science Venn Diagram

Drew Conway's conceptual model defining data science at the intersection of computer science, math and statistics knowledge, and substantive domain knowledge.

Vector (R)

The fundamental 1-dimensional data structure in R that holds a sequence of data elements that must all belong to the identical data class.

Data Frame (R)

A 2-dimensional tabular structure in R representing rows and columns, where each column is an equal-length vector that can store a different data type.

str() (R function)

A base R diagnostic function that compactly outputs the internal structure, dimensions, column names, classes, and first few values of an R object.

sqldf (R package)

An R package that enables users to manipulate R data frames using standard SQLite SQL queries within an in-memory database.

set.seed() (R function)

An R function that sets the starting state for the random number generator, guaranteeing exact reproducibility for random sampling and modeling tasks.

merge() (R function)

A base R function used to join two data frames based on common key columns, allowing inner, left, right, or full outer joins via the all.x and all.y arguments.

Predictive Analytics

The systematic computational analysis of historical data using mathematical and statistical models to predict unknown future values or states of the world.

Dependent Variable

The target or outcome variable in a predictive model (denoted as $y$, also known as the predicted, response, or explained variable).

Independent Variable

A predictor, control, or explanatory variable (denoted as $X$) used in a statistical model to explain or estimate changes in the dependent variable.

Supervised Learning

A branch of machine learning where algorithms are trained on historical data paired with known target outputs ($y$) to predict continuous values (regression) or categorical labels (classification).

Unsupervised Learning

A branch of machine learning where algorithms discover intrinsic patterns, cluster groupings, or anomalies in data without relying on predefined outcome labels or dependent variables.

Artificial Intelligence (AI)

A branch of computer science focused on developing computer systems capable of executing complex tasks that typically require human intelligence.

K-Means Clustering

An unsupervised clustering algorithm that groups observations into $k$ predefined clusters by iteratively calculating centroid coordinates and assigning data points to the closest centroid.

Ordinary Least Squares (OLS)

A linear regression modeling technique that determines the line of best fit by minimizing the sum of squared residuals between observed values and model-predicted values.

Residual

The difference between the actual observed value of the dependent variable and the value predicted by a regression line, defined as $\hat{u}_i = y_i - \hat{y}_i$.

Adjusted R-squared

A goodness-of-fit statistic for linear models that measures the proportion of variance explained by predictors while penalizing the model for adding non-informative variables.

Dummy Variable

A binary indicator variable coded as 0 or 1 in a regression equation to represent categorical status; its coefficient reflects the average difference in $y$ between categories.

Logistic Regression

A generalized linear modeling method (glm(family = "binomial")) that models the probability of a binary outcome ($0$ or $1$), producing an S-shaped curve constrained strictly between 0 and 1.

Confusion Matrix

A tabular evaluation layout for classification models that compares actual outcomes against model predictions, categorizing results into True Positives, False Positives, True Negatives, and False Negatives.

Overfitting

A modeling flaw where an algorithm models the training data and random noise too closely, failing to generalize accurately to unseen test data.

Train-Test Split

A model validation procedure where data is partitioned (commonly 80% training and 20% testing) to fit the model on one portion and independently assess predictive accuracy on the other.

K-Nearest Neighbors (K-NN) Classification

A supervised machine learning classification method that assigns a new observation to the majority class among its closest data points in feature space.

Interaction Term

A derived regression variable formed by the product of two predictors (e.g., $x \times z$) to capture how the effect of one variable changes depending on the level of the other.

Short Position

A trading strategy in which an investor borrows shares to sell immediately at current market prices, anticipating repurchasing them at a lower price in the future to return to the lender.