
As a Financial Analyst, your key goal, while building a financial model, is to improve its transparency and audibility.
Below are some of the Excel best practices while preparing financial models and list of important MS-Excel shortcuts
1. Color Coding: There are no hard and fast rules in assigning color codes to various inputs, however the below mentioned guidelines are mostly followed across the industry.
Types of cell |
Excel formula |
Color |
Hardcoded Numbers |
=1234 |
Blue |
Formulae / Calculation |
=H3*H2 |
Black |
Links to other sheets |
=Sheet2!D9 |
Green |
Different formula |
=H8/2 |
Red |
2. Sign convention: There are two approaches
a. All income should be positive and
all expense should be negative (Preferred)
b. All income and expenses should be positive while the formula will include negative sign
Line Item |
Option 1 |
Option 2 |
Revenue |
300 |
300 |
COGS |
200 |
(200) |
Gross profit |
100 |
100 |
3. Write simple
formulae: A
good financial model is the one which does not contain complicated formulae,
and not the other way around! A complicated formula should be broken down into
multiple cells.
a.
Always remember to keep the formula constant in the entire row for all the
years
b. Most formulae in a financial model will refer to i) a value
which corresponds to a period (for example, 2019) and ii) a metric (sales)
Example: =$M$9/N$3
4. Avoid
constants in the formula: Do not include hard coded numbers i.e. constants into a cell
reference. You'll likely forget there is an assumption inside a formula
Example: N45 * 0.23
5. Never
hide row or columns, instead the
best practice is to use group function (Shortcut key: Alt + D + G + G), Ungroup
function (Shortcut key: Alt + D + G + U)
When we group them then that particular row or column is not shown while viewing or printing the model and it also helps us while updating the model
6. Never use merge cells function instead we can use “Centre across selection” under “format cells -alignment”. As when we merge cells there are a lot of restrictions like we cannot drag formulas or we cannot insert sort and filter function which is one of the important function in financial models.
7. Macros for the most widely used functions while building a financial model. Make sure you set the desired macros before and while preparing the model
a. Currency ($, INR etc.)
b. Decimal points (no decimal, one
decimal etc.)
c. Multiples (P/E of 8.9x, P/B of
1.20x)
d. Color coding (input cells,
calculation cells, linked to a different sheet, warning cells etc.)
e. Separator rows and columns
8. Always try to connect cells if
possible: For example, current year’s opening
balance should be connected to the closing balance of previous year
9. Never use long names for worksheet
instead use shortest sign possible
like using “IS” for Income statement, BS for balance sheet, CF for cash flow, COGS,
Revs for revenue, etc.
10. Last but not the least, Excel Shortcuts: While its extremely important to be fast and efficient at the same time while preparing models and hence excel shortcut comes into picture. Some important functions are given below
Shortcut Key |
Function |
F2 |
Edit cell |
F4 |
Repeat
last action |
Ctrl + Z |
Undo last action |
F9 |
Recalculate
workbook |
Alt + W + F + F |
Freeze panes function applies to the selected cell |
SELECTION |
|
Ctrl + Arrow key |
Jump to edge of content |
Ctrl + spacebar |
Select
column |
Shift + spacebar |
Select row |
Alt + I + R |
Insert
row |
Alt + I + C |
Insert column |
Alt + E + D |
Delete
row / column |
F5 |
Go to |
Shift + F11 |
Insert
worksheet |
Ctrl + Page up / page down |
Move to next / previous worksheet tab |
Alt + E + L + M |
Delete
worksheet |
Alt + O + H + R |
Rename worksheet |
FORMATTING |
|
Ctrl + B |
Bold |
Ctrl + I |
Italics |
Ctrl + U |
Underline |
Ctrl + D |
Copy
formula down to fill section |
Ctrl + R |
Copy formula right to fill section |
Alt + E + S |
Paste
special |
Ctrl + L |
Open formatting dialogue |
Ctrl + shift + 7 |
Outline
cell |
Ctrl + shift + - |
Remove cell border |
Alt + O + C + A |
Autoformat
column width |
Alt + O + R + A |
Autoformat row height |
Alt + D + E |
Text
to column |
Alt + D + F + F |
Filter data |
Alt + D + S |
Sort
data |
Alt + O + D |
Conditional formatting |
Ctrl + Shift + $ |
Apply
currency format to the selected cells |
CELL NAVIGATION |
|
Ctrl + Arrow keys |
Shows
the end of range |
Shift + Arrow keys |
Select a specific range |
Shift + Ctrl + Arrow keys |
Highlight
the entire range of data |
Home |
Move to beginning of line |
Ctrl + Home |
Move
to cell “A1” |
Shift + Enter |
Move to cell above |
Tab |
Move
to cell to the right |
Shift + Tab |
Move to cell to the left |
Backspace |
Delete
cell and start writing |
Ctrl + ~ |
Show formulas/Values |
If you are not aware than this post is a part of the Financial Modeling Series. To check the previous post on the Basics of Financial Modeling, Click here. Also, the list will keep updating so kindly drop your email id in the comment section below to be notified as and when the new addition is made to the series.
Below is the snapshot of the three financial statements:
Three Financial statements (Financial Modeling Process)
1. Income statement:
Revenue |
- Cost of goods
sold (also known as direct cost) |
= Gross Profit |
- Operating
expense (also known as indirect or office expense) |
= Net operating income (EBIT) |
- Non-operating
expense and interest expense |
= Pre-tax income (EBT) |
- Income tax |
= Net income (PAT) |
* One important term EBITDA i.e.
nothing but we add back non-cash expenses to EBIT. EBITDA is used to understand
the actual cash profitability of the business.
2. Balance sheet: Total assets +
(total liabilities + equity)
* Total
assets= Non-current assets + current asset
* Total
liabilities + equity = Non-current liabilities + current liabilities + equity
3. Cash flow statement: Cash flow from operating activities + cash
flow from investing activities + cash flow from financing activities = Net
change in cash i.e. Cash balance, we find on balance sheet)
This was all about Excel best practices one should keep note of while preparing financial models and snapshot of three financial statements. Keep checking out our latest post for the next part of the financial modeling series which will be out any soon.
Thanks for reading
The Finance Magic
Financial Modeling series:
2. Excel best practices while preparing financial models | List of important MS-Excel shortcuts
3. Things to understand before buildingthe financial model {NEW}
2 comments
Click here for commentsThanks for posting these kinds of post its very helpful and very good content a really appreciable post apart from that if anyone looking for best Core and Advanced Java training institute in delhi so contact here +91-9311002620 visit https://www.htsindia.com/java-training-courses
ReplyThank you for sharing an amazing & wonderful blog. This content is very useful, informative and valuable in order to enhance knowledge. Keep sharing this type of content with us & keep updating us with new blogs. Apart from this, if anyone who wants to join the SAS Training institute in Delhi, can contact 9311002620 or visit our website-
ReplyBest SAS Training Institute in Delhi | Best SAS Training Institute in Noida (htsindia.com)
Please do not spam ConversionConversion EmoticonEmoticon