Excel & Modelling

Question 1

Change 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

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