Financial modelling techniques between Microsoft Excel and Excel for Mac
The shift by business professionals from using MS DOS/Windows to Apple Macintosh computers has been significant in recent years. It now presents unique challenges to financial modelling professionals, because not all of the assumed functionality on a DOS computer carries over to an Apple computer.
There are different file structures between MS DOS and Mac. Evidently the Microsoft Windows and OS X Mac operating systems differ, hence both shortcuts and keyboards are different. On the area of VBA macros, remember there are certain elements of VB script that are Windows specific, hence will not work if running the code in Excel for Mac.
When building a best practice financial model in MS Excel, there are some important factors to consider if end-users are Excel for Mac users.
ActiveX macros won’t work on a Mac
An easy mistake to make! All ActiveX macros in a financial model will need to be replaced with Form Controls, which really is not a big deal and will help reduce file size. Fortunately a move from ActiveX to Form Controls will result in a material reduction in the file size of the financial model.
Data Analysis Toolpak is not officially supported
Any use of the Toolpak in the model will require Mac users to install a free third-party add-in called StatPlus:mac LE from the AnalystSoft web site, because Microsoft currently doesn’t support that in a Mac. One caveat to remember is, given it is third-party software; Microsoft does not guarantee its reliability or performance.
“Allow Users to Edit Ranges” function doesn’t exist on a Mac
This great Microsoft Excel feature enables different users to create different permission (via passwords) to edit different ranges of cells. Sadly this function is not available currently on a Mac. Hence you will need to write VBA code to prompt a user to successfully enter a password, which will unlock the cell range and permit users to edit cells in this range.
Inserting Adobe Objects in Excel for Mac
The powerful feature of inserting adobe PDF documents into a financial model in MS Excel does not exist in Excel for Mac currently. The best advice is to create a hyperlink to the PDF file, which can be located on a computer, physical corporate server or cloud solution such as Google Drive, Dropbox or BitCasa.
Referencing the User Name property in VBA
This is an example of VB script that is Windows specific and will not work on a Mac. If you are composing VB code to create a keylog of a financial model’s activity and need script for “username” via the “ENVIRON$” function (which returns a PC’s environmental settings list), you will need to rewrite the code for the keylog to work on a Mac with “Application.UserName”.
The potential added work for a seamless financial model
Although Excel for Mac 2011 has greatly improved functionality that mirrors its Microsoft cousin, there are still many subtle aspects that can stump a financial modeller, when creating a seamless spreadsheet between the two operating systems. Although Solver is now available, Data Analysis Toolpak does not exist – even though there is a third-party solution. It is important to remember ActiveX macros, inserting of Adobe objects, editing ranges and Windows-specific VB script will not be compatible with a spreadsheet from Microsoft onto a Mac.