Bitm Flashcards
subtotaling data
a summary calculation, such as a total or average, of values for a category determined by your sorting, sort first
can add a second level of subtotal rows
what does the Subtotal command create
an outline, a hierarchical structure that groups related detailed data in rows to summarize.
could collapse a category to show only the subtotals
Grouping Data
process of joining rows or columns of related data together into a single entity so that groups can be collapsed or expanded for data analysis.
data mining and its 6 steps
Analyzing large volumes of data to discover patterns and identify trends in the data
-define the problem(goals)
-identify required data
-prepare (data) and pre-process
-model the data
-train and test
-verify and deploy
what are the different pivot table options
-Create pivot table
-adjust field settings
-format pivot table
-refresh a pivot table
-create a pivot chart
Pivot table
DYNAMIC and INTERACTIVE table that uses calculations to consolidate and summarize data from a data source into a separate table
analyze without altering
fields
named rows or columns that describe the data present in those rows or columns.
Value field settings
used to select different functions and formats for numeric fields
sum is default for the numeric fields
count is for the text fields
GETPIVOTDATA function
used to obtain the summary data visible in a PivotTable
2 required arguments: data_field then pivot_table
are pivot tables automatically updated after new changes
NO, must click refresh
slicer
a small window containing one button for each unique item in a field so that you can FILTER the PivotTable quickly
color codes selected fields
calculated field
user-defined field that obtains its value based on performing calculations in other fields in a PivotTable
within a discipline percentages total 100%
banding
format odd and even columns or rows differently
data model
collection of related tables that contain structured data used to create a database. (relationship)
range names and their rules
a word or string of characters assigned to one or more cells.
no spaces, no special symbols, cant start with a number
To insert a list of range names
press F3 to display the Paste Name dialog box
data table
organizes the results of several what-if analyses within a single table
What-if analysis
allows you to see how changing variables impacts calculated results
one-variable data table
dynamic range containing different values for one variable to compare how those values affect one or more calculated results.
two-variable data table.
to examine the interaction between two variables
can focus on only one result
Data Table dialog box
used to enter the information.
matrix
rectangular array of numbers
Goal Seek
what-if analysis tool that identifies the necessary input value to obtain a desired goal by changing ONE variable. (backsolve)
not always possible to back solve
Scenarios
saved set of values in a worksheet allowing you to vary numbers and see potential results
-best case, worst case, and most likely
Original scenario
the starting point, the baseline against which you will create and compare other scenarios/possibilities
Scenario summary
generate a data matrix that highlights the values selected for change for each scenario, as well as the cell(s) impacted by the changed cells.
not dynamically updated, re-run
Solver and optimization models
is an add in that searches for the best or optimum solution to a problem. (OPTIMAL)
-Extends the function of Goal Seek for more complex problems
Optimization models find the highest, lowest, or exact value for one result
adding an add in
file, options, add ins, manage, go when done
constraints
specify the binding and non binding restrictions
Solver reports
answer report: displays the original values and solver results
Sensitivity Report: how sensitive results are to changes in the variable cells
Limits Report
Displays impact on objective as variables are maximized or minimized
P&L statement
profit and loss, shows how much money a business makes or loses over a specified period of time
Switch function
logical function that evaluates an expression, compares it with a list of values, and returns the first corresponding result
IFS function
evaluates multiple conditions and returns a result that corresponds to the first true condition
AND function
true if all conditions are true
NESTED FUNCTION
a function that is embedded or “nested” within an argument of another function.
OR function
true , only one has to be true
SUMIF function
calculates the total of values in a range that meet a specified condition
=SUMIF(range,criteria,sum_range)
1 condition
COUNTIF function
counts the number of cells in a range that meet a specified condition
=COUNTIF(range, criteria)
1 condition
AVERAGEIF function
calculates the average of all cells in a range that meet a specified condition
=AVERAGEIF(range,criteria,average_range)
1 condition
COUNTIFS function
counts the number of cells in arange that meet multiple criteria (conditions)
=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2…)
SUMIFS function
calculates the total of values in arange that meet multiple criteria (conditions
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2, criteria2…)
AVERAGEIFS function
calculates the average of all cells in a range that meet multiple criteria (conditions)
=AVERAGEIFS(average_range,criteria_range1,criteria1, criteria_range2,criteria2…)
MAXIFS function
returns the highest value in a rangethat meets multiple criteria
=MAXIFS(max_range,criteria_range1,criteria1,criteria_range2, criteria2…)
MINIFS function
returns the lowest value in a rangethat meets multiple criteria
=MINIFS(min_range,criteria_range1,criteria1,criteria_range2, criteria2…)
Map chart
for comparing values across those geographical regions
dataset must contain countries, states, counties, or postal codes
aggregated data
PV function
=PV(rate,nper,pmt,[fv],[type]
if no pmt, must have fv
FV function
=FV(rate,nper,pmt,[pv],[type]
if no pmt, must have pv
fixed
NPV function
net PV of an investment, given a fixed rate and future payments or income that may be identical or different
=NPV(rate,value1,[value2],…)
variable payments
Amortization
the process of decreasing or accounting for an amount; over a period.
-Loan payments
-Assets valuation
is the analysis tool pak a add in
yes
Creating a Loan Amortization Table
payment function=PMT(rate,nper,pv)
periodic interest *&/12=IPMT(rate,per,nper,pv)
principal payment =PPMT(rate,per,nper,pv)
cumulative interest
=CUMIPMT(rate,nper,pv,start_period,end_period,type)
cumulative principal
=CUMPRINC(rate,nper,pv,start_period,end_period,type)
Depreciation
reduction in the value of an asset with the passage of time, due to wear and tear and/or obsolescence.
IBITDA
Income before interest taxes depreciation and amortization
Statistics
concepts, rules, and procedures that help us to:
organizenumerical information in the form of tables, graphs, and charts;
understandstatistical techniques underlying decisions that affect our lives and well-being; and
makeinformed decisions.
Variable
property of an object or event that can take on different values.
Measures of Center (Central Tendency)
Plotting data in a frequency distribution shows the general shape of the distribution and gives a general sense of how the numbers are bunched.
mode (most common score), median(middle point), and mean(average)
Measures of Spread (Variability)
provides information about the degree to which individual scores are clustered about or deviate from the average value in a distribution.
Range- difference between the highest and lowest score in a distribution.
Variance- a number that shows how much the values vary from the average.
Standard deviation- positive square rootof the variance, average distance” from the mean
what is the difference between STDEV.S and STDEV.P
.S is standard deviation of a population
.P is standard deviation of a sample
same goes for the variance functions
Descriptive statistics
-Fast access to Mean, Median, Mode, Variance, and higher order distribution stats
Histogram: Counts values that fall within bins
Population—dataset containing all the data to evaluate
Sample—smaller, more manageable portion of the population
CORREL function
determines the strength of a relationship between two variables
=CORREL(array1,array2)
Rank.EQ and Rank.AVG
.EQ(equivalent) function—identifies a value’s rank within a list of values, discarding the next rank when tie values exist
=RANK.EQ(number,ref,[order])
.AVG- rank of a value but assigns an average rank when identical values exist
=RANK.AVG(number,ref,[order])
example tie for 8th = 8.5 for both winners of the rank
PERCENTRANK.INC AND PERCENTRANK.EXC
.INC- displays a value’s rank as a percentile of the range of data in the dataset
=PERCENTRANK.INC(array,x,[significance])
returns a value’s rank as a percent
=PERCENTRANK.EXC(array,x,[significance])
excludes max and min (0 and 1)
QUARTILE.INC and QUARTILE.EXC
Quartile: value used to divide a range of numbers into four equal groups
INC. identifies the value at a specific quartile for a dataset, including quartile 0 and quartile 4
EXC. returns the value at a specific quartile, excluding quartiles 0 and 4
=QUARTILE.EXC(array,quart)
A N O V A (analysis of variance)
to see if samples represent the same population)
df(degrees of freedom)
The number of data points in the sample – 1 (N – 1)
histogram
visual display of tabulated frequencies
needs bins into which to sort the data, If you don’t provide bins, Excel will
has no gaps between bars
Trendline
a visualization that shows patterns in data.
draws a line that falls between 2 data points
FORECAST.LINEAR function (for trends
calculates, or predicts, a future value along a linear trend by using existing values
=FORECAST.LINEAR(x, known_ys, known_xs)
FREQUENCY function
determines the frequency distribution of a dataset.
percentile functions
the kth value within a list of values
