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:

https://docs.google.com/spreadsheets/d/e/2PACX-1vSiEpLo7giy6qXrHGQpZOegK9p9oTFI9vIVK8UwUrKPKmbEMZp-qAQDeYiF71xBFg/pubhtml

All Relevant Tables:

P&G P&L Source