|
You have reached an old page that is no longer updated in this location.
You will be redirected to a new page automatically.
Please update your bookmarks!
If the redirect doesn't work for some reason,
please continue to the new
location.
Possible Problems in analysis or in Excel Model
Index
 Net
sales is in division level but EBIT is in group level
Quarterly
information is not available
Associated
companies' profit is reported before EBIT
What
to do if company tells nothing about taxes?
What
to do if company does not report depreciation?
How
much history data should I fill at minimum?
Company
does not tell the share of interest bearing debt
Fair
value is negative in the model
Strictly Excel-related problems:
I
have to change black output cell even though it is not
allowed
Circular
reference problem
How
to start new division or end existing one?
Visual
Basic Run-time error '1004': Method 'SaveAs' of object
'_Workbook' failed
I have to change black output cell even though it is
not allowed.
Sometimes you may think that you should override a formula
in a cell with black font, even though you know that the
rules deny it. The answer to this kind of problem is that
there is always an input cell that you can use. Here are
some examples:
Net sales or EBIT info fields in I-divQ sheet
You do not ever have to change info fields in I-divQ
sheet. If the quarter in question has actualized, the
cells get their values from cumulative figures. If it
is an estimate quarter, the cells use estimate fields.
So the info fields are output cells that tell you the
actual periodic figure, either based on cumulative or
estimate numbers.
Current year balance sheet
When the company reports interim results, you will get
new information about the balance sheet. Some parameters
(e.g. Other intangible assets) you can change directly
since they are input parameters in the balance sheet.
However, some others are output parameters (e.g. Inventories).
In the latter case do not still override the formula but
adjust the corresponding estimate parameter (Inventories
/ Net sales %) instead, if needed.
Goodwill (in balance sheet)
If the company's goodwill has changed during the current
year (or will change in the future) in a way that cannot
be explained with previous year goodwill and goodwill
amortization, use the field Additional goodwill in estimate
parameters.
Any other
The cases above are just examples describing that there
is always a way to use input cells. If you cannot figure
out how to do it, please do not hesitate to ask
from us.
Circular reference problem
When you open a model, you may see the following warning
message (click picture for a larger image):
Or while using the model, you may notice that the following
tool set has appeared:
Both refer to circular reference which is caused by the
loop that exists in
the model between income statement and balance sheet.
In order to solve the problem do the following:
- If it still exists, click 'Cancel' to the warning
message (if you accidently clicked Ok, Excel help opens;
you can close it).
- Choose from the menu: Tools - Options - Calculation.
- Check the iteration box.
- Set maximum iterations e.g. 10 and maximum change
0.001.
- Click Ok.
If you feel that the calculation gets slow, decrease
the amount of maximum iterations. The bigger the number,
the more accurately the model calculates but also requires
more calculation power from your computer.
Net sales is in division level but EBIT is in group
level
Should I estimate also net sales in group level?
When EBIT (Operating Profit) is only in group level,
it is perhaps not even worth of effort to follow either
net sales in division level. Of course it gives you some
guidance about the ability of the company to grow in the
future: if some divisions/products are growing quickly
and some are not, then the relative weight of the divisions
is interesting information.
However, more interesting is to know how profitable the
different divisions are. If you do not know it or you
cannot even estimate it properly using the current information,
then it is perhaps not worth of effort to follow either
net sales at division level in a small company.
I would like to estimate sales in divisional level,
what to do?
If you decide to follow the net sales in divisional level
then:
Enter division names normally but add one extra "division"
called Group (or Total etc.). Then use the actual divisions
for net sales and the Group division for EBIT.
The only limitation is that you cannot estimate the Group
division with EBIT-% (margin). However, you can avoid
also this problem by writing formulas to the absolute
cells: my EBIT estimate = my EBIT-% x total net sales.
Otherwise you can use the model normally.
Quarterly information is not available
If the company does not report in quarterly level, use
Q4 column in history data and estimates and leave columns
Q1-Q3 empty. If financial information is reported twice
a year, then use columns Q2 and Q4.
Remember that sometimes quarterly information can be
found in notes.
How to start new division or end existing one?
Sometimes division structure changes. The most common
cases are that company buys a new business group or sells
an existing one. Another case might be such that company
starts to report more detailed. Then you have to start
new (detailed) divisions and end the current group division.
Start new division
You can start a new division simply by writing a new
division name and filling history data or estimates for
it.
Just remember that you cannot use net sales growth %
as an estimate parameter during the first year. For example
if your growth estimate would be 5% then the absolute
estimate would become: 1.05 x 0 = 0. So, use absolute
net sales in first year.
End old division
Ending a division does not require anything. Just stop
filling new history data and estimates for the division.
However, keep the division name as long as there exist
values for this division in history.
Associated companies' profit is reported before EBIT
In normal case associated companies' profit or loss is
reported after EBIT; however, sometimes companies tell
it before EBIT. In the latter case you have two possibilities:
- Ignore the figure especially if it is not very big.
- When the figure is essential, you can make on own
division for it.
What to do if company tells nothing about taxes?
When company has not said anything about the taxes, they
can often be calculated from other figures.
Company often reports "Profit before tax" and
"Net earnings". Between those two there are
no other items than taxes and thus you can calculate taxes
simply:
Taxes
= Net earnings - Proft before tax
What to do if company does not report depreciation?
There are few things you can do:
- Quite often depreciation is announced separately from
income statement, either in notes or somewhere in the
text. So, you can search the word "depreciation"
and may find the value.
- For the fiscal year result announcement the depreciation
figure might be missing. However, companies typically
tell it afterwards in the official annual report. You
can take the figure from there.
- If you know the depreciation for full-year but not
for quarters, you can divide the full-year figure by
four, which allocates the depreciation equally to quarters.
Even though the values are not fully right, it is "allowed"
to make some own estimates; the historic EBIT figures
will still be correct. Besides, it is often quite easy
to estimate the quarter depreciation figures so you
will probably not make big mistakes hereby.
- We have also pumped into reports where depreciation
is not told directly but in cash-flow statement there
is "earnings before depreciation". With that
you can calculate depreciation.
- One last thing to do is just to leave depreciation
fields blank. In some industries depreciation is not
proportionally very big item, so it does not even harm
much.
How much history data should I fill at minimum?
This question can be splitted to two:
1. What should I fill in order to get model work fluently?
This means that you need a certain amount of data in
order to get model work.
See
the minimum filling requirements.
2. What should I fill so that the model would be meaningful
for other users?
It is hard to draw any specific guidelines here. Basically,
the more historic data, the better. However, often the
company has changed its structure so dramatically that
long history data loses its meaning.
If any dramatic changes has not happened, we would recommend
that every company would have at least two years history
data as complete: divisional (if company reports divisions)
and quarterly net sales and EBIT figures. Furthermore
more rough (income statement and balance sheet) historic
data should be for additional two years. So, althogether
4 years of historic data makes a decent model.
Company does not tell the share of interest bearing
debt
Case
A company reports long-term and short-term debt but not
how they are divided into interest and non-interest bearing
debt.
Solution
Even though there would be no information about the interest
bearing debt in the balance sheet, the company usually
tells the total interest bearing debt or the net
debt in the texts of interim report or fiscal year
announcement. Using these parameters you are able to estimate
the needed values. So you should search (Ctrl+F) for these
terms in the text.
Long-term debt
If you get the net debt figure, simply add cash
& equivalents (from the assets' side) to it in order
to have total interest bearing liabilities.
Interest bearing debt = Net debt + Cash & cash
equivalents
Then it is reasonable to assume that all the long-term
liabilities are interest bearing. And what remains
from them, can be allocated to short-term debt.
Short-term debt = Interest bearing debt - Long-term
debt
On the other hand, you may directly find total interest
bearing debt. Again, assume that all long-term
debt is interest bearing and allocate the rest to the
short-term debt.
Short-term liabilities
The part of total short-term debt that is thus not allocated
to short-term interest bearing debt can be allocated to
short-term non-interest bearing liabilities.
This allocation would perhaps not be exactly true, but
it is fair enough because the most important thing is
that total interest bearing debt is correct even though
the allocation of them to short-term and long-term would
be a bit inaccurate.
Detailed information in notes
Often the more detailed information can be found from
"notes to the financial statements" which can
be found from the annual report only. Thus normally from
late January up till mid-March there is only rough information
available from the balance sheet items and the analyst
has to do some assumptions, which can be later on refined
with annual report information.
Fair value is negative in the model
Case
The DCF fair value (and EVA as well) are negative even
though the estimates are quite positive and thus the cumulative
value of cash flows should be much higher than the value
of debt. However, the value of cash flow in terminal
year seems to be negative.
Reason
The reason is most likely that your terminal growth rate
(net sales growth percentage in the last estimate year)
is greater than WACC. The terminal value of cash flows
are calculated with the help of so called "Gordon
model" and it is both mathematically and practically
an illegal/insensible assumption that the growth rate
would be greater - or even close to - WACC.
Gordon has constructed his formula to calculate the value
of a firm that pays an infinite stream of dividends (cash
flow) when the dividend is growing at certain pace. Gordon
model states that value of an eternal cash flow = cash
flow / (r - g) , where g = growth rate and r= discount
rate. Thereby you cannot of course have g > r as it
would produce a negative output. Actually it is not a
sensible assumption that your growth rate (g) would be
even very close to discount rate (r) as you would get
an infinite value. And of course in real life there has
not been any case where a company would have increased
its dividend/profits with such growth rate even for decades,
not to mention eternally...
Solution
Drop your terminal growth rate to a more sensible level.
The suitable level is discussed separately at Step-By-Step
tour in general instructions section Estimating
long-term growth and profitability.
Visual Basic Run-time error '1004': Method 'SaveAs'
of object '_Workbook' failed
When you click the Update to Database button in Excel,
you may get the following error message:
Check C:\valuatum folder
Make sure that you have the following folders on your
local disk:
C:\valuatum\Excel2DB
C:\valuatum\jars
Also check that the Excel2DB folder mentioned above is
not read-only.
How to get the folders and files into them?
See
about setting Valuatum files in Excel2DB preparations.
|
