Accounting exam 2

Importing External Data

the process of getting data from an outside source into an Excel worksheet.

Cleanse Data

fix obvious errors and convert to useful format

Transform Data

convert data to anther format

consolidate data

combine data from different sources

Power Query Editor

-use to transform data
-tools to groups rows, filter, replace values, remove duplicates, edit columns

Data in Editor

-A local copy: not original data
-editing data does not change original data source
-local data becomes a table/query

What is a connection (when you click get external data group on the data tab, excel establishes a connection)?

-an identifier and a link for data that originated outside the workbook
-the connection can be edited, refreshed, named or removed.

Web Query:

-a way of importing data into a spreadsheet direct from a web page
-links spreadsheet to data on a webpage (query will update if website updates)

What is HTML?

Hypertext Markup Language, used to display web data

What is XML?

Extensible Markup Language, helps give structure to web page data so it can be searched and processed more efficiently

.csv mean

comma separated

.txt mean

tab delimited

.prn

space delimited

What is a Relational database?

stores data in tables linked by shared fields

What is Primary Key?

unique identifier for each row or record used to link to other tables

What is Foreign key?

Common field of a table linked to a primary key field in another table

What is a Microsoft query used for?

use to import individual data fields into excel instead of importing a complete table

Data Cleansing

fixes obvious data errors and converts data into a useful format

Data verification

validates that data is correct and accurate

Flash Fill

recognizes simple patterns in data as you type and automatically fills in values

Why is using text functions more preferable than using flash fill?

flash fill can make mistakes and its results are not dynamic (won't update if the original data isn't updated)

Lower text function

changes uppercase characters to lowercase

Upper text function

changes all characters in a cell to uppercase

Proper text function

capitalizes first letter of each word in the text string

TRIM text function

removes extra spaces from a string of text

clean

removes nonprintable characters

Nesting

Using a function as an argument within another function.

FIND function

function finds where a specific string of text is located within a larger string

LEFT function

extracts a specific number of characters from a text string beginning at the left of the string

RIGHT function

extracts a specific number of characters from a text string beginning at the right of the string
=RIGHT(text,[num_chars])

Mid Function

extracts characters from left to right based on a starting location and the number of characters
=MID(text,start_num,num_chars)

len function

calculates the length of a string
=LEN(text)

EXACT function

compares two text strings, values, or characters to determine if they are identical

TEXT Function

Can display numeric data as text using special formatting strings to display the text

TEXTJOIN

Combines strings of text, values, or characters

DATEVALUE function

Converts a date in the form of text to a serial number

What-if analysis (also called sensitivity analysis)

use several different values in one or more formulas to explore all the various results

Variable

A value that can be changed to see how the change affects other values

break-even analysis

-a type of what-if analysis
-calculates the break-even point in sales volume or dollars
-can be used to evaluate the impact of changes in sales volume on profits

break-even point

sales level at which revenue equals total costs

Scroll Bar Form Control

- Use a Scroll Bar to quickly enter or change a range of values
- Scrolls through a range of values when you click the scroll arrows or drag the scroll box
- Useful for setting or adjusting a large range of values, or for cases when precision is not important
- Link the scroll bar to the cell containing the value you want to change.

Excel What-if Analysis Tools: Data Tools

-take sets of input values, determine possible result, and display results in a table

One-variable data table

Uses one set of input values, substitutes those values in one or more formulas and displays results for all the formulas

Two-variable data table

Uses two sets of input values, one in a column, the other in a row, and displays results for only one formula

Goal Seek

Uses iteration to find the values needed to achieve a goal or objective

Backsolving

knowing the results and determining the value needed to reach those results

Scenario

-A saved set of values in a ws
-user can vary numbers and see potential results
-often used to review best and worst possibilities
-ws can have up to 32 scenarios

Scenario summary report

A generated worksheet that displays values for changing and result cells for each scenario in a workbook

Solver

data analysis tool that optimizes a problem by manipulating the values of selected variables but bound by constraints

Simplex LP

Used when objective function and all constraints are linear

GRG Nonlinear method

-Default method
-used for more complex models
-used when the objective or at least one of the constraints is nonlinear and smooth

Evolutionary

-most complex model
-used when worksheet model is nonlinear and not smooth
-"Nonsmooth"-line has sharp bends and no slope

Median

the middle value of a set of values

Mode

the value that appears most often in a set of values

MODE.SNGL

Returns only one mode value.
Returns the first mode value that it finds.
If there are no duplicate values, returns the error #N/A.

MODE.MULTI

will return multiple values if the data has more than one mode. an array function*

RANK.EQ and RANK.AVG Functions

-calculate where a particular value appears in the data
-when duplicated values exist in the range, RANK.EQ shows the lower rank
-RANK.AVG calculates duplicate values with and average rank

RAND Function

return a random number greater than or equal to 0 or less than 1

RANDBETWEEN function

Returns a random number between the numbers you specify

Measuring dispersion

-tells us how spread out (or variable) a set of data is
-measures: range, variance, standard deviation

Variance

measures how far the data varies from the mean

standard deviation

calculates average spread of data set from the mean

Histogram Chart

-like a column or bar chart except each column represents a range of values instead of a specific data series
-each column is called a bin
-the range of values in a bin is called the class interval

Pareto Chart

A type of histogram chart where the columns are sorted from largest to smallest with a line chart on a secondary axis showing the cumulative total percentage.

Frequency Function

an array function that calculates how often values occur in a bin

box and whisker chart

another way to represent the range of values for each category based on dividing the values into quartiles

waterfall chart

- Shows a running total as values are added or subtracted
- Useful for understanding how a value is affected by a series of positive and negative values
- Columns are color coded to easily differentiate between positive and negative numbers
- The initial and the final value columns often start on the horizontal axis, while the intermediate values are floating columns

Analysis ToolPak

-An Excel add-in with statistical and engineering functions
-Generates descriptive statistics

moving average

a series of averages that is recalculated
through time based on a window of observations.

Business Intelligence (BI)

Software applications used to analyze an organizations data to improve: decision making, cut costs, identify new opportunities

BI tools

-gather, process, analyze and visualize large volumes of past, current, and future data
-create interactive reports

Digital Dashboards

-deliver BI in graphical form
-some features include: interactive tools, integration of different types of data from different sources, user interface that is easy to navigate and understand

Key performance indicators (KPIs)

Quantifiable measures of performance used to gauge progress toward strategic objectives or agreed standards of performance.

Data Modeling

The process of creating a small scale representation of data and the relationships among pieces of data

data model

an abstract model that organizes elements of data and standardizes how they relate to one another and to the properties of real-world entities

Relational database

-collection of tables that can be joined through a common field
-data can be accessed or reassembled without changing the tables

Get & Transform (Power Query)

Use to extract, connect, clean and transform large amounts of data. Can modify data types and extract PivotTables.

Power Pivot

use to import and compare large amounts of data from multiple sources to analyze relationships between tables of data

Power View

an interactive visualization tool used to provide a drag-and-drop interface for rapid model building. can connect to different data models within the same workbook

3D Maps

-Can plot and visualize geographic or temporal data on a three-dimensional map. -Can build cinematic time tours or animations.

data analysis expressions (DAX)

A library of functions and operators that can be combined to build formulas and expressions in power pivot in excel data models (focus on performing calculations on columns-not cells

KPI

-a base value and status are evaluated against a target value

Status threshold

-defined by the range between a high value and a low value
-displays with a graphic to help users easily determine the status of the base value compared to the target value.

base value

a calculated field that resolves to a value as part of a KPI.

target value

a calculated field that results in a value or an absolute value

Positive KPI

-the greater the value the better the KPI
-Example: Company profit

Negative KPI

-The greater the value, the worse the KPI
-Example: Employee turnover rate

Bidirectional KPI

-Value becomes worse the farther it deviates from target value in either direction
-Example : inventory on hand must not be too high or too low

Dynamic Array formulas

returns arrays of variable size which can spill into adjacent cells

spilling

when formulas return values into neighboring cells

unique function

returns a unique list of values from an array and updates dynamically

sort function

customizes the sorting of a range or array

TRANSPOSE function

converts a vertical array of cells to a horizontal array or vice versa

Data Types

connections to an online data source that return information related to the selected data

algorithms

a sequence of steps or set of instructions for doing something

information processing

transforming data to produce new information

features of an algorithm

consists of a finite number of instructions,
each individual instruction is well defined, describes a process that eventually halts after arriving at a solution to a problem

Hardware

physical devices required to execute algorithms

software

set of the algorithms, represented as programs in particular programming languages

CPU (processor)

responsible for processing data