EXCEL CRASH COURSE EXAM FROM WALL STREET PREP-WALL STREET PREP – QUESTIONS AND ANSWERS |
VERIFIED AND WELL DETAILED ANSWERS | PLUS RATIONALES | DOWNLOAD AND PASS | LATEST EXAM UPDATE
2026/2027
Section One: Questions 1-50
1. What is the primary purpose of using absolute cell references in a financial model?
A. To prevent other users from editing the cell's contents.
B. To ensure a cell reference remains constant when copied to other locations.
C. To automatically adjust the reference based on the destination cell's position.
D. To highlight the cell for presentation purposes.
Correct Answer: B. To ensure a cell reference remains constant when copied to other locations.
Rationale: Absolute cell references (e.g., $A$1) are a cornerstone of robust financial modeling. They lock a
reference to a specific cell, ensuring that when a formula is copied across rows or columns, the reference point does
not change, which is critical for applying a single constant (like a tax rate or growth assumption) across many
calculations. Option A describes worksheet protection, C describes relative references, and D is a formatting
function, none of which align with the primary utility of absolute referencing.**
2. In a professional context, which Excel function is most appropriate for determining the internal rate of
return for a series of irregular cash flows?
,A. IRR
B. MIRR
C. XIRR
D. RATE
Correct Answer: C. XIRR
Rationale: XIRR is specifically designed to calculate the internal rate of return for a schedule of cash flows that are
not necessarily periodic. This is the most common real-world scenario in project finance and investment analysis.
While IRR (A) is for periodic cash flows, MIRR (B) modifies the rate, and RATE (D) is for annuities with constant
periodic payments, XIRR is the professional standard for irregular cash flow analysis.**
3. When auditing a complex spreadsheet, you encounter a formula that includes the function =VLOOKUP(A2,
D:F, 3, FALSE). What does the '3' in this formula represent?
A. The row number in the lookup table to return a value from.
B. The column index number in the table array from which to retrieve the value.
C. The number of the worksheet containing the lookup table.
D. The number of the workbook where the table array is located.
Correct Answer: B. The column index number in the table array from which to retrieve the value.
Rationale: In the VLOOKUP function, the third argument is the column index number, which specifies which
column in the table array contains the return value. In this case, with a table array of D:F, '3' refers to column F.
Option A is incorrect as it describes a row number, and C and D are not arguments within the VLOOKUP syntax.**
,4. A junior analyst creates a model where the sum of quarterly values does not equal the annual total due to
rounding. Which of the following practices is the most professional and recommended way to handle this
discrepancy?
A. Manually adjust the quarterly figures to force them to sum to the annual total.
B. Use the ROUND function on individual quarterly calculations to ensure they sum correctly.
C. Apply a plug figure to the annual total to absorb the rounding difference.
D. Build the model so that annual totals are calculated by summing the rounded quarterly values.
Correct Answer: D. Build the model so that annual totals are calculated by summing the rounded quarterly
values.
Rationale: The most transparent and reliable methodology is to calculate the annual total by summing the
rounded quarterly figures. This ensures the model is arithmetically consistent and avoids hidden "plugs" or manual
adjustments, which are considered poor practice and increase the risk of errors. Option A involves manual
manipulation, B is inefficient as it doesn't solve the sum issue, and C introduces an arbitrary, non-transparent
figure.**
5. Which of the following is the most critical best practice when constructing a financial model for a merger
and acquisition (M&A) transaction?
A. Using a high discount rate to be conservative.
B. Ensuring all cells are formatted with two decimal places.
C. Clearly separating historical data, assumptions, and calculated outputs on different worksheets or distinct
, sections.
D. Maximizing the use of nested IF functions to reduce the number of cells.
Correct Answer: C. Clearly separating historical data, assumptions, and calculated outputs on different
worksheets or distinct sections.
Rationale: A clean and logical model structure is paramount in M&A modeling. Separating inputs (assumptions),
data (history), and calculations (outputs) enhances transparency, reduces errors, and makes the model significantly
easier to audit, review, and update. Option A is a flawed strategic choice, B is a presentation issue, and D leads to
complex, error-prone formulas.**
6. What is the primary advantage of using INDEX and MATCH together as a lookup method over VLOOKUP?
A. INDEX and MATCH is faster to type than VLOOKUP.
B. INDEX and MATCH can look up values to the left of the lookup column.
C. INDEX and MATCH are the only functions that can return text values.
D. INDEX and MATCH do not require the lookup table to be sorted.
Correct Answer: B. INDEX and MATCH can look up values to the left of the lookup column.
Rationale: A key limitation of VLOOKUP is that it can only search in the first column of the table array and return
values to the right. INDEX and MATCH, when combined, offer far greater flexibility, including the ability to perform
leftward lookups, which is a common requirement in complex data sets. While both functions can handle text (C)
and VLOOKUP can also work with unsorted data when using FALSE (D), the left-lookup capability is the primary
differentiator.**
VERIFIED AND WELL DETAILED ANSWERS | PLUS RATIONALES | DOWNLOAD AND PASS | LATEST EXAM UPDATE
2026/2027
Section One: Questions 1-50
1. What is the primary purpose of using absolute cell references in a financial model?
A. To prevent other users from editing the cell's contents.
B. To ensure a cell reference remains constant when copied to other locations.
C. To automatically adjust the reference based on the destination cell's position.
D. To highlight the cell for presentation purposes.
Correct Answer: B. To ensure a cell reference remains constant when copied to other locations.
Rationale: Absolute cell references (e.g., $A$1) are a cornerstone of robust financial modeling. They lock a
reference to a specific cell, ensuring that when a formula is copied across rows or columns, the reference point does
not change, which is critical for applying a single constant (like a tax rate or growth assumption) across many
calculations. Option A describes worksheet protection, C describes relative references, and D is a formatting
function, none of which align with the primary utility of absolute referencing.**
2. In a professional context, which Excel function is most appropriate for determining the internal rate of
return for a series of irregular cash flows?
,A. IRR
B. MIRR
C. XIRR
D. RATE
Correct Answer: C. XIRR
Rationale: XIRR is specifically designed to calculate the internal rate of return for a schedule of cash flows that are
not necessarily periodic. This is the most common real-world scenario in project finance and investment analysis.
While IRR (A) is for periodic cash flows, MIRR (B) modifies the rate, and RATE (D) is for annuities with constant
periodic payments, XIRR is the professional standard for irregular cash flow analysis.**
3. When auditing a complex spreadsheet, you encounter a formula that includes the function =VLOOKUP(A2,
D:F, 3, FALSE). What does the '3' in this formula represent?
A. The row number in the lookup table to return a value from.
B. The column index number in the table array from which to retrieve the value.
C. The number of the worksheet containing the lookup table.
D. The number of the workbook where the table array is located.
Correct Answer: B. The column index number in the table array from which to retrieve the value.
Rationale: In the VLOOKUP function, the third argument is the column index number, which specifies which
column in the table array contains the return value. In this case, with a table array of D:F, '3' refers to column F.
Option A is incorrect as it describes a row number, and C and D are not arguments within the VLOOKUP syntax.**
,4. A junior analyst creates a model where the sum of quarterly values does not equal the annual total due to
rounding. Which of the following practices is the most professional and recommended way to handle this
discrepancy?
A. Manually adjust the quarterly figures to force them to sum to the annual total.
B. Use the ROUND function on individual quarterly calculations to ensure they sum correctly.
C. Apply a plug figure to the annual total to absorb the rounding difference.
D. Build the model so that annual totals are calculated by summing the rounded quarterly values.
Correct Answer: D. Build the model so that annual totals are calculated by summing the rounded quarterly
values.
Rationale: The most transparent and reliable methodology is to calculate the annual total by summing the
rounded quarterly figures. This ensures the model is arithmetically consistent and avoids hidden "plugs" or manual
adjustments, which are considered poor practice and increase the risk of errors. Option A involves manual
manipulation, B is inefficient as it doesn't solve the sum issue, and C introduces an arbitrary, non-transparent
figure.**
5. Which of the following is the most critical best practice when constructing a financial model for a merger
and acquisition (M&A) transaction?
A. Using a high discount rate to be conservative.
B. Ensuring all cells are formatted with two decimal places.
C. Clearly separating historical data, assumptions, and calculated outputs on different worksheets or distinct
, sections.
D. Maximizing the use of nested IF functions to reduce the number of cells.
Correct Answer: C. Clearly separating historical data, assumptions, and calculated outputs on different
worksheets or distinct sections.
Rationale: A clean and logical model structure is paramount in M&A modeling. Separating inputs (assumptions),
data (history), and calculations (outputs) enhances transparency, reduces errors, and makes the model significantly
easier to audit, review, and update. Option A is a flawed strategic choice, B is a presentation issue, and D leads to
complex, error-prone formulas.**
6. What is the primary advantage of using INDEX and MATCH together as a lookup method over VLOOKUP?
A. INDEX and MATCH is faster to type than VLOOKUP.
B. INDEX and MATCH can look up values to the left of the lookup column.
C. INDEX and MATCH are the only functions that can return text values.
D. INDEX and MATCH do not require the lookup table to be sorted.
Correct Answer: B. INDEX and MATCH can look up values to the left of the lookup column.
Rationale: A key limitation of VLOOKUP is that it can only search in the first column of the table array and return
values to the right. INDEX and MATCH, when combined, offer far greater flexibility, including the ability to perform
leftward lookups, which is a common requirement in complex data sets. While both functions can handle text (C)
and VLOOKUP can also work with unsorted data when using FALSE (D), the left-lookup capability is the primary
differentiator.**
