Arðsemismat verkefna Reiknilíkön í Excel Námskeið hjá Endurmenntun HÍ

24.04.23 Páll Jensson 1

Arðsemismat verkefna

Reiknilíkön í Excel Námskeið hjá Endurmenntun HÍ

Markmið: Að gera þátttakendur færa um þróa reiknilíkön í Excel til að meta hvort verkefni eða fjárfesting er arðsöm eða

ekki


Arðsemimat, yfirlit 1. Mælikvarðar á arðsemi 2. Stofnkostnaður, fjármögnun 3. Rekstursáætlanir 4. Efnahagsreikningur 5. Fjárstreymi 6. Áhættumat, næmnigreining 7. Fýsileikakannanir, viðsk.áætlanir


Course Motto: Plan before act!


1. Mælikvarðar á arðsemi Núllpunktsgreining, framlegð Tímagildi peninga, afvöxtun Mælikvarðar:

Endurgreiðslutími (PBP), Núvirði (NPV), Innri vextir (IRR), Ábata/Kostnaðar hlutfall (B/C Ratio)

Samanburður valkosta


NPV Example:Interest

Project A: 10%Year Cash Flow Present Accum.

n An: Value: NPV0 -100 -100.0 -100.01 30 27.3 -72.72 30 24.8 -47.93 30 22.5 -25.44 30 20.5 -4.95 30 18.6 13.7

Sum: 50 13.7Internal Rate of Return 15.2%


Samhengi IRR og NPVInterest Net Present

Rate Value0% 50.02% 41.44% 33.66% 26.48% 19.8

10% 13.712% 8.114% 3.016% -1.8 18% -6.2 20% -10.3

Net Present Value

-20.0 -10.0

0.010.020.030.040.050.060.0

0% 2% 4% 6% 8% 10%

12%

14%

16%

18%

20%

Interest Rate


-500.0

-400.0

-300.0

-200.0

-100.0

0.0

100.0

200.0

300.0

2005 2007 2009 2011 2013 2015

Cash Flow Series

Total Cash Flow & Capital

Net Cash Flow & Equity


Try to see the big picture!


2. Stofnkostnaður, fjármögnun Aðferðir við að meta stofnkostnað Rekstrarfjárþörf (Working Capital) Fjármögnun Hlutafé, lán, … (WACC) Lán: Venjuleg, jafngreiðsla, kúla, ... Afskriftir


Every small link is important!


Initial Costs EstimatesCase Study ExampleTraditional Methodestimating contingencies:

Most likelyBuildings: estimate

Land, roads etc 20 MUSDWater wells & ditches 5 "Farm house & Store 20 "Contingencies 5 "

Buildings Total: 50 "

Equipment:Construct ponds 130 "Tanks 10 "Pumps & pipes 15 "Feeding Equipment 25 "Contingencies 20 "Equipment Total: 200 "

Other Investment:

Consultation 10 "Design 35Contingencies 5 "Other Inv. Total: 50 "


2005 2006 2007 2008 2009 2010Investment and Financing 1 2 3 4 5Investment: Buildings 50.0 48.0 46.0 44.0 42.0 40.0 Equipment 200.0 170.0 140.0 110.0 80.0 50.0 Other 50.0 40.0 30.0 20.0 10.0 0.0 Booked Value 300.0 258.0 216.0 174.0 132.0 90.0

Depreciation: Depreciation Buildings 4% 2.0 2.0 2.0 2.0 2.0 Depreciation Equipm. 15% 30.0 30.0 30.0 30.0 30.0 Depreciation Other 20% 10.0 10.0 10.0 10.0 10.0Total Depreciation 0.0 42.0 42.0 42.0 42.0 42.0

Financing: 445 Equity 30% 133.5 Loans 70% 311.5 Repayment 6 0.0 51.9 51.9 51.9 51.9 Principal 311.5 311.5 259.6 207.7 155.8 103.8 Interest 12% 0.0 37.4 37.4 31.2 24.9 18.7 Loan Managem. Fees 2% 6.2


Are all expenses necessary?


3. Rekstraráætlanir Markaðsáætlun (vörur, magn,

verð) Mat á rekstrarkostnaði

(breytilegur, fastur) Rekstursreikningur Tekjuskattur Arðgreiðslur


Operational Costs EstimatesCase Study Example

Variable Costs: Raw Materials 1.4 KUSD/tonLabour Cost 1.2 "Transportation 0.4 "Variable Cost Total 3 "

Fixed Costs:Maintenance 5 MUSD/yearHousing 3 "Management 9 "Sales 3 "Fixed Costs Total 20 "

Break Even Analysis:Future Sales Price 15 KUSD/tonNet Profit Contribution 12 "

Break Even Quantity 1.7 Ktons/year


Using models is like using glasses to see things better!


2005 2006 2007 2008 2009 2010Operations Statement Sales 0.0 5.0 10.0 15.0 20.0 20.0 Price 6.0 12.0 14.0 15.0 15.0Revenue 0.0 30.0 120.0 210.0 300.0 300.0

Variable Cost 3 0.0 60.0 30.0 45.0 60.0 60.0 Fixed Cost 20 0.0 20.0 20.0 20.0 20.0 20.0 Diverse Taxes 0% 0.0 0.0 0.0 0.0 0.0 0.0Operating Surplus 0.0 -50.0 70.0 145.0 220.0 220.0

Inventory Movement 45.0 Depreciation 0.0 42.0 42.0 42.0 42.0 42.0Operating Gain/Loss 0.0 -47.0 28.0 103.0 178.0 178.0

Interest 6.2 37.4 37.4 31.2 24.9 18.7Profit before Tax -6.2 -84.4 -9.4 71.9 153.1 159.3

Loss Transfer 0 -6.2 -90.6 -100.0 -28.1 0.0 0.0 Taxable Profit 0.0 0.0 0.0 0.0 124.9 159.3 Income Tax 18% 0.0 0.0 0.0 0.0 22.5 28.7 Net Worth Tax 0% 0.0 0.0 0.0 0.0 0.0 0.0Profit after Tax -6.2 -84.4 -9.4 71.9 130.6 130.6 Dividend 30% 0.0 0.0 0.0 21.6 39.2 39.2Net Profit/Loss -6.2 -84.4 -9.4 50.3 91.4 91.4


We have built a great model!


4. Efnahagsreikningur Eignir: Veltufé + fastafjármunir Skuldir:

Skammtímaskuldir Langtímaskuldir

Eigið fé: Hlutafé + uppsafn. hagnaður Kennitölur Villuprófun!


2005 2006 2007 2008 2009 2010Balance SheetAssets Cash Account 0 138.8 52.9 6.6 48.3 149.6 237.4 Debtors 25% 0.0 7.5 30.0 52.5 75.0 75.0 Stock 0 0.0 45.0 45.0 45.0 45.0 45.0Current Assets 138.8 105.4 81.6 145.8 269.6 357.4 Fixed Assets 300.0 258.0 216.0 174.0 132.0 90.0Total Assets 438.8 363.4 297.6 319.8 401.6 447.4

Debts Dividend Payable 0.0 0.0 0.0 21.6 39.2 39.2 Taxes Payable 0.0 0.0 0.0 0.0 22.5 28.7 Creditors 15% 0.0 9.0 4.5 6.8 9.0 9.0 Next Year Repayment 0.0 51.9 51.9 51.9 51.9 51.9Current Liabilities 0.0 60.9 56.4 80.2 122.6 128.8 Long Term Loans 311.5 259.6 207.7 155.8 103.8 51.9Total Debt 311.5 320.5 264.1 236.0 226.4 180.7

Equity 133.5 133.5 133.5 133.5 133.5 133.5 Profit & Loss Balance 0 -6.2 -90.6 -100.0 -49.7 41.7 133.2Total Capital 127.3 42.9 33.5 83.8 175.2 266.7

Debts and Capital 438.8 363.4 297.6 319.8 401.6 447.4

Kennitölur Eiginfjárstaða (Debt Ratio) Skuldaþekja (DSC, LLCR) Greiðsluhæfi (Liquidity, Current

Ratios) Veltuhlutföll (Turnover Ratios) Markaðsvirði (P/E, Internal Value) “Arðsemi” (ROI, ROE)24.04.23 Páll Jensson 21


Sure your model is debugged?


5. Fjárstreymi (Cash Flow) Ath vel muninn á rekstursreikningi og

fjárstreymi (reikningar vs greiðslur) Fjárstreymi útfrá EBITDA Greiðslur: Skattar, vextir, afborganir,

arður Kennitölur fyrir fjárstreymi Mælikvarðar á arðsemi (NPV, IRR) Grafísk framsetning


Cash Flow

Equity Drawdown Dividend

Taxes Interest &Repayment

Loans Drawdown

Sales Costs

Investment

Shareholders

Government Deb t Holders

Company Cash Account

Customers Suppliers

Fixed Assets


2005 2006 2007 2008 2009 2010Cash Flow Operating Surplus 0.0 -50.0 70.0 145.0 220.0 220.0 Debtor Changes 0.0 -7.5 -22.5 -22.5 -22.5 0.0 Creditor Changes 0.0 9.0 -4.5 2.3 2.3 0.0Cash Flow before Tax 0.0 -48.5 43.0 124.8 199.8 220.0

Paid Taxes 0.0 0.0 0.0 0.0 0.0 22.5Cash Flow after Tax 0.0 -48.5 43.0 124.8 199.8 197.5

Interest 6.2 37.4 37.4 31.2 24.9 18.7 Repayment 0.0 0.0 51.9 51.9 51.9 51.9Net Cash Flow -6.2 -85.9 -46.3 41.7 122.9 126.9

Paid Dividend 0.0 0.0 0.0 0.0 21.6 39.2 Financing - Expenditure 145.0 0.0 0.0 0.0 0.0 0.0Cash Movement 138.8 -85.9 -46.3 41.7 101.4 87.7


You learn by reading the text, but also by thinking!


2005 2006 2007 2008 2009 2010Profitability MeasurementsNPV and IRR of Total Cash Flow Cash Flow after Taxes 0.0 -48.5 43.0 124.8 199.8 197.5 Loans 311.5 0.0 0.0 0.0 0.0 0.0 Equity 133.5 0.0 0.0 0.0 0.0 0.0Total Cash Flow & Capital -445.0 -48.5 43.0 124.8 199.8 197.5NPV Total Cash Flow15% -387.0 -423.6 -395.4 -324.0 -224.7 -139.3IRR Total Cash Flow 0.0% 0.0% 0.0% 0.0% 3.6%NPV and IRR of Net Cash Flow Net Cash Flow -6.2 -85.9 -46.3 41.7 122.9 126.9 Equity 133.5 0.0 0.0 0.0 0.0 0.0Net Cash Flow & Equity -139.7 -85.9 -46.3 41.7 122.9 126.9NPV Net Cash Flow 15% -121.5 -186.4 -216.9 -193.1 -131.9 -77.1IRR Net Cash Flow 0.0% 0.0% 0.0% 0.0% 1.9%


Accumulated Net Present Value

-500.0

-400.0

-300.0

-200.0

-100.0

0.0

100.0

200.0

2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015

NPV Total Cash Flow 15%

NPV Net Cash Flow 15%


Internal Rate of Return

-5.0%

0.0%

5.0%

10.0%

15.0%

20.0%

25.0%

30.0%

2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015

IRR Total Cash Flow

IRR Net Cash Flow


Computer models are powerful!


6. Áhættumat, næmnigreining Næmnigreining (Sensitivity Analysis)

Einn þáttur í einu, línurit Sviðsmyndir (Scenarios)

Mörgum þáttum breytt í einu, ekki myndræn Monte Carlo Hermun (Simulation)

Slembuframköllun Líkindadreifing IRR Viðbætur við Excel (@Risk eða Crystal Ball)


Impact Analysis

0.0%

10.0%

20.0%

30.0%

40.0%

50.0%

60.0%

-50% -40% -30% -20% -10% 0% 10% 20% 30% 40% 50%

Deviation

IRR

of E

quity

Price

Sales

Equipment


Scenario SummaryCurrent Values: Optimistic Pessimistic

Changing Cells:Equipment 100% 90% 120%Sales Quantity 100% 120% 90%Sales Price 100% 130% 80%

Result Cells:IRR_Total 19.2% 33.4% 9.5%IRR_Equity 23.7% 48.1% 7.4%


Histogram for Risk Assessment

0

20

40

60

80

100

120

140

160

IRR of Equity

Freq

uenc

y


Pessimistic scenarios are more common than optimistic!


Hvað næst? Notkun líkansins Gagnasöfnun: Mat á stofnkostn,

fjármögnun, markaði og reksturskostn Aðlaga líkanið að ykkar tilviki Keyra líkanið (tilraunastofa) Greining á niðurstöðum Önnur sjónarmið/viðmið Niðurstöður grunnur að

fýsileikakönnun og viðskiptaáætlun


Remember, there is more to life than numbers!


Skýrsla um fjárhagsáætlun Hluti af viðskiptaáætlun/fýsileikakönnun:

Samantekt Bakgrunnur, markmið, afmörkun Megin forsendur og gögn Megin niðurstöður, grunntilvik Áhættumat Umræða, lokaorð Heimildaskrá Viðaukar: Töflur, gögn, ...


Have we achieved anything?

Arðsemismat verkefna Reiknilíkön í Excel Námskeið hjá Endurmenntun HÍ

Documents

Transcript of Arðsemismat verkefna Reiknilíkön í Excel Námskeið hjá Endurmenntun HÍ