Advanced Excel Information
This page offers answers for some Excel related advanced questions.
Index
Sheets
Function of the model
Parameter estimation
Macros
Sheets
What does "IO-sheets are mirror sheets of I-sheets" mean?
It means that IO-sheets include the same information as I-sheets. The information comes with VLOOKUP functions on the basis of row (index number) and column (year/quarter).
(See the following question for more info.)
Why are the O-sheets not directly linked to the I-sheets?
The reason is year change operation. When you run the YearChange macro, it removes in the I-sheets the latest year and adds one estimate year so that the model would stay similar year after year.
In O-sheets it is reasonable to assume that there is one column that always refers to the current (ongoing) year. Now when there are columns removed in the I-sheets, direct links in an O-sheet would mean that columns would not change year.
However, when we use IO-sheets in between, we can always have the current year in a same column in the O-sheets.
What is Database-interface sheet?
Database-interface is a sheet where all the input figures are collected from the IO-sheets (originally from the I-sheets).
(Input figures are such figures that a user must enter him/herself, i.e. the model cannot calculate them using other figures. An example: net sales is an input figure, ROE % is an output figure, since it is calculated on the basis of the other parameters.)
The input figures continue from the Database-interface sheet to the Valuatum Database when a user starts a macro called DBUpdate. When the update process finishes, all the customers can see the model in ValuModels.
Function of the model
Why iteration is used?
When income statement and balance sheet are estimated, there is always a problem with a loop:
Interest expenses are estimated with a parameter that checks debt, debt depends on the balance sheet total, balance sheet total depends on equity, equity depends on earnings, and earnings depend on interest expenses.
Iteration must be used so that the model would work even with this loop.
How iteration is used?
Iteration can be set from the menu: Tools - Options - Calculation - Iteration. There you can define the rules for the iteration.
If there is an error inside of the loop (like always in the beginning when you start filling an empty model), the iteration does not work anymore. The problem can however be solved using FixInterestExpenses macro.
Parameter estimation
Financial items
In the I-divQ sheet net financials are estimated quarterly. Thereby sub items cannot be estimated in the sheet (we have concluded with the analysts that sub items are not relevant at quarter-level).
In the I-main sheet you also have financial items for the current year, but they are divided into the four sub items. Since we have different accuracy (in I-divQ only "net financial" items and in I-main the sub items) in different sheets, we have to make sure that there is consistency in the estimates at different levels.
Therefore we have taken the sum of quarterly net financial items and subtracted from it the three net financials sub items in I-main. These sub-items are: "Other financial expenses", "Exchange rate differences" and "Financial income" (so all the sub items except "Interest expenses"). The result of this computation is presented in an OUTPUT figure "Interest expenses" in the I-main sheet.
Why this kind of arrangement?
Well, for the user everything is now fairly simple: If (s)he does not want to go into details, (s)he can only estimate the quarterly net financials and does not have to do anything in I-main. In this case the value in I-divQ is totally allocated to interest expenses, which is probably not so bad assumption.
If the analyst would like to estimate net financials in sub items in whole year level (as (s)he normally does with big companies), then it is also possible. It is even quite handy as the quarterly and whole year estimates go automatically hand in hand: If you change net financial estimates in I-divQ, then also the estimates in I-main change automatically. For example, if you change financial income estimates in I-main, then your interest expenses item in I-main decreases automatically to ensure that estimates in I-divQ and I-main are consistent. Of course the automatic decrease in interest expenses in I-main might be something that the analyst would not like to do, but as the items are side by side, it is easy to notice and also very easy to change back by adjusting the net financials in I-divQ. If this kind of connection would not be there, then the estimates would be easier different in different sheets.
Another possibilities would be that we would have one of these:
1. Net financials would be split to sub-items also in quarterly estimates. This would mean 16 different values to estimate.
2. Net financials would be only net financials also on whole year level (without sub-items: int. expenses, fin. income etc.).
3. The consistency of different estimates (quarter level and whole year level) would be manually done. Then there would always be a great danger of human mistakes and inconsistency.
Macros
Year Change - what does it do exactly?
YearChange macro have to be run so that the structure of the model remains same year after year (current year stays in the same column etc.).
All the changes are done in the I-sheets. The following things are to be done:
1. At first the macro inserts new columns and thus adds new estimate year to the end of the current estimate period.
2. Then the macro copies formulas to the added columns.
3. Since the first year will be removed in both I-sheets, all the links to these cells will be destroyed. In order to remain the values (and avoid #REF), macro first copies cells and then makes 'paste as values' to the same cells.
4. There are many places in the model where in one cell there is a formula and in the following cell (year) there is an input figure (or the opposite). These limiting places have to be fixed to correspond the new lay-out: the macro copies and pastes formulas in these kind of cells.
5. The macro also runs the FixInterestExpenses macro since the year change causes a problem in the loop.
6. At the end the YearChange macro removes cells in the left and so the latest history year is removed in every I-sheet.
If you now have to do the year change in practice, please follow the Year change instructions.
How does the macro 'FixInterestExpenses' work?
At first the macro sets values in interest expenses and financial income to be zero. This is done for the first problematic year. The operation fixes the year in question.
Then the formulas are copied from the following year to the first problematic year. This does not cause the error any more.
Then all the following estimate years are set to be zero, which removes the errors from those years. After that the formulas are copied from the first problematic year to the rest of the years and now the loop has been started again.
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 chosen 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).
