Keep versions under control

One simple technique to avoid outdated or erroneous information is to develop a file nomenclature. The easier it is to understand it, the better as the process normally requires the participation of various people who can be less adept to complicated names. I usually follow this one:

Mymodel_V5_AS.xlsx

This is very easy to understand: File name (underscore) Version number (underscore) My initials.

You can use this technique if have to ask for a revision of a certain element, for example, the latest sales forecast. The person would revise this file and create his own version of the model. The next version could be something like this:

Mymodel_V6_JS.xlsx

And so on...

You could also add the date to the nomenclature, if necessary. However, as mentioned before, the simpler the better. It is likely that not everybody will respect more complicated naming rules and, besides, if you have this file linked to other files, it will be harder to update the links.

Avoid links as much as possible or make sure they are periodically updated

The first thing you should consider when working with linked files in a complex business model is to avoid them like a disease. Think of external links as a non-value-added-complexity-when-the-model-became-too-big-too-complicated-and-you-are-under-extreme-time-pressure-to-present-the-final-conclusions-to-the-board-of-directors.

If you can have the whole model in a single workbook you will avoid broken links and/or the hassle of updating them regularly.

This is especially true if you have to send files by email to lots of people to be revised and receive them back. You will get a full mailbox (which will be heavy on your server) and probably some broken links to fix.

Another problem with links is that they make formula expressions very long, especially when the source files are closed. If you need to use sophisticated formulas in your model, it can be quite daunting to evaluate its accuracy with long links. Look at the following example to see how a simple formula used to pick the value of cell E4 in tab 'Data' looks like.

Without external links it looks like this:

=Data!E4

But with external links like this:

='C:\Users\Ana\Desktop\sheets\[balancesheet2011_.xlsm]Data'!$E$4

If you really wish to use links to other spreadsheets, however, you will have to check if they are working and updating them as the source changes. Here's how to do this:

1. Go to Data
2. Click Edit Links

A dialog box will open where you can see a list of all existing sources of the links in the workbook.

You can now choose to check the status of a link by clicking on the button 'Check Status'. In the example below you can see that the link to file balancesheet.xlsx is broken.

You can also see that in this workbook links are updated automatically. If you wish to have a better control over the links of your spreadsheet you may choose the option "Manual" in the radio button at the bottom of the dialog box.

If you wish to fix the broken link, click Change Source... and indicate where the source file is located.

The link will now be fixed and automatically updated. Problem solved!