Excel for Finance Flashcards

Question 1

What is a cell in an Excel worksheet?

Accepted Answer

A cell is the single rectangle where a row and column intersect, identified by its address such as B4. Cells hold constants such as numbers and text, or formulas that calculate results from other cells. Keeping inputs in separate, labeled cells makes financial models easier to audit and update.

Question 2

What does the equal sign do at the start of an Excel entry?

Accepted Answer

Typing an equal sign at the start of a cell tells Excel that the entry is a formula, so the program calculates and displays a result instead of storing the typed characters as text. Formulas can use arithmetic operators such as plus, minus, asterisk for multiplication, and slash for division.

Question 3

What is the purpose of the fill handle in Excel?

Accepted Answer

The fill handle is the small square at the bottom right corner of a selected cell. Dragging it copies a formula or extends a series into adjacent cells. When a formula is filled down, relative references adjust to each new row. This saves time when building repeated schedules like a loan table.

Question 4

What does an absolute reference do in a formula?

Accepted Answer

An absolute reference locks a cell address so it does not change when the formula is copied to other cells. It is written with dollar signs before the column letter and row number, such as $B$4. Analysts use it for fixed inputs like a tax rate or discount rate.

Question 5

What does the SUM function return for a range of cells?

Accepted Answer

The SUM function returns the total of all numeric values in the range you specify, ignoring text and empty cells. For example, SUM(B2:B6) adds five cash flows in one step. Using SUM is clearer and less error-prone than typing a long chain of plus signs.

Question 6

What is a named range and why is it useful?

Accepted Answer

A named range is a descriptive label assigned to a cell or block of cells, such as TaxRate, that can be used in formulas in place of a cell address. Named ranges make formulas easier to read and reduce errors from pointing to the wrong cell.

Question 7

What does the IF function do in a financial model?

Accepted Answer

The IF function tests a condition and returns one value if the condition is true and a different value if it is false. The general form is IF(logical_test, value_if_true, value_if_false). Analysts use it to flag a loan covenant breach or to apply a different tax rate above a threshold.

Question 8

What is data validation used for in a model?

Accepted Answer

Data validation restricts what a user can enter into a cell, such as allowing only percentages between zero and one or a list of approved currency codes. It helps prevent typing errors from flowing into calculations. Input cells are often shaded and protected so that formulas are not overwritten by accident.

Question 9

What is the time value of money principle in finance?

Accepted Answer

The time value of money is the principle that a dollar available today is worth more than the same dollar received later, because today's dollar can be invested to earn a return. This idea underlies present value and future value calculations. Spreadsheets automate these calculations, letting analysts compare cash flows at different times.

Question 10

What does the FV function calculate in Excel?

Accepted Answer

The FV function calculates the future value of an investment based on a constant interest rate, a number of periods, and optional regular payments. Its syntax is FV(rate, nper, pmt, pv, type). Enter the rate per period and the number of periods in matching units, such as monthly rate with monthly periods.

Question 11

Why must the rate and number of periods use matching time units?

Accepted Answer

The rate and the number of periods must describe the same time interval, so an annual rate is paired with annual periods and a monthly rate with monthly periods. Mixing units produces results that look reasonable but are wrong. Divide an annual rate by twelve to get a monthly rate.

Question 12

What does the PV function return when valuing a future payment?

Accepted Answer

The PV function returns the present value of a series of future cash flows, discounted at a constant rate per period. Its syntax is PV(rate, nper, pmt, fv, type). Excel shows the result with the opposite sign of the payments, so a positive payment stream produces a negative present value.

Question 13

What is net present value and how is it used in capital budgeting?

Accepted Answer

Net present value is the sum of all discounted cash flows of a project, with the initial outlay entered as a negative amount at time zero. A positive result means the project adds value at the discount rate used. Excel's NPV assumes its first value arrives one period ahead, so add the outlay separately.

Question 14

How does the IRR function relate to NPV?

Accepted Answer

IRR is the discount rate at which the net present value of a project's cash flows equals zero. If the IRR exceeds the required rate of return, the project is generally considered acceptable. Excel's IRR function takes a range of cash flows that begin with the initial outlay and returns this rate.

Question 15

What does the PMT function calculate for a fixed rate loan?

Accepted Answer

The PMT function calculates the fixed periodic payment that repays a loan at a constant interest rate. Its syntax is PMT(rate, nper, pv, fv, type). Excel returns the payment as a negative number because it is a cash outflow for the borrower.

Question 16

What is an amortization schedule and how is it built?

Accepted Answer

An amortization schedule is a table that shows, for each payment period, how much of the payment covers interest and how much reduces the principal balance. Each row starts with the prior ending balance, so the layout is built with formulas that reference the row above.

Question 17

What is a good basic structure for a financial model in Excel?

Accepted Answer

A well built financial model separates inputs, calculations, and outputs into clearly labeled areas or sheets. Inputs such as growth rates and costs sit in one place, calculations reference them, and outputs summarize the results. This layout lets one changed assumption flow through the whole model.

Question 18

How do you calculate a gross margin with a formula?

Accepted Answer

Gross margin equals gross profit divided by revenue, so a formula such as =B5/B3 gives the ratio, which you then format as a percentage. Gross profit itself is revenue minus cost of goods sold. Keeping each ratio in its own labeled row makes the statements easier to review.

Question 19

What does the ROUND function do, and when should analysts use it?

Accepted Answer

The ROUND function rounds a number to a specified count of decimal places, such as ROUND(A1, 2) for cents. Analysts use it when a displayed total must match the sum of rounded components, since formatting alone changes only the appearance. Rounding inside a calculation can affect results, so apply it deliberately.

Question 20

What does the XNPV function do differently from NPV?

Accepted Answer

XNPV discounts cash flows that occur on specific dates rather than at regular periods, using a stated annual rate and a day count based on actual calendar days. Its arguments pair each cash flow with a date, and the first date is treated as the start of the analysis.

Question 21

What is the purpose of the MIRR function?

Accepted Answer

MIRR, the modified internal rate of return, assumes negative cash flows are financed at one rate and positive cash flows are reinvested at another. It often gives a more realistic single figure than IRR when cash flows change sign more than once. Excel's MIRR function takes the values range and both rates as arguments.

Question 22

What does EBITDA measure in a financial statement analysis?

Accepted Answer

EBITDA stands for earnings before interest, taxes, depreciation, and amortization, and it is often used as a rough proxy for operating cash generation. Analysts compute it in Excel by adding back interest, taxes, depreciation, and amortization to operating income. It is a non-GAAP measure, so state its definition clearly.

Question 23

How is the current ratio calculated in a spreadsheet?

Accepted Answer

The current ratio equals current assets divided by current liabilities, and it measures short-term liquidity. In Excel, enter the two totals in labeled cells and write a formula such as =B10/B14. A result above one means current assets exceed current liabilities, though acceptable levels vary by industry.

Question 24

What does a waterfall chart show in financial reporting?

Accepted Answer

A waterfall chart shows how a starting value rises or falls through a series of positive and negative steps to reach an ending total. Each bar floats from the prior running total, which makes the contribution of each item visible. Excel can build this chart from a column of signed values.

Question 25

What does the VLOOKUP function do when pulling data from a table?

Accepted Answer

VLOOKUP searches for a value in the first column of a table and returns a value from a specified column in the same row. Its syntax is VLOOKUP(lookup_value, table_array, col_index_num, range_lookup). Setting range_lookup to FALSE forces an exact match, which is safer for most financial data.

Question 26

Why does INDEX and MATCH often replace VLOOKUP?

Accepted Answer

INDEX and MATCH work together to look up a value in any direction, not only to the right of the lookup column. MATCH finds the position of an item in a range, and INDEX returns the value at that position in another range. Inserting columns is also less likely to break the formula.

Question 27

What does the SUMIF function do when summarizing a budget by category?

Accepted Answer

SUMIF adds only the values in a range that meet a single condition, such as all expenses tagged with the category Marketing. Its syntax is SUMIF(range, criteria, sum_range). SUMIFS extends the same idea to several conditions applied at once.

Question 28

Why might an analyst prefer MEDIAN over AVERAGE for certain financial data?

Accepted Answer

MEDIAN returns the middle value of a sorted set, so a few extreme values do not distort it the way they distort the mean. Analysts often prefer it for skewed data such as home prices or executive pay. In Excel, MEDIAN(range) ignores text and empty cells, just as AVERAGE does.

Question 29

What is a pivot table used for when summarizing transactions?

Accepted Answer

A pivot table summarizes a large list of records by grouping rows and columns, then aggregating a value field with sum, count, or average. You build it by dragging field names into areas such as Rows, Columns, and Values. Changing the layout does not alter the source data.

Question 30

What does the IFERROR function do in a financial model?

Accepted Answer

IFERROR returns a replacement value you specify whenever a formula produces an error, such as N/A from a failed lookup or a division by zero. Its syntax is IFERROR(value, value_if_error). Use it carefully, because hiding errors can conceal real problems in the model.

Question 31

What is the purpose of a helper column in a spreadsheet model?

Accepted Answer

A helper column holds an intermediate calculation, such as a flag or a converted value, that other formulas can reference. Breaking a complex formula into labeled steps makes errors easier to find and check. Helper columns are a practical alternative to long nested formulas that are hard to audit.

Question 32

What does the COUNTIFS function do with multiple conditions?

Accepted Answer

COUNTIFS counts the cells that satisfy every one of several conditions, such as counting transactions in a given region that exceed a set amount. Each condition is entered as a range and criteria pair, and the pairs are separated by commas.

Question 33

What is a sensitivity analysis in financial modeling?

Accepted Answer

Sensitivity analysis measures how a key output, such as net present value, changes when one input, such as a growth rate or discount rate, is varied. It shows which assumptions matter most and how robust a conclusion is. Analysts usually change one variable at a time while holding others constant.

Question 34

How does a one variable data table show results for several inputs?

Accepted Answer

A one variable data table lists a series of values for a single input in one column, then calculates a chosen output for each value. Excel's Data Table tool fills in the results automatically. The output formula must reference the input cell, which is set in the tool's dialog.

Question 35

What is a two variable data table used for in a valuation?

Accepted Answer

A two variable data table shows how one output changes across combinations of two inputs, arranged in a grid, such as discount rate across the top and terminal growth rate down the side. Each cell in the grid holds the output for one pair of assumptions. It is a common way to present valuation ranges in a single view.

Question 36

What is scenario analysis and how does it differ from sensitivity analysis?

Accepted Answer

Scenario analysis changes several inputs together to represent a coherent story, such as a base case, an optimistic case, and a downside case. Sensitivity analysis isolates one variable at a time, while scenarios reveal how combined assumptions interact. Excel's Scenario Manager can store and switch between named sets of input values.

Question 37

What does Goal Seek do when you know the desired result?

Accepted Answer

Goal Seek works backward from a target output to find the input value that produces it. You specify the cell to set, the target value, and the input cell to change. It finds one solution by iteration, so check the result afterward.

Question 38

What is break even analysis and what is its main formula?

Accepted Answer

Break even analysis finds the sales volume at which total revenue equals total costs, so profit is zero. The basic formula divides fixed costs by the contribution margin per unit, which is price minus variable cost per unit. In Excel, link this calculation to the price and cost assumptions.

Question 39

Why should a financial model include built in error checks?

Accepted Answer

Error checks are formulas that test whether the model behaves as expected, such as confirming that a balance sheet balances. A failed check flags a problem before anyone relies on the output. Checks should be visible and labeled so that reviewers can verify the model quickly.

Question 40

How should an analyst document assumptions in a financial model?

Accepted Answer

Assumptions should appear in a dedicated input area with labels, units, and a brief note on their source or rationale. Color coding, such as blue text for hard coded inputs and black for formulas, helps readers tell inputs from calculations at a glance. Consistent documentation lets colleagues audit the model.

Excel for Finance Flashcards

1Core Spreadsheet Skills

2Time Value of Money Functions

3Investment and Ratio Analysis

4Lookup and Summary Functions

5Scenario and Sensitivity Testing

Learning Resources