Excel & Modelling

Question 1

hange links in sheet (e.g. when copying a sheet from another file)

Answer 1

• Data > Queries and Connections > Workbook Links
• Select More Commands (…) next to the required workbook > select Change source
• e.g. link the Excel file to itself

Question 2

Access settings

Answer 2

Alt + T + O

Question 3

Format as number

Answer 3

Shift + CTRL + !

Question 4

Hide whole Excel file

Answer 4

Alt + W + H

Question 4

Format as date

Answer 4

CTRL #

Question 5

Preferred way to hide columns/rows

Answer 5

Grouping

Question 6

Create group

Answer 6

Shift + Alt + Left/Right Arrow

Question 7

Copy right

Answer 7

CTRL + R

Question 8

Copy down

Answer 8

CTRL + D

Question 9

To copy data from a PDF

Answer 9

Use Adobe, hold down ALT, then select with box data, then copy paste

Question 10

Shift cells around, e.g. push all cells down or up

Answer 10

CTRL + “+” or CTRL + “-“

Question 11

Select e.g. all hardcoded numbers from a couple of cells

Answer 11

F5 + Special

Question 12

blue that banks use?

Answer 12

00255

Question 13

The shortcut to re-calculate all formulae in the current workbook is

Answer 13

F9

Question 14

Keyboard shortcut to save as is …

Answer 14

F12

Question 15

Formula for multiplying a range of e.g. 3 cells with another range of 3 cells and getting the sum of that (i.e. cell 1 * cell a + cell 2 * cell b + …)

Answer 15

Sumproduct

Question 16

Which tax rate to use for a company?

Answer 16

Look at footnotes for Income Taxes: Reconciliation of statutory tax rate to firm’s effective rate –> This footnote provides a reconciliation that explains differences between statutory tax rate and the firm’s effective tax rate. The reconciliation starts with the federal statutory tax rate and then shows each component of pre-tax income that is not taxed at the statutory rate to derive the effective tax rate

Question 17

Financial analysts need to analyse the components of
deferred tax liabilities and decide, on a case-by-case basis,
whether the tax liabilities are likely to reverse over time or
not. Factors to consider in making this decision are:

Answer 17

• Future tax rates, tax laws and accounting standards
• Firm’s growth rate
• Non-recurring items

Question 18

Format for numbers in general - what to “choose”

Answer 18

“accounting” with no decimal places (there you can also easily add a $ sign if needed)

Question 19

How to unmerge cells

Answer 19

Under alignment –> text control

Question 20

How to format e.g. FY21?

Answer 20

Format as ‘FY’ yy

Question 21

+/- sign in IS - when to use what

Answer 21

To keep it simply: Everything which is positive stays positive, rest with minus-sign

Question 22

how to calculate stub (with formula)

Answer 22

=yearfrac(current_date, end_date)

Question 23

Toggle case by using offset function

Answer 23

offset(current_cell,$case_number$, 0) –> the 3 case assumptions in rows directly below

Question 24

Best modelling practices:

Answer 24

* Use as many checks as possible --> for TRUE/FALSE you can use e.g. =SUM(D24:D27)=D28 * Keep formulas short and clear, nothing "complicated", break it down * Explain clearly the basis for each forecast assumption

Question 25

Excel technique for building CF statement from scratch

Answer 25

(1) Start with "change in "&... for each item in BS (2) make sure it sums up to the correct amounts (3) then break the relevant items down into their components (4) rearrange into the 3 sections of the CF statement

Question 26

Shortcut for naming a cell

Answer 26

Ctrl + F3 (---> enters name manager)

Question 27

Can you name a range? How to refer to a specific cell in the range?

Answer 27

Yes, same like normal cell - use the @ sign to refer to the cell which is in the same column as the cell in the range (if it is a row) | @ is only for Excel Tables (structured references)

Question 28

Shortcut for data validation:

Answer 28

Alt + A + V + V (can also add message, next to settings)

Question 29

For scenarios, what Excel functions are commonly used?

Answer 29

Choose, offset, and (recommended) index

Question 30

Shortcut for pasting a named cell into a formula

Answer 30

F3

Question 31

How to see the output of a formula in a cell while "being somewhere else"?

Answer 31

With the "Watch Window" (under formulas)

Question 32

How to see the output of cells (Not Watch Window) - e.g. good for creating a dashboard

Answer 32

With the camera tool

Question 33

How to create a "control box" to e.g. easily select the scenario

Answer 33

Developer --> Insert --> Combo Box

Question 34

Whenever you use formulas like index that use ranges you may have REF errors when Excel uses index to make calculations (e.g. with tables) - in that case...

Answer 34

...put the @ sign in front of the formula

Question 35

Common control ratios for forecast to check for sales and capex

Answer 35

Capex / Depreciation & Capex / sales

Question 36

In excel formula, switch to "point mode" to change cell reference

Answer 36

Press F2 again

Question 37

Income from Associates

Answer 37

Equity Method: Recorded at cost Adjusted for net profits & dividends - and revaluations --> Net profits: Asset goes up; Dividends: Asset goes down

Question 38

Return on Invested Capital (ROIC)

Answer 38

Operating assets (excludes cash) minus liabilities (excluding debt and equity)

Question 39

Return on Capital (ROC) formula

Answer 39

Net Income / Total Assets

Question 40

Conditional aggregation - how does it work?

Answer 40

Question 41

two mechanisms in Excel for aggregating data:

Answer 41

* Pivot tables * Aggregate functions --> simple aggregates like sum(), and conditional aggregates like sumif()

Question 42

Simple and conditional aggregate functions

Answer 42

Question 43

Can you name ranges (e.g. for whole tables)? Also tables? Where can you see names?

Answer 43

Yes; Yes; and under Formulas --> Name Manager

Question 44

Can you reference columns in tables, e.g. for a sum() function?

Answer 44

Question 45

Easy-to-use buttons to quickly filter data on-the-fly for any dataset / table:

Answer 45

Question 46

Shortcut to freeze top row

Answer 46

Alt + w + f + r

Question 47

Answer 47

Question 48

Answer 48

Question 49

Date Time Functions

Answer 49

Question 50

Text Functions

Answer 50

Question 51

Should you take fair value or book value for debt in the EV/Equity bridge?

Answer 51

technically market value of debt

Question 52

Incorporating redeemable NCI into the bridge

Answer 52

Question 53

Easiest way to treat leases:

Answer 53

Deduct full amount as operating (so you don't have to worry about them in EV/Equity bridge, WACC calculation, etc.)

Question 54

If debt is trading above book value, and you want to find the yield on the FV of the debt

Answer 54

2030 ~midpoint where debt matures

Question 55

Formula for cost of equity

Answer 55

risk free rate + ERP * **levered** beta

Question 56

Shortcut for goal seek and example usage

Answer 56

Alt + A + W + G e.g. summing up cash flows, and have a plug, and want to set sum to "260", then set one cell to plug the sum

Question 57

What drives value in a stock?

Answer 57

Question 58

Forecasting frameworks?

Answer 58

--> "your cycle/trends forecasting" e.g. Zoom/Paypal, and "ripples" - e.g. Vertiv

Question 59

Factors that influence valuation

Answer 59

Question 60

70h deep dive into business - time allocation

Answer 60

Question 61

Constructing a thesis: What a thesis contains

Answer 61

Question 62

3 "general ingredients" for a good thesis

Answer 62

Question 63

First 12 hours on a stock

Answer 63

Question 64

Six example model use cases

Answer 64

Question 65

"Good" vs "Great" analysts - go levels deeper to "second order effects":

Answer 65

1) What are *contribution* margins doing over time? 2) What is growth over a multi-period *stacked* basis? 3) How do outputs compare to a single baseline (*alpha*)? 4) Can you organize into *cohorts* as opposed to averages? 5) What *set of factors together* explain performance? 6) What's the *growth rate of relative market shares*? 7) What is the *rate of change in the rate of change*? 8) What if you separate *fixed vs. variable effects*? 9) What if you *comp two diff 2nd order effects*?

Question 66

Clear cell formats/all (data, formats, comments)

Answer 66

Alt + h + e + f/a

Question 67

Outline/remove border

Answer 67

Ctrl + Shft + &/-

Question 68

Split screen

Answer 68

Alt + w + s

Question 69

Close Excel Help (and other task panes)

Answer 69

Ctrl + spacebar + c

Question 70

Sort a data range

Answer 70

Alt + a + ss

Question 71

Insert a chart

Answer 71

Alt + n + r

Question 72

Zoom

Answer 72

Alt + w + q

Question 73

Trace immediate precedents/dependents

Answer 73

Alt + m + p/d

Question 74

Remove tracing arrows

Answer 74

Alt + m + aa

Question 75

Inspect cell values (edit mode)

Answer 75

F9

Question 76

Switch to formula view

Answer 76

Alt + m + h

Question 77

Model deferred income taxes as % of ...

Answer 77

income statement taxes

Question 78

Effective tax rate a little below statutory for next few years - how to model that?

Answer 78

Simplified: Just model slight positive deferred taxes