Excel best practices while preparing financial models | List of important MS-Excel shortcuts ~ The Finance Magic – Stock Market | Personal Finance

Reading Time: 5 minutes


 

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:

1. What is financial modeling – It’s scope, uses, types, and how to build a financial model? | Explained

2. Excel best practices while preparing financial models | List of important MS-Excel shortcuts

 

We’re dedicated to providing you vast investment knowledge, with an emphasis on making you understand the “WHY” in finance. Our aim is to spread financial literacy and show you how magical the world of finance is?



Source link

Every Wednesday and Saturday, we send Info-Graphic and FinMedium Weekly Digest newsletters to our 25000+ Subscribers.

Join Them Now!


The Finance Magic

The Finance Magic

We're dedicated to providing you vast investment knowledge, with an emphasis on making you understand the "WHY" in finance.
Please Share :)