WALL STREET PREP EXCEL CRASH COURSE
RETAKE EXAM ,QUESTIONS , ANSWERS WITH
WELL ELABORATE RATIONALES
1. Question: What is the purpose of the SUM function in Excel?
o Answer: The SUM function is used to add together a range of
numbers in Excel.
o Rationale: It’s a basic but essential function for performing
arithmetic calculations on datasets, and it's widely used in financial
modeling for summing revenues, costs, or other numerical data points.
2. Question: How can you reference a cell in a different worksheet?
o Answer: To reference a cell in a different worksheet, use the syntax
SheetName!CellReference (e.g., Sheet2!A1).
o Rationale: Cross-referencing is essential for linking data across
multiple sheets in a model, allowing for a dynamic and organized
structure.
3. Question: How do you calculate the compound annual growth rate
(CAGR) in Excel?
o Answer: The formula to calculate CAGR is = (End Value /
Start Value) ^ (1 / Number of Years) - 1.
o Rationale: CAGR is a key metric used in financial modeling to assess
the average annual growth rate of an investment or metric over a
specified period. Excel’s formula allows for easy calculation of this
growth.
4. Question: What is the purpose of using an IF statement in Excel?
o Answer: An IF statement is used to perform logical tests in Excel,
returning one value if the condition is true and another if it’s false.
o Rationale: Logical functions like IF are critical in financial models
for scenarios such as tax calculations, determining if certain
conditions are met (e.g., profitability), or applying specific rules.
5. Question: How do you create a dynamic range in Excel?
o Answer: You can create a dynamic range by using Excel's Table
feature or by using the OFFSET and COUNTA functions to define a
range that automatically adjusts as data is added or removed.
, o Rationale: Dynamic ranges are essential in financial models where
the data set might change frequently, such as in projections or
historical financial data.
6. Question: What does the VLOOKUP function do?
o Answer: VLOOKUP is used to search for a value in the leftmost
column of a range and return a value from another column in the same
row.
o Rationale: VLOOKUP is commonly used in financial models to pull
data from large datasets, such as looking up financial information for
specific dates, company names, or other key identifiers.
7. Question: How can you calculate a moving average in Excel?
o Answer: You can calculate a moving average by using the AVERAGE
function on a specified range of cells that shifts over time, such as
=AVERAGE(B2:B6) for a 5-period moving average.
o Rationale: Moving averages are used in financial analysis to smooth
out fluctuations in data over a specified period, making trends easier
to identify.
8. Question: How do you use the INDEX and MATCH functions together?
o Answer: The INDEX function returns a value from a specific position
in a range, while MATCH provides the position of a lookup value.
Combined, they allow for more flexible lookups than VLOOKUP,
e.g., =INDEX(B1:B10, MATCH("Apple", A1:A10, 0)).
o Rationale: INDEX and MATCH are more versatile than VLOOKUP
because they allow lookups based on any column and can work with
both vertical and horizontal ranges, providing more control over your
data retrieval.
9. Question: What is the purpose of the NPV function in Excel?
o Answer: The NPV function calculates the Net Present Value of an
investment or series of cash flows, using a discount rate and future
cash flows.
o Rationale: NPV is a crucial financial metric used in discounted cash
flow (DCF) analysis to assess the profitability of an investment. It
helps determine the value of future cash flows in today's terms.
10.Question: How do you apply conditional formatting in Excel?
o Answer: Conditional formatting is applied by selecting the range of
cells and choosing "Conditional Formatting" from the Home tab, then
setting rules such as color scales, data bars, or specific thresholds for
formatting.
o Rationale: Conditional formatting helps in financial modeling by
visually highlighting key values or trends, such as highlighting
, negative cash flows or profitable years, making the data easier to
interpret.
11.Question: How do you use the PMT function in Excel?
Answer: The PMT function is used to calculate the periodic payment for a
loan based on constant payments and a constant interest rate. The formula is
=PMT(rate, nper, pv) where rate is the interest rate per period,
nper is the number of periods, and pv is the present value (loan amount).
Rationale: The PMT function is critical in financial modeling for
determining loan repayments, mortgages, or annuities. It helps in building
debt models or calculating the impact of financing decisions on cash flows.
12.Question: What does the COUNTIF function do in Excel?
Answer: The COUNTIF function counts the number of cells within a range
that meet a specified condition. The formula is =COUNTIF(range,
criteria).
Rationale: COUNTIF is useful for performing data analysis, such as
counting how many times a particular value (e.g., a specific sales target) is
met within a dataset. It helps to summarize data for reporting purposes.
13.Question: How do you calculate the internal rate of return (IRR) in
Excel?
Answer: The IRR function calculates the internal rate of return for a series
of cash flows. The formula is =IRR(values, [guess]) where
values is an array of cash flows, and [guess] is an optional estimate of
the rate.
Rationale: IRR is a key metric used in financial modeling to evaluate
investment opportunities. It represents the discount rate that makes the net
present value (NPV) of an investment equal to zero, helping in the decision-
making process.
14.Question: How do you use the CONCATENATE function in Excel?
Answer: The CONCATENATE function combines multiple text strings into
one string. The formula is =CONCATENATE(text1, text2, ...), or
in Excel 2016 and later, the TEXTJOIN function can also be used.
, Rationale: CONCATENATE is often used in financial modeling to create
custom labels, such as combining company names with years, or creating
unique identifiers in datasets.
15.Question: What is the difference between relative and absolute cell
references in Excel?
Answer: A relative reference (e.g., A1) changes when the formula is copied
to another cell, while an absolute reference (e.g., $A$1) remains constant
regardless of where the formula is copied.
Rationale: The difference between relative and absolute references is
essential when copying formulas across a model. Absolute references are
used when you need to fix a value, such as a discount rate in a DCF model,
while relative references are used for calculations across different rows or
columns.
16.Question: How do you use the OFFSET function in Excel?
Answer: The OFFSET function returns a reference to a range that is offset
from a starting cell or range by a specified number of rows and columns.
The formula is =OFFSET(reference, rows, cols, [height],
[width]).
Rationale: OFFSET is used in dynamic ranges and in constructing complex
financial models where you need to reference data that changes over time or
relative to other data points. It is often used in conjunction with other
functions like COUNTA for creating dynamic named ranges.
17.Question: How do you perform a two-variable data table in Excel?
Answer: A two-variable data table allows you to analyze how changes in
two input variables affect an output formula. It is done by selecting a range
with one variable across the top and another down the side, and using the
"Data Table" feature under the "What-If Analysis" tool.
Rationale: Two-variable data tables are powerful for scenario analysis in
financial models, allowing you to examine the effect of multiple variables
(like changes in interest rate and sales volume) on a key financial metric
(e.g., NPV or IRR).
18.Question: How do you calculate a weighted average cost of capital
(WACC) in Excel?
RETAKE EXAM ,QUESTIONS , ANSWERS WITH
WELL ELABORATE RATIONALES
1. Question: What is the purpose of the SUM function in Excel?
o Answer: The SUM function is used to add together a range of
numbers in Excel.
o Rationale: It’s a basic but essential function for performing
arithmetic calculations on datasets, and it's widely used in financial
modeling for summing revenues, costs, or other numerical data points.
2. Question: How can you reference a cell in a different worksheet?
o Answer: To reference a cell in a different worksheet, use the syntax
SheetName!CellReference (e.g., Sheet2!A1).
o Rationale: Cross-referencing is essential for linking data across
multiple sheets in a model, allowing for a dynamic and organized
structure.
3. Question: How do you calculate the compound annual growth rate
(CAGR) in Excel?
o Answer: The formula to calculate CAGR is = (End Value /
Start Value) ^ (1 / Number of Years) - 1.
o Rationale: CAGR is a key metric used in financial modeling to assess
the average annual growth rate of an investment or metric over a
specified period. Excel’s formula allows for easy calculation of this
growth.
4. Question: What is the purpose of using an IF statement in Excel?
o Answer: An IF statement is used to perform logical tests in Excel,
returning one value if the condition is true and another if it’s false.
o Rationale: Logical functions like IF are critical in financial models
for scenarios such as tax calculations, determining if certain
conditions are met (e.g., profitability), or applying specific rules.
5. Question: How do you create a dynamic range in Excel?
o Answer: You can create a dynamic range by using Excel's Table
feature or by using the OFFSET and COUNTA functions to define a
range that automatically adjusts as data is added or removed.
, o Rationale: Dynamic ranges are essential in financial models where
the data set might change frequently, such as in projections or
historical financial data.
6. Question: What does the VLOOKUP function do?
o Answer: VLOOKUP is used to search for a value in the leftmost
column of a range and return a value from another column in the same
row.
o Rationale: VLOOKUP is commonly used in financial models to pull
data from large datasets, such as looking up financial information for
specific dates, company names, or other key identifiers.
7. Question: How can you calculate a moving average in Excel?
o Answer: You can calculate a moving average by using the AVERAGE
function on a specified range of cells that shifts over time, such as
=AVERAGE(B2:B6) for a 5-period moving average.
o Rationale: Moving averages are used in financial analysis to smooth
out fluctuations in data over a specified period, making trends easier
to identify.
8. Question: How do you use the INDEX and MATCH functions together?
o Answer: The INDEX function returns a value from a specific position
in a range, while MATCH provides the position of a lookup value.
Combined, they allow for more flexible lookups than VLOOKUP,
e.g., =INDEX(B1:B10, MATCH("Apple", A1:A10, 0)).
o Rationale: INDEX and MATCH are more versatile than VLOOKUP
because they allow lookups based on any column and can work with
both vertical and horizontal ranges, providing more control over your
data retrieval.
9. Question: What is the purpose of the NPV function in Excel?
o Answer: The NPV function calculates the Net Present Value of an
investment or series of cash flows, using a discount rate and future
cash flows.
o Rationale: NPV is a crucial financial metric used in discounted cash
flow (DCF) analysis to assess the profitability of an investment. It
helps determine the value of future cash flows in today's terms.
10.Question: How do you apply conditional formatting in Excel?
o Answer: Conditional formatting is applied by selecting the range of
cells and choosing "Conditional Formatting" from the Home tab, then
setting rules such as color scales, data bars, or specific thresholds for
formatting.
o Rationale: Conditional formatting helps in financial modeling by
visually highlighting key values or trends, such as highlighting
, negative cash flows or profitable years, making the data easier to
interpret.
11.Question: How do you use the PMT function in Excel?
Answer: The PMT function is used to calculate the periodic payment for a
loan based on constant payments and a constant interest rate. The formula is
=PMT(rate, nper, pv) where rate is the interest rate per period,
nper is the number of periods, and pv is the present value (loan amount).
Rationale: The PMT function is critical in financial modeling for
determining loan repayments, mortgages, or annuities. It helps in building
debt models or calculating the impact of financing decisions on cash flows.
12.Question: What does the COUNTIF function do in Excel?
Answer: The COUNTIF function counts the number of cells within a range
that meet a specified condition. The formula is =COUNTIF(range,
criteria).
Rationale: COUNTIF is useful for performing data analysis, such as
counting how many times a particular value (e.g., a specific sales target) is
met within a dataset. It helps to summarize data for reporting purposes.
13.Question: How do you calculate the internal rate of return (IRR) in
Excel?
Answer: The IRR function calculates the internal rate of return for a series
of cash flows. The formula is =IRR(values, [guess]) where
values is an array of cash flows, and [guess] is an optional estimate of
the rate.
Rationale: IRR is a key metric used in financial modeling to evaluate
investment opportunities. It represents the discount rate that makes the net
present value (NPV) of an investment equal to zero, helping in the decision-
making process.
14.Question: How do you use the CONCATENATE function in Excel?
Answer: The CONCATENATE function combines multiple text strings into
one string. The formula is =CONCATENATE(text1, text2, ...), or
in Excel 2016 and later, the TEXTJOIN function can also be used.
, Rationale: CONCATENATE is often used in financial modeling to create
custom labels, such as combining company names with years, or creating
unique identifiers in datasets.
15.Question: What is the difference between relative and absolute cell
references in Excel?
Answer: A relative reference (e.g., A1) changes when the formula is copied
to another cell, while an absolute reference (e.g., $A$1) remains constant
regardless of where the formula is copied.
Rationale: The difference between relative and absolute references is
essential when copying formulas across a model. Absolute references are
used when you need to fix a value, such as a discount rate in a DCF model,
while relative references are used for calculations across different rows or
columns.
16.Question: How do you use the OFFSET function in Excel?
Answer: The OFFSET function returns a reference to a range that is offset
from a starting cell or range by a specified number of rows and columns.
The formula is =OFFSET(reference, rows, cols, [height],
[width]).
Rationale: OFFSET is used in dynamic ranges and in constructing complex
financial models where you need to reference data that changes over time or
relative to other data points. It is often used in conjunction with other
functions like COUNTA for creating dynamic named ranges.
17.Question: How do you perform a two-variable data table in Excel?
Answer: A two-variable data table allows you to analyze how changes in
two input variables affect an output formula. It is done by selecting a range
with one variable across the top and another down the side, and using the
"Data Table" feature under the "What-If Analysis" tool.
Rationale: Two-variable data tables are powerful for scenario analysis in
financial models, allowing you to examine the effect of multiple variables
(like changes in interest rate and sales volume) on a key financial metric
(e.g., NPV or IRR).
18.Question: How do you calculate a weighted average cost of capital
(WACC) in Excel?
