Transcription
FINANCIAL MODELINGFORCORPORATE VALUATIONPrepared byVIJAY MAHAWAR14PGGMS071Presented toSri Sri University, Cuttack, Odisha2014-2016 BATCH
2FINANCIAL MODEING FOR CORPORATE VALUATIONDECLARATION BY THE LEARNERThis is to declare that I have carried out this project work myself in part fulfillment of theTrimester VI of MBA program in General Management from Sri Sri University, Cuttack,Odisha.The work is original, has not been copied from anywhere else and has not been submitted toany other University/Institute for an award of any degree/diploma.Date: 28th March, 2016Signature:(VIJAY MAHAWAR)Place: Sri Sri University, Cuttack, OdishaCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
3FINANCIAL MODEING FOR CORPORATE VALUATIONCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
4FINANCIAL MODEING FOR CORPORATE VALUATIONCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
5FINANCIAL MODEING FOR CORPORATE VALUATIONTABLE OF CONTENTSCHAPTER 1: INTRODUCTION .6CHAPTER 2: EXCEL AS A MODELING TOOL .7CHAPTER 3: BLOOMBERG MARKET CONCEPTS .8CHAPTER 4: ABSOLUTE VALUATION .9Step 1: Estimate long-term future cashflows based on assumptions .9Step 2: Estimate discount rate aka “WACC” .10Step 3: Discount the estimated future cashflows by “WACC” .11Step 4: Take total firm value and derive Market Capitalization .11Step 5: Calculate the share price.12CHAPTER 5: RELATIVE VALUATION .13CHAPTER 6: ABSOLUTE VALUATION VS RELATIVE VALUATION .14CASE STUDY: FMCG SECTOR .14Exhibit: Balance Sheet Assumption .23Exhibit: Revenue Assumption .24Exhibit: Asset Schedule Assumption .26Exhibit: Financing Assumption .26Exhibit: Tax and Valuation Assumption .26Exhibit: Revenue Build Up .27Exhibit: Cost Build Up .28Exhibit: Debt Schedule .29Exhibit: Asset Schedule .30Exhibit: Balance Sheet .31Exhibit: Profit & Loss .32Exhibit: Cash Flow .33Exhibit: Ratio Analysis .34Exhibit: Valuation .35Exhibit: Comps .36APPENDIX I: REFERENCES .37APPENDIX II: BLOOMBERG TERMINAL CODES .38CAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
6FINANCIAL MODEING FOR CORPORATE VALUATIONCHAPTER 1: INTRODUCTIONFinancial modeling is the task of building an abstract representation (amodel) of a real world financial situation. This is a mathematical modeldesigned to represent (a simplified version of) the performance of a financialasset or portfolio of a business, project, or any other investment.The process by which a firm constructs a financial representation of some, orall, aspects of the firm or given security. The model is usually characterized byperforming calculations, and makes recommendations based on thatinformation. The model may also summarize particular events for the end userand provide direction regarding possible actions or alternatives.Financial models can be constructed in many ways, either by the use ofcomputer software, or with a pen and paper. What's most important, however,is not the kind of user interface used, but the underlying logic thatencompasses the model. A model, for example, can summarize investmentmanagement returns, such as the Sortino ratio, or it may help estimate marketdirection, such as the Fed model.In corporate finance, investment banking, and the accounting professionfinancial modeling is largely synonymous with financial statement forecasting.This usually involves the preparation of detailed company specific modelsused for decision making purposes and financial analysis.Applications include: Business valuation, especially discounted cash flow, but including othervaluation problems Scenario planning and management decision making ("what is"; "what if";"what has to be done") Capital budgeting Cost of capital (i.e. WACC) calculations Financial statement analysis (including of operating- and finance leases, andR&D) Project finance Mergers and Acquisitions (i.e. estimating the future performance ofcombined entities)CAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
7FINANCIAL MODEING FOR CORPORATE VALUATIONCHAPTER 2: EXCEL AS A MODELING TOOL Excel is one of the most widely used tools in financial industry– Easy to use– High reach & access to software across geographies– Flexibility– Robustness– Inbuilt features (Most people would not even be using 95% of thefeatures) & Extendibility– Modular and Object Oriented Architecture Excel as a data-store– Easy to store and retrieve information– Flexibility to put many data-types in the same sheet Functions and a range of features– Excel is easily extendible to be used as a Modeling tool Modeling Context– Understand the industry models being used– Create your own models Rather than just using them– Improve & enhance productivity in work– Extend these models for your use– Debug ProblemsCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
8FINANCIAL MODEING FOR CORPORATE VALUATIONCHAPTER 3: BLOOMBERG MARKET CONCEPTSAN INTRODUCTION TO FINANCE FROM BLOOMBERG.Bloomberg Market Concepts (BMC) is an 8 hours self-paced e-learning coursethat provides a visual introduction to the financial markets. BMC consists of 4modules –1.2.3.4.Economics,Currencies,Fixed Income andEquitiesThe modules are woven together from Bloomberg data, news, analytics andtelevision.CAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
9FINANCIAL MODEING FOR CORPORATE VALUATIONCHAPTER 4: ABSOLUTE VALUATIONAn absolute value is a business valuation method that uses discounted cashflow analysis to determine a company's financial worth. The absolute valuemethod differs from the relative value models that examine what a company isworth compared to its competitors. Absolute value models try to determine acompany's intrinsic worth based on its projected cash flows. Absolute valuation involves the discounting of future cashflows.The discount rate is higher for long-term cashflows and for riskier firms.Good financial models balance simplicity with insights.Absolute valuation is theoretically perfect.Absolute valuations are usually precisely wrong.The five steps involved in valuation using Absolute valuation are:Step 1: Estimate long-term future cashflows based on assumptionsCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
10FINANCIAL MODEING FOR CORPORATE VALUATIONStep 2: Estimate discount rate aka “WACC”CAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
11FINANCIAL MODEING FOR CORPORATE VALUATIONStep 3: Discount the estimated future cashflows by “WACC”Step 4: Take total firm value and derive Market CapitalizationCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
12FINANCIAL MODEING FOR CORPORATE VALUATIONStep 5: Calculate the share price.5 steps process in valuation summarized.CAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
13FINANCIAL MODEING FOR CORPORATE VALUATIONCHAPTER 5: RELATIVE VALUATIONA relative valuation model is a business valuation method that compares afirm's value to that of its competitors to determine the firm's financial worth.Relative valuation models are an alternative to absolute value models, whichtry to determine a company's intrinsic worth based on its estimated future freecash flows discounted to their present value. Like absolute value models,investors may use relative valuation models when determining whether acompany's stock is a good buy.P/E Ration Benchmarking:1. Itself2. Its Peers3. The Market Relative valuation is the quick and easy comparison of one valuation toanother The risk of relative valuation is being led astray by unrealistic referencepoints Earnings and multiples are used to estimate fair share prices Fast growing companies warrant high multiples and vice versa. The state of the economy is a key driver of relative valuation.CAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
14FINANCIAL MODEING FOR CORPORATE VALUATIONCHAPTER 6: ABSOLUTE VALUATION VS RELATIVE VALUATIONCASE STUDY: FMCG SECTORLooking through the window of his office, Akshay was wondering how quickly time haspassed by. The day was pleasant with bright sunshine outside. With a mug of coffee in hishand, he was looking at the day to day activities in the premises of Renuka ConsumerProducts Limited (“RCPL”).Akshay had just completed his MBA when his father expired in late 2004. Since then he hadtaken control of RCPL, a company started by his father and named after his mother Mrs.Renuka Devi.RCPL, a popular name in one of the western states of India, operates in fast movingconsumer products segments. It manufactures and sells three products: Soaps in packets of 100 gmsHair colour in bottles of 250 mlLiquid Detergents in the bottles of 500 mlSince the time Akshay took control of the operations at RCPL, the company has grown leapsand bounds. The volumes sold across each of the three categories of products have shownsignificant growth over last five years.CAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
15FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit 1: Volumes sold over FY05 – FY09Besides, RCPL has also managed to consistently increase the sale prices of its productsprimarily because of lack of competition in the areas where it has achieved goodmarketability.Exhibit 2: Sales Price MovementHowever, Akshay knows that things are no longer going to be as comfortable as they areright now. Specially, in a situation when a leading listed player in the consumer productsarea has announced plans to expand in the geographies where RCPL operates. Given thechanging dynamics of the business, Akshay was thinking through the potential course ofactions he can take to counter the increasing competition. As a first step he decided to takestock of the situation. He asked the chartered account for the historical financial statementsof the company.Exhibit 3: Historical P&L Statement of RCPLCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
16FINANCIAL MODEING FOR CORPORATE VALUATIONCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
17FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit 4: Historical Balance Sheet of RCPLCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
18FINANCIAL MODEING FOR CORPORATE VALUATIONWhile the past performance looked phenomenal, he knew that he along-with hismanagement team has to constantly improve and think ahead of curve lest RCPL should beeaten by the competition. He was well aware of the fact that however good his team mightbe, past performances may not be sustained in future unless he does plan well in advance.He quickly summoned his Sales and Marketing team to seek their inputs on the future saleprice points for the various products RCPL manufacture.Excerpts from the discussion he had with his Sales & Marketing team:“.Soap segment has seen a high growth phase so far, but due to enhanced penetration inthe rural markets, this segment will see a tapering growth rate from the expected 15% inFY10 to 12% in five years time. Due to lack of fashion consciousness in the villages, haircolour segment will, at the best see a CAGR of 0.5% less than historical CAGR over a fiveperiod time frame. .Liquid detergents have gained popularity in the rural markets and willcontinue to display a growth rate of 5% - 6% over the same time frame, very much in linewith its growth rate last year.”“.While RCPL has so far controlled the sale prices of these products at its will, the samemay not happen in future. It will be almost impossible to increase the prices of soaps anddetergents for the next two years and even beyond that, the increase will be limited to 2% to3%. Hair colour segment still has a possibility of seeing a price CAGR growth rate same athistorical levels.”Akshay quickly realized that in the competitive world, revenue growth alone may not fetchthe desired shareholders’ value creation. He quickly turned his attention to the key costelements in the P&L statement. He knew that raw material was the key constituent of thetotal cost base. There were just four categories of raw materials required for the operationsof RCPL.Exhibit 5: Raw materials requirement of key products of RCPLAcids were the single primary key raw material in terms of quantum of consumption.Specialty chemicals were added to achieve the desired colour and perfumes were added forfragrance. Akshay quickly noticed the raw materials price trend from the exhibit overleafprovided to him by the Materials DepartmentCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
19FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit 6: Raw Materials Price movementHe quickly invited Mr. Rao, Head of Materials Department for a discussion on the prices ofthe raw materials.Excerpts from the discussion he had with Mr. Rao“.For the acids, RCPL has entered into a long term contract with the leading supplier inthe region. Under the terms of the contract, acids will see a price escalation of constant 5%every year for the next five years. For specialty chemicals, he has multiple vendors and dueto high bargaining power RCPL enjoys with the suppliers, the price rise in this segment willbe restricted to 5% every year. Ordinary chemicals will display a price rise of 15% to 16%over the same time horizon while perfumes will display the historical CAGR in terms of itsprice growth.Power cost will display a growth rate in the range of 10% – 11%.”As soon as the discussion was concluded and Mr. Rao left the room; a very commandingand authoritative female voice shouted at him, “You never seem to keep track of time. It’slunch time, where had you been?”From the authority and power in the voice, it was evident that the voice was of none otherthan Raveena, Akshay’s wife. Raveena was Akshay’s childhood friend and was also hisfather’s choice as his wife. The two got married in early 2005 when Akshay took control ofRCPL and Raveena of Akshay. A wife’s entry into the scene need not always be welcomewhen one is thinking about the future, however this time Raveena’s entry was perfect intime. Raveena, besides being Akshay’s wife, was also the HR head of RCPL. Over lunch,Akshay figured out that his HR department had been quite cautious in growing themanpower in the organization.Exhibit 7: Manpower in RCPLRaveena told him that manpower count will remain the same for this year while she hadbudgeted a headcount increase of 5% beyond. Knowing that he must be thinking of the costcontrol, she anticipated his next question, thus displaying a quality rare in HR and providedinformation on the monthly salary figures of the employees in different categories.CAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
20FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit 8: Average monthly salaryShe also indicated that the employees have not received any salary hike this year; howevershe had budgeted for 5% hike year on year for the next five years. The firm will continue topay 15% of the salary as bonus in the years to come unless something unforeseen happens.The two had finished lunch, post which she left the room without any debate; something youwon’t expect at least from an HR professional who has been queried about the “resources”.Post lunch, it was time for a quick walk and he was joined by none other than Mr.Tibrewala, the guy behind the operations of RCPL. Akshay was aware that the packaging ofthe end products of RCPL was outsourced to another firm through a long term contract.Mr. Tibrewala clarified that packaging cost will see a meager rise of 2% after remainingconstant this year (with respect to FY09). Besides, he promised to mail Akshay thehistorical packaging rates.At times, our mailbox is so flooded with mails that we simply keep staring at it withoutknowing which ones to open first. But Akshay was quite clear in his mind and he clickedopen the mail from Mr. Tibrewala as soon as he reached his office after the walk.Exhibit 9: Packaging cost per unitAkshay knew that RCPL had not been an aggressive advertiser so far. However, in order toincrease penetration of the products and reach wider markets, advertisement-spend had toincrease. He has increased the budgetary allocation of advertising spend to 10% of salesthis year and he decided to raise it to 14% gradually over next few years. Besides toprevent the retailers and salesman from partnering with any new player in the area, heknew he would have to gradually increase their commission to 7% from the current levels of5%.Having been through key items of revenues and growth, it was now time to interact withCorporate Finance department about the future capital expenditure and their funding plans.The firm had raised a debt of Rs. 500 Lakhs at an interest rate of 12% in FY05 to be repaidin 20 equal installments beginning FY06. The capital expenditure plans for the firm was asfollowsCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
21FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit 10: Capital expenditure plansThe firm depreciated all capex items using a straight line method over a period of 10 years.Knowing the cash balance available with RCPL and good results so far in FY10, herealized that no additional external funding will be required in order to carry out theplanned expansion.Having assimilated all the information now, Akshay was now wondering how to make use ofthem to figure out the cash position of the company, its financial health and projectedperformance. He was also thinking of a possible way of arriving at the valuation of thecompany his father had created. He knew that he had a tough task in his hand. He wasreminded of an investment banker, Christopher, his batch mate from his MBA College.However, he did not want to employ his services as he feared the magnitude of the fees.Eventually, he called him to seek information on trading multiples of the listed companies inthis sector and also an update on the recent M&A or PE transactions that might havehappened.Gone are the days when investment bankers used to be working 100 hours a week. Thanksto the recent downturn, bankers now enjoy some free evenings with their loved ones.However, greed for fees never leaves them. Within minutes of the call, Akshay saw a mailfrom Christopher who has concisely compiled the data he was looking for.Exhibit 11: Trading multiples of the listed companies in FMCG sectorNote: *EV and M Cap in Rs. LakhsCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
22FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit 12: Transaction MultiplesAkshay has a voluminous and strenuous task at hand to determine the value of his firm.However, he had been quick to break a big problem into a series of smaller problems tofacilitate his task.Step 1:As a first step, prepare cash flow statements from the historical P&L and Balance sheetstatements for the period of FY05 to FY09Step 2:Identify key drivers of the revenue and cost. Prepare projected financial statements basedon the historical trends and the inputs gathered from various departments.Step 3:Based on projected financial statements, build a DCF model to arrive at an approximateequity value of the firm. Examine this valuation with respect to the trading multiples andtransaction multiples in the industry.CAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
23FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Balance Sheet AssumptionCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
24FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Revenue AssumptionCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
25FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Cost AssumptionCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
26FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Asset Schedule AssumptionExhibit: Financing AssumptionExhibit: Tax and Valuation AssumptionCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
27FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Revenue Build UpCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
28FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Cost Build UpCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
29FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Debt ScheduleCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
30FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Asset ScheduleCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
31FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Balance SheetCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
32FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Profit & LossCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
33FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Cash FlowCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
34FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: Ratio AnalysisCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
35FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: ValuationCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
36FINANCIAL MODEING FOR CORPORATE VALUATIONExhibit: CompsCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
37FINANCIAL MODEING FOR CORPORATE VALUATIONAPPENDIX I: REFERENCES1. Financial Modeling definition: https://en.wikipedia.org/wiki/Financial modeling2. URL (video): https://vimeo.com/1333486003. URL (About BMC): etconcepts/4. Financial Modeling material (case study) from Pristine –http://www.edupristine.comCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
38FINANCIAL MODEING FOR CORPORATE VALUATIONAPPENDIX II: BLOOMBERG TERMINAL CODESCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
39FINANCIAL MODEING FOR CORPORATE VALUATIONCAPSTONE Project ReportPrepared by VIJAY MAHAWAR (14PGGMS071)
Financial modeling is the task of building an abstract representation (a model) of a real world financial situation. This is a mathematical model designed to represent (a simplified version of) the performance of a financial asset or portfolio of a business, project, or any other investment. The process by
