As an analyst you have been provided with the following financials of Vitex ltd.
and the assumptions for forecast
Income Statement 2012 2013 2014 2015
Sales ₹ 1,234.90 ₹ 1,251.70 ₹ 1,300.40 ₹ 1,334.40
Cost of Sales ₹ 679.10 ₹ 659.00 ₹ 681.30 ₹ 667.00
Gross Operating Income ₹ 555.80 ₹ 592.70 ₹ 619.10 ₹ 667.40
Selling, General & Admn. Expenses ₹ 339.70 ₹ 348.60 ₹ 351.20 ₹ 373.30
Depreciation ₹ 47.50 ₹ 52.00 ₹ 55.90 ₹ 75.20
Other Net (Income)/Expenses -₹ 11.80 -₹ 7.60 -₹ 7.00 -₹ 8.20
EBIT ₹ 180.40 ₹ 199.70 ₹ 219.00 ₹ 227.10
Interest (Income) -₹ 1.30 -₹ 1.40 -₹ 1.70 -₹ 2.00
Interest Expense ₹ 16.20 ₹ 15.10 ₹ 20.50 ₹ 23.70
Pre-Tax Income ₹ 165.50 ₹ 186.00 ₹ 200.20 ₹ 205.40
Income Taxes ₹ 56.80 ₹ 64.20 ₹ 67.50 ₹ 72.60
Net Income ₹ 108.70 ₹ 121.80 ₹ 132.70 ₹ 132.80
Dividends ₹ 38.30 ₹ 38.70 ₹ 39.80 ₹ 40.10
Addition to Retained Earnings ₹ 70.40 ₹ 83.10 ₹ 92.90 ₹ 92.70
Balance Sheet
Assets 2012 2013 2014 2015
Cash and Marketable Securities ₹ 25.60 ₹ 23.00 ₹ 32.10 ₹ 28.40
Accounts Receivable ₹ 99.40 ₹ 102.90 ₹ 107.30 ₹ 120.10
Inventories ₹ 109.60 ₹ 108.00 ₹ 114.90 ₹ 116.80
Other Current Assets ₹ 96.70 ₹ 91.40 ₹ 103.70 ₹ 97.50
Total Current Assets ₹ 331.30 ₹ 325.30 ₹ 358.00 ₹ 362.80
Property, Plant and Equipment, Gross ₹ 680.90 ₹ 734.30 ₹ 820.80 ₹ 913.10
Accumulated Depreciation ₹ 244.80 ₹ 296.80 ₹ 352.70 ₹ 427.90
Property, Plant and Equipment, Net ₹ 436.10 ₹ 437.50 ₹ 468.10 ₹ 485.20
Other Non-Current Assets ₹ 203.20 ₹ 205.10 ₹ 407.00 ₹ 456.30
Total Non-Current Assets ₹ 639.30 ₹ 642.60 ₹ 875.10 ₹ 941.50
Total Assets ₹ 970.60 ₹ 967.90 ₹ 1,233.10 ₹ 1,304.30
Liabilities and Shareholders' Equity
Accounts Payable ₹ 82.80 ₹ 77.10 ₹ 71.80 ₹ 80.50
Short-Term Debt ₹ 39.10 ₹ 29.70 ₹ 79.80 ₹ 110.30
Other Current Liabilities ₹ 152.00 ₹ 123.80 ₹ 172.10 ₹ 111.30
Total Current Liabilities ₹ 273.90 ₹ 230.60 ₹ 323.70 ₹ 302.10
Long-Term Debt ₹ 163.50 ₹ 145.00 ₹ 201.80 ₹ 218.10
Deferred Income Taxes ₹ 22.30 ₹ 19.60 ₹ 15.00 ₹ 12.70
Other Non-Current Liabilities ₹ 100.60 ₹ 80.10 ₹ 115.00 ₹ 94.50
Total Liabilities ₹ 560.30 ₹ 475.30 ₹ 655.50 ₹ 627.40
Paid-In Capital ₹ 46.90 ₹ 46.10 ₹ 38.20 ₹ 44.80
Retained Earnings ₹ 363.40 ₹ 446.50 ₹ 539.40 ₹ 632.10
Total Shareholders' Equity ₹ 410.30 ₹ 492.60 ₹ 577.60 ₹ 676.90
Total Liabilities and Shareholders' Equi ₹ 970.60 ₹ 967.90 ₹ 1,233.10 ₹ 1,304.30
Other Data 2012 2013 2014 2015
Stock price (year-end) ₹ 55.50 ₹ 65.30 ₹ 55.70 ₹ 51.40
Average number of shares outstanding (mill ₹ 47.99 ₹ 47.28 ₹ 46.81 ₹ 46.19
Required:
1. Calculate the forecasted financials (Income statement+ Balance sheet+ Cash flow statement) for the next four years
2. Calculate the below listed parameters for the historical & forecasted period
Valuation Ratios Profitability Ratios Growth Rates
EPS Return on Equity EPS Growth Rate
Dividend per Share Return on Sales Dividend Growth Rate
P/E Ratio Sales Growth Rate
P/B (price to book) Ratio EBIT Growth Rate
Dividend Payout Ratio Net Income Growth Rate
3. The management also wants to look at a few scenarios for the future years.
Create a scenario manager table for 2016 with the following information:
Scenario
Input variables Optimistic Pessimistic
Sales growth rate (p.a) 7% 3%
Cost of Sales to Sales ratio 49% 51%
SG&A to Sales Ratio 28% 30%
Gross PP&E growth rate (p.a) 7% 9%
4. Value the firm and equity based on the following further data
Risk free rate of return is 7.5%, the beta of the company is 0.658 and risk premium being 9.8%. The cost of debt is 12%.
ssumptions for forecast
Assumption
Will grow at 5%, based on input from management
52% of sales, little better than historical average
29% of sales, based on expected worsening
8% of Gross PP&E, based on analysis of depreciation schedules
-0.7% of sales, based on historical average
Interest on cash & marketable securities; 6%, of opening balance (Cash and cash equivalents)
Interest on Short term Debt: 7% of average balance (op & cls) + Interest on Long term debt: Rs13.5throughout
35% of pre-tax income
40%, based on discussion with management (As face value is not given, so we are computing dividend on the
Calculated from other items
8.4% of sales, based on historical average
8.8% of sales, based on historical average
7.6% of sales, based on historical average
Will grow at 11% per year, based on discussion with management
(Used for deprication schedule and this also becomes
Will remain unchanged at 2015 level
6.1% of sales, same as historical average
10% of sales
8.3% of sales, same as historial average
Will remain unchanged at 2015 level EPS = Retained Earnings / No o
Will remain unchanged at 2015 level DPS = Dividend / No of Shares
Will remain unchanged at 2015 level PE Ratio = Market Price / EPS
PB Ratio = Market price / Book
Dividend Payout Ratio = Divide
ROE = (PAT / Equity) *100
Return on Sales = Sales / Equit
All growth rates = Y2 - Y1/ Y1
Current Ratio = CA/Cl
Quick Ratio = CA - Inv / CL
ITR = COGS / Avg Inv
RTR = Sales / Avg Receivables
P/E ratio Will decline to 16 in 2016 and then improve to 18, 20, and 22 in the following years
(Calculated For dividend computation)
ement) for the next four years
Liquidity Ratios Operating Efficiency Ratios Leverage Ratios
Current Ratio Inventory Turnover Ratio Total Debt to Total Capitalization
Quick Ratio Receivable Turnover Ratio Long-Term Debt to Total Capitalization
Total Debt to Equity
9.8%. The cost of debt is 12%.
Rs13.5throughout
re computing dividend on the equity capital)
chedule and this also becomes CAPEX while calulcating FCFF)
EPS = Retained Earnings / No of Shares
DPS = Dividend / No of Shares
PE Ratio = Market Price / EPS
PB Ratio = Market price / Book value per share
Dividend Payout Ratio = Dividend / PAT Total Debt = Long term debt + Deffered Income tax + Other NC Lia
ROE = (PAT / Equity) *100 (Total Capitalization = Debt + Equity)
Return on Sales = Sales / Equity
All growth rates = Y2 - Y1/ Y1 TIE = Interest / EBIT
Current Ratio = CA/Cl CCR = Cash / Operating expenses
Quick Ratio = CA - Inv / CL
ITR = COGS / Avg Inv
RTR = Sales / Avg Receivables
Coverage Ratios
Times Interest Earned (TIE)
Cash Coverage Ratio
me tax + Other NC Lia
2012A 2013A 2014A 2015A
Income Statement
Sales ₹ 1,234.90 ₹ 1,251.70 ₹ 1,300.40 ₹ 1,334.40
Cost of Sales ₹ 679.10 ₹ 659.00 ₹ 681.30 ₹ 667.00
Gross Operating Income ₹ 555.80 ₹ 592.70 ₹ 619.10 ₹ 667.40
Selling, General & Admn. Expenses ₹ 339.70 ₹ 348.60 ₹ 351.20 ₹ 373.30
Depreciation ₹ 47.50 ₹ 52.00 ₹ 55.90 ₹ 75.20
Other Net (Income)/Expenses -₹ 11.80 -₹ 7.60 -₹ 7.00 -₹ 8.20
EBIT ₹ 180.40 ₹ 199.70 ₹ 219.00 ₹ 227.10
Interest (Income) -₹ 1.30 -₹ 1.40 -₹ 1.70 -₹ 2.00
Interest Expense ₹ 16.20 ₹ 15.10 ₹ 20.50 ₹ 23.70
Pre-Tax Income (PBT) ₹ 165.50 ₹ 186.00 ₹ 200.20 ₹ 205.40
Income Taxes ₹ 56.80 ₹ 64.20 ₹ 67.50 ₹ 72.60
Net Income ₹ 108.70 ₹ 121.80 ₹ 132.70 ₹ 132.80
Dividends ₹ 38.30 ₹ 38.70 ₹ 39.80 ₹ 40.10
Addition to Retained Earnings ₹ 70.40 ₹ 83.10 ₹ 92.90 ₹ 92.70
Balance Sheet 2012 2013 2014 2015
Assets
Cash and Marketable Securities ₹ 25.60 ₹ 23.00 ₹ 32.10 ₹ 28.40
Accounts Receivable ₹ 99.40 ₹ 102.90 ₹ 107.30 ₹ 120.10
Inventories ₹ 109.60 ₹ 108.00 ₹ 114.90 ₹ 116.80
Other Current Assets ₹ 96.70 ₹ 91.40 ₹ 103.70 ₹ 97.50
Total Current Assets ₹ 331.30 ₹ 325.30 ₹ 358.00 ₹ 362.80
Property, Plant and Equipment, Gross ₹ 680.90 ₹ 734.30 ₹ 820.80 ₹ 913.10
Accumulated Depreciation ₹ 244.80 ₹ 296.80 ₹ 352.70 ₹ 427.90
Property, Plant and Equipment, Net ₹ 436.10 ₹ 437.50 ₹ 468.10 ₹ 485.20
Other Non-Current Assets ₹ 203.20 ₹ 205.10 ₹ 407.00 ₹ 456.30
Total Non-Current Assets ₹ 639.30 ₹ 642.60 ₹ 875.10 ₹ 941.50
Total Assets ₹ 970.60 ₹ 967.90 ₹ 1,233.10 ₹ 1,304.30
Liabilities and Shareholders' Equity
Accounts Payable ₹ 82.80 ₹ 77.10 ₹ 71.80 ₹ 80.50
Short-Term Debt ₹ 39.10 ₹ 29.70 ₹ 79.80 ₹ 110.30
Other Current Liabilities ₹ 152.00 ₹ 123.80 ₹ 172.10 ₹ 111.30
Total Current Liabilities ₹ 273.90 ₹ 230.60 ₹ 323.70 ₹ 302.10
Long-Term Debt ₹ 163.50 ₹ 145.00 ₹ 201.80 ₹ 218.10
Deferred Income Taxes ₹ 22.30 ₹ 19.60 ₹ 15.00 ₹ 12.70
Other Non-Current Liabilities ₹ 100.60 ₹ 80.10 ₹ 115.00 ₹ 94.50
Total Liabilities ₹ 560.30 ₹ 475.30 ₹ 655.50 ₹ 627.40
Paid-In Capital ₹ 46.90 ₹ 46.10 ₹ 38.20 ₹ 44.80
Retained Earnings ₹ 363.40 ₹ 446.50 ₹ 539.40 ₹ 632.10
Total Shareholders' Equity ₹ 410.30 ₹ 492.60 ₹ 577.60 ₹ 676.90
Total Liabilities and Shareholders' Equity ₹ 970.60 ₹ 967.90 ₹ 1,233.10 ₹ 1,304.30
FCFF
EBIT
Depriciation
NOPBT
Tax
NOPAT
CAPEX
OPEX
FCFF
PV Factor
DCF
Stage 1 DCF
TV
Stage 2 DTV
Value of firm
FCFE
EBIT
Depriciation
NOPBT
Tax
NOPAT
CAPEX
OPEX
FCFF
PV Factor
DCF
Stage 1 DCF
TV
Stage 2 DTV
Value of equity
2016F 2017F 2018F 2019F 2020F 2021F 2022F
1 2 3 4 5 6 7
₹ 1,401.12 ₹ 1,471.18 ₹ 1,544.73 ₹ 1,621.97 ₹ 1,703.07 ₹ 1,788.22 ₹ 1,877.63
₹ 728.58 ₹ 765.01 ₹ 803.26 ₹ 843.43 ₹ 885.60 ₹ 929.88 ₹ 976.37
₹ 672.54 ₹ 706.16 ₹ 741.47 ₹ 778.55 ₹ 817.47 ₹ 858.35 ₹ 901.26
₹ 406.32 ₹ 426.64 ₹ 447.97 ₹ 470.37 ₹ 493.89 ₹ 518.58 ₹ 544.51
₹ 81.08 ₹ 90.00 ₹ 99.90 ₹ 110.89 ₹ 123.09 ₹ 136.63 ₹ 151.66
-₹ 9.81 -₹ 10.30 -₹ 10.81 -₹ 11.35 -₹ 11.92 -₹ 12.52 -₹ 13.14
₹ 194.94 ₹ 199.82 ₹ 204.41 ₹ 208.64 ₹ 212.41 ₹ 215.65 ₹ 218.23
₹ 1.70 ₹ 7.87 ₹ 12.67 ₹ 17.67 ₹ 22.86 ₹ 28.20 ₹ 33.65
₹ 22.26 ₹ 23.55 ₹ 24.06 ₹ 24.58 ₹ 25.14 ₹ 25.72 ₹ 26.33
₹ 174.38 ₹ 184.14 ₹ 193.02 ₹ 201.73 ₹ 210.14 ₹ 218.13 ₹ 225.55
₹ 61.03 ₹ 64.45 ₹ 67.56 ₹ 70.60 ₹ 73.55 ₹ 76.34 ₹ 78.94
₹ 113.35 ₹ 119.69 ₹ 125.47 ₹ 131.12 ₹ 136.59 ₹ 141.78 ₹ 146.61
₹ 17.92 ₹ 17.92 ₹ 17.92 ₹ 17.92 ₹ 17.92 ₹ 17.92 ₹ 17.92
₹ 95.43 ₹ 101.77 ₹ 107.55 ₹ 113.20 ₹ 118.67 ₹ 123.86 ₹ 128.69
2016 2017 2018 2019 2020 2021 2022
₹ 131.16 ₹ 211.16 ₹ 294.56 ₹ 380.98 ₹ 469.94 ₹ 560.84 ₹ 652.97
₹ 117.69 ₹ 123.58 ₹ 129.76 ₹ 136.25 ₹ 143.06 ₹ 150.21 ₹ 157.72
₹ 123.30 ₹ 129.46 ₹ 135.94 ₹ 142.73 ₹ 149.87 ₹ 157.36 ₹ 165.23
₹ 106.49 ₹ 111.81 ₹ 117.40 ₹ 123.27 ₹ 129.43 ₹ 135.90 ₹ 142.70
₹ 478.64 ₹ 576.02 ₹ 677.66 ₹ 783.23 ₹ 892.30 ₹ 1,004.32 ₹ 1,118.62
₹ 1,013.54 ₹ 1,125.03 ₹ 1,248.78 ₹ 1,386.15 ₹ 1,538.63 ₹ 1,707.88 ₹ 1,895.74
₹ 508.98 ₹ 598.99 ₹ 698.89 ₹ 809.78 ₹ 932.87 ₹ 1,069.50 ₹ 1,221.16
₹ 504.56 ₹ 526.04 ₹ 549.90 ₹ 576.37 ₹ 605.76 ₹ 638.37 ₹ 674.58
₹ 456.30 ₹ 456.30 ₹ 456.30 ₹ 456.30 ₹ 456.30 ₹ 456.30 ₹ 456.30
₹ 960.86 ₹ 982.34 ₹ 1,006.20 ₹ 1,032.67 ₹ 1,062.06 ₹ 1,094.67 ₹ 1,130.88
₹ 1,439.50 ₹ 1,558.36 ₹ 1,683.85 ₹ 1,815.90 ₹ 1,954.36 ₹ 2,099.00 ₹ 2,249.51
₹ 85.47 ₹ 89.74 ₹ 94.23 ₹ 98.94 ₹ 103.89 ₹ 109.08 ₹ 114.54
₹ 140.11 ₹ 147.12 ₹ 154.47 ₹ 162.20 ₹ 170.31 ₹ 178.82 ₹ 187.76
₹ 116.29 ₹ 122.11 ₹ 128.21 ₹ 134.62 ₹ 141.35 ₹ 148.42 ₹ 155.84
₹ 341.87 ₹ 358.97 ₹ 376.92 ₹ 395.76 ₹ 415.55 ₹ 436.33 ₹ 458.14
₹ 218.10 ₹ 218.10 ₹ 218.10 ₹ 218.10 ₹ 218.10 ₹ 218.10 ₹ 218.10
₹ 12.70 ₹ 12.70 ₹ 12.70 ₹ 12.70 ₹ 12.70 ₹ 12.70 ₹ 12.70
₹ 94.50 ₹ 94.50 ₹ 94.50 ₹ 94.50 ₹ 94.50 ₹ 94.50 ₹ 94.50
₹ 325.30 ₹ 325.30 ₹ 325.30 ₹ 325.30 ₹ 325.30 ₹ 325.30 ₹ 325.30
₹ 44.80 ₹ 44.80 ₹ 44.80 ₹ 44.80 ₹ 44.80 ₹ 44.80 ₹ 44.80
₹ 727.53 ₹ 829.29 ₹ 936.84 ₹ 1,050.04 ₹ 1,168.71 ₹ 1,292.57 ₹ 1,421.26
₹ 772.33 ₹ 874.09 ₹ 981.64 ₹ 1,094.84 ₹ 1,213.51 ₹ 1,337.37 ₹ 1,466.06
₹ 1,439.50 ₹ 1,558.36 ₹ 1,683.85 ₹ 1,815.90 ₹ 1,954.36 ₹ 2,099.00 ₹ 2,249.51
₹ 194.94 ₹ 199.82 ₹ 204.41 ₹ 208.64 ₹ 212.41 ₹ 215.65 ₹ 218.23
₹ 81.08 ₹ 90.00 ₹ 99.90 ₹ 110.89 ₹ 123.09 ₹ 136.63 ₹ 151.66
₹ 276.02 ₹ 289.82 ₹ 304.31 ₹ 319.53 ₹ 335.50 ₹ 352.28 ₹ 369.89
₹ 96.61 ₹ 101.44 ₹ 106.51 ₹ 111.83 ₹ 117.43 ₹ 123.30 ₹ 129.46
₹ 179.41 ₹ 188.38 ₹ 197.80 ₹ 207.69 ₹ 218.08 ₹ 228.98 ₹ 240.43
₹ 100.44 ₹ 111.49 ₹ 123.75 ₹ 137.37 ₹ 152.48 ₹ 169.25 ₹ 187.87
₹ 76.07 ₹ 80.28 ₹ 83.70 ₹ 86.73 ₹ 89.28 ₹ 91.24 ₹ 92.48
₹ 2.91 -₹ 3.39 -₹ 9.65 -₹ 16.40 -₹ 23.68 -₹ 31.51 -₹ 39.92
0.88 0.77 0.67 0.59 0.52 0.46 0.40
₹ 2.55 -₹ 2.60 -₹ 6.51 -₹ 9.70 -₹ 12.29 -₹ 14.34 -₹ 15.93
-₹ 58.82
-₹ 499.11 [last year FCFF*(1+growth rate)/(Cost of Equity - Growth rate)]
-₹ 199.18
-₹ 258.00
₹ 194.94 ₹ 199.82 ₹ 204.41 ₹ 208.64 ₹ 212.41 ₹ 215.65 ₹ 218.23
₹ 81.08 ₹ 90.00 ₹ 99.90 ₹ 110.89 ₹ 123.09 ₹ 136.63 ₹ 151.66
₹ 276.02 ₹ 289.82 ₹ 304.31 ₹ 319.53 ₹ 335.50 ₹ 352.28 ₹ 369.89
₹ 96.61 ₹ 101.44 ₹ 106.51 ₹ 111.83 ₹ 117.43 ₹ 123.30 ₹ 129.46
₹ 179.41 ₹ 188.38 ₹ 197.80 ₹ 207.69 ₹ 218.08 ₹ 228.98 ₹ 240.43
₹ 100.44 ₹ 111.49 ₹ 123.75 ₹ 137.37 ₹ 152.48 ₹ 169.25 ₹ 187.87
₹ 76.07 ₹ 80.28 ₹ 83.70 ₹ 86.73 ₹ 89.28 ₹ 91.24 ₹ 92.48
₹ 2.91 -₹ 3.39 -₹ 9.65 -₹ 16.40 -₹ 23.68 -₹ 31.51 -₹ 39.92
0.86 0.74 0.64 0.55 0.47 0.41 0.35
₹ 2.50 -₹ 2.51 -₹ 6.16 -₹ 9.03 -₹ 11.23 -₹ 12.87 -₹ 14.04
-₹ 53.34
-₹ 499.11 [last year FCFF*(1+growth rate)/(Cost of Equity - Growth rate)]
-₹ 175.56
-₹ 228.90
Input Data Projection data
Rf 0.075 Sales 0.05
Default Rate 0 Cost of Sales 0.52 Value of Firm
Adj Rf 0.075 S&G Exp 0.29
Beta Depriciation 0.08
Levered 0.88 Other Exp -0.007
Unlevered 0.658 Interest Income 0.06
Rm 0.173 Interest Income 0.07
Risk Premium 0.098 Long 13.5
Cost of debt 0.12 Dividend 0.4
Post Tax cost of Debt 0.078 A/R 0.084
Tax Rate 0.35 Inventory 0.088
Debt Ratio 0.25 Other Assets 0.076
Equity Ratio 0.75 PP&E 0.11
Ke 0.16 A/P 0.061
Short Term Debt 0.1
WACC 0.14 Other CL 0.083
Value of Firm -₹ 258.00
Scenario Summary
Current Values: Optimistic
Changing Cells:
$F$3 0.05 0.07
$F$4 0.52 0.49
$F$5 0.29 0.28
$F$15 0.11 0.07
Result Cells:
$J$4 -257.99712768 -350.60551694
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
