Excel & Modelling Flashcards
Change links in sheet (e.g. when copying a sheet from another file)
- 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
Access settings
Alt + T + O
Format as number
Shift + CTRL + !
Hide whole Excel file
Alt + W + H
Format as date
CTRL #
Preferred way to hide columns/rows
Grouping
Create group
Shift + Alt + Left/Right Arrow
Copy right
CTRL + R
Copy down
CTRL + D
To copy data from a PDF
Use Adobe, hold down ALT, then select with box data, then copy paste
Shift cells around, e.g. push all cells down or up
CTRL + “+” or CTRL + “-“
Select e.g. all hardcoded numbers from a couple of cells
F5 + Special
blue that banks use?
00255
The shortcut to re-calculate all formulae in the current workbook is
F9
Keyboard shortcut to save as is …
F12
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 + …)
Sumproduct
Which tax rate to use for a company?
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
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:
- Future tax rates, tax laws and accounting standards
- Firm’s growth rate
- Non-recurring items
Format for numbers in general - what to “choose”
“accounting” with no decimal places (there you can also easily add a $ sign if needed)
How to unmerge cells
Under alignment –> text control
How to format e.g. FY21?
Format as ‘FY’ yy
+/- sign in IS - when to use what
To keep it simply: Everything which is positive stays positive, rest with minus-sign
how to calculate stub (with formula)
=yearfrac(current_date, end_date)
Toggle case by using offset function
offset(current_cell,$case_number$, 0) –> the 3 case assumptions in rows directly below
