Explore some awesome excel shortcuts to audit financial models

In a research company, Ms. Annie, trainee, with the basic knowledge of excel has built a financial model but unfortunately the financials of the model did not tally.

Ms. Annie went to every cell to find out where the model went wrong but got confused going cell wise and failed to find out the error.

Ms. Annie got frustrated and was wondering “Building a financial model is so easy but auditing the financial model is a tedious task. I wish I could explore some awesome excel shortcuts to audit financial models.”

In this thought Annie went to her friend named Ms. Annika who was working as a senior research analyst in the same company. Annie told her the problem and Ms. Annika helped her to explore some awesome excel shortcuts to audit financial models. Let’s see what did she taught Annie.

Financial model and Excel Shortcuts

• Ctrl page down / Ctrl page up

A financial model has different sheets to navigate.Using a mouse to navigate through these sheets every now and then can be a bit tough.So avoid using mouse and use the keyboard shortcut CTRL+Page up OR Ctrl + Page Dwn to navigate to different worksheets.

• F2 Key

F2 key of the key board is used if you want to know:-

• The formula used to calculate a certain item
• The cells used to calculate the formula
• Edit the formula

Recommended courses

Let’s assume you wanted to check which formula is used and which cells are taken into consideration to calculate the EBITDA.

For this go to the desired  EBITDA cell  and click on F2 key.

 

You will see something like this wherein the cells that are used to calculate has been highlighted by different colors. Now if you want to change the cell linked in the formula I.e. to calculate EBITDA if you want to use F8 instead of E8 then just click in the highlighted rectangle and drag and drop to the required cell. The changes will be incorporated

To come out of it Click on any cell of the Worksheet or click ESC key of the key board.

• Ctrl {

An effective financial model does not have sheet linked from one worksheet only but it has cells linked from different worksheets from the same workbook as well.

Imagine a situation where your balance sheet is not tallying due to wrong linkages of interest expense and you need to check which cell it got linked to. The interest expense of income statement needs to be linked from interest expense schedule which is available in different worksheet.

What will you do to overcome this situation? Will you check the cell number it got linked to and go to that worksheet and manually find which cell it was? Don’t you think this is time consuming and confusing as well?

To overcome this Use the shortcut CTRL + {.This shortcut will bring you to the cell it got linked from.

 

Now you need to check from where did interest expense got linked so go to the cell you want to check then use the shortcut key Ctrl + { . This shortcut will bring you to the cell it got linked to from any worksheet available in the same work book.

 

 

 

 

 

 

So in this example, for the interest expense it will bring to the interest expense schedule and the cell which got linked will be highlighted.

• F5

Using the above shortcut key you have reached to the cell which actually got linked. But you have not completed the audit of income statement completely so you want to go back to income statement and start from the same cell you were checking the linkage and complete the audit.

Again now what will you do? Getting a thought of doing it manually is a bad idea. To overcome this situation use the F5 shortcut key .

For example , you had used this ctrl +{ Shortcut and came to the page it got linked and now to again go to the interest expense cell in income statement which you were linking  Press F5 on the same sheet  you will see the cell it is referring to and click ok .

 

This will bring you to the cell it was referring. Reference will generally refer to the previous cell you were at.

• Ctrl ~

This shortcut is used where for the entire worksheet you want to see the formulas used together. So go to any cell of the worksheet where you want to see only the formulas and press CTrl +~ .  This is how it would appear  

To check the cells linked to calculate  EBITDA just click on the desired Cell and the cells that are linked will get highlighted.

To come back to numbers do the same procedure again go to any cell of the same worksheet and press Ctrl +~

Ms. Annie got highly delighted as she could explore some awesome excel shortcuts to audit financial models. She thanked Ms. Annika in helping her.

Ms. Annie went back to her financial model and went through the entire model using these shortcuts very easily. She found her mistake and she rectified it and was very happy as her financial model got tallied.

Ms. Annie with a sigh of relief wondered “Auditing a financial model was definitely a tedious job but this awesome excel shortcuts has made it so easy.”

Explore some awesome excel shortcuts to audit financial models, Infographics

Learn the juice of this article in just a single minute, Explore some awesome excel shortcuts to audit financial models Infographics

Recommended Articles

Here are some articles that will help you to get more detail about the Audit financial models so just go through the link.

1. Pivot Chart in Excel
2. Useful Features of 2016 Excel Workbook
3. Advanced Excel Formulas and Functions