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)?
Instructions:
1. Open the Excel model that you would like to convert
2. Press the ‘Upgrade model’ button on the I-Main sheet
3. Login using your Valautum credentials and select a download folder for the needed files. When you are done click on ‘Continue’
4. After the files are finished downloading, if you only want to upgrade one model click OK. If you want to add more models to be converted, click cancel and add the models using the ‘Add’ button and then press the ‘Start conversion’ button
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.
On some machines this method does not work due to incompatibility issues with older VBA code. In this case you have to convert models manually using the instructions below. This issue has been fixed in the new version so in the future you will be able to use the normal upgrade method.
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 (Note! Click the link with Mouse2 and choose “Save link as …”)
- Download the latest conversion macro to your computer (Note! Click the link with Mouse2 and choose “Save link as …”)
2. Create a backup copy of your model(s).
3. Open the conversion macro in your computer and close all other Excel files.
4. First select the new Excel model that you downloaded into the ‘Select new emptymodel’ by clicking the ‘Browse’ button.
5. Then choose the model(s) you would like to update by clicking the ‘Add’ button.
6. Press the ‘Start conversion’ button
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.