Updated October 17, 2023
Introduction to Excel Shortcuts to Audit Financial Models
In a research company, Ms. Annie, a trainee with a 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 determine the error.
Ms. Annie got frustrated and wondered, “Building a financial model is so easy, but auditing the financial model is tedious. I wish I could explore some awesome Excel shortcuts to audit financial models.”
In this thought, Annie went to her friend, Ms. Annika, a senior research analyst in the same company. Annie told her the problem, and Ms. Annika helped her explore some awesome Excel shortcuts to audit financial models. Let’s see what she taught Annie.
Financial Model and Excel Shortcuts
1. Ctrl page down / Ctrl page up
A financial model has different sheets to navigate. Using a mouse to navigate these sheets now and then can be a bit tough. So avoid using the mouse and use the keyboard shortcut CTRL+Page up OR Ctrl + Page Down to navigate different worksheets.
2. F2 Key
The F2 key of the keyboard is used if you want to know the following:
- The formula used to calculate a certain item
- The cells used to calculate the formula
- Edit the formula
Let’s assume you wanted to check which formula is used and which cells are considered to calculate the EBITDA.
Go to the desired EBITDA cell and click on the F2 key.
You will see something like this wherein the cells used to calculate have been highlighted by different colors. Now if you want to change the cell linked in the formula, I.e., to calculate EBITDA, if you’re going to use F8 instead of E8, 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 the ESC key on the keyboard.
3. Ctrl {
To ensure an effective financial model, link cells from different worksheets within the same workbook, not just linking sheets from one worksheet. If your balance sheet is not tallying due to incorrect linkages of interest expense, check which cell it is linked to. The interest expense of the income statement must be linked to the interest expense schedule, which is available in different worksheets.
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 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 workbook.
So, in this example, the interest expense will bring to the interest expense schedule, and the cell that got linked will be highlighted.
4. F5
Using the above shortcut key, you have reached the cell that got linked. But you have not completed the audit of the income statement completely, so you want to go back to the income statement and start from the same cell you were checking the linkage and completing the audit.
Again, now, what will you do? Getting the thought of doing it manually is a bad idea. To overcome this situation, use the F5 shortcut key.
For example, you used this ctrl +{ Shortcut and came to the page where it got linked and now go to the interest expense cell in the income statement you were linking again. Press F5 on the same sheet to see the cell it refers to, and click OK.
This will bring you to the cell it was referring to. Reference will generally refer to the previous cell you were at.
5. Ctrl ~
You can use this shortcut to see the formulas used together for the entire worksheet. So go to any worksheet cell 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, click on the desired Cell, and the linked cells 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 was delighted as she could explore some awesome Excel shortcuts to audit financial models. She thanked Ms. Annika for helping her. Ms. Annie returned to her financial model and easily went through the entire model using these shortcuts. She found her mistake, 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 shortcut has made it so easy.”
Recommended Articles
Here are some articles that will help you get more details about the Audit financial models, so just go through the link.