Archive for the ‘Excel’ Category

Representing a corporate graphical profile in Excel is not always easy and can sometimes take a bit of work to get right. We have recently experimented a bit with the use of different fonts to improve the presentation of some recent financial models.

Using Webdings to insert new characters

To re-create the Corality ‘check’-symbol in the logo we created a list based on the Character ‘a’ and formatted as Webdings.

To insert a new symbol from an unusual font click Insert -> Symbol.

By choosing Webdings and the selecting the ‘check’ symbol we can achieve the a symbol that is similar to the Corality check.

Make sure to format the cells as Webdings to see the ‘a’ represented as a ‘check symbol’.

This is unlikely to revolutionize your financial modelling, but could be a nice effect for a financial model that needs to be prepared for professional printing, like an Information Memorandum of a pitch document.

EDIT: Enhanced functionality with built in colour formatting (Thanks Denver McCann at Numeritas for inspriration)

The image below should be read after you have read the discussion below (the comment from Denver McCann at Numeritas and my reply)

Custom Format with 'custom conditional formatting

I was having a coffee this afternoon with Darren Miller, the CEO of Sumwise and the author of their blog with the topic ‘our fascination with financial modeling’. We discussed the lack of collaboration functionality in Excel 2003 and 2007 and how there is an open whole for someone to fill when it comes to improving the current spreadsheet space.

Google have made a big effort in this sector but they are clearly aiming at the mass market of consumers and are not really catering for us professional users of spreadsheet software. Darren’s Sumwise has some interesting projects underway in this space and I think there could be some potential for future cooperation for us.

Has Excel got any functionality for collaboration?

The discussion with Darren reminded me of the Share Workbook functionality in Excel which I tested a couple of year’s ago. The Share Workbook functionality allows multiple users to work simultaneously in the same workbook without being ‘locked out’. When one users saves the file on the network gets updated and the other user gets informed of recent updated by other users upon saving the file.

This gives companies new options in terms of dividing projects between team members in ways not possible without this functionality. For example, one could ask a junior team member to code up the operational calculations of a financial model while the more senior person would focus on the cashflow waterfall and lock-up mechanics.

How to use the ‘Share Workbook’ functionality in Excel

It is pretty straight forward to test this functionality so I would recommend that you give it a go if you haven’t already tested it. Simply click Tools -> Share Workbook and you are half way there already. Check the ‘Allow changes by more than one user at the same time. This also allows workbook merging’ and you are done!

Excel Share Workbook functionality

Now, ask a colleague to open the same file from the network and have a play with performing updates and then saving the file. Excel will then identify areas modified by the other user - it is pretty good so far!

Excel Share Workbook - Advanced Options

So, what is the problem with Shared Workbooks?

Limited functionality in Excel Shared Workbooks

Well, there are two groups of problems with Shared Workbooks. The first one is well-known and properly documented in the Excel help and is the limited functionality available for Shared Workbooks.

From Microsoft Excel help

Workbook corruptions and freezing Excel

The second issue with Shared Workbooks is that Excel gets really unstable and often crashes or corrupts the file. This may not be too much of an issue if you are working on simple spreadsheets at home, but it you are working under time pressure on large financial models in the project finance or PPP sectors then this alone is enough to drive you crazy.

This massive limitation to Excel is also why we are not using the Share Workbook functionality at Corality even though it would in some cases have a massive positive impact. If Microsoft ever sorts this out in Excel 2003 I would be the first to congratulate them!

The built in functionality for Data Validation in Excel is quite useful in my view and a lot of people would benefit from improving their knowledge in this area. It gives developers the option to ensure that the inputs are of a certain category or type (like a date, integer, or from a data list). In financial models of the types we work with at Corality (project finance, M&A, corporate finance, valutations, etc) the most powerful Validation Criteria is List, in particular when linked to a Named Range (as it gives the ability to create off-sheet referenced data lists).

Setting up Data Validation in Excel

Using Data Validation of a List is very useful when you want to give a user the ability to choose between a number of cases (say for forward curves for oil prices) and you want to make it easily selected in a drop-down menu.

Data Validation in Excel dialog window

It is almost impossible to build a financial model in such a way that it is impossible for a user to input an incorrectly structured assumption. Even though the technical foundations for this has been included in Excel it means in practice that so much testing would have to be allocated to the strictness of the user interface that the mechanical areas of the model would suffer from lack of attention.

How much Data Validation should you use?

In my view on should focus the Data Validation to certain areas/structures of a model

• Selection of named cases (scenarios, prices, options, assets, etc)
• Whole number data validation in inputs used for LOOKUP (as there is otherwise a risk of the LOOKUP failing to find data)
• Time resolution data. For example if you are building a quarterly financial model and have an input for the sizing of the Major Maintenance Reserve Account as an input (coded with a SUM(OFFSET(…)) then you should restrict the user to only use 0,3,6,9,12 etc.

Data Validation - List Option

Applications of Data Validation to avoid

It is easy to go overboard with Data Validation and even though there are not strict rules of right/wrong these are some applications that I would recommend avoiding. The key thing to keep in mind is to test working with your own model. If you find your own Data Validation annoying then it is probably also annoying for other people- even if it adds integrity to your financial model. If you would like to take this one step further then ask a friend/colleague to answer a simple question with assistance of you new shiny financial model (like ‘what is the NPV of the investment if you were to update the assumptions A,B,C,D with X,Y,Z,W).

Want to learn more about Data Validation - more advanced?

• Check out Chandoo’s blog - Pointy Haired Dilbert - article ‘Advanced Data Validation’
• We have a full tutorial on the Corality corporate site ‘Data Validation in Excel’

You may not have noticed just yet, but today is actually day 40,000 in Excel and it probably warrants a tiny celebration for all Excel lovers out there. Sure, if you are working on a Mac this is not the case but then again chances are pretty low that you are an Excel lover…

With the next big Excel celebration not happening until 50,000 (21 November 2036) I have already taken the opportunity to make myself an extra strong instant coffee and give a toast to my favorite shortcut Ctrl+Shift+[.

James Rowe, our friend at Nu Numbers, started his preparations for the celebration of Excel 40,000 a long time ago. He also has a deeper understanding of Gregorian calendars than I will ever get and if you share those interests please contact James for an inspirational chat. James is also a very talented financial modeller with a background as the Global Head of Structured Finance at National Australia Bank.

It is not always easy to ensure that a financial model presents well on all screen types and resolution settings. I notice frequently while working on spreadsheets on four different platforms

• Office desktop (3 x 19 inch screens)
• Laptop 13.3 inch laptop
• Blackberry (2 inch screen)
• Home desktop (24 inch widescreen)

There is not one simple way of making sure that your financial model looks great on all platforms but one trick that often helps is the ‘zoom to selection’ functionality. This Excel functionality gives the user the possibility to zoom in on a selected range to fill the screen which is quite useful for disclaimers and cover sheets of financial models. Clearly not even this trick can improve the presentation on the blackberry, but I am sure you can live with that.

Manual ‘Zoom to Selection’ in Excel

To understand the functionality, select a range in Excel (say A1:N44) and then click ‘Selection’ in the Zoom-menu.

Zoom to selection in Excel - dropdown

Excel will then set the zoom-level to whatever is necessary to make the specified range fill the screen.

Automated ‘Zoom to Selection’ using VBA in Excel

The manual Zoom to Selection has limited value as it will only set the zoom level to be right just for your computer and since your clients and colleagues may have different settings they would have to redo it.

By using VBA code this process can be automated and it is quite simple.

Sub ZoomToSelectionDemo()

    'Select the range you want to zoom to
    Range("A1:N44").Select

    'Zoom to the selected range
    ActiveWindow.Zoom = True

    'Select range A1
    Range("A1").Select

End Sub

VBA code for zoom to selection

To see a working example of this, feel free to download one of our Excel Tutorials and have a look at the VBA code in the Disclaimer section.