Financial Statements to Estimate the Intrinsic Value of a Firm Listed on a US Stock Exchange

Develop the proforma financial statements to estimate the intrinsic value of a firm listed on a US stock exchange. Students can use the format provided in Chapter 6 of the textbook as guidance. Submission requires a write up and the spreadsheet you have used for calculations.

For the purpose of this assignment I have selected PepsiCo as my company. The biggest problem that I see in forecasting of financial statements is that, analyst tend to forget the business of the company. The reason I am bringing this up is because generally the sales growth rate is forecasted on the basis of the compounded annual growth rate or CAGR. This CAGR is generally taken of the last five years. But sales prediction is not that easy. We just cannot take the CAGR of the sales. This is because companies these days are massive, have multiple businesses, are present in different geographies, owns various subsidiary and associates and are involved in various joint ventures. Therefore, we have to take a look at the annual report of the company. We have to take the various business segment in their individual capacity and look at the sales growth for each and every segment, these segments can be on the basis of different products, such as in our above example, PepsiCo can be further bifurcated into Pepsi and Frito lays or these segment can be geographical in nature, like in our above example the company, PepsiCo, can be subdivided into geographical locations such as USA, Americas other than USA, Europe, MENA (Middle East Nations and Africa), China, Indian Subcontinent and Rest of Asia Pacific. Dividing on these basis gives us the true picture about the sales of the company, this is because a company might have entered a segment recently and thus the sales over there would be growing at faster pace than the company as a whole. Vice versa is also true, there may be a segment the company might be exiting which means no sales from that particular segment in the future. Sales are the most important for the purpose of forecasting, because almost all of the financial statements are dependent on the sales of a company.

Moving along the income statement, next we come across is cost of goods sold. COGS is mostly related to the amount of sales that we have made in the year, there might be a fixed cost related to it, but for the purpose of forecasting we tend to ignore that fixed cost and assume that the COGS is all of the variable cost and is directly proportion to the amount of sales that are made in that particular year. In our above calculation we have taken an average of last five years of COGS/Sales ratio to forecast the future COGS. From this we get the gross profit.

Moving along, next up is Selling, General and Administrative Expenses. Please note that, in PepsiCo depreciation is also a part of SG&A Expenses, therefore, it is very important before forecasting to segregate the depreciation from SG&A Expenses to properly forecast the future financial statements. The SG&A expenses are taken as a percentage of sales. The percentage has been calculated using the last five year’s average.

Next up is depreciation. For the purpose of depreciation, I calculated the depreciation rate by dividing the depreciation expense of the year with the closing Net closing Property, plant and equipment. Average of the five year’s rate was taken as the depreciation rate for the purpose of future forecasting. This rate was multiplied by the net PP&E of the year to arrive at the depreciation figure.

Next up is the interest expense. For the purpose of interest calculation, I have taken the average interest rate of the last five years. The interest rate was arrived by dividing the interest expense for the year and the closing long term debt figure. One could argue that we should have included the current portion of the long-term debt, but in this I have ignored the current portion and calculated the rate on the long-term debt only.

Unusual item generally includes forex expenses, restructuring charges, M&A Expenses among others. Regarding these we have two options either to totally ignore the expense or take it as a proportion of the sales. For PepsiCo, these are regular expenses and thus I have taken them as percentage of sales.

Last part of the income statement is about the tax expense. Tax rate can be taken as the applicable corporate tax in a country. But the problem with that is tax rate are never related to that corporate tax rate, as tax expenses also includes deferred expenses or incomes. Therefore, while calculating the tax rate we should take the average of the tax expense divided by EBT for the past years. For PepsiCo, we have taken the average of the four years, this is because in one year there is an unusual deferred tax asset is being created, which according to me is a one-off thing.

After completing the income statement, we move on to balance sheet. In balance sheet the most important thing to notice is the current asset and the current liabilities. These two things are directly related to the operation of the company. The increase or the decrease in the current assets or the current liability is directly related to the sales and therefore they are calculated on the basis of the sales of the company. Past 5 years average rate has been taken to forecast the current assets and current liability.

For Property plant and equipment, others assume that they will remain constant over the period of forecast. But here I have assumed that they are proportional to the sales of the company and thus increase or decrease as per the sales.

Same is the case with other long non-current assets and non-current liability, they can be assumed to remain constant over the years but in our case I have taken them to grow with the sales.

Coming to equity part, the company has already redeemed the preference shares and thus they will remain nil. All other components of equity other than retained earnings will remain constant over the period of forecast. This includes the minority interest also. Retained earnings is the balancing figure.