A |
|
Absolute Referencing |
56–64 |
Array |
26 |
Array Function |
53 |
Audit |
298–299 |
B |
|
Best Practice |
117–123 |
C |
|
Capital Expenditure |
230 |
Circular references |
243–244 |
Conditional Formatting |
77–80 |
Consistency |
118 |
Control Accounts |
181–183 |
Costs of Goods |
218 |
D |
|
Data tables |
96–105 |
Data validation |
90–95, 225 |
Dates |
48–51 |
Debt |
241 |
Depreciation |
|
Declining |
231 |
Declining balance |
263 |
Diminishing Value |
231 |
Reverse |
236 |
Straight Line |
230–236 |
Sum Of Digits |
231 |
Usage Basis |
231 |
Dividends |
273–278 |
E |
|
Equity |
269 |
Errors |
|
Checks |
198, 200 |
Error traps |
7 |
IFERROR |
10–11 |
F |
|
Financial Statements |
|
Balance Sheet |
174, 197–198, 281 |
Cash Flow Statement |
175, 196 |
Cash Flow Statement - indirect |
286 |
Income Statement |
171, 193–194 |
Linking |
179, 202 |
Opening Balance Sheet |
199–200, 280 |
Flexibility |
121 |
Formula |
|
Length |
3 |
Functions |
|
Array function |
53 |
CELL |
137–139 |
CHOOSE |
33–34 |
COLUMNS |
22 |
COUNT |
6 |
DAY |
49 |
EDATE |
48–51 |
EOMONTH |
48–51 |
FIND |
138 |
HLOOKUP |
21, 30 |
IF |
7 |
IFERROR |
10–11, 139 |
INDEX |
30–32 |
Index Match |
32 |
INT |
39 |
LEFT |
138 |
LOOKUP |
26–29 |
MATCH |
30–32 |
MAX |
52–53 |
MID |
138 |
MIN |
52–53 |
MOD |
9, 39–47 |
MONTH |
49 |
NOT |
8 |
OFFSET |
35–37, 43–47, 63–64 |
OR |
8 |
RIGHT |
138 |
ROUND |
153 |
SUM |
4–6 |
SUMIF |
12 |
SUMIFS |
14–15 |
SUMPRODUCT |
16–20 |
VLOOKUP |
21–25 |
YEAR |
49 |
G |
|
Goal Seek |
106–114 |
H |
|
Hyperlinks |
115–116 |
I |
|
IF |
7 |
Flags |
9 |
Inputs |
|
Entering |
194, 209 |
Interest |
244, 250, 253–255 |
M |
|
Macros |
75–77, 113–114, 162–163 |
Market Value |
312 |
Model layout |
134 |
N |
|
Named ranges |
81–89 |
Number formatting |
27, 65–70 |
O |
|
Operating Expenditure |
224 |
Options |
|
Advanced |
132 |
Formulas |
128 |
New workbook |
127 |
Save |
130 |
P |
|
PivotTables |
6 |
Q |
|
Quick Access Toolbar |
132 |
Quick Analysis |
125 |
R |
|
Range names |
81–89 |
Ratios |
301–314 |
Asset Management |
306 |
Debt |
308 |
Equity |
310 |
Liquidity |
304 |
Profitability |
302 |
Revenue |
208 |
Calculations |
213 |
Robustness |
120 |
S |
|
Self-Review |
295 |
Shortcuts |
|
SUM |
4 |
Single entry accounting |
279–281 |
Solver |
109–114 |
Styles |
71–76 |
T |
|
Tax |
256 |
Deferred tax |
258–260, 263–264 |
Template |
159–169 |
Time series |
186–187 |
Transparency |
122–123 |
V |
|
Vector |
16, 26 |
Volatile functions |
38 |
W |
|
Working Capital |
210 |
Whilst I put together a companion book on Financial Modelling, it occurs to me that we should pass on a few more “tricks of the trade” regarding building financial models. May I suggest that you take the time to look at the website of our company (SumProduct) www.sumproduct.com and avail yourself of daily tips, tricks, ideas and blogs.
For example, I talk on several occasions of copying a formula consistently across a row. That’s fine when there are just a few columns, but when there are more rows, it can be-come cumbersome. Well, here’s a useful trick.
Sometimes when modelling you need to copy a formula across many columns (e.g. building a 20+year monthly forecast model). Dragging a formula across using the mouse on a regular basis could lead to a claim for Repetitive Strain Injury. Unless these cells are al-ready non-blank keyboard shortcuts such as CTRL + SHIFT + Right Arrow appear useless. However, all is not lost. The following trick can be used on many occasions.
Imagine you needed to copy formulae across columns J to XZ (say):
•Select the column TWO columns to the right of the last column required (here, this would be column YB)
•A quick way to get there would be to press the F5 function key and then type YB1 + ENTER to go to cell YB1 and then press CTRL + SPACEBAR to select the column YB
•Next, highlight all columns to the right (assuming these are all blank) using CTRL + SHIFT + Right Arrow
•Right click on the mouse and select ‘Hide’ (this will hide columns YB to the end)
•Now return to where the formula will start (say, cell J9)
•Type the formula in and press ENTER
•Select the cell again and then use the keystroke CTRL + SHIFT + Right Arrow which will highlight cells J9:YA9 inclusive
•Having removed all fingers from the keyboard press SHIFT + Left Arrow which will re-duce the range to cells J9:XZ9 inclusive
•Having removed all fingers from the keyboard once more, press CTRL + R to fill the formulae into all cells simultaneously.
Practice will make this second nature!
Three points to note:
1.On first glance, there appears to be no reason to retain a blank column. However, if column XZ is the final column, CTRL + SHIFT + Right Arrow will take you straight to column XZ. However, if you fill in this point, in some versions of Excel, all of the hidden columns will have formulae copied into them too, leading to potential model errors and needless file size bloating.
2.Some modellers will hide or group columns of populated data instead of using the above technique. The problem with this approach is that whilst these columns are hidden, errors may occur in these columns which are only picked up when the fields are made visible once more. This can lead to tremendous re-work which could have been avoided if the developer had inspected ranges periodically instead.
3.CTRL + R will not always retain all formats. If this is important, copy the formula (CTRL + C) before highlighting the whole range and then paste in the usual way (CTRL + V) instead.
Finally, let me end on an extremely pragmatic note. I quizzed my colleagues about what we considered to be the most useful keyboard shortcuts for financial modelling (on a PC). This is what they came up with. Yes, it’s an entirely subjective list, but hopefully, you’ll find one or two useful shortcuts below. Enjoy!
There are over 540 keyboard shortcuts in Excel. For a comprehensive list, please down-load the Excel file at https://www.sumproduct.com/thought/keyboard-shortcuts?L=0.
Also, as I mentioned before, check out the new daily Excel Tip of the Day feature on the www.sumproduct.com homepage.