Financial Statement Modelling Proctor & Gamble 2013-2023

Financial Statement Modelling Proctor & Gamble 2013-2023

We started off by finding all the numbers that was supposed to be in the model (template). We had some difficulties finding all the correct numbers, given that the categories from the webpage and the template had some different labels, but in the end we found all the numbers and also calculated the numbers for the next 10 years. To find most of the numbers from year 1 to 10 we multiplied the last year's number by a functional relationship. For instance to find the number for sales in year 1, we multiplied the last year's sales with the growth rate of 4 %. Then we could find cost of goods sold for year 1 by multiplying the sale in year 1 with the cost of goods sold/sales ratio from year 0.

We had some problems calculating the free cash flow in the start, but once we found out that we forgot to bring in the change from the year before we got some reasonable numbers. They showed that P&G's cash flow would increase every year for the next 10 years.

We calculated the terminal value by using the constant growth model. We assumed that the WACC was 10 % and that the growth rate was 2 %.

After finding the terminal value we discounted the free cash flow and found that it was decreasing every year until year 10. When we had found the discounted cash flow we could easily compute the enterprise value, which turned out to be $ 141 377 000 million.

The last thing we did was to conduct a sensitivity analysis. We took the long run FCF growth and WACC as the two variables and found the estimated value of the company for various combinations of these numbers.

...