Accounting Analysis: Royal Bank of Canada, Hewlett Packard, and Exxon Mobil - Example

The companies we selected are: Royal Bank of Canada (RBC) (Banking Industry, Listed on NYSE) Hewlett Packard (HP) (Technology Industry, Listed on NYSE) Exxon Mobil (EM) (Oil and Energy Industry, Listed on NYSE) For theses three companies, we searched from the annual report, financial report, and seasonal report to find out the required data (Turnover, Operating Profit and Net Profit after Tax). Data sources are: http://www.rbc.com/investorrelations/quarterly-financial-statements.html (Royal Bank of Canada) https://www.stock-analysis-on.net/NYSE/Company/Hewlett-Packard-Co/Financial-Statement/Income-Statement (Hewlett Packard) https://www.stock-analysis-on.net/NYSE/Company/Exxon-Mobil-Co/Financial-Statement/Income-Statement (Exxon Mobil) Part a: For each of the Excel files bellowed, components are identified as: St – Seasonal Tt – Trend Ct – Cyclical Rt – Random (presents every time series analysis) Time series analysis for Royal Bank of Canada: 1. Turnover: 2. Operating Profit: 3. Net Profit after Tax: Time Series analysis for Hewlett Packard: 1. Turnover: 2. Operating Profit: 3. Net Profit after Tax: Time Series analysis for Exxon Mobil: 1. Turnover: 2. Operating Profit: 3. Net Profit after Tax: Calculation Process: 1. Use the data we got and put it into Excel. 2. Trend Analysis: a. Use the regression method to decide which model we should use to do the trend analysis. b. Two different models can be used for the long-term trend: i. Linear model (y = b0 + b1t + e) , or ii. Quadratic model (y = b0 + b1t + b2t2 + e) c. Insert a time series T as a number sequences (1, 2, 3…) and a time square series T2 (1, 4, 9 …). d. Use Excel Regression analysis to calculate: i. Use the real data sets (Turnover, Operating Profit and Net Profits after Tax) as the Y value. ii. Use T as X value for the linear model. iii. Use T and T2 as the X value for the quadratic model. iv. Excel will provide the result for each model. e. Select the model with a higher “R Square” (more close to 1) as the more accurate model which we use to calculate the trend. f. Reflect to the model formula to calculate the Trend, which in the Excel sheet shown as Tt. 3. Cyclical Analysis: a. Determine the trend line as we showed above (either y = b0 + b1t + e or y = b0 + b1t + b2t2 + e). b. For each time period, calculate the Y^t by the formula above. c. The percentage of the trend is calculated by the formula: Yt/Y^t*100%. d. Inside the Excel sheet, the Cyclical trend is presented by using Ct. 4. Seasonal Analysis: a. Calculate the Moving Average for each four quarters (Moving Average in the Excel Sheet). b. Calculate the Centre Moving Average from previous result (CMA). c. Use the formula Yt/CMA to calculate the Seasonal Trend and Random Trend St, Rt. d. Calculate the average of each quarter’s St, Rt to get rid of Rt. e. Normalize the seasonal trend value by making the total value as 4. f. The Seasonal Trend is showed as St in the Excel sheet. 5. Random Analysis a. Random trend is calculated by StRt/St. b. Random trend is in every single time series and it is marked as Rt in the Excel sheet. Part b: 1. Royal Bank of Canada Data for the bank does not reflect seasonality. This is because financial clients do not have a particular season or time during which they visit the bank. Neither is it cyclical since there are no factors that influence the operations of a bank over a certain period of time. Long-term trend is more related to the overall performance of the bank itself. The seasonal trends in the turnover and are all very closed to 1, ranging between 0.98 to 1.04, we can see that seasonal is not really an important factor. Somehow for operating profit, seasonal trend for Q3 is 0.78. This is majorly due to a charge of 1,000 million for goodwill impairment in Q2 2009, which drag the average down (RBC Annual Report, 2009). As a result, net profit is affected as well. In Q3 2011, due to the net loss from discontinued operations, 1656 million were lost (RBC Annual Report, 2011), which drags the Q3 seasonal trend for net profit after tax down to 0.79. Cyclical trend is only stable on the figures on turnover, which are all very close to 1. For the operating profit and net profit after tax are random. 2. Hewlett Packard Hewlett Packard is a manufacturing company. The company sells products to retailers and customers, and therefore, may be influenced by seasonality. The seasonal trend for turnover shows a divergence of 0.07 points between 0.97 and 1.04. For the operating profit the difference is between 0.77 and 1.36. The trend is much more manifest in net profit with a huge difference between 0.95 and 3. For all turnover, operating profit and net profit, quadratic model is applied and it shows an increasing long-term trend. 3. Exxon Mobil Exxon Mobil operates in the oil and gas industry. The industry is influenced by cyclical factors. From the data we can see, for turnover the seasonality is 1 for Q3, Q4 and Q1. Q2 has a value of 1.01, indicating no much difference. The operating profit does not indicate seasonality with a range of 0.97 and 1.04. The same for net profit between 0.95 and 1.09. Based on the regression analysis, the quadratic model is chosen to be used. The long-term trend is going up based on the model and graph. Part c: Regression analysis has already been provided in part a when doing the time series analysis, the forecasting is using the formula Y^t = Tt*St The forecasting results are shown as below: Forecast for Royal Bank of Canada: 1. Turnover: Forecasting Value: 2631.02 Real Value: 8990 2. Operating Profit: Forecasting Value: 2305.01 Real Value: 3094 3. Net Profit after Tax: Forecasting Value: 1841.58 Real Value: 2376 Forecast for Hewlett Packard: 1. Turnover: Forecasting Value: 15683.45 Real Value: 27585 2. Operating Profit: Forecasting Value: 2199.77 Real Value: 1458 3. Net Profit after Tax: Forecasting Value: 1944.87 Real Value: 985 Forecast for Exxon Mobil: 1. Turnover: Forecasting Value: 95190.19 Real Value: 103,566 2. Operating Profit: Forecasting Value: 7020.38 Real Value: 9574 3. Net Profit after Tax: Forecasting Value: 6572 Real Value: 8070 Part d: As shown above, the forecast values are somewhat similar to the real value for Exxon Mobil. This indicates that it is easier to forecast for the oil and gas industry. Somehow, for Royal Bank of Canada and Hewlett Packard, different factors might need to be taken into concern to decide the actual trend of the model. Forecast result and difference: Result Forecast Value Real Value Difference RBC Turnover 2631.02 8990 71% Operating Profit 2305.01 3094 26% Net Profit AT 1841.58 2376 23% HP Turnover 15683.45 27585 43% Operating Profit 2199.77 1458 33% Net Profit AT 1944.87 985 49% EM Turnover 95190.19 103566 8% Operating Profit 7020.38 9574 27% Net Profit AT 6572 8070 19% Also, it is shown in the table above that the turnover is easier to forecast than the operating profit and net profit after tax. That is based on the fact that more factors are taken into calculation when decided the operating profit and net profit after tax. Read More