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
“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
Best modelling practices:
- 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
Excel technique for building CF statement from scratch
(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
Shortcut for naming a cell
Ctrl + F3 (—> enters name manager)
Can you name a range? How to refer to a specific cell in the range?
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)
Shortcut for data validation:
Alt + A + V + V (can also add message, next to settings)
For scenarios, what Excel functions are commonly used?
Choose, offset, and (recommended) index
Shortcut for pasting a named cell into a formula
F3
How to see the output of a formula in a cell while “being somewhere else”?
With the “Watch Window” (under formulas)
How to see the output of cells (Not Watch Window) - e.g. good for creating a dashboard
With the camera tool
How to create a “control box” to e.g. easily select the scenario
Developer –> Insert –> Combo Box
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…
…put the @ sign in front of the formula
Common control ratios for forecast to check for sales and capex
Capex / Depreciation & Capex / sales
