|
Advanced Excel Information
This page offers answers for some Excel related advanced
questions. A plenty of Excel information is also available
at:
 Step
by step guide
Excel
manual
Index
Sheets
What
does "IO-sheets are mirror sheets of I-sheets"
mean?
Why
are the O-sheets not directly linked to the I-sheets?
What
is the Database-interface sheet?
Function of the model
Why
iterations is used?
How
iterations is used?
Parameter estimation
Financial
items
Macros
Year
Change - what does it do exactly?
How
does the macro 'FixInterestExpenses' work?
What
does the conversion macro do?
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 subitems cannot be estimated in the sheet (we
have concluded with the analysts that subitems are not
relevant at quarter-level).
In the I-main sheet you also have financial items for
the current year, but they are splitted to the four
subitems. Since we have different accuracy (in I-divQ
only "net financial" items and in I-main the
subitems) 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
subitems in I-main. These sub-items are: "Other
financial expenses", "Exchange rate differences"
and "Financial income" (so all the subitems
except "Interest expences").
The result of this computation is presented in an OUTPUT
figure "Interest expences" 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 subitems 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 expences
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 expences 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:
- Net financials would be split to sub-items also
in quarterly estimates. This would mean 16 different
values to estimate.
- Net financials would be only net financials also
on whole year level (without sub-items: int. expences,
fin. income etc.).
- 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 runned 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:
- At first the macro inserts new columns and thus
adds new estimate year to the end of the current estimate
period.
- Then the macro copies formulas to the added columns.
- 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.
- 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.
- The macro also runs the FixInterestExpenses macro
since the year change causes a problem in the loop.
- 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 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).
|
