Bitm

Question 1

subtotaling data

Answer 1

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

Question 2

what does the Subtotal command create

Answer 2

an outline, a hierarchical structure that groups related detailed data in rows to summarize.

could collapse a category to show only the subtotals

Question 3

Grouping Data

Answer 3

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.

Question 4

data mining and its 6 steps

Answer 4

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

Question 5

what are the different pivot table options

Answer 5

-Create pivot table
-adjust field settings
-format pivot table
-refresh a pivot table
-create a pivot chart

Question 6

Pivot table

Answer 6

DYNAMIC and INTERACTIVE table that uses calculations to consolidate and summarize data from a data source into a separate table

analyze without altering

Question 7

fields

Answer 7

named rows or columns that describe the data present in those rows or columns.

Question 8

Value field settings

Answer 8

used to select different functions and formats for numeric fields

sum is default for the numeric fields

count is for the text fields

Question 9

GETPIVOTDATA function

Answer 9

used to obtain the summary data visible in a PivotTable

2 required arguments: data_field then pivot_table

Question 10

are pivot tables automatically updated after new changes

Answer 10

NO, must click refresh

Question 11

slicer

Answer 11

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

Question 12

calculated field

Answer 12

user-defined field that obtains its value based on performing calculations in other fields in a PivotTable

within a discipline percentages total 100%

Question 13

banding

Answer 13

format odd and even columns or rows differently

Question 14

data model

Answer 14

collection of related tables that contain structured data used to create a database. (relationship)

Question 15

range names and their rules

Answer 15

a word or string of characters assigned to one or more cells.

no spaces, no special symbols, cant start with a number

Question 16

To insert a list of range names

Answer 16

press F3 to display the Paste Name dialog box

Question 17

data table

Answer 17

organizes the results of several what-if analyses within a single table

Question 18

What-if analysis

Answer 18

allows you to see how changing variables impacts calculated results

Question 19

one-variable data table

Answer 19

dynamic range containing different values for one variable to compare how those values affect one or more calculated results.

Question 20

two-variable data table.

Answer 20

to examine the interaction between two variables

can focus on only one result

Question 21

Data Table dialog box

Answer 21

used to enter the information.

Question 22

matrix

Answer 22

rectangular array of numbers

Question 23

Goal Seek

Answer 23

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

Question 24

Scenarios

Answer 24

saved set of values in a worksheet allowing you to vary numbers and see potential results

-best case, worst case, and most likely

Question 25

Original scenario

Answer 25

the starting point, the baseline against which you will create and compare other scenarios/possibilities

Question 26

Scenario summary

Answer 26

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

Question 27

Solver and optimization models

Answer 27

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

Question 28

adding an add in

Answer 28

file, options, add ins, manage, go when done

Question 29

constraints

Answer 29

specify the binding and non binding restrictions

Question 30

Solver reports

Answer 30

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

Question 31

P&L statement

Answer 31

profit and loss, shows how much money a business makes or loses over a specified period of time

Question 32

Switch function

Answer 32

logical function that evaluates an expression, compares it with a list of values, and returns the first corresponding result

Question 33

IFS function

Answer 33

evaluates multiple conditions and returns a result that corresponds to the first true condition

Question 34

AND function

Answer 34

true if all conditions are true

Question 34

NESTED FUNCTION

Answer 34

a function that is embedded or “nested” within an argument of another function.

Question 35

OR function

Answer 35

true , only one has to be true

Question 36

SUMIF function

Answer 36

calculates the total of values in a range that meet a specified condition =SUMIF(range,criteria,sum_range) 1 condition

Question 36

COUNTIF function

Answer 36

counts the number of cells in a range that meet a specified condition =COUNTIF(range, criteria) 1 condition

Question 37

AVERAGEIF function

Answer 37

calculates the average of all cells in a range that meet a specified condition =AVERAGEIF(range,criteria,average_range) 1 condition

Question 38

COUNTIFS function

Answer 38

counts the number of cells in arange that meet multiple criteria (conditions) =COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2...)

Question 39

SUMIFS function

Answer 39

calculates the total of values in arange that meet multiple criteria (conditions =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2, criteria2...)

Question 40

AVERAGEIFS function

Answer 40

calculates the average of all cells in a range that meet multiple criteria (conditions) =AVERAGEIFS(average_range,criteria_range1,criteria1, criteria_range2,criteria2...)

Question 41

MAXIFS function

Answer 41

returns the highest value in a rangethat meets multiple criteria =MAXIFS(max_range,criteria_range1,criteria1,criteria_range2, criteria2...)

Question 42

MINIFS function

Answer 42

returns the lowest value in a rangethat meets multiple criteria =MINIFS(min_range,criteria_range1,criteria1,criteria_range2, criteria2...)

Question 43

Map chart

Answer 43

for comparing values across those geographical regions dataset must contain countries, states, counties, or postal codes aggregated data

Question 44

PV function

Answer 44

=PV(rate,nper,pmt,[fv],[type] if no pmt, must have fv

Question 45

FV function

Answer 45

=FV(rate,nper,pmt,[pv],[type] if no pmt, must have pv fixed

Question 46

NPV function

Answer 46

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

Question 47

Amortization

Answer 47

the process of decreasing or accounting for an amount; over a period. -Loan payments -Assets valuation

Question 48

is the analysis tool pak a add in

Answer 48

yes

Question 49

Creating a Loan Amortization Table

Answer 49

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)

Question 50

Depreciation

Answer 50

reduction in the value of an asset with the passage of time, due to wear and tear and/or obsolescence.

Question 51

IBITDA

Answer 51

Income before interest taxes depreciation and amortization

Question 52

Statistics

Answer 52

concepts, rules, and procedures that help us to: organize numerical information in the form of tables, graphs, and charts; understand statistical techniques underlying decisions that affect our lives and well-being; and make informed decisions.

Question 53

Variable 

Answer 53

property of an object or event that can take on different values. 

Question 54

Measures of Center (Central Tendency)

Answer 54

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)

Question 55

Measures of Spread (Variability) 

Answer 55

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 root of the variance, average distance" from the mean 

Question 56

what is the difference between STDEV.S and STDEV.P

Answer 56

.S is standard deviation of a population .P is standard deviation of a sample same goes for the variance functions

Question 57

Descriptive statistics

Answer 57

-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

Question 58

CORREL function

Answer 58

determines the strength of a relationship between two variables =CORREL(array1,array2)

Question 59

Rank.EQ and Rank.AVG

Answer 59

.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

Question 60

PERCENTRANK.INC AND PERCENTRANK.EXC

Answer 60

.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)

Question 61

QUARTILE.INC and QUARTILE.EXC

Answer 61

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)

Question 62

A N O V A (analysis of variance)

Answer 62

to see if samples represent the same population)

Question 63

df(degrees of freedom)

Answer 63

The number of data points in the sample – 1 (N – 1)

Question 64

histogram

Answer 64

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

Question 65

Trendline

Answer 65

a visualization that shows patterns in data. draws a line that falls between 2 data points

Question 66

FORECAST.LINEAR function (for trends

Answer 66

calculates, or predicts, a future value along a linear trend by using existing values =FORECAST.LINEAR(x, known_ys, known_xs)

Question 67

FREQUENCY function

Answer 67

determines the frequency distribution of a dataset.

Question 68

percentile functions

Answer 68

the kth value within a list of values