Financial Analysis on Square Pharmaceuticals Limited.
Department of Finance and Accounting
Financial Modeling using Excel
Section: 1
April 21, 2018
Prepared for,
Ms. Farhana Zahir
Prepared by,
Rasheeq Ryan Khan1430321030
Nazmul Haq1430496030
Sadia Tabassum Bristi1430679030
Letter of Transmittal
April 21, 2018
Ms. Farhana Zahir
Senior Lecturer, Department of Accounting & Finance School of Business & Economics
North South University
Subject: Report on financial modelling of Square Pharmaceuticals Limited.

Dear Ma’am,
It is indeed a great pleasure to have the opportunity to submit the report. We are really grateful for having the opportunity to do so under your guidance and for having gained the knowledge that helped vastly in the completion of this report. We have enjoyed preparing the project. In preparing this project, we have tried our level best to include all the relevant information of Pro forma modelling that we learned in class. As we are beginners, we may make some mistakes. If you consider our mistakes, we will be very pleased and it will be very helpful for our future career. Furthermore, if you have any query about this project please feel free to ask any of our group members.
Sincerely Yours,
Rasheeq Ryan Khan
Nazmul Haq
Sadia Tabassum Bristi
Executive Summary
The purpose of this financial modelling report is to conduct an analysis of Square Pharmaceuticals Limited and make projections of their future position. Models such as anticipated sales growth, cost of goods sold, etc. has been projected. These were used to come up with pro forma statements for the next 5 years. Several assumptions have been made to support our analysis. Finally, we have come up with a share value and compared it with their current share value.

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!

order now

TOC o “1-3” h z u Company Overview PAGEREF _Toc512110171 h 1Product PAGEREF _Toc512110172 h 1Pricing PAGEREF _Toc512110173 h 2Promotion PAGEREF _Toc512110174 h 2Distribution PAGEREF _Toc512110175 h 2Mission PAGEREF _Toc512110176 h 3Vision PAGEREF _Toc512110177 h 3Industry Analysis PAGEREF _Toc512110178 h 4The Model PAGEREF _Toc512110179 h 5Sales Growth PAGEREF _Toc512110180 h 5Cost of Goods sold PAGEREF _Toc512110181 h 8Current Assets and Current Liabilities PAGEREF _Toc512110182 h 9Net Fixed Assets and Depreciation PAGEREF _Toc512110183 h 10Dividend Growth PAGEREF _Toc512110184 h 12Further assumptions PAGEREF _Toc512110185 h 12Results PAGEREF _Toc512110186 h 13Pro Forma Statement PAGEREF _Toc512110187 h 13Income statement PAGEREF _Toc512110188 h 13Balance sheet PAGEREF _Toc512110189 h 14Free cash flow PAGEREF _Toc512110190 h 14WACC PAGEREF _Toc512110191 h 14Valuation PAGEREF _Toc512110192 h 15Reference PAGEREF _Toc512110193 h 21
Prev. Close 313.6
Day’s Range 313-314.7
Revenue 39.13 B
Open 314.7
52 wk Range 269.77-335
EPS 15.76
Volume 260,133
Market Cap 231.61 B
Dividend (Yield) 3.26 (1.04%)
Average Volume (3m) 520629
P/E Ratio 19.93
Beta 0.67
One Year Change 12.02%
Shares Outstanding 737391090
Net Sales
2015 26,212,862,068
2016 29,058,349,851
2017 28,885,939,051
Operating Income
2015 7,304,768,902
2016 8,910,591,464
2017 8,169,344,686
Net Income
2015 5,559,701,088
2016 7,507,095,819
2017 8,099,245,404
Gross Margins
2015 39.11%
2016 41.82%
2017 42.23%
Operating margin
2015 24.12%
2016 26.51%
2017 24.53%
Net Margin
2015 18.36%
2016 22.33%
2017 24.32%
Revenue Growth
2015 25.36%
2016 10.86%
2017 -0.593%
Operating Profit Growth
2015 45.84%
2016 21.98%
2017 -8.32%
Company OverviewSquare Pharmaceuticals Ltd. (SPL), the pharmaceutical giant in the country, is a trusted name in the industry of manufacturing quality medicines for more than four decades. From the inception in 1958, it has today burgeoned into one of the top line conglomerates in Bangladesh. SQUARE Pharmaceuticals Ltd., the flagship company, is holding the strong leadership position in the pharmaceutical industry of Bangladesh since 1985 and is now on its way to becoming a high performance global player.

SQUARE Pharmaceuticals Limited has been continuously in the 1st position among all national and multinational companies since 1985. It was established in 1958, converted into a public limited company in 1991 and listed with stock exchanges in 1995. The turnover of Square Pharma was Taka 30.28 Billion (US$ 385.22 million) with about 18.64% market share having a growth rate of about 25.36% (April 2014– March 2015).

ProductSquare’s product is viewed among the most quality products in the country. This quality image has increased its credibility among the doctors. It is also pioneer in introducing many new products sought by the doctors. Introducing new products is one of the important objectives of the company. But there are some complaints regarding packaging of the products. But the company has now concentrated in this area and working hard to bring attractive and good packaging
Net Profit Growth
2015 33.92%
2016 35.03%
2017 7.89%
Earnings Per share
2015 10.36
2016 10.95
2017 11.36
2015 30%
2016 40%
2017 45%

Book Value per share
2015 56.09
2016 65.04
2017 71.49
Net Working Capital
2015 7,183,152,033
2016 14,372,183,997
2017 20,814,385,970
2015 158,561,700.00
2016 12,584,309.00
2017 295,466,575.00
Total Debt
2015 659,147,818
2016 –
2017 –
2015 3,892,185,634
2016 8,522,555,131
2017 15,768,683,854.00

PricingGovernment fixes Price of most of the essential drugs. The number is 118 products. The company can fix price of other products but needs to take approval of government. In pricing a product, Square Pharmaceuticals Ltd. usually follows target pricing. Premium prices cannot be charged, as all the competitor products are similar and not much distinguishable from each other. But prices of some products are still higher than the competitors. But since Square Pharmaceuticals Ltd. does not compromise with the quality, sometimes they have to charge higher to ensure the highest quality possible.

PromotionPersonal selling is the main weapon in pharmaceutical industry. Medical representatives of the company go to the doctors to promote the products. The quality of medical reps is assumed to be the best in Square Pharmaceuticals Ltd. They are selected after a careful scrutiny and are sent to market after some extensive training. This helps Square Pharmaceuticals Ltd. to maintain the quality of its medical reps. Advertising can be given only in magazines related to health profession. Square also utilizes every opportunity to explore this area
DistributionSquare distributes its products all over the country using its own distribution channel. It has a large number of vehicles and sales depots to ensure coverage of the whole country. Its coverage is the best in the country.

Although Square started its operation in pharmaceutical sector and leader in the field, it is today a synonym of quality Debt/ Equity
2015 2.12%
2016 –
2017 –

toiletries, health products, textile products and AgroVet products. It has also expanded its business in real estate, engineering construction, hospitals, electronic media and other trade ; services. SQUARE is now one of the fastest growing and fastest diversifying conglomerate in Bangladesh.

MissionSquare Pharmaceutical Limited’s mission is to produce and provide quality & innovative healthcare relief for people, maintain stringently ethical standard in business operation also ensuring benefit to the shareholders, stakeholders and the society at large.

VisionThey view business as a means to the material and social wellbeing of the investors, employees and the society at large, leading to accretion of wealth through financial and moral gains as a part of the process of the human civilization.

Their objectives are to conduct transparent business operation based on market mechanism within the legal & social framework with aims to attain the mission reflected by their vision.

Industry AnalysisThe pharmaceutical sector, which is widely regarded as an influential industry, is the most developed among the manufacturing industries in Bangladesh. The industry satisfies more than 90% of the domestic demand and also controlled by Directorate General of Drug Administration (DGDA) and Pharmacy Council of Bangladesh (PCB). The sector produces generic drugs where 80% of it is sold domestically and the rest 20% is patented by companies.

Currently 150 companies are operating in the market. “A recent research from BMI Research suggests, Bangladesh’s pharmaceutical market will continue to post relatively high growth rates in 2017, an estimated +11.1% in local currency terms and +8.1% in US dollar terms from BDT 190bn (USD 2.4bn) in 2016 to BDT 211bn (USD 2.6bn) in 2017. by 2020 the market is estimated to be USD 3.369bn.” (future startup). The market size in 2008 was BDT 47 billion with a growth of 6.9%. Emerging markets also hold promise for Bangladesh’s exports: their spending for pharmaceutical products stood at $249 billion in 2015 and is expected to reach $340-$370 billion by 2020. In the year 2015-16, the annual sales of pharmaceutical products stood at Tk 15,600 crore, a huge increase in the industry size which was only Tk 170 crore in 1982.

“One of the fastest growing sectors, Bangladesh’s pharmaceutical industry is the third largest tax paying industry in the country. Bangladeshi pharmaceutical firms focus primarily on branded generic final formulations using imported APIs. Branded generics are a category of drugs including prescription products that are either novel dosage forms of off-patent products produced by a manufacturer that is not the originator of the molecule, or a molecule copy of an off-patent product with a trade name. About 80% of the drugs sold in Bangladesh are generics and 20% are patented drugs. The country manufactures about 450 generic drugs for 5,300 registered brands which have 8,300 different forms of dosages and strengths. These include a wide range of products from anti-ulcerants, flouroquinolones, anti-rheumatic non-steroid drugs, non-narcotic analgesics, antihistamines, and oral anti-diabetic drugs. Some larger firms are also starting to produce anti-cancer and anti-retroviral drugs.” (Brac EPL)

The ModelIn the construction of the pro forma income statement, balance sheet, free cash flow statement and ultimately determining the share value, we have taken a number of percentage value Models that influence the income statement and balance sheet. Multiple factors account for each model as shown below.
Sales GrowthSales growth is the most important model in our project. A lot of the other models are percentages based on the projected yearly sales growth. Our initial assumption was to take a CAGR of the last 5 years to project future sales growth but that was a naïve assumption. Then we tried to work out an auto regression model instead of an actual percentage growth, however the figures generated by the model did not match our assumptions.
Year Total sales Year on year growth
2013 20,202,005,922  
2014 23,268,413,217 15.18%
2015 26,684,573,116 14.68%
2016 40,423,958,859 51.49%
2017 36,543,140,106 -9.60%
Finally, we found out several factors that might influence the sales growth and made an assumption of how significantly each factor may affect the sales growth, then made a weighted average percentage as a model for the sales growth. The factors affecting sales growth are listed below:
Growth Measures
AAGR 17.94%
CAGR 15.97%

Industry growth: One of the two primary factors in our sales growth assumption. Several drivers influence the growth of the pharmaceutical industry. The economic growth is a primary driver. The GDP growth rate of Bangladesh for 2016-2017 period was around 7.3%. Bangladesh is also targeting higher Middle income growth by 2021. The GDP is growing at a faster rate than the population. As the health care expenses also rise, this plays a key role in growth of the pharmaceutical industry. Population growth is another driver of industry growth. Bangladesh is a densely populated country. As the population grows, there is a need for more medical expenses by the government and less restrictions for people seeking aid. Steps have also been taken to increase people’s awareness of medical needs. The life expectancy of people in Bangladesh has also increased. Taking all these factors into account, there is a grand opportunity for growth in the pharmaceutical industry. We are currently observing the success and growth of the industry.
Average company growth: Another major factor, it is a key element in sales growth increase. Square Pharmaceuticals is set to make investments in new pharma plants and technology from their generated internal funds according to their annual reports. Increase in operational efficiency, R;D developments and higher training of employees contribute to the growth rate of the company. The historical average growth rate of the company was used to forecast future growth.
Growth in market share: Of all the companies that comprise the pharmaceutical sector of Bangladesh, Square has the highest market share. The market share fell slightly in 2017 but we predict it will rise because of the investments in technology, mergers with Square Formulations and Square Herbal ; Nutraceuticals and the establishment of a new subsidiary in Kenya.
Exports: Growth in the export of medicine from Bangladesh will contribute to the sales growth of Square. There has been massive growth in exports from Bangladesh in the last 5 years. This is expected to grow as barriers to trade go down further. Bangladesh also has plans for a deep sea port in Chittagong. Exports are expected to increase greatly when it is complete.
Kenyan Subsidiary and Increase in products: There has been about a 5% increase in Square Pharma products from 2015 to 2017. It is a minor component in the sales growth. Furthermore, as the future of the Kenyan subsidiary is still uncertain as in is only in development right now, it is also a minor component for out projected sales growth.
Finally, the historical sales growth was taken as the CAGR of the sales of the last 5 years. The major factors, Industry growth and Growth of the company were given a 25% significance in determining sales growth. Growth in market share and Historical growth were given 15% each, Export was given 10%, Kenyan Subsidiary and Increase in products were given 5% each. These add up to a total of 100% in our assumptions of sales growth.

Cost of Goods sold
For the Cost of goods sold of our pro forma statement, we took into account the cost of goods sold, selling and distribution expense and administrative expense which make up the total operating expense. We took the operating expense of the last 5 years from the income statements and subtracted the depreciation costs of selling, distribution and administrative expense, from them. We divided the value with the yearly revenue from and generated cost of goods sold as a percentage of sales for the last 5 years. It was apparent that each year the percentage was decreasing. So we calculated the per year change in percentage and took the average of all years. Then we generated our final model forecast of cost of goods sold as a percentage of sales by taking the figure calculated for the 2016-2017 period and accounted for the average percentage change. However, our model percentage may rise as the government might increase fuel prices.
Current Assets and Current Liabilities
For both these figures, we divided the operating current assets and current liabilities with the sales revenue for the period of 2016-2017 and came up with current asset and current liabilities as a percentage of sales. We used the percentage generated as a forecast for next 5 years.
Net Fixed Assets and Depreciation
Net Fixed asset/Sales
55% Since Property, Plant and Equipment is the only fixed asset we are assuming for Square Pharmaceuticals, we calculated the fixed asset at cost and also the depreciation of the last 5 years. Then we deducted the depreciation from fixed asset at cost to give us the net fixed asset. Furthermore, we divided all the historical net fixed asset with their respective sales each year. Finally, we did an average of all those historical percentages and obtained a value.
Depreciation Factories Rates
Building 10%
Boundary Wall 10%
Plant ; Machinery 15%
Laboratory Equipment 10%
Furniture ; Fixture 10%
Office Equipment 10%
Computer 10%
Motor Vehicles 20%
Motor Vehicle-Lease 20%
Electromechanical Equipment’s 15%
Electrical Installation 15%
Gas Line Installation 15%
Head Office and Others  
Building 10%
Boundary Wall 10%
Furniture & Fixture 10%
Office Equipment 10%
Computer 10%
Motor Vehicles 20%
Motor Vehicle-Lease 20%
Motor Cycle 20%
Books & Periodicals 30%
SAP Software 20%
VSAT 10%
Electrical Installation 15%
Average Depreciation 14%
During the calculation of depreciation, we first found out all the items which falls under PPE along with their depreciated rates from the annual report. Then we calculated an average annual depreciation rate as a forecast for the next 5 years.
Year 2012-2013 2013-2014 2014-2015 2015-2016 2016-2017
Dividends Paid 662,086,900 926,921,660 1,445,997,789 1,662,897,456 2,494,346,184
Year on Year growth   40% 56% 15% 50%
CAGR 7.7%        
To calculate the dividend growth, we took the amount of dividends they paid in the last 5 years from the cash flow statement and calculated the year on year growth. We then calculated the CAGR of the dividends and used the percentage generated to forecast the dividend growth for the next 5 years. Square Pharma has had varying dividend policies in the last 5 years.
Further assumptionsAs per the current policy for listed companies in Bangladesh, a 25% tax rate has been taken.
Bangladesh’s current 91-day treasury bill rate has been used for Interest on cash and marketable securities.
The company currently has zero long term debt but plans to take a loan for their subsidiary in Kenya. The current interest rate on debt for Kenya’s central bank, 9.5%, has been taken to forecast the next 5 years as it is difficult to determine the changes in interest rate over the forecasted period.
Equal installment payment of loan for the subsidiary over a six-year period.

We have assumed a long term FCF growth rate of 4% based on our anticipation of movements in inflation and GDP.
Market Multiple has been used to calculate expected market return used in CAPM.

Classic CAPM model has been used to calculate cost of equity.
ResultsFrom our pro forma statements, we expect the profits after tax to increase per year. However, the free cash flow is expected to rise for the first 2 years then decrease each year after that. This is because Square Pharma has to pay back their loans obtained for the Kenyan subsidiary.
For our overall anticipated share value, we have taken an average of our valuation and the share value calculated from P/E ratio and earnings per share. The company seems to be slightly undervalued.
Pro Forma StatementIncome statementTo construct the pro forma income statement, each element mentioned above has been used. For the 2016 – 2017 base year period, the value from their income statement has been taken. The sales for the next 5 years has then been forecasted by applying the sales growth rate. The cost of goods sold has then been calculated by multiplying the sales for the year with the cost of goods sold as a percentage of sales. The interest paid on debt and interest earned on cash and marketable securities are calculated after the balance sheet is complete. Same goes for depreciation, which is applied on the fixed asset at cost from the balance sheet. Interest earned on cash and marketable securities is added and everything else is deducted, to obtain the profit before tax. The base year value for dividend is obtained from the free cash flow statement and the dividend growth rate is applied to this value to forecast the next 5 years. Tax is deducted to obtain the profit after tax, after which dividends are deducted to obtain the retained earnings.

Balance sheetThe current assets obtained for each year by multiplying the rate calculated with the sales for each year. The base year values for the fixed asset at cost and depreciation are obtained from the annual report and the net fixed asset is calculated. The rate calculated for net fixed asset is then applied for the forecasted 5 years. Accumulated depreciation is deducted from this value to obtain fixed asset at cost for the forecasted years. The other asset value is taken from their balance sheet and a constant amount is applied for the whole period.
Current liabilities are calculated in a similar manner to current assets. The base year value for debt is zero. Since Square Pharma plans to take a long term loan for their subsidiary in Kenya, the amount of debt they will take is used for the 2017-2018 period. They plan to pay of their loan in equal installments for the next 6 years. Debt for the following years have been applied accordingly. Value for stock and non-controlling interest are taken from their balance sheet and a constant amount is applied for each year. The base year accumulated retained earnings is also taken from their balance sheet. The following amounts for retained earnings are calculated by adding the retained earnings obtained in the income statement.
Finally, cash and marketable securities are calculated after which total assets are balanced with total liabilities. Interest on debt and cash and marketable securities for the income statement are then calculated.
Free cash flowFor the free cash flow statement, Profit after tax is first taken from the income statement. The depreciation is added back. The net working capital and capital expenditure is subtracted. Tax is applied on interest on debt and added back. Tax is also applied on cash and marketable securities and deducted. This is how the free cash flow is obtained.
For the WACC, the classis CAPM model that we learned is applied. Equity value is calculated using shares price and number of shares outstanding obtained from the DSE website. The beta is calculated using regression of the daily closing price of Square Pharma with the DSEX index. The expected market return is calculated and risk free rate obtained online. Using these values, the cost of equity is calculated with the CAPM model. Since they currently have zero debt, it is not used in the calculation of the WACC. The cost of equity and equity value is used to obtain WACC.
ValuationFor our projection of the share price, first the free cash flow for the projected 5 years is taken. Terminal value is calculated using WACC and long term FCF growth. Using the values obtained, the enterprise value is calculated using NPV. Initial cash value, which is the cash and marketable securities from the base year of the pro forma balance sheet, is added back. There is no year zero debt to deduct. Finally, the share price is obtained by dividing the equity value with the number of shares outstanding.
Percentages Sales growth 13.69%
Current assets/Sales 63.36%
Current liabilities/Sales 6.46%
Net fixed assets/Sales 54.89%
Costs of goods sold/Sales 58.98%
Depreciation rate 14.00%
Interest rate on debt 9.50%
Interest on cash & marketable securities 3.31%
Tax rate 25.00%
Dividend growth 7.72%

Alpha 0.000487
Beta 0.388351
R-squared 0.071719
Covar(Square Pharma,DSE) 1.5827E-05
Variance(DSE) 4.07544E-05
Beta 0.388350594
Market Return   Market price/earnings multiple, 7 April 2018 315.2
Equity cash flow payout ratio 50%
Anticipated growth of market equity cash flow 7.72%
Expected market return, E(rM) 7.89%
Shares outstanding 737391090
Share price, 7 April 2018 315.2
Equity value, E 2.32426E+11
Net debt, D 0.00
Tax rate, TC 25%
Cost of debt, rD 0
Expected market return, E(rM) 0.078926129
Risk-free rate, rf 3.31%
Equity beta, b 0.388350594
    Cost of equity, rE 0.050918624
WACC 5.09%

P/E Ratio 20.23
Earnings per Share 15.51
Share Value 313.77
Enterprise value: PV of FCFs and terminal value 266,589,285,056.22
Add back initial cash 5,430,649,473.00
Asset value 272,019,934,529.22
Subtract year 0 debt 0.00
Imputed equity value 272,019,934,529.22
Divide by # shares outstanding 737391090
Share value 368.90
Average Estimated Share value 341.33
Current market value per share 315.20