Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

would you pls set the excel formulas in the DDM sensitivity analysis

1 answer below »
would you pls set the excel formulas in the DDM sensitivity analysis
Answered Same Day Sep 01, 2021

Solution

Nitish Lath answered on Sep 02 2021
161 Votes
Profit Loss
    IFN    Statement of Comprehensive Income
            2015    2016    2017    2018    2019
            $000    $000    $000    $000    $000
        Operating Revenue    133,807,000    100,916,000    195,828,000    193,367,000    247,383,000
        Other Revenue    8,354,000    72,320,000    11,813,000    31,892,000    17,177,000
        Total Revenue Excluding Interest    142,161,000    173,236,000    207,641,000    225,259,000    264,560,000
        Operating Expenses    -50,326,000    -59,457,000    -62,817,000    -74,653,000    -92,769,000
        EBITDA    91,835,000    113,779,000    144,824,000    150,606,000    171,791,000
        Depreciation    -46,535,000    -46,524,000    -46,516,000    -46,630,000    -49,692,000
        Amortisation    -7,962,000    -5,426,000    -5,247,000    -4,814,000    -4,863,000
        Depreciation and Amortisation    -54,497,000    -51,950,000    -51,763,000    -51,444,000    -54,555,000
        EBIT    37,338,000    61,829,000    93,061,000    99,162,000    117,236,000
        Interest Revenue    827,000    783,000    1,633,000    4,834,000    2,278,000
        Interest Expense    -56,414,000    -51,963,000    -47,644,000    -84,467,000    -48,217,000
        Net Interest Expense    -55,587,000    -51,180,000    -46,011,000    -79,633,000    -45,939,000
        PreTax Profit    -18,249,000    10,649,000    47,050,000    19,529,000    71,297,000
        Tax Expense    -183,000    -3,616,000    -14,786,000    26,144,000    -20,505,000
        Net Profit after Tax Before Abnormals    -18,432,000    7,033,000    32,264,000    45,673,000    50,792,000
        Abnormals    0    0    0    0    -9,901,000
        Abnormals Tax    0    0    0    0    0
        Net Abnormals    0    0    0    0    -9,901,000
        Reported NPAT After Abnormals    -303,603,000    7,033,000    32,264,000    45,673,000    40,891,000
        Outside Equity Interests    585,000    1,079,000    101,000    0    -96,000
        Shares Outstanding at Period End    767,888,000    772,469,000    950,259,000    954,060,000    956,562,000    2019    2,502,000    0.002616
        Weighted Average Number of Shares    767,428,000    771,643,000    804,644,000    952,938,000    956,163,000    2018    3,801,000    0.003984
        EPS Adjusted (cents/share)    -2.33    1.05    4.02    4.79    5.30    2017    177,790,000    0.187096
        EPS After Abnormals (cents/share)    -39.48    0.72    4.02    4.79    4.27    2016    4,581,000    0.005966
                                        4.991543
Balance Sheet
        Balance Sheet
            2015    2016    2017    2018    2019
        Cu
ent Assets
         Cash    45,182,000    147,602,000    251,786,000    144,898,000    103,681,000
         Receivables    71,243,000    15,992,000    8,262,000    14,935,000    20,317,000
         Prepaid Expenses    1,313,000    4,377,000    4,154,000    0    0
         Inventories    12,695,000    20,620,000    26,951,000    43,327,000    27,157,000
         Investments    566,000    355,000    1,551,000    2,080,000    3,502,000
         NCA Held Sale    1,286,840,000    0    0    0    0
         Other    0    0    0    0    0
        Total Cu
ent Assets    1,417,839,000    188,946,000    292,704,000    205,240,000    154,657,000
        Non Cu
ent Assets
        Receivables    4,163,000    3,769,000    3,475,000    3,512,000    2,828,000
        Inventories    0    0    0    0    0
        Investments    505,000    1,390,000    1,211,000    11,935,000    12,238,000
        PP&E    830,167,000    783,819,000    799,937,000    896,431,000    991,815,000        46,516,000    46,630,000    49,692,000
        Intangibles(ExGW)    111,687,000    107,535,000    103,143,000    100,184,000    86,185,000
        Goodwill    15,136,000    15,136,000    15,136,000    15,136,000    15,136,000        62,634,000    143,124,000    145,076,000
        Future Tax Benefit    49,301,000    51,937,000    20,315,000    26,376,000    14,400,000
        Other    0    0    0    0    0
        Total Non Cu
ent Assets    1,010,959,000    963,586,000    943,217,000    1,053,574,000    1,122,602,000
        Total Assets    2,428,798,000    1,152,532,000    1,235,921,000    1,258,814,000    1,277,259,000
        Cu
ent Liabilities
        Account Payable    28,981,000    17,356,000    19,786,000    18,254,000    18,689,000
        Short-Term Debt    46,259,000    73,601,000    83,252,000    41,219,000    53,513,000
        Provisions    32,286,000    28,581,000    30,264,000    6,754,000    16,018,000
        NCL Held Sale    965,279,000    0    0    0    0
        Other    277,588,000    0    0    0    9,566,000
        Total Cu
ent Liabilities    1,350,393,000    119,538,000    133,302,000    66,227,000    97,786,000
        Non Cu
ent Liabiities
        Account Payable    0    0    0    0    0
        Long-Term Debt    740,624,000    668,889,000    570,600,000    608,880,000    565,902,000
        Provisions    76,877,000    83,540,000    52,645,000    12,014,000    30,269,000
        Other    0    0    0    0    0
        Total Non Cu
ent Liabiities    817,501,000    752,429,000    623,245,000    620,894,000    596,171,000    INFINGEN
        Total Liabilities    2,167,894,000    871,967,000    756,547,000    687,121,000    693,957,000    Total Liabilities    693,957,000
        Net Assets    260,904,000    280,565,000    479,374,000    571,693,000    583,302,000    Shareholders Equity    583,302,000
        Shareholders Equity                        Debt/ Equity Ratio    1.19
        Share Capital    763,169,000    764,314,000    915,963,000    918,870,000    914,223,000
        Reserves    -120,481,000    -106,451,000    -91,555,000    -47,816,000    -69,146,000
        Retained Earnings    -381,784,000    -377,298,000    -345,034,000    -299,361,000    -261,775,000
        Total Equity    260,904,000    280,565,000    479,374,000    571,693,000    583,302,000
Ratio Analysis
        2015    2016    2017    2018    2019
    Profitability ratios
    Net Profit Margin (%)    -13.34    8.04    16.53    23.62    20.49
    EBIT Margin (%)    27.90    61.27    47.52    51.28    47.39
    EBITA Margin (%)    33.85    66.64    50.20    53.77    49.36
    EBITDA Margin (%)    68.63    112.75    73.95    77.89    69.44
    ROE (%)    -6.84    2.89    6.75    7.99    8.69
    ROA (%)    0.89    3.86    5.32    8.33    6.61
    ROIC (%)    2.88    5.62    8.06    10.12    8.19
    NOPLAT Margin (%)    21.26    47.85    35.60    54.94    35.50
    Asset Management Ratios
    Invested Capital Turnover    0.14    0.12    0.23    0.18    0.23
    Inventory Turnover    10.54    4.89    7.27    4.46    9.11
    Asset Turnover    0.06    0.09    0.16    0.15    0.19
    LT Asset Turnover    0.13    0.10    0.21    0.18    0.22
    PPE Turnover    0.16    0.13    0.24    0.22    0.25
    Depreciation/PP&E (%)    4.01    4.01    3.81    3.42    3.29
    Depreciation/Revenue (%)    34.78    46.10    23.75    24.11    20.09
    Wkg Capital/Revenue (%)    51.21    -4.55    -4.66    18.27    2.71
    Working Cap Turnover    1.95    -21.97    -21.44    5.47    36.91
    Debt and Safety Ratios
    Financial Leverage    9.31    4.11    2.58    2.20    2.19
    Gross Gearing (D/E) (%)    301.60    264.64    136.40    113.71    106.19
    Net Gearing (%)    284.28    212.03    83.87    88.37    88.42
    Net Interest Cover    0.67    1.21    2.02    1.25    2.55
    Cu
ent Ratio    1.05    1.58    2.20    3.10    1.58
    Quick Ratio    1.04    1.41    1.99    2.44    1.30
    Gross Debt/CF    9.76    7.80    5.59    5.38    4.53
    Net Debt/CF    9.20    6.25    3.44    4.18    3.77
    NTA per Share ($)    $ 0.17    $ 0.20    $ 0.38    $ 0.48    $ 0.50
    BV per Share ($)    $ 0.34    $ 0.36    $ 0.50    $ 0.60    $ 0.61
    Cash per Share ($)    $ 0.06    $ 0.19    $ 0.27    $ 0.15    $ 0.11
    Cashflow Ratios
    Receivables/Op. Rev. (%)    11.57    15.60    2.97    7.72    8.21
    Inventory/Trading Rev. (%)    9.49    20.43    13.76    22.41    10.98
    Creditors/Op. Rev. (%)    21.66    17.20    10.10    9.44    7.55
    Funds from Ops./EBITDA (%)    0.00    0.00    0.00    86.20    105.95
    Depreciation/Capex (%)    4,440.36    1,264.24    108.45    32.47    30.91
    Capex/Operating Rev. (%)    0.78    3.65    21.90    74.27    64.98
    Days Inventory    34.63    74.58    50.23    81.78    40.07
    Days Receivables    42.23    56.93    10.83    28.19    29.98
    Days Payables    79.05    62.77    36.88    34.46    27.57
    Gross CF per Share ($)    $ 0.10    $ 0.12    $ 0.15    $ 0.13    $ 0.14
    Sales per Share ($)    $ 0.17    $ 0.13    $ 0.24    $ 0.20    $ 0.26
    Market Data
    Year End Share Price    $ 0.32    $ 1.01    $ 0.73    $ 0.66    $ 0.48
    Market Cap.    $ 245,724,160    $ 776,331,345    $ 693,689,070    $ 629,679,600    $ 454,366,950
    Net Debt    $ 741,701,000    $ 594,888,000    $ 402,066,000    $ 505,201,000    $ 515,734,000
    Enterprise Value    $ 987,425,160    $ 1,371,219,345    $ 1,095,755,070    $ 1,134,880,600    $ 970,100,950
    Valuation Multiples
    EV/EBITDA    10.75    12.05    7.57    7.54    5.65
    EV/EBIT    26.45    22.18    11.77    11.44    8.27
    Market Cap./Rep NPAT    --    173.06    21.50    13.79    11.11
    Market Cap./Trading Rev.    1.84    7.69    3.54    3.26    1.84
    Price/Book Value    0.94    2.77    1.45    1.10    0.78
    Price/Gross Cash Flow    3.05    8.15    5.93    5.21    3.33
    PER    -13.73    95.71    18.16    13.78    8.96
Growth Rates
    ASX Code    Company Name    Item    06/15    06/16    06/17    06/18    06/19
    IFN    Infigen Energy    Operating Rev. 1 Year (%)    65.16    -24.58    94.05    -1.26    27.93
    IFN    Infigen Energy    Operating Rev. 3 Year (%)    42.12    6.06    34.21    13.06    34.84
    IFN    Infigen Energy    Operating Rev. 5 Year (%)    -18.39    17.20    33.25    17.98    25.01
    IFN    Infigen Energy    Operating Exp. 1 Year (%)    -43.84    18.14    5.65    18.84    24.27
    IFN    Infigen Energy    Operating Exp. 3 Year (%)    -27.26    -23.60    -11.17    14.05    15.98
    IFN    Infigen Energy    Operating Exp. 5 Year (%)    -18.36    -14.06    -13.64    -10.95    0.70
    IFN    Infigen Energy    EBITDA 1 Year (%)    -63.18    23.90    27.29    3.99    14.07
    IFN    Infigen Energy    EBITDA 3 Year (%)    -25.79    -23.10    -16.58    17.93    14.72
    IFN    Infigen Energy    EBITDA 5 Year (%)    -18.34    -12.93    -8.41    -9.65    -7.19
    IFN    Infigen Energy    EBIT 1 Year (%)    -70.26    65.59    50.51    6.56    18.23
    IFN    Infigen Energy    EBIT 3 Year (%)    -23.86    -18.03    -9.50    38.48    23.77
    IFN    Infigen Energy    EBIT 5 Year (%)    -18.87    -7.46    1.93    -2.45    -1.36
    IFN    Infigen Energy    EBT After Abs. 1 Year (%)    57.01    --    341.83    -58.49    214.38
    IFN    Infigen Energy    EBT After Abs. 3 Year (%)    -32.04    --    --    --    79.31
    IFN    Infigen Energy    EBT After Abs. 5 Year (%)    -18.96    --    --    --    --
    IFN    Infigen Energy    EAT 1 Year (%)    3,605.74    --    481.58    41.12    -10.68
    IFN    Infigen Energy    EAT 3 Year (%)    76.41    --    --    --    94.26
    IFN    Infigen Energy    EAT 5 Year (%)    32.66    --    --    --    --
    IFN    Infigen Energy    EPS Adj 1 Year (%)    --    --    290.29    19.15    10.65
    IFN    Infigen Energy    EPS Adj 3 Year (%)    -31.49    --    54.03    --    72.64
    IFN    Infigen Energy    EPS Adj 5 Year (%)    -19.51    --    --    --    36.95
    IFN    Infigen Energy    Total Div. Ex Spec 1 Year (%)    --    --    --    --    --
    IFN    Infigen Energy    Total Div. Ex Spec 3 Year (%)    --    --    --    --    --
    IFN    Infigen Energy    Total Div. Ex Spec 5 Year (%)    --    --    --    --    --
    IFN    Infigen Energy    DPS Adj Ex Spec 1 Year (%)    --    --    --    --    --
    IFN    Infigen Energy    DPS Adj Ex Spec 3 Year (%)    --    --    --    --    --
    IFN    Infigen Energy    DPS Adj Ex Spec 5 Year (%)    -100.00    -100.00    --    --    --
    IFN    Infigen Energy    Total Assets 1 Year (%)    -3.86    -52.55    7.24    1.85    1.47
    IFN    Infigen Energy    Total Assets 3 Year (%)    -6.72    -27.21    -21.21    -19.67    3.48
    IFN    Infigen Energy    Total Assets 5 Year (%)    -9.10    -18.72    -16.21    -15.88    -12.75
    IFN    Infigen Energy    Net PP&E 1 Year (%)    -56.95    -5.58    2.06    12.06    10.64
    IFN    Infigen Energy    Net PP&E 3 Year (%)    -30.09    -32.16    -25.42    2.59    8.16
    IFN    Infigen Energy    Net PP&E 5 Year (%)    -23.22    -20.45    -19.93    -18.61    -12.45
    IFN    Infigen Energy    Total Equity 1 Year (%)    -46.98    7.54    70.86    19.26    2.03
    IFN    Infigen Energy    Total Equity 3 Year (%)    -20.83    -16.62    -0.87    29.89    27.63
    IFN    Infigen Energy    Total Equity 5 Year (%)    -18.37    -15.23    -1.83    3.39    3.46
    IFN    Infigen Energy    Retained Profits 1 Year (%)    388.33    -1.18    -8.55    -13.24    -12.56
    IFN    Infigen Energy    Retained Profits 3 Year (%)    --    75.94    64.03    -7.79    -11.47
    IFN    Infigen Energy    Retained Profits 5 Year (%)    --    --    --    34.00    27.34
    IFN    Infigen Energy    Net Debt 1 Year (%)    -25.41    -19.79    -32.41    25.65    2.08
    IFN    Infigen Energy    Net Debt 3 Year (%)    -7.68    -14.01    -26.05    -12.01    -4.65
    IFN    Infigen Energy    Net Debt 5 Year (%)    -9.06    -8.89    -15.67    -11.59    -12.30
    IFN    Infigen Energy    Invested Capital 1 Year (%)    -23.39    -12.97    0.72    21.35    2.08
    IFN    Infigen Energy    Invested Capital 3 Year (%)    -12.00    -15.09    -12.43    2.08    7.65
    IFN    Infigen Energy    Invested Capital 5 Year (%)    -12.11    -11.34    -9.79    -5.64    -3.62
    IFN    Infigen Energy    NOPLAT 1 Year (%)    -72.99    69.77    44.39    52.37    -17.34
    IFN    Infigen Energy    NOPLAT 3 Year (%)    -21.38    -18.71    -12.84    55.16    22.06
    IFN    Infigen Energy    NOPLAT 5 Year (%)    -15.27    -8.99    3.56    3.40    -3.57
    IFN    Infigen Energy    Free Cashflow 1 Year (%)    75.45    --    --    --    --
    IFN    Infigen Energy    Free Cashflow 3 Year (%)    23.48    --    -24.34    --    --
    IFN    Infigen Energy    Free Cashflow 5 Year (%)    30.89    --    -14.21    --    -52.27
Cash Flow
    Infigen Energy (INF)
    Cash Flow**
    Financial Year    2015    2016    2017    2018    2019
    Operating Cash...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here