3_financial formulas and lookup functions
3.4 Which of the following is NOT allowed when you enter the IRR function in Excel?
A non-negative number as the first entry in the series of values. "Zero" cash flow entries denoted by blank cells rather than hard-coded 0's. A negative number followed by all negatives or 0's and no positives.
3.11 Consider a formula component such as: INDIRECT($C11&$D$2&"!B2:M100"). Why is it necessary to anchor the C11 and D2, but NOT the B2:M100 range?
Because INDIRECT interprets B2:M100 as text and will not change it as you copy and paste the formula around. Because the C11 and D2 are direct cell references, not text, so they will shift around as the formula is copied around. Because Excel interprets anything in double quotes as a hard-coded value.
3.16 Why might the threaded comments in Office 365 not be that useful, in practice?
Because not everyone is using the subscription-based version of Office/Excel. Because in finance, one person tends to "own" a file and make edits and additions to it. Because senior staff often review only the output of models, not their internal workings.
3.9 When using INDEX and MATCH to select assumptions based on a scenario in a financial model, why do you often leave out the Column Number input?
Because the indexed area is typically just 1 column wide.
3.7 Why is it so important to select the correct cell range when using VLOOKUP and HLOOKUP?
Because these functions only move from left to right or top to bottom. Because these functions only search the leftmost column or top row of a range. Because Excel will not alert you about a mismatched range; it will just generate an error message.
3.10 Why will this formula not work correctly? =SHEET(INDIRECT(Sheet_1))
Because you must also include a specific cell reference, not just the spreadsheet name.
3.3 What is the difference between the COUNT and COUNTA functions?
COUNTA includes all non-blank cells, but COUNT includes only non-blank cells with numbers.
3.13 Why won't our version of the MINIF function work in Excel if we fail to enter it as an array function?
If entered as a normal function, it will just evaluate the first entry in the column.
3.6 What is the most appropriate use case for the "Paste Links" command?
Inserting rows of dates into new schedules. Creating summary pages. Re-using existing data on another spreadsheet.
3.14 Which of the following is a DISADVANTAGE of the OFFSET function in Excel?
It does not allow you to trace direct precedents to the function easily. It is a volatile function. It can be very inefficient in large spreadsheets.
3.12 Why can't you use the MATCH function by itself inside of SUMIFS to create a summation range that changes as you copy the formula around?
SUMIFS requires a fixed range of cells for the summation range, or a range that you construct using INDIRECT.
3.5 What is one key limitation of the IPMT and PPMT functions?
The interest rate must be fixed.
3.15 When is the "Select Direct Precedents" (Ctrl + [ ) command most useful?
When a formula with several simple inputs seems fine, but one of the inputs may be wrong.
3.2 When is the IFS function MOST useful as a replacement for the normal IF function?
When you have a complex, nested IF statement with multiple logical tests.
3.18 In which of the following situations would the Solver function in Excel NOT be appropriate?
When you need to find the purchase price that would result in a specific IRR.
3.8 When entering the INDEX function in Excel, can you omit the "Column Number" input at the end of the function?
Yes, but only if the indexed range has 1 column.
3.1 Why do you need double quotes in this formula? =SUMIF(B5:B20,"200000",B5:B20)
You don't need double quotes in this case, so they can be safely deleted
3.17 If you've created a sensitivity table in a financial model, but you get an error message or incorrect numbers in the table, what might be causing these problems?
You're using cells in another spreadsheet for the row and column input. Your top row or left-hand column are linked to cells within the financial model. You have not linked to the output cell in the top-left-hand corner of the table.
