This document provides you with information on using the Valuatum Excel Model.
1.1 DIFFERENT SHEET TYPES
There are four different sheet types in the Valuatum Excel Model:
These sheet types are important for the analyst:
I-sheets (I-divQ, I-main, I-investments) – Used for imputting values by the analyst
O-sheets – Broker specific sheets that can be modified
(I refers to ‘Input’, O refers to ‘Output’.)
Sheets that the analyst does not have to care about:
IO-sheets (IO-divQ, IO-main) – These are mirror sheets of I-sheets. IO-sheets exists so that changing year would be easier.
Database-interface – A sheet where all the important figures are collected for sending them to the Valuatum Database.
Besides of four main sheet types, you can have own sheets (see section 7.4 Own sheets and workbooks) for making more detailed estimates.
Information path through the different sheets:
1.2 MEANING OF DIFFERENT COLOURS
Please, keep these basic rules in mind:
You can change a cell if it is blue.
Black cells should not be changed.
When a blue figure is in a white background, it is an estimate. And when it is in a grey background, it is a history figure.
In some cells you can also see light blue colour. It means that we have made a formula to ease your work but you can still override it by entering an input figure.
The main reason for why you should not touch black cells is that the conversion macro copies only input values.
2 Parameter estimation
2.1 INCOME STATEMENT
Chapter 2.1 tells you how to estimate income statement parameters. At first this is explained for different sheets and then for different parameters.
2.1.1 I-divQ sheet>
I-quarter is a sheet where you can handle income statement information on the quarterly level.
When starting to use this sheet you should first do the following things:
Enter division names of the company to the cells E9:E18 (max 10 divisions). Remove the divisions you don’t need.
Enter “x” to the row 4 for every actualized quarter in current year.
Input history figures to the cumulative part (net sales, ebit and income statement beginning from the row #157). If there is a formula in the cell, you can override the formula whenever the font is in blue colour.
Input estimates to the periodic parts (to the ‘ESTIMATE’ fields, see section 7.6 Estimate and info fields). For net sales and ebit, fill the quarters at least for current year, the rest of the estimates can be in full-year level. For income statement, only current year quarters are needed; the other years are estimated in the I-main sheet.
ABSOLUTE OR RELATIVE (net sales and ebit)?
You have two ways to estimate net sales and ebit: absolute and relative figures. Just remember the following rule:
Absolute figures dominate relative figures …which means: If you have entered any figure to an absolute cell, the corresponding relative estimate is ignored.
Note: If you need some extra area in order to better estimate a value for one cell, please use user’s own area or own sheets (see sections 7.3 User’s own area and 7.4 Own sheets and workbooks).
-See what is the difference between Estimate and Info fields in section 7.6. Estimate and info fields.
QUARTER OR FULL-YEAR (net sales, ebit, income statement)?
After the first estimate year you have two ways to estimate net sales and ebit: quarter and full-year level. Just remember the following rule:
Quarter figures dominate full-year figures …which means: If you have entered any figure to quarter-level, the same year’s corresponding full-year estimate is ignored.
2.1.2 I-main
The first year of income statement is estimated mainly in the I-divQ sheet. All the other years (as full-year level) are estimated in the I-main sheet, either as directly to income statement or with a help of an estimate parameter.
If a parameter is in blue colour (e.g. other financial expenses, minorities), you can directly enter it to the income statement. Otherwise (e.g. depreciation), use estimate parameters at the upper side of the sheet. (See about colour codes in section 1.2 Meaning of different colours.)
2.1.3 Net sales and EBIT
Three different levels You can estimate both net sales and EBIT in three different levels.
(quarter and) division level (Y+0, Y+1, Y+2 and Y+3; I-divQ)
full-year level (from Y+4 onwards; I-main)
(Y+0 means the current year.)
Absolute or relative figures Also you have a possibility to choose, whether you would like to use absolute or relative figures for the first four years (see more information in section 2.1.1 I-divQ sheet). From the fifth year onward, please use net sales growth % and EBIT % in the Estimate parameters section at the upper side of I-main.
Quarter or full-year? The current year should be estimated in quarterly level. For the following three years, you have a possibility to choose whether to use quarter or full-year figures. If you input both, the following rules are used for deciding the final group-level net sales and ebit:
Quarterly figures dominate full-year figures.
Absolute figures dominate relative figures.
2.1.4 Financial items
Net financial items consist of four subitems:
Interest expenses
Other financial expenses
Exchange rate differences
Financial income
Estimating the items is different for different years:
Current year (Y+0) net financial items In I-divQ you can estimate net financial items quarterly. Thus subitems cannot be estimated in I-divQ (we have concluded with the analysts that the 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 you can estimate the current year financial items in two different sheets and levels, we have had to make sure that the estimates are consistent with each other. Thus current year’s interest expenses include the following formula:
Interest expenses =
Sum of quarterly net financial items —
Sum of subitems 2, 3 and 4
(output, I-main)
(input, I-divQ)
(input, I-main)
Note: In practice quarterly net financial items are estimated in I-quarter’s periodic income statement. From there on the numbers continue to the cumulative income statement, in which Q4 naturally means same as full-year net financial items. This value is used in the formula in I-main.
Interest expenses (for the years Y+1…) You have two ways to estimate interest expenses from the second estimate year onwards: as an absolute figure or relatively to the interest bearing liabilities. Both parameters can be found in estimate parameters section in I-main.
If you enter both absolute and relative figure, the absolute is dominating just like for net sales and ebit (see more information in section 2.1.1 I-divQ sheet).
Financial income Financial income for the four first estimate years is estimated with absolute figures. For the rest of the estimate years, you can use financial income % (relative to financial assets) in the estimate parameters section.
Other financial expenses and exhange rate differences Both subitems are estimated directly as absolute figures to the income statement.
2.1.5 Other income statement parameters
EBITDA EBITDA is an output parameter, calculated as a sum of EBIT and depreciation.
Depreciation and goodwill amortization Normal depreciation for the current year comes from the I-divQ sheet where you can estimate it as an absolute figure for each quarter. For the rest of the estimate years, an estimate parameter Depreciation of fixed assets % is used.
Goodwill amortization is an absolute parameter in the Estimate parameters section. Since it is a pure input figure, it could locate just in the income statement. However, using a separate estimate row, we can get all the relevant estimate parameters close to themselves.
Costs The 9 cost rows are not connected to any other figures. So it does not do any harm if you leave them blank (or keep unupdated).
They exist only for your own use: it might be helpful to fill historical cost figures and to estimate future costs in order to make better EBIT estimates.
Share of associated companies’ profit or loss and Minorities The current year figures come from I-divQ sheet and the following years are estimated directly.
Taxes on operations The current year figure comes from I-divQ sheet where you can estimate taxes as an absolute figure for each quarter. Taxes for the other estimate years are based on tax rate and pre-tax profit. You can find the tax rate in Financial key value drivers (WACC) section (see more information about WACC in section 2.3 WACC (weighted average cost of capital).
Earnings rows Pre-tax and after-tax profit, net earnings and the profit for the period are output parameters.
Extraordinary items The current year figures come from I-divQ sheet and the following years are estimated directly to the I-main sheet. Taxes on EO items are estimated separately so that adjusted EPS can be calculated right.
2.2 BALANCE SHEET
All the balance sheet items can be found in the I-main sheet as the model does not include quarter-level balance sheet. The following subchapters tell you how to estimate the items and also how the assets and liabilities are balanced.
2.2.1 Balancing assets and liabilities
Valuatum Excel model balances assets and liabilities automatically.
If liabilities are bigger, the difference is allocated to the parameter called generated financial assets.
If assets are bigger, the difference is allocated to the (short and long-term) interest bearing debt.
You do not necessary have to do anything yourself. You can however affect to the minimum level of liabilities and how the difference is allocated between the short and long-term liabilities. The parameters needed can be found below the balance sheet rows.
Interest bearing liabilities Interest bearing liabilities are output-figures. They are part of the process of balancing assets and liabilities. They are in most cases the items that adjust themselves to make assets and liabilities equal (see the previous chapter).
If you want, you can of course determine also the level of interest bearing liabilities but that must be done either undirectly by estimating other balance sheet items or by determing the minimum level of interest-bearing liabilities.
Non-interest bearing liabilities Long-term liabilities are an input figure and short-term are estimated with the corresponding percentage figure in the Estimate parameters section.
Interest bearing financial assets (cash) The normal parameter is estimated with the percentage figure (compared to net sales). The generated parameter depends on the difference of assets and liabilities (see the previous chapter).
2.2.3 Investments and fixed assets
In order to calculate fixed assets, you need to estimate the investments. You have two possible ways to estimate them: as absolute investments or as relative to fixed assets. Both options can be found in the Estimate parameters section of the I-main sheet.
If you enter numbers to both fields, again the absolute figure is dominating, just like in net sales and ebit.
Exceptional change in fixed assets When fixed assets increase in a way that cannot be explained with normal items (investment, depreciation, asset sales), you can use a parameter called Additional fixed assets at the end of the I-main sheet. This kind of situation may be relevant for example in a case of a merge of two companies.
Inventories, financial assets and non-interest bearing current liabilities These items form Working capital. Each of them can be estimated with an own estimate parameter in the Estimate parameters section of the I-main sheet.
Retained earnings In the normal case the value of this parameter is calculated from the previous year’s retained earnings, income statement’s profit for the period and dividends.
However, for example during a merge there may come extra retained earnings that have to be added to the balance sheet. For these kind of situations there is a parameter called Additional retained earnings, which can be found at the end of the I-main sheet.
Other parameters The rest of the figures are with blue colour and thus can be estimated directly as an input figure.
2.3 WACC (weighted average cost of capital)
WACC is calculated in Financial key value drivers section. Its subparameters (cost of debt, equity beta, etc.) can be found in the column B.
Tax rate Tax rate is a bit exceptional compared to the other subparameters since it is used also outside of WACC.
Tax rate in the column B is used for calculating WACC and thus discount factor.
Tax rates in the other columns are used for calculating taxes in income statement and certain parameters in DCF and EVA calculations.
2.4 DIVIDENDS AND SHARE INFORMATION
Estimating dividends Total dividends are calculated as:
-Total dividends = Dividend/share x No of shares (series I + II)
…when either dividend/share has been imputted or pre-tax profit is negative.
-Total dividends = Payout ratio x Net earnings
…when dividend/share cell is empty and pre-tax profit is positive.
(In history total dividends are always calculated using dividend/share.)
Share prices Historical share prices are entered in Capitalization section but for the current prices you can use the cells N3:N4 in the upper fields.
Notice that the share prices in the Excel model are only for inner use. When you update the model available for users, the latest prices are retrieved from the database.
Splits and issues For adjusting share prices and no of shares in history, you have a parameter called Splits and stock issues. See more about splits and stock issues.
3 History years and quarters
3.1 HOW TO TELL TO THE MODEL WHAT IS THE CURRENT QUARTER
Years (Y-1…Y-4) in history (and current year, Y+0) Actualized income statement is mostly filled to I-divQ, where at the end of the sheet there is a section of cumulative figures (from the row #154). When the numbers are in blue font, you can override a possible formula in the cell.
There are also certain rows that you cannot fill in quarter-level figures (I-divQ sheet). These include costs, goodwill depreciation, financial items in sublevel and other adjustments. You may fill these in I-main for history years, if you like.
History years Y-5, Y-6 and Y-7 In I-divQ there are only four history years in quarterly level. The three years behind them can be filled in full-year level in the I-divQ sheet (net sales and ebit) and in the I-main sheet (the rest of the parameters).
Balance sheet figures (both history and estimates) exist only in the I-main sheet as we do not have quarterly balance sheets.
In historical balance sheet the figures are in blue font, i.e. every figure is an input figure (excl. two sum rows: total equity and total financial assets). You can fill these cells using the historical financial information you have about the company.
After running the year change macro, some cells (in blue font) might include formulas, since they used to be estimated indirectly with another parameter. Since they are not estimate numbers anymore, you should fill the actualized numbers also to these cells. It is important not to leave any formulas to history years.
All the key figures are in principle calculated in output sheets (O-sheets) that are created and maintained by the stockbroker (see section 1.1 Different sheet types).
When the company announces its interim report (Q1, Q2, Q3), do the following things:
Insert “x” to the row #4 in I-divQ.
In the cumulative part of I-divQ (from the row #154) the colours have now changed to blue. Fill net sales, ebit and income statement values in CUMULATIVE-section (I-diVQ-sheet row 156- ) for the actualized quarter. (You can override a formula in a cell when the font is blue.)
When Q4 result is published, see the following chapter.
5.2 CHANGING YEAR (Q4)
Every time when the company publishes its Q4 result, the model must be updated.
In order to avoid a lot of manual work and to ensure that everything is done correctly without human errors, you must use YearChange macro in this operation. Basically it removes the latest history year, adds a new estimate year and updates dozens of links involved.
Please follow these instructions, when you fill Q4 results:
Insert “x” to the cell AF4 in I-divQ (the current year’s latest quarter).
Fill cumulative Q4 net sales, ebit and income statement to the I-quarter sheet.
In I-main enter the actualized balance sheet.
Update the cells in blue font also to the I-main income statement.
Go back to I-divQ and enter new quarter estimates: net sales, EBIT and income statement – if you have not already done it before. (If the quarter estimates would be blank, then current year net sales growth becomes 0% after year change.)
Make a backup copy of the file.
Press ‘Change year’ button.
Now the macro runs a few seconds and when it has finished, it will give a message box. Do the following things after the macro:
Enter new estimates, especially to the I-divQ sheet, where macro has copied the Y+3 values from I-main (Y+0 means the current year). If you do not yet have the quarter estimates for the new current year, please make them too.
Check that the model looks mainly ok. If there are errors in interest expenses, run the FixInterestExpenses macro.
(In Capitalization section update the actualized share information.)
You do not need to do anything to the O-sheets. IO-sheets make sure that the current year in O-sheets is in the same column than before the YearChange macro. Same goes for the Database-Interface sheet.
6 Possible problems
6.1 #DIV0 / #REF IN INCOME STATEMENT AND BALANCE SHEET
When income statement and balance sheet are estimated, there is always a problem with a loop (interest expenses affect to themselves):
Interest expenses are estimated with a parameter that checks debt, debt depends on the total liabilities and assets, total liabilities depend on equity, equity depends on earnings, and earnings depend on interest expenses.
In Valuatum Excel Model this loop has been solved with iteration (Tools – Options – Calculation – Iteration). However, 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.
That is why the model includes a macro called ‘FixInterestExpenses‘. It fixes the error and activates the iteration again. You can start the macro with the corresponding button on the left side of income statement (I-main).
6.2 NEED FOR CHANGE IN MODEL INPUT SHEETS (e.g. in a formula)
If you feel that you would like to change something in the model in I-sheets (outside users’ own area), please do not change anything on your own but contact the “model officer” within your own organisation or contact Valuatum. The change will be done using conversion macro simultaneously to all models.
7 Other issues
7.1 INFORMATION IN UPPER FIELDS
The main information of a company is filled to the upper fields of the I-main sheet. The information is needed when the model is updated to the database. The important fields are:
-Company’s name -Industry -Subindustry (leave empty, if does not exist)
The current year’s share prices are only for your own purposes since the latest prices are retrieved from the database when the model is updated to the database.
7.2 ANALYST INFORMATION
In order to update the model to the database, you need to fill also a bit of information of yourself: write your username and password of the Valuatum system to the Database-interface sheet. If you like, you can also write your name to the I-main sheet (see picture above) but it won’t be used outside of the Excel model.
7.3 USER’S OWN AREA
On the right side of each sheet there is an area called User’s own area. That is the place where you can make larger preparations to you single estimates.
Please, do not use any cells outside of the area.
That is because sometimes the empty model is updated and then all the models in use are converted with a macro to the new model. The macro copies only the input (blue) figures and the own area cells from the old model to the new one. Thus if you have made your own calculations somewhere else, they will disappear.
7.4 OWN SHEETS AND WORKBOOKS
Besides of the own areas (see section 7.3 User’s own area), you are allowed to have extra sheets. They may be useful if you need to make more specific estimates, e.g. about unit volumes, sales prices, raw-material-prices, utilization degrees etc.
When the model is updated, the conversion macro will copy your own sheets to the new model.
Also having links to external workbooks is allowed. During the conversion these links are not affected.
7.5 DIVISION NAMES
The division names that are shown in ValuViews (html pages) and ValuModels (java model) are written to the I-divQ sheet. See more info from estimating parameters in I-divQ in section 2.1.1.
7.6 ESTIMATE AND INFO FIELDS
In the periodic part of the I-divQ sheet, you can see ‘Estimates’ and ‘Info’ fields.
‘Estimates’ are naturally for your estimates and ‘Info’ tells you the actual periodic figures. The latter takes the figures from cumulative or periodic part depending on whether the quarter in question has actualized.
7.7 HOW TO UPDATE MODEL IN GENERAL LEVEL (conversion)?
Sometimes there is a need to make a change to the model in general level. These kind of situations may be, for example:
-A need to change a formula (e.g. another way to calculate a key figure) -A change in an output sheet is needed -A new output sheet is needed
The change will be made to a file called emptymodel.xls. Then by using conversion macro, the input data in the old files will be copied to the new file. The process is quite simple and requires about 20 seconds per model.
The macro is used so that the changes can be easily implemented to each model in use. Analysts do not need to do the work themselves; after the changes have been made to the empty model, the macro does the rest.
Excel2DB / DB2Excel: Used when the model is updated to the database.
FixInterestExpenses: Used for fixing #DIV0 problem in income statament and balance sheet. For more information about the #DIV0 problem, see section 6.1
YearChange: Used for keeping the model consistent after imputting actualized full-year figures.
Please note that this website uses cookies for statistical and analytical purposes and for improving the user experience of the site. AcceptCookie settings
Privacy & Cookies Policy
Privacy Overview
This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
For more information, please refer to our Privacy Policy
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
