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

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.

The Finance Magic

Financial Modeling series:

Previous
Next Post »

A really appreciable content you put in your blog and detailed information you provide helps me to enhance my knowledge and skills. Further More Information About Advance Excel Training Institute in Delhi So Contact Here-+91-9311002620 Or Visit Website- https://htsindia.com/Courses/business-analytics/adv-excel-training-course.

Thanks 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

A big thank you for sharing this post your content is really good 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

Thanks for sharing this content its really a great post and very helpful thanks for sharing this knowledgeable content and if anyone looking for best java institute in delhi so contact here +91-9311002620 visit https://www.htsindia.com/java-training-courses

Your post is really good thanks for sharing these kind of post but if anyone looking for Best Consulting Firm for Fake Experience Certificate Providers in Noida, India with Complete Documents So Dreamsoft Consultancy is the Best Place.Further Details Here- 9599119376 or VisitWebsite-https://experiencecertificates.com/experience-certificate-provider-in-Noida.html

Your post is really good thanks for sharing these kind of post but if anyone looking for Best Consulting Firm for Fake Experience Certificate Providers in pune, India with Complete Documents So Dreamsoft Consultancy is the Best Place.Further Details Here- 9599119376 or VisitWebsite-https://experiencecertificates.com/experience-certificate-provider-in-pune.html

Thank you for sharing this great post its very helpful but if anyone looking for Experience Certificate Providers in Noida, India with Complete Documents So Dreamsoft Consultancy is the Best Place.Further Details Here- 9599119376 or Visit Website-Website-https://experiencecertificates.com/experience-certificate-provider-in-Noida.html

Thank you for providing thishelpful content its very knowledgeable i hope that you will continue to post these kinds of contents in future apart from that if anyone looking for Advance Excel institute in delhi so Contact Here-+91-9311002620 Or Visit Website- https://www.htsindia.com/Courses/business-analytics/adv-excel-training-course