Have you created models which run into 20 – 30 years? You might have noticed that navigating to the last year (the last column) is probably the most boring part (and also the most time consuming part). Excel does provide you a shortcut (Ctrl + end), but that hardly works!
It’s been a while since we spoke and in this tutorial, I would like to make up for our lack of interaction by introducing a clever trick to cut down your time and effort in creating such models.
In most of the financial projections that we create for Project Finance, Project Management (Especially for long gestation projects), month on month projections, navigating to the last year/ month in such a sheet is a slow process. Typically you would have 100s of years/ months and you have the following choices with you:
· If you use Shift + Right key, you can easily take a quick nap by the time you reach the right cell.
· If you use Ctrl + Shift + Right, Excel will take you to the end. If you are planning to come back with Shift + Left Key, I suggest you have a comfortable pillow to sleep!
· I earlier used to resort to Ctrl + End shortcut key, but that does not work if your sheet has end characters placed at random places in your sheet.
The basic techniques do not work here!
The trick is to use a combination of Excel shortcuts and use the modeling process more intelligently. The first step is a manual process and can take the usual time – Creating a guiding row.
For example, in my model, I have created a row for Construction counter flag. For this row, I typically just write the formula and use Shift + Right key to navigate to the end of the model and use one of the following:
· Ctrl + R (Copy to the full row)
· F2 (to Edit), followed by Ctrl + Enter (Please note that it is not Ctrl + Shift + Enter)
· Copy (Ctrl + C) in the beginning and then press Enter (I avoid using Ctrl V to make sure that my clipboard is always empty)
Once we have the guiding row ready, we can use a combination of the excel shortcuts that we already know of. Let me show you the sequence:
1. Copy the formula
2. Navigate to the guiding row
3. Use Ctrl + Right Arrow to navigate to the end of the guiding row
4. Go Down one row (From the guiding row)
5. Use Ctrl + Shift + Left key to select and reach the beginning
6. Press Enter (or Ctrl + R Key) to fill all the cells
7. Final Shortcut Usage
Just like in this case we are using a row as a guiding row, I also use a guiding column to navigate quickly to the last column. What I would do is simple – Put a cross after the last column and then use Shift + Ctrl + Right key to navigate to the end.
I will speak about this trick in another tutorial!
Shortcuts are cool! They help you concentrate on the modeling process rather than waste time fiddling with the Excel. Which shortcuts do you use in your long models? Share and learn!
I have created a template for you, where the subheadings are given and you have use the functions to get the right values for you! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
Also you can download this filled template and check, if the information you recorded, matches mine or not! 
For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com
Chandoo.org has partnered with Pristine to launch a Financial Modeling Course. For details click here.
RSS feed for comments on this post. TrackBack URI
No comments:
Post a Comment