Exam study

Question 1

Simple IF statements

Answer 1

IF (x,y,z)…
- x: criteria
- y: output if true
- z: output if false

Question 2

Nested IF statements (2016+)

Answer 2

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3])

Question 3

If error

Answer 3

=IFERROR(value, value_if_error): returns a value you specify if a formula evaluates to an error. If the formula does not result in an error, IFERROR returns the result of the formula.

Question 4

EOMONTH vs. EDATE

Answer 4

-EOMONTH(start_date, months): outputs the last day starting from a specified month
-EDATE(start_date, months): outputs a specific day starting from specified month

Question 5

YEARFRAC

Answer 5

Returns proportion of year between two given dates, start_date and end_date.

Question 6

HLOOKUP

Answer 6

• H means horizontal, searches HORIZONTALLY for look-up value
  =HLOOKUP(lookupvalue, table range, row #) : searches for lookup value across, then returns the # row you specify

Question 7

VLOOKUP

Answer 7

• V means vertical, searches UP AND DOWN for look-up value
  =VLOOKUP(lookupvalue, table range, column #): searches for lookup value up and down, then returns the # column you specify

Question 8

OFFSET

Answer 8

Give a reference point, # of columns to go across, # of rows to go down

Question 9

MATCH

Answer 9

Combine with HLOOKUP and VLOOKUP and OFFSET to change static references to non-static

Question 10

COLUMN/ROW (3 functions)

Answer 10

-ROW()/COLUMN(): outputs row/column # of cell you’re typing in

-ROW(reference)/COLUMN(reference) :row/column # of selected cell

-ROW(array)/COLUMN(array): # of rows/columns in an array

Question 11

Need to do data tables and XLOOKUP

Question 12

SUMPRODUCT

Answer 12

=SUMPRODUCT(array1, array2…): multiplies corresponding components in two or more arrays, and returns the sum of these products.

Question 13

TRUE/FALSE

Answer 13

-TRUE: not a number
-TRUE * 1.0: a number (1 * 1 = 1)
- TRUE * TRUE: a number (1 * 1 = 1)

Question 14

SUMPRODUCT + TRUE FALSE

Answer 14

Only will sum up the columns that match the criteria (ex: SUMPRODUCT(A1:A8=C8,

Question 15

SUMIF/SUMIFS

Answer 15

SUMIF(range, criteria, sum_range)
SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2…)
- Allows for multiple criteria

Question 16

MIN/MAX

Answer 16

=MIN(number1, number2), =MAX(number1, number2): returns smallest or largest value in a range of values

Question 17

COUNT, COUNTA, COUNTIF/COUNTIFS

Answer 17

COUNT(value1, value2): counts number of cells that contain #s in the list

COUNTA: same as count except #s and text are ommitted

COUNTIFS: allows for multiple criteria – similar to the SUMIFS function.

Question 18

Present value

Answer 18

=PV(rate, nper, pmt, fv, type)
-Rate: interest rate per period
-Nper: total # of payment periods
-Pmt = payment made during each period (it cannot change)
-Fv = future value
-Type = indicates when payments are due:
0 or omitted = end of the period
1 = beginning of the period

Question 19

Future value

Answer 19

=FV(rate, nper, pmt, fv, type)

Question 20

What is present value?

Answer 20

The amount you would need to invest today, at a given interest rate/fixed payment, to end up with a certain amount in the future.

Question 21

What is future value?

Answer 21

The value of a certain asset at a specified date in the future based on the assumed rate of growth.

Question 22

NPV

Answer 22

=NPV(rate, value1, value2)
The difference between the present value of cash inflows and the present value of cash outflows over a period of time (assumes UNFIXED payments, assumes payments occur at regular intervals at end of each period)

Question 23

XNPV

Answer 23

=XNPV(rate, values, dates)
Set of cash flows that don’t occur at equal time intervals (need to include the dates to indicate when cash is going out/coming in)

Question 24

IRR

Answer 24

Rate for which NPV of a series of cash flows equals 0 (for regular intervals)

Question 25

XIRR

Answer 25

Similar to IRR, used for irregular cash flow intervals