Financial Modeling Exam Wall Street Prep
VERIFIED
Q1: In a three-statement financial model built in Excel, which color convention is used to
indicate a cell containing a hard-coded input assumption?
A. Black font with yellow fill
B. Blue font with no fill
C. Green font with no fill
D. Red font with no fill
Correct Answer: B [CORRECT]
Rationale: Wall Street Prep's color coding standard specifies blue font for hard-coded inputs
(assumptions), black font for calculations (formulas), and green font for outputs that pull from
other sheets. This allows model users to quickly identify which cells can be modified (blue)
versus which cells contain formulas that should not be overwritten.
Q2: Which of the following Excel functions is most appropriate for performing a vertical lookup
when the lookup value is in the LEFT column of the table array and you need to return a value
from a column to the RIGHT?
A. HLOOKUP
B. INDEX/MATCH
C. VLOOKUP
D. OFFSET
Correct Answer: C [CORRECT]
Rationale: VLOOKUP is specifically designed for vertical lookups where the lookup value is in the
leftmost column of the table array and the return value is in a column to the right. The syntax is
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). HLOOKUP performs
horizontal lookups, while INDEX/MATCH is more flexible but more complex, and OFFSET is
volatile and less efficient.
Q3: [CALCULATION QUESTION]
,A company reported revenue of $500 million in 2024 and projects 8% revenue growth in 2025.
The company's EBITDA margin is expected to be 25%. What is the projected EBITDA for 2025?
Formula: EBITDA = Projected Revenue × EBITDA Margin
Answer: $135 million [CORRECT]
Rationale: Step 1: Calculate projected revenue = $500M × (1 + 0.08) = $540M. Step 2: Calculate
EBITDA = $540M × 0.25 = $135M. This demonstrates the revenue-driven approach to expense
projection, where EBITDA is derived as a percentage of projected revenue.
Q4: Which of the following is the correct order of statement construction in a three-statement
model?
A. Balance Sheet → Income Statement → Cash Flow Statement
B. Income Statement → Cash Flow Statement → Balance Sheet
C. Cash Flow Statement → Income Statement → Balance Sheet
D. Balance Sheet → Cash Flow Statement → Income Statement
Correct Answer: B [CORRECT]
Rationale: The correct model flow is Income Statement first (to calculate net income), then
Cash Flow Statement (starting with net income and adjusting for non-cash items and working
capital changes), then Balance Sheet (using ending cash from the CFS and retained earnings
from the IS). This sequential order ensures all linkages are properly established.
Q5: A company has Accounts Receivable of $75 million and annual Revenue of $450 million.
Assuming 365 days in the year, what is the company's Days Sales Outstanding (DSO)?
Formula: DSO = (Accounts Receivable / Revenue) × 365
Answer: 60.8 days (round to 61 days) [CORRECT]
Rationale: DSO = ($75M / $450M) × 365 = 60.8 days. This means the company takes
approximately 61 days on average to collect payment from customers after a sale is made. DSO
is a key working capital metric used in financial modeling to project future accounts receivable
balances.
Q6: In a financial model, when a company's projected cash balance becomes negative, what is
the most common "plug" used to balance the balance sheet?
, A. Reduce retained earnings
B. Increase accounts payable
C. Add a revolving credit facility draw
D. Decrease inventory
Correct Answer: C [CORRECT]
Rationale: A revolving credit facility (revolver) is the standard plug mechanism in three-
statement models. When projected cash falls below zero (or below a minimum cash balance),
the model automatically draws on the revolver to cover the deficit. This creates a circular
reference because interest expense on the revolver affects net income, which affects cash,
which determines the revolver balance.
Q7: Which Excel setting must be enabled to properly handle circular references created by the
revolver balance and interest expense relationship in a three-statement model?
A. Manual calculation mode
B. Iterative calculation
C. Precision as displayed
D. Automatic calculation mode
Correct Answer: B [CORRECT]
Rationale: Iterative calculation must be enabled in Excel (File → Options → Formulas → Enable
iterative calculation) to resolve circular references. Without this setting, Excel will display a
circular reference warning and the model will not calculate correctly. The iteration settings
typically allow for 100 iterations with a maximum change of 0.001.
Q8: [CALCULATION QUESTION]
A company has the following financial data for 2024:
• Revenue: $800 million
• Operating Expenses (excluding D&A): $600 million
• Depreciation & Amortization: $50 million
• Interest Expense: $30 million
• Tax Rate: 25%
VERIFIED
Q1: In a three-statement financial model built in Excel, which color convention is used to
indicate a cell containing a hard-coded input assumption?
A. Black font with yellow fill
B. Blue font with no fill
C. Green font with no fill
D. Red font with no fill
Correct Answer: B [CORRECT]
Rationale: Wall Street Prep's color coding standard specifies blue font for hard-coded inputs
(assumptions), black font for calculations (formulas), and green font for outputs that pull from
other sheets. This allows model users to quickly identify which cells can be modified (blue)
versus which cells contain formulas that should not be overwritten.
Q2: Which of the following Excel functions is most appropriate for performing a vertical lookup
when the lookup value is in the LEFT column of the table array and you need to return a value
from a column to the RIGHT?
A. HLOOKUP
B. INDEX/MATCH
C. VLOOKUP
D. OFFSET
Correct Answer: C [CORRECT]
Rationale: VLOOKUP is specifically designed for vertical lookups where the lookup value is in the
leftmost column of the table array and the return value is in a column to the right. The syntax is
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). HLOOKUP performs
horizontal lookups, while INDEX/MATCH is more flexible but more complex, and OFFSET is
volatile and less efficient.
Q3: [CALCULATION QUESTION]
,A company reported revenue of $500 million in 2024 and projects 8% revenue growth in 2025.
The company's EBITDA margin is expected to be 25%. What is the projected EBITDA for 2025?
Formula: EBITDA = Projected Revenue × EBITDA Margin
Answer: $135 million [CORRECT]
Rationale: Step 1: Calculate projected revenue = $500M × (1 + 0.08) = $540M. Step 2: Calculate
EBITDA = $540M × 0.25 = $135M. This demonstrates the revenue-driven approach to expense
projection, where EBITDA is derived as a percentage of projected revenue.
Q4: Which of the following is the correct order of statement construction in a three-statement
model?
A. Balance Sheet → Income Statement → Cash Flow Statement
B. Income Statement → Cash Flow Statement → Balance Sheet
C. Cash Flow Statement → Income Statement → Balance Sheet
D. Balance Sheet → Cash Flow Statement → Income Statement
Correct Answer: B [CORRECT]
Rationale: The correct model flow is Income Statement first (to calculate net income), then
Cash Flow Statement (starting with net income and adjusting for non-cash items and working
capital changes), then Balance Sheet (using ending cash from the CFS and retained earnings
from the IS). This sequential order ensures all linkages are properly established.
Q5: A company has Accounts Receivable of $75 million and annual Revenue of $450 million.
Assuming 365 days in the year, what is the company's Days Sales Outstanding (DSO)?
Formula: DSO = (Accounts Receivable / Revenue) × 365
Answer: 60.8 days (round to 61 days) [CORRECT]
Rationale: DSO = ($75M / $450M) × 365 = 60.8 days. This means the company takes
approximately 61 days on average to collect payment from customers after a sale is made. DSO
is a key working capital metric used in financial modeling to project future accounts receivable
balances.
Q6: In a financial model, when a company's projected cash balance becomes negative, what is
the most common "plug" used to balance the balance sheet?
, A. Reduce retained earnings
B. Increase accounts payable
C. Add a revolving credit facility draw
D. Decrease inventory
Correct Answer: C [CORRECT]
Rationale: A revolving credit facility (revolver) is the standard plug mechanism in three-
statement models. When projected cash falls below zero (or below a minimum cash balance),
the model automatically draws on the revolver to cover the deficit. This creates a circular
reference because interest expense on the revolver affects net income, which affects cash,
which determines the revolver balance.
Q7: Which Excel setting must be enabled to properly handle circular references created by the
revolver balance and interest expense relationship in a three-statement model?
A. Manual calculation mode
B. Iterative calculation
C. Precision as displayed
D. Automatic calculation mode
Correct Answer: B [CORRECT]
Rationale: Iterative calculation must be enabled in Excel (File → Options → Formulas → Enable
iterative calculation) to resolve circular references. Without this setting, Excel will display a
circular reference warning and the model will not calculate correctly. The iteration settings
typically allow for 100 iterations with a maximum change of 0.001.
Q8: [CALCULATION QUESTION]
A company has the following financial data for 2024:
• Revenue: $800 million
• Operating Expenses (excluding D&A): $600 million
• Depreciation & Amortization: $50 million
• Interest Expense: $30 million
• Tax Rate: 25%
