05 01 Interview Question Model

Instructions on How to Use This "Interview Question" 3-Statement Model:
First off, note that this model can and will "break" under the appropriate conditions.
Unfortunately, due to the way Excel's data validation function works, it is impossible to error-check every single condition that might result in errors or weird numbers.
So stick to relatively standard numbers, make sure nothing too crazy happens, and focus on 1 or 2 changes at a time - those are, by far, the most likely interview questions anyway.
You'll see that MANY items on the Income Statement and Cash Flow Statement are $0 initially.
I've taught this material for years now, and I've seen repeatedly that it is MUCH more confusing to understand the changes when they're not $0 initially.
No, it's not realistic, but this exercise is all about conceptual understanding - and that is much easier when these items are $0 to begin with.
Most of these changes are modeled as INCREASES because that is how most interview questions are phrased.
If you really want, you can remove data validation and allow for decreases at your own peril, because that can create problems.
A few notes / restrictions / comments on specific items here:
Income Statement Items:
Depreciation and Amortization: Make sure that these do NOT exceed the PP&E balance and the Other Intangible Assets number, respectively. Otherwise you'll get negatives, which makes no sense.
Interest Income and Interest Expense: Interest Income always needs to be positive, and Interest Expense always needs to be negative.
Gain / (Loss) on Sale of PP&E: Can be positive or negative. You MUST have an accompanying change in PP&E Sale Proceeds for this one to make sense. Otherwise, it's just a Write-Up or Write-Down.
Gain / (Loss) on Sale of ST Investments: Can be positive or negative. You MUST have an accompanying change in Sell Short-Term Investments for this one to make sense. Otherwise, it's just a Write-Up or Write-Down.
Goodwill Impairment: Must be positive because this is always an expense on the IS. This will directly reduce Goodwill, but produce a tax savings (in this simplified example - not necessarily in real life).
PP&E Write-Down: Must be positive because this is always an expense on the IS. This will directly reduce PP&E, but produce a tax savings (in this simplified example - not necessarily in real life).
Deferred Income Taxes: These MUST be less than the total Income Tax Provision for the year. So if total Income Taxes are $400 and you set this to $500, it won't make sense. Don't do that. These are a PORTION of the total taxes.
Balance Sheet Line Items:
These must all be positives. They are separated into Increases or Decreases for VERY specific reasons - because an Increase vs. a Decrease means something quite different for these.
Once again, don't enter number so big that you make these negative. If Inventory is $100 and you assume that it decreases by $200, that would not make any sense.
Cash Flow Statement Items:
Similar rules as above - these must all be positive. Purchases and Sales are separated because they work differently - Purchases represent cash outflows, and Sales represent cash inflows.
Some of these items may not have hard limitations - for example, you can assume a huge number for new share issuances.
But others DO have hard limitations - you can't sell off more PP&E than you actually have, for example - so be careful with the numbers you select.
3 Financial Statements - Interview Question Model

($ in Millions)
Assumptions & Model Output
Assumptions:
Tax Rate:
Initial Cash Balance:
40%
100
Here's What Happens on the Statements

Cash Changes By
Shareholders' Equity Changes By
Net Income Changes By.
Assets Side Changes By
Liabilities & Equity Side Changes By
Balance Sheet Still Balanced?
$
$
$
$
$
OK!
Interview Questions - Possible Scenarios, by Statement

Income Statement Line Items (COGS Handled via Inventory) Operational Balance Sheet Line Items:
Revenue Increases By:
Operating Expenses Increases By:
Depreciation Increases By:
Stock-Based Compensation Increases By:
Amortization of Intangibles Increases By:
Interest Income Increases By:
Interest Expense Increases By:
Gain / (Loss) on Sale of PP&E Changes By:
Gain / (Loss) on Sale of ST Investments Changes By:
Goodwill Impairment Increases By:
PP&E Write-Down Increases By:
Deferred Income Taxes Increases By:
Accounts Receivable:
Increases By:
Decreases By:
Prepaid Expenses:
Increases By:
Decreases By:
Inventory:
Increases By:
Decreases By (This represents a COGS increase):
Accrued Expenses:
Increases By:
Decreases By:
Accounts Payable:
Increases By:
Decreases By:
Deferred Revenue:
Increases By:
Decreases By:
Cash Flow Statement Line Items:

Purchase Short-Term Investments Increases By:
Sell Short-Term Investments Increases By:
Purchase Long-Term Investments Increases By:
Sell Long-Term Investments Increases By:
Capital Expenditures Increases By:
PP&E Sale Proceeds Increases By:
Dividends Issued Increases By:
Issue Long-Term Debt Increases By:
Repay Long-Term Debt Increases By:
Issue Short-Term Debt Increases By:
Repay Short-Term Debt Increases By:
Repurchase Shares Increases By:
Issue New Shares Increases By:
Income Statement
Revenue:
Cost of Goods Sold (COGS):
Gross Profit:
Operating Expenses:
Depreciation:
Stock-Based Compensation:
Amortization of Intangibles:
Operating Income:
Interest Income:
(Interest Expense):
Gain / (Loss) on Sale of PP&E:
Gain / (Loss) on Sale of ST Investme
Goodwill Impairment:
PP&E Write-Down:
Pre-Tax Income:
Income Tax Provision:
Current Portion of Income Taxes:
Deferred Portion of Income Taxes:
Net Income:
Balance Sheet
Current
Period
Before
After
Changes Changes
###
###
100
100
1,200
1,200
200
200
1,000
1,000
400
1,000
1,000
400
400
-
400
-
$ 600
$ 600
Assets:
Current Assets:
Cash & Cash-Equivalents:
Short-Term Investments:
Prepaid Expenses:
Inventory:
Total Current Assets:
Long-Term Assets:
Plants, Property & Equipment (PP&E):
Other Intangible Assets:
Long-Term Investments:
Goodwill:
Total Long-Term Assets:
Cash Flow Statement

Start of End of Current
Period
Period
Before
After
Changes Changes
$
100
100
100
100
100
500
1,000
200
100
100
1,400
Total Assets:
700
100
100
100
100
1,100
1,000
200
100
100
1,400
1,000
200
100
100
1,400
###
700
100
100
100
100
1,100
###
###
Liabilities & Shareholders' Equity:

Current Liabilities:
Revolver (Short-Term Debt):
Accounts Payable:
Accrued Expenses:
Total Current Liabilities:
Long-Term Liabilities:
Deferred Revenue:
Deferred Tax Liability:
Long-Term Debt:
Total Long-Term Liabilities:
Total Liabilities:
100
200
200
500
200
200
100
500
100
200
200
500
200
200
100
500
100
200
200
500
200
200
100
500
###
###
###
600
(100)
300
100
900
600
(100)
900
100
1,500
600
(100)
900
100
1,500
Total Liabilities & Equity:
###
###
###
Balance Sheet Balanced?
OK!
OK!
OK!
Shareholders' Equity:
Common Stock & APIC:
Treasury Stock:
Retained Earnings:
Accumulated Other Compr. Income:
Total Shareholders' Equity:
Current
Period
Before
After
Operating Activities:
Changes Changes
Net Income:
$ 600
$ 600
Non-Cash Expenses & Other Adjustments:
Depreciation:
Stock-Based Compensation:
Amortization of Intangibles:
Deferred Income Taxes:
(Gain) / Loss on Sale of PP&E:
(Gain) / Loss on Sale of ST Investme
Goodwill Impairment:
PP&E Write-Down:
Changes in Operating Assets & Liabilities:
Prepaid Expenses:
Inventory:
Accounts Payable:
Accrued Expenses:
Deferred Revenue:
Cash Flow from Operations:
600
600
Investing Activities:
Purchase Short-Term Investments:
Sell Short-Term Investments:
Purchase Long-Term Investments:
Sell Long-Term Investments:
Capital Expenditures:
PP&E Sale Proceeds:
Cash Flow from Investing:
Financing Activities:
Dividends Issued:
Issue Long-Term Debt:
Repay Long-Term Debt:
Issue Short-Term Debt:
Repay Short-Term Debt:
Repurchase Shares:
Issue New Shares:
Cash Flow from Financing:
Beginning Cash:
$ 100
$ 100
Increase / (Decrease) in Cash:

Cash & Cash Equivalents:
$ 600
$ 700
$ 600
$ 700

05 01 Interview Question Model

Uploaded by

Copyright:

Available Formats

05 01 Interview Question Model

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

05 01 Interview Question Model

Uploaded by

Copyright:

Available Formats

Instructions on How to Use This "Interview Question" 3-Statement Model:

3 Financial Statements - Interview Question Model

Initial Cash Balance:

Here's What Happens on the Statements

Interview Questions - Possible Scenarios, by Statement

Cash Flow Statement Line Items:

Cash Flow Statement

Liabilities & Shareholders' Equity:

Total Liabilities & Equity:

Balance Sheet Balanced?

Increase / (Decrease) in Cash:

You might also like