dynamic financial modelling and dcf valuation for sia pallogs

7/30/2019 Dynamic Financial Modelling and DCF Valuation for SIA Pallogs

1/15

Dynamic Financial Modeling and DCF Valuationfor

SIA Pallogs(a privately held Latvian wooden pallet producer)

byVishal Verma

April 2013


2/15

Dynamic Financial Modeling and DCF Valuationfor

SIA Pallogs(a privately held Latvian wooden pallet producer)

byVishal Verma

Submitted for completion of the course

Topics in Corporate Finance (PMB700)

By Prof. Raimonds Lieksnis

Riga Business School, Riga, LVApril 2013


3/15

Please download the working Excel model here:http://goo.gl/GiI8y
http://goo.gl/GiI8yhttp://goo.gl/GiI8yhttp://goo.gl/GiI8y


4/15

Outline

About SIA Pallogs

SIA Pallogs is a private wood production company established in 2001. Its core business is production of

wooden pallets (pallogs). The company currently employs 152 people. It has two production sites - first

located in Koknese and the other in Kraslava.

Position in the EU market

90% of the Pallogs revenues are generated through export while the remaining 10% come from sales in

the local market. Revenues have increased significantly over the recent years26% increase in 2010-

2011 period. Revenues are expected to reach 5.2 mLVL mark in 2012.

The main export markets for Pallogs are Netherlands, Germany, Belgium, France and Denmark. In

Netherlands, the company owns 30% of the overall market of pallets. In France they occupy the 3rd

spot

for pallets import while in Denmark they are placed 5th

. In Germany, they are ranked number 8.

Future developments

Due to the development of new market tendencies and rising production costs, SIA Alpha is inclined on

exploring ways to trim costs and increase market share. Due to the presence of large number of woodenpallet produces in Europe, Alpha realizes the unsustainability of dominance gained on price alone.

Keeping this view in mind, Alpha is planning to invest in new trucks and trailers amounting to 650,000

EUR in co-operation with its long term transportation services provider. This way the company plans to

limit its dependence on logistic companies and ensure timely deliveries to its clients in the EU. In

addition, to increase the capacity utilization of the new assets, the transportation partner will help

secure EU-to-Latvia cargo agreements for Alpha. To accomplish its goals, Alpha is planning to increase its

credit line by 100,000 LVL to cover its working capital needs.

Purpose of the study

The purpose of the study is to project the company's financials for varying circumstances - optimistic,

pessimistic, realistic etc. and perform equity and firm valuation using DCF method. Three separate

dynamic models have been developed to understand the effects of various decisions on firm's value.


5/15

Contents and purpose of the worksheets

- Models, Drivers: Explains the features of various models, reasons behind the choice of assumptions

(drivers).

- Cost breakup: Shows the break up of manufacturing and non-manufacturing costs for 2012 and 2011.

Also shows the variation of variable SGA with sales and the increase in fixed part of SGA with time.

- Historicals, Trends: Shows the historical statements of years 2006-12. Based on these statements,

trends of various drivers have been evaluated.

- Dashboard: Allows you to choose various modes (optimistic, pessimistic, base) and also to set your

own assumptions. These assumptions work as inputs to the 3 models - Mod-normal, Mod-cash sweep

and Mod-cs,div.

- Mod-Normal, Mod-cash sweep, Mod-cs,div: The 3 models. More information on the next sheet.

- Dep Loan: Shows caluclation pertaining to depreciation of fixed assets and interest and principal

calculation for various loans. Used primarily in the Mod-normal model.

-AX-Repay Sched-old loans, AX-Repay Sched-new loans, AX-Repay Sched-new loans2:Bank loan

repayment schedules

-AX-Total-EU: Shows the total betas for various industries in EU.

-AX-ERPs by country: Allows calculation of ERP for various countries.



6/15

About the 3 models

All the 3 models described below are completely dynamic which means one only needs to change the

inputs on the dashboard and the model would respond without the need for any other change

elsewhere, all the way up to the valuation.

The main dashboard is located in the worksheet 'Dashboard'. However, for cash sweep and dividending

features, specific dashboards have been placed in the sheets containing the respective models.

Mod-normal

This model tries to model the company policy decis ions as much as possible. This is evident in the

dividending policy where the company plans to disburse LVL 400,000 dividends in 2013. However, since

their new long term loan prohibits them from paying dividends for another 4-5 years, no dividending has

been shown in any of the remaining years.

The company has recently invested in physical assets and considers unlikely making serious capital

expenditures in another 4-5 years. For this reason, no addition to fixed assets has been shown in the

projections.

The loan and the interest payments follow the schedule laid out by the bank and they are refelcted in

the pro-forma statements.

Mod-cash sweep

This model tries to model sweeping of debt using the excess cash bank. It's possible to control the

sweeping behaviour by deciding the percentage of cash that can be used for sweeping debt. Also it's

possible to turn sweeping on/off, thus allowing one to see effect of sweeping on the valuation.

In addition, this model tries to alter the fixed assets in proportion with the sales, a behavior lacking in

Mod-normal. This addition was opted for considering the past fixed asset balance variation with sales,

making the projections more realistic.

Mod-cs,div

This model goes one step further and utlises the excess cash to not just sweep debt but also to disburse

dividends. Considering cash sweep would end the debt liability faster than what the bank schedule

suggests, it would not be wrong to consider that dividending would come into play.

This model allows one to see the effect of cash sweeping and dividending in tandem. It's possible to

define what percentage of excess cash to be used for each purpose. Just like in the last model, i t's also

possible to turn on/off the cash sweep and dividending feature. In case dividend feature is turned off,

this model would behave exactly like Mod-cash sweep.


7/15

Care has been take to ensure cash sweep takes precedence over dividending. Therefore always cash

sweep would take place before any dividending takes place.

Again, fixed assets have been varied in proportion with sales making the projections appear more in line

with reality.

One major change in this model has been the way balance sheets have been balanced. Unlike in the last

two models where B/S have been balanced using CF statements, in this case the B/S have been balanced

using B/S alone. This change was necessitated by the need to avoid circular references. However, CF

statements have been drawn up for reconciliation and are later used for valuation purpose.

Explanations on default drivers used in 'Dashboard'

Trends for all the drivers below are shown in the 'Historicals, Trends' sheet.

Sales Growth Rate

Decision was based on recent growth rates, long term industry average and company growth rate

average.

COGS/Sales

This ratio has largely stayed in the 60% range. Years 2008 and 2009 saw a 20% hike in the ratio due to

decline in sales and rise of manufacturing costs.

Inventory Turnover

Again the decision was based on recent turnover figures. The rise of the ratio in 2007-08 can be

attributed to rise in manufacturing costs.

DSO

This number has stayed in the vicinity of 30 for most years, though the number did take an upswing

during the economic recession of 2007-08 when most businesses were affected with liquidity problems.

Prepayment/COGS

Decision was made considering strong liquidity state of the company and seeing the trend of the ratio in

the past years.

DPO

This number has stayed in the region of 15-20 for the last couple of years. However, during 2007-08, this

number went really high up in the 40-50 range.


8/15

Cash/Sales

This ratio has generally stayed between 0.5-2% but in 2012 it went really up to 5%, a sign of high

liquidity in the company.

Net FA/Sales

This ratio has consistently stayed in the 11-14% bracket. In the years 2007-2008 however, the ration

climbed up to 33-40%, a jump most l ikely caused by the decline in sales rather than capex going up.

SGA expense projection

SGA expense has been projected as two components - fixed and variable. Variable part has been varied

in proportion to sales while fixed part has been deemed to rise at 10% per year. These assumptions are

supported by cost figures of the last two years. (See 'Cost Breakup').

Additional sales

285000 - This is the f igure hypothesized by the operations department of the company keeping in view

its capacity limits and the demand side. In all the scenarios, we shall assume the additional sales to stay

at the same level.



9/15

Total Cost Breakup


10/15

Historical Statements and Trends Analysis


11/15

The Main Dashboard


12/15

A snapshot ofMod-normal being run on Pessimistic mode with default drivers


13/15

A snapshot of Mod-cash sweep being run with cash-sweep turned on (Pessimistic, default drivers)


14/15

A snapshot of Mod-cs, div being run with cash-sweep and auto-dividending turned on (Pessimistic,

default drivers)


15/15

Depreciation of fixed assets and loan repayment calculation for Mod -normal

dynamic financial modelling and dcf valuation for sia pallogs

Documents