Skip to main content

3 Statement of P&G

 P&G's 3-statement was a lot more conventional than Netflix's, because it includes values like inventory and trade receivables. Consequently, it was a lot easier to comprehend. However, I still need to figure out how to calculate Net Cash Flow: once I figure that out, I will immediately add NCFs to both my Netflix and P&G models. 

I googled P&G 10-K for the years of 2019 and 2020. I copied the historical data from those 10-K's into what would be my P&L source and BS source sheets. Then, I copied the historical data from the sources sheets onto my assumptions sheets. For P&L Assumptions, which serves as the core of forecasting predictions, I created a switch for different scenarios. For most of the forecasted values, I used % of revenues as the basis for future growth rates. To switch between scenarios, I used the CHOOSE function. For the BS Assumptions sheet, I didn't use a scenario switcher, because most of the values were going to use % of revenues as the forecasted growth rate. The only exception to this was the forecasting of trade receivables, trade payables and inventory values. To forecast these values you have to divide these values by revenues (for trade receivable) or cost of goods sold ( for inventory and trade payable) and then multiply by 365, since we are doing an annual report. 

For the P&L sheet, I used the SUMIF function to move the values from P&L Assumptions onto the P&L sheet easily. The main assumption I made for forecasting was assuming that Goodwill and indefinite-lived intangibles impairment changes would be flat or 0 for every year, except for 2019 where it was an exception. As quoted in P&G's 10-K, "During fiscal 2019, we determined that the estimated fair value of our Shave Care reporting unit was less than its carrying value. We also determined that the estimated fair value of the Gillette indefinite-lived intangible asset was less than its carrying value." So it seems that due to the estimated fair value of these intangible assets being less than the carrying value, further deductions were made unlike in 2018 and 2020. I feel like make any further assumptions without the proper knowledge in how intangible assets are evaluated would hurt the legitimacy of this valuation. 

For the BS sheet, other than the Cash and cash equivalents forecasted values which could not be predicted without knowing how to get the Net Cash Flow values from Unlevered Free Cash Flow, I was able to forecast values with no hiccups. Once again, I used SUMIF to ease the tedium, and I applied a check to make sure the values balanced to 0. But once again, this check isn't a 100% legitimate due to the unknown forecasted cash values. 

In the case of the Cash Flow statement, the only problem is that I couldn't calculate the NCFs. Moreover, for the calculation of 'Investments in other assets/liabilities', I subtracted using both current and fixed assets/liabilities, and may not be the most accurate approach. Other than that, filling out the CF statement was not much of an issue. Using values from both the P&L and BS, I filled out the CF. and received the UFCF values at the end. Using these values I will make a DCF model in order to evaluate P&G's stock and determine whether its current stock price is overvalued or undervalued based on its future cash flows. 

Moreover, I will add NCF and Cash and cash equivalents calculations to both my Netflix and P&G models once I have understood how to calculate NCF from UFCF. 

P&G 3 Statement:

All Relevant Tables:

P&G P&L Source 

P&G BS Source 

P&G P&L Assumptions 

P&G BS Assumptions






Popular posts from this blog

Apple Multiples Valuation

Unlike a traditional DCF model, doing a multiples analysis with comparable companies is a lot easier and quicker to do. The four companies I used to compare with Apple are: Microsoft, Amazon, Netflix, and Google. I used Microsoft because Apple and Microsoft are competitors in the field of hardware, mainly computers. Amazon and Apple are comparable because they're both tech giants in their respective fields and even have services in common such as Amazon Prime/Apple TV and Siri/Echo. Similarly, Netflix and Apple compete with each other in regards to their streaming services. Google and Apple both produce hardware like  home-pods, phones and computers, despite Apple being considerably larger.  The multiples that I used for the valuation are: EV/Sales, EV/EBITDA, EV/EBIT, and P/E. EV is the Enterprise Value of a company, which can be calculated by subtracting cash and cash equivalents and adding total debt to the Market cap. EV is the true value of a company while taking into account

DCF Valuation - Netflix

Time to put everything together with this blog post looking at DCF for Netflix. In the case of WACC, I got a percentage of 12%. I still have difficulties calculating it, so I know I need to improve on that front.  Conclusion: Netflix stock is overvalued at 12%. I think in order to make my analysis more effective I can include a What-If analysis that includes a variety of WACC and perpetual growth rate percentages. 

3-Statement Model - Netflix

To be honest, this was a lot more time consuming than I thought it would be. But, I'm glad I got it done. First thing to note with this 3S model is that it was designed specifically to integrate with a DCF Valuation, which will be shown in the next blog post.