Financial Analysis Case Study: BP, Repsol and Royal Dutch Shell

Introduction

The purpose of this case study is to encourage you to combine both financial analysis and Excel 2007 functionality. By the end of this case study you will not only have analysed three Oil & Gas industry companies but you will also have used a wide variety of Excel 2007 formulas, functions and techniques. However, because this is an open ended case study, the exact nature of the analysis is not something that can simply be written down and applied: the case can be considered at least a level two case, therefore.

In this case you are presented with the following information for three Oil & Gas Industry companies, BP, Repsol and Royal Dutch Shell:

• income statements
• balance sheets
• most pessimistic, consensus and most optimistic scenarios
• historical oil prices
• future oil prices

The case begins with the tasks you need to complete and then provides you with notes of advice and guidance to help you with your work.

You tasks are

• compare the performance of the three companies from the financial information  provided
• prepare scenarios for each company using the most pessimistic, consensus and most optimistic forecasts: there is no reason why all three scenarios should be the same as each other
• use the historical and future oil prices to support or reject the scenarios you have prepared in the previous task

Company Backgrounds: hover over the company names for a description of the company's activities

• BP plc
• Repsol YPF (YPF)
• Royal Dutch Shell plc

Source: http://markets.ft.com/ft/markets/companyResearch.asp

The Excel 2007 File

In the Excel 2007 file that accompanies this case, bp_repsol_shell.xlsx (see the link at the end of the page), you will find the following templates:

• a ratio analysis template
• a common size statement template
• rates of change templates

You can use these templates for your answers to task 1.

You can use as much of these templates as possible and if you are free to provide further analyses as you feel appropriate.

It is worth pointing out that BP and Royal Dutch Shell have reported in US Dollars while Repsol has reported in Euro.

Notes for Task 1

Ratio Analysis

The ratio analysis formulas are given as guidance only: there are alternative formulas for some of those presented and there are additional ratios to consider: hundreds of them, in fact. Feel free to add further ratios as you wish.

Common Size Statements

Common size statements can reveal a lot of additional information over and above the raw data and the ratios. All common size statements are essentially the same as each other but we feel the need to help as follows:

for the income statement, the common size statement starts in this way:

for the balance sheet, the common size statement, starts this way:

Rates of Change

There are various ways of calculating a rate of change ratio but the simplest method we use is as follows:

This formula provides the rate of change from one year to the next.

Alternatively, you might be familiar with:

This formula provides a change from a base year, much like an index number.

There are even more ways of showing a rate of change: use the one that you are most familiar with as the two approaches given above are suggestions only.

Preparatory Work

The income statement and balance sheet data have been copied from the Financial Times database of company data and have not been formatted in any way. In addition, we have made no attempts to program any functions or formulas into the financials but you ought to do that before you begin. For example, in the income statements, see the screenshot below, row 10, Total Revenue, has been hard coded by the FT as have rows 16 and 17, total Operating Expenses and Operating Income respectively. All other totals and sub totals have similarly not been programmed.

Notes for Task 2

In the accompanying Excel 2007 file, you will find the worksheet scenarios. That worksheet contains the scenario data for the years 2008 – 2011. Those scenario variables have been copied from Palepu et al. We think it will be advisable for you to reorganise the schedule you see there before you try to use and program with it.

It’s not vital to reorganise the schedule but in terms of using the information in it, you will find it easier to do that when constructing the various formulas and functions that you will probably want to build into your scenarios.

You should then prepare a series of schedules to reflect the three scenarios. The layout and format of your schedules is for you to decide and again no guidance is given here.

By the end of this task you should have formed and stated your opinion of which scenario you favour: in order to do this you should include any relevant parts of your solution to task 1.

Notes for Task 3

This task has the further objective of testing your scenario analysis by asking you to imagine that you have completed your analyses as per tasks 1 and 2 and then the information relating to oil prices is made available to you.

Before you begin this task, you are advised to look at and reorganise the schedule to be found on worksheet history_oil. Reorganising that schedule will allow you to isolate the relevant parts of it for your use in this task. Additionally, you might find that not all of the data in that table are useful for you.

We have prepared a correlation matrix and put it on that worksheet: you should be able to find a use for it in your analysis in this task.

You should find a useful way to include the table to be found in the future_oil worksheet: oil prices taken from oil price futures as found on the Yahoo Finance web site.

References

Krishna G Palepu, Paul M Healy, Victor L Bernard and Erik Peek (2007) Business Analysis and Valuation: IFRS Edition South Western Cengage Learning

Company Financials from http://markets.ft.com/ft/markets/companyResearch.asp

Historical Oil Prices from http://www.interconnresources.com/news/wp-content/uploads/2009/08/HIstorical%20Oil%20Prices%20NYMEX%20Close.pdf

Crude Oil Futures Prices http://finance.yahoo.com/q/fc?s=CLV09.NYM

© Duncan Williamson
August 2009

Download this page in PDF Format

Download the Excel 2007 file for this case study