Pro Forma and Valuation

PRO FORMA AND VALUATION

For purposes of the first deliverable, you will be computing a valuation for a theoretical segment of Procter & Gamble. I have provided an Excel template labeled “Procter & Gamble Data Matrix” with the relevant data for this part on the course website.  Please use the provided tab labeled “Pro Forma” for your pro forma financial statements.  Record your answers to the questions below on the “Answers” tab of the spreadsheet, provide a brief discussion of any additional assumptions you made on the “Assumptions” tab and upload the spreadsheet to the course webpage.  Note that P&G has a June 30 fiscal year-end.  For example, FY 2019 is from July 1, 2018 – June 30, 2019.

1. Compute Beta and Weighted Average Cost of Capital for the P&G segment and record it in the Answers tab.  Use the entire time series in the Returns tab to compute Beta.

1. Forecast sales for the 20 quarters from fiscal Q1 2019 to Q4 2023 based on the historical data provided in the Sales Forecast tab.  Use the first sixteen quarters (FY 2014 - 17) to estimate your forecasting model and the last four quarters (FY 2018) as a validation sample to arrive at the best fitting forecasting model.  Use your best fitting forecasting model (based on fit in the validation sample) to forecast sales for the next 20 quarters (Q1 2019 to Q4 2023).  Fill in your sales forecasts on the Forecast Pro Forma tab, leaving enough detail in the Sales Forecast tab so that we can understand the approach you took.  

Be sure to check if sales exhibit seasonality and trend, and think about using linear regression with a lagged dependent variable versus moving average.  In particular, compare (based on fit in the validation period):

1. Regression based on all variables (Q1, Q2, Q3, Trend and Lagged Sales).
2. Regression based on variables determined using Max-Validation R-square.
3. Forecasts based on the Moving Average of sales in the corresponding quarter of the three prior years.

Assumptions

In performing your analysis, make the following assumptions:

Tax rate: 20%

Risk free rate: 2%

Historical market risk premium: 6.5%

Cost of debt (yield on corporate debt): 2.5%

Debt-to-value ratio: 11.5%

Perpetuity growth rate (annual growth rate beyond FY 2023): 2%

• Forecast the Income Statement, Balance Sheet, and Statement of Cash Flows on a quarterly basis. Do your valuation as of the end of fiscal Q4 2018 (the most recent quarter), and assume that all cash flows occur at the end of the associated quarter (i.e., Q1 2019 cash flows would be discounted back one quarter).  Forecast quarterly cash flows through Q4 2023.

• You will need to figure out an annual weighted average cost of capital (WACC).  To convert it to quarterly, simply divide it by 4. The provided spreadsheet includes the stock price history for PG and the market, which you should use to compute beta.  Use the complete time series, 1/2013-6/2018, to compute beta.  Assume that capital structure and WACC will not change going forward.

• Forecast the Income Statement on the Forecast Pro Forma tab.

• You have already forecasted sales based on the discussion above.
• Forecast the remaining lines on the income statement based on the past income statements.  Assume that Cost of Sales and Other Expenses will remain constant as a percentage of sales at the average for the entire past series (Q1 2013 – Q4 2018).
• Assume that Interest Expense will remain constant going forward at $20 million per quarter ($80 million/year).

• Forecast the Balance Sheet assuming the following:

• Assume no sales of Property and Equipment (P&E).  
• Forecast quarterly depreciation as 3.5% of the previous quarter’s ending balance in “Property and Equipment, net”.  Depreciation is already included in “Other Expenses” on the Income Statement, but you will need the number for the Statement of Cash Flows.
• Assume that “Property and Equipment, net” will grow at the growth rate of Sales, but will not exhibit the same cyclicality as sales.  To compute the growth of “Property and Equipment, net”, start at the 4th fiscal quarter of a given year and compute the average growth in sales over the four quarters of that year.  Then, apply that average quarterly growth rate to “Property and Equipment, net” during each quarter of that year.  (For example, if quarterly sales growth averaged 1% during 2020, “Property and Equipment, net” would grow at 1% each quarter during 2020.)  
• Compute Capital Expenditures based on the change in “Property and Equipment, net” and the Depreciation from above.  
• Cash, Operating Assets and Operating Liabilities have been trending as a percentage of sales over time.  Assume that quarter-end balances for each of these accounts will remain the same percentage of sales as they were on average for the most recent eight quarters of data (fiscal 2017 and 2018).  I.e., compute each of the account balances as a percentage of sales for each of the most recent eight quarters and use that average going forward.  
• Assume Intangible Assets, Interest Bearing Debt, Contributed Capital and Other Shareholders’ Equity will remain at the same level as in Q4 2018.
• Use Treasury Stock as a plug for the balance sheet. Assume that any excess cash (above the Cash balance computed above) is used to repurchase shares and any cash shortfall is made up by issuing shares from Treasury. If you issue new shares, Treasury Stock will become less negative by the amount of cash generated and if you repurchase shares, Treasury Stock will become more negative by the amount of cash spent to repurchase.    

• For periods beyond 4th quarter, 2023, take the perpetuity of your projected free cash flows for 2023 (summed over the four quarters) using the perpetuity growth rate. Recall that the present value as of year 0 of a perpetuity that starts with a value of CF0 and grows at g% (2% annually) and is discounted at a rate of r% is: PV0 = CF0 * (1+g) /(r-g) = CF1 /(r-g). You may assume that future cash flows take place at year-end so that, PV2023 = CF2023 * (1+g) /(r-g).  To bring the balances to the current period, please discount them by 5 years (i.e., PV2023/(1 + r)5).

• Assume that the fair value of the segment can be computed by present valuing (Cash from Operations + Cash from Investing + After-Tax Interest).  Note this is the same as present valuing (((EBIT x (1 – Tax Rate)) + Depreciation – Change in Operating Assets + Change in Operating Liabilities – Capital Expenditures) as you did to compute enterprise value in finance class.

• Subtract Interest Bearing Debt ($3,200) to arrive at the fair value of equity.

...