Excel Model Update (Conversion)

What, when & why?

Updating your Excel model means that you update the model structure (formulas, macros, etc.) to the latest version available. (In this case it does not mean same as for example updating numbers in the model or updating numbers to the database.) After update you will have the same numbers as before but the latest properties. The changes we do to the model may be for example:

Change in an output sheet or a new output sheet.
Change in a formula (e.g. another way to calculate a key figure).
New estimate variable.
Change in macros or a new macro that offers a new property.

It is recommended to update the model regurlarly so that possible errors in the model will get fixed and you will get the latest properties. You can consider Valuatum Excel model as a program that requires updating same way as any other program.

One reason to update is that occasionally we make changes to the model formulas, we may for example add new estimate parameters. If you don't update your Excel model, these changes won't be available for you.

If you notice differences in the numbers in your Excel model and numbers in the web pages, the first thing you should do is to update the model and then upload the numbers to the database again.

How to update Excel model(s)?

You have two ways to update your Excel model(s):

1. Download your data to the latest model version

When to use?
This option is possible to use if

you don't have any own sheets in the model,
you don't have any links to external workbooks,
you haven't used User's own areas in the model.

So basically you can use this option if you have just the data in your model and you haven't done much of customizing.

Pros and cons

+ Updating is quick and simple (just download the data to the latest model).
+ Error risk is smaller (the second option may have problems with old models).
- The new model will contain only the basic data; e.g. external links will disappear.

Instructions:
1. Download the latest model version (latest empty model):

Go to the system's Analyst section.
Click Excel files button.
Download the latest empty model to your computer.

2.   Open the empty model in your computer.
3.   Run DB2Excel and select your company from the list.
4.   Save the file with another name.

As a result you will have the latest model version including the same estimates as you have currently in the database.

2. Convert your old model to the latest model version

When to use?
This option is recommended if

you have added own sheets to the model,
you have links to external workbooks,
you have used User's own areas in the model,
you have added many user comments to the model.

Compared to the first update option this option is needed when you have a lot of your own changes that you would like to remain in the new version too.

One good reason to use this option is that it enables updating multiple models at once. It also gives you a possibility to easily check if numbers have changed during the update.

Pros and cons

+ Your own properties (external links, own sheets, etc.) will remain in the new model.
+ You can update multiple models at once..
+ You can easily compare the old and new values using O-compare sheet.
- The process is a bit more complicated than in the first option.
- If you have a very old model, the conversion macro may not be able to update it properly.

Instructions:
1. Download the latest model version (latest empty model) and the latest conversion macro:

Go to the system's Analyst section.
Click Excel files button.
Download the latest empty model to your computer.
Download the latest conversion macro to your computer.

2.   Create a backup copy of your model(s).
3.   Open the conversion macro in your computer and close all other Excel files.
4.   Run the macro by clicking the button.
5.   First choose the empty model.
6.   Then choose the model(s) you would like to update.
7.   Finally set 0's to the waiting questions (0 h 0 min, i.e. start immediately).

The macro goes through each model one by one and copies the input values to the empty model. After the process has finished, you have models with the latest properties.

The process takes time about 20-40 seconds per file depending on the computer efficiency. Thus updating 10 models takes about 5 minutes.

After the update you are able to compare possible changes in the values in O-compare sheet. This sheet will appear during the update and you can remove it after you don't need it anymore. If you notice some significant changes in the values, you should compare the old and new version more detailed and see if anything has lost during the conversion. You can also always contact Valuatum if you have any problems.

Where to find the conversion macro and empty model?

You can find the latest empty model and conversion macro from the analyst pages (click the Excel Files button in the Analyst section).

More information about conversion macro

What does the conversion macro do?

Conversion macro is used when the latest version of the empty model is wanted to adopt. The macro has been developed so that it can convert in the same run as many excel models as wanted. All the user has to do is to point the empty model and the models to be converted.

The macro is quite big and it is not reasonable to explain too detailed its operation. Basically the macro copies all the input values from the old model to the empty model. This way the empty model is sort of built again. The formatting is also copied as much as possible. In the macro there are also some special properties like recognizing of quarter and read-only. These affect to the actions taken by the macro.

At the end of the macro the old model is closed and the empty model is saved as the name of the old model. If more than one file was choosed in the beginning, the macro starts to operate with the next model.

While macro updates the old models, it creates a log file. From the file you can see whether the conversion has succeeded, how much the fair value has changed and what is the difference of the new DCF and EVA fair values.

Each model takes about 20 seconds to be converted. The time depends mostly on the saving time used (local disk is naturally faster than network drive).

Picture of the conversion process

In practise the process goes so that first Valuatum or broker makes the changes to a file called empty model. The change can be e.g. a new output sheet or change in a formula anywhere in the model.

Then someone starts a macro and selects all the models that will be updated. The beginning of the process can be delayed e.g. to the late night so that no analyst work will be interrupted. When the macro finishes, all the selected models include the latest changes.

During the process the macro copies all the input values (history and estimates) and own sheets to the selected empty model.