1. introduction headquartered in the uk, astrazeneca is a world’s leading biopharmaceutical...

7/29/2019 1. Introduction Headquartered in the UK, AstraZeneca is a worlds leading biopharmaceutical company with $32.8

1/32

PHUONG DO 000625381 BUSINESS ANALYSIS

1

Coursework Header Sheet

175240-16

Course STAT1024: Business Analysis (MBA) Course School/Level BU/PG

Coursework Portfolio Assessment Weight 100.00%

Tutor N Hasan Submission Deadline 10/01/2011

Coursework is receipted on the understanding that it is the student's own work and that it has not, inwhole or part, been presented elsewhere for assessment. Where material has been used from other sourcesit has been properly acknowledged in accordance with the University's Regulations regarding Cheatingand Plagiarism.

000625381 Phuong Do

Tutor's comments

Grade Awarded___________ For Office Use Only__________ Final Grade_________

Moderation required: yes/no Tutor______________________ Date _______________


2/32


2

PORTFOLIO TASK 1: POWER OF ANALYSIS

ASTRAZENECA AND ANALYTICAL APPLICATION

1. Introduction

Headquartered in the UK, AstraZeneca is a worlds leading biopharmaceutical company with

$32.8 billion of sales in 2009 and 62,700 employees worldwide (AstraZeneca, 2010). One of the

most key factors which contribute to AstraZenecas success is effective using of analytics in all

core area of its business: marketing, R&D, finance and accounting, human resource,

manufacturing, etc. In 2000, AstraZeneca invested over $1.6 billion with Business Objects

software, an E-business Intelligence solution to support R&D activity (Farlex, 2010).

2.Challenge

Finding a new drug is highly competitive due to the fact that getting a new drug to market a day

earlier can help a drug company saving millions of dollars. On the other hand, it takes average 15

years for researching a new drug but drug patents last only 8 to 10 years. Therefore, drug

companies need to shorten R&D time in order to achieve brand leadership and revenue before

generic drugs appear in the market. Reducing time-to-market requires maximizing project

management efficiency, resource utilization and cost control (Business Objects, 2008,p.1);

hence immediately accessing to R&D information is crucial step to facilitate those processes.

3.Approach

BusinessObjects software is used to query and analyze R&D data worldwide which is stored in

an Oracle data warehouse. By using this solution, all users at all eight R&D sites easily access

and analyze information relating to project schedules, resource utilization and project cost.

Therefore, all scientists, managers and project teams can share their information with others

within their organization as well as finding optimal solutions based on data analysis. Moreover,

Business Objects supports reporting on several of R&D functions: clinical trials, project

budgeting and drug safety. As a result, the efficiency in R&D activity is enhanced by speeding

development and better managing cost. Ultimately, this efficiency leads to decrease time-to-

market of a drug.

4.Result

Widely using this E-business Intelligence solution helps AstraZeneca transforming its

employees from information consumers to information power users (Farlex, 2001, p.1);

thereby increasing effectiveness in AstraZeneca business as well as its capacity to enhance the

potential of R&D activities.


3/32


3

On the other hand, this solution enables AstraZeneca to understand, track and manage the

numerous volumes of data generated every day. All users in R&D department enable to instant

access to up-to-date information, thereby making faster, more confident and more accurate

decisions.

In addition, accessing to R&D data fast and easily helps AstraZeneca better understanding

global view on project activity as well as the amount of money is spending on new products

development. Therefore, R&D insight helps AstraZeneca to reduce cost and plan more

effectively thanks to more accurate on project schedule, resource allocation and project

budgeting.

As a result, Business Objects helps AstraZeneca to gain a considerable competitive advantage

by leveraging R&D information, thereby quickening time-to-market of new drugs. By

launching new drugs earlier than its competitors, AstraZeneca secures brand leadership and

sales revenues.

Although offer various advantages, Business Objects also shows some limitations because of

the complexity when applied, which costs companies much time and funding spending on

training for their staffs. Moreover in spite of vast sum of money spending on purchasing,

maintaining and upgrading this software, the company still fails to introduce a new blockbuster

drug (which revenue is more than $1 billion a year). Therefore, some shareholders raise concern

about the effective of this system in the R&D process.

5. Suggest other areas of work which could benefit from the use of analytics

Not only R&D sector gain benefits from this business intelligence solution, other sectors suchas manufacturing, marketing and finance can also be supported from Business Objects. For

example, in manufacturing sector, analytics can be applied for better manage drug

manufacturing process from ingredient stage to packaged products, in which manufactures

regulate material tracking. In particular, during manufacturing process, software is used to

control and store data which is generated by analytical machine system such as chromatography

or capillary electrophoresis. When a batch of drug is completely produced, base on data is

collected; this software will calculate productivity, labour and material utilization, waste and

other parameters. All parameters then are automatically reported and analyzed, thereby helping

employees to enhance operational efficiency and optimize manufacturing process. Therefore,

analyzing data collection is a crucial step to transform data into valuable knowledge (Pharma

manufacturing, 2010).

6. Conclusion


4/32


4

In conclusion, analytics facilitates AstraZeneca better collect, control information in its core

business operation as well as simplify and quicken data access, delivery this information across

the company. Business Intelligence solution makes it easier for AstraZeneca in tracking,

understanding and managing key business metrics in order to improve cost cutting, rational

efficiency and competitive advantage.

REFERENCES

1. AstraZeneca (2010) 2009 Annual report. Available at http://www.astrazeneca-

annualreports.com/2009/directors_report/therapy_area_review/gastrointestinal/index.html.

(Accessed 22 November 2010).

2. Business Objects (2008) AstraZeneca speeds to market with insight from Business Objects.

Available at http://download.sap.com/about/analystrelations/customer-

references/download.epd?context=F640A345D095D3F32B7F5FE73126EE027C03E737D52CB

388E0E0D1C4D73352939CF6F9861530D09D1592B67FA2915613893643CE00767B79

(Accessed 22 November 2010)

3. Farlex (2001)AstraZeneca Invests Over $1.6 Million With Business Objects for E-Business

Intelligence Solution; Business Objects Helps Speed the Development of Essential

Pharmaceuticals. Available at

http://www.thefreelibrary.com/AstraZeneca+Invests+Over+$1.6+Million+With+Business+Obje

cts+for...-a074506195(Accessed 22 November 2010)

4. Pharma manufacturing (2010) Materials tracking: Its All in the Data. Available athttp://www.pharmamanufacturing.com/articles/2007/076.html (Accessed 22 November 2010).

PORTFOLIO TASK 2: PRESENTING AND COMMUNICATING

NUMBERS

Part 1: 3 example of statistical diagrams

1.World pharmaceutical market (AstraZeneca, 2010, p.13)

1.1. Diagram main ideas
http://www.astrazeneca-annualreports.com/2009/directors_report/therapy_area_review/gastrointestinal/index.htmlhttp://www.astrazeneca-annualreports.com/2009/directors_report/therapy_area_review/gastrointestinal/index.htmlhttp://download.sap.com/about/analystrelations/customer-references/download.epd?context=F640A345D095D3F32B7F5FE73126EE027C03E737D52CB388E0E0D1C4D73352939CF6F9861530D09D1592B67FA2915613893643CE00767B79http://download.sap.com/about/analystrelations/customer-references/download.epd?context=F640A345D095D3F32B7F5FE73126EE027C03E737D52CB388E0E0D1C4D73352939CF6F9861530D09D1592B67FA2915613893643CE00767B79http://download.sap.com/about/analystrelations/customer-references/download.epd?context=F640A345D095D3F32B7F5FE73126EE027C03E737D52CB388E0E0D1C4D73352939CF6F9861530D09D1592B67FA2915613893643CE00767B79http://download.sap.com/about/analystrelations/customer-references/download.epd?context=F640A345D095D3F32B7F5FE73126EE027C03E737D52CB388E0E0D1C4D73352939CF6F9861530D09D1592B67FA2915613893643CE00767B79http://www.thefreelibrary.com/AstraZeneca+Invests+Over+$1.6+Million+With+Business+Objects+for...-a074506195http://www.thefreelibrary.com/AstraZeneca+Invests+Over+$1.6+Million+With+Business+Objects+for...-a074506195http://www.thefreelibrary.com/AstraZeneca+Invests+Over+$1.6+Million+With+Business+Objects+for...-a074506195http://www.pharmamanufacturing.com/articles/2007/076.htmlhttp://www.pharmamanufacturing.com/articles/2007/076.htmlhttp://www.thefreelibrary.com/AstraZeneca+Invests+Over+$1.6+Million+With+Business+Objects+for...-a074506195http://www.thefreelibrary.com/AstraZeneca+Invests+Over+$1.6+Million+With+Business+Objects+for...-a074506195http://download.sap.com/about/analystrelations/customer-references/download.epd?context=F640A345D095D3F32B7F5FE73126EE027C03E737D52CB388E0E0D1C4D73352939CF6F9861530D09D1592B67FA2915613893643CE00767B79http://download.sap.com/about/analystrelations/customer-references/download.epd?context=F640A345D095D3F32B7F5FE73126EE027C03E737D52CB388E0E0D1C4D73352939CF6F9861530D09D1592B67FA2915613893643CE00767B79http://download.sap.com/about/analystrelations/customer-references/download.epd?context=F640A345D095D3F32B7F5FE73126EE027C03E737D52CB388E0E0D1C4D73352939CF6F9861530D09D1592B67FA2915613893643CE00767B79http://www.astrazeneca-annualreports.com/2009/directors_report/therapy_area_review/gastrointestinal/index.htmlhttp://www.astrazeneca-annualreports.com/2009/directors_report/therapy_area_review/gastrointestinal/index.html


5/32


5

From the bar charts above we can draw a conclusion that although their growth rates

insignificantly increased, North America and other Established markets are always the main

segments for world pharmaceutical market, which represent 84% of total market share in 2009.

In addition, Emerging markets with high growth rate are the high potential markets which

pharmaceutical companies need to focus on.

1.2 . Pie charts would more informative

46.11%

40.47%

13.13%

2007

North America

Other establish Markets

Emerging Markets


6/32


6

These figures are more informative if represented by pie charts instead of bar charts because we

can know exactly the market share of each market each year as well as whether market share of

each market increases or decreases year- by year. For example, compare to 2008, North

America and other established markets market share slightly decreased why emerging markets

market share increased more than 2%.

1.3. Misleading points

However, this diagram can be misleading when applicable because although the growth in sales

of both emerging market in 2009 compares to 2008 and established markets in 2008 compares to

2007 are $14 billion, but the growth percentages for emerging market and established market are

15% and 5.4%, respectively. This result is explained by the fact that, established market sales

value is nearly three times than emerging markets sales value.

2. Cardiovascular World Market (AstraZeneca, 2010, p.57)


45.02%

40.56%

14.41%

2008

North America


Emerging Markets

44.43%

39.92%

15.66%

2009

North America


Emerging Markets


7/32


7

From the chart above, we can draw a conclusion that high blood pressure is the largest market

sector in the CV market with $51.1 billion, followed by abnormal levels of high blood pressure,

diabetes, thrombosis, making them the main sectors for CV therapeutics markets. Because CV

is the single largest therapy area in the global healthcare market with a worldwide market value

of $160 billion (AstraZeneca, 2010, p.57), this figure helps pharmaceutical companies find the

right strategy for developing product portfolio to gain optimal benefit.

2.2. Pie chart would be more informative

32.00%

22.17%17.85%

14.40%

13.59%

High blood pressure

Abnormal levels of blood

cholesterol

Diabetes

Throbosis

Others


8/32


8

From this pie chart we can know exactly the market share for each sector as well as compare

each sector with others. The original donut chart only informs us the value of each sector

without percentage of market share.

2.3 Misleading points

There will be no misleading with the donut chart above if applicable.

3. R&D spending in manufacture industries in the UK in 2002 (Abpischool, 2010, p.2)


From the chart above we can draw a conclusion that pharmaceutical industry was the sector with

highest R&D spending of about 3,300 million, which was four times higher than machine &

equipment industry. Although aerospace is a high-tech industry, its spending on R&D activity

was only more than a third compares to spending in pharmaceutical sector. The highest spending

on R&D partly explained the success of this industry in the UK economy as well as in the

worlds pharmaceutical industry.

3.2. Donut chart may be more informative


9/32


9

This donut chart clearly shows that the pharmaceutical industry spending is greater than totalother 3 industries.

3.3. Misleading points

The chart above, if applicable can mislead readers that 4 sectors above are those which spent on

R&D the most in the UK. However, there are various sectors spending a vast amount of money

on R&D such as IT, transportation etc.

REFERENCES

1.AstraZeneca (2010) 2009 Annual report. Available at http://www.astrazeneca-annualreports.com/2009/ (Accessed 25 November 2010)

2.Abpischool (2010) The pharmaceutical business. Available at

http://www.abpischools.org.uk/res/coResourceImport/resources04/pharm_business/manubusch1

pg1.cfm (Accessed 25 November 2010).

Part 2: Find a suitable example and turn a routine statistical story into one with a much

stronger storyline and more effective use of data.

12.44%

15.52%

18.44%

53.59%

Marchinery & equipment

TV, radio and

communications

Aeroplane

Pharmaceutical industry
http://www.abpischools.org.uk/res/coResourceImport/resources04/pharm_business/manubusch1pg1.cfmhttp://www.abpischools.org.uk/res/coResourceImport/resources04/pharm_business/manubusch1pg1.cfmhttp://www.abpischools.org.uk/res/coResourceImport/resources04/pharm_business/manubusch1pg1.cfmhttp://www.abpischools.org.uk/res/coResourceImport/resources04/pharm_business/manubusch1pg1.cfm


10/32


10

Example: Highlight some main ideas in market report Pharmaceutical Industry - 2010

Yearbook (GBI research, 2010)

The Pharmaceutical industry 2010 Yearbook provides important data, information as well as

major issues and trends which affect pharmaceutical market. This essay will highlight a storylinefrom this yearbook about the pharmaceutical therapeutic market share and the reason behind the

trend of market share by making the use of data more effective than data in this yearbook.

Figure 1: Therapeutic market share

As can be seen from the bar chart above, cardiovascular (CV) therapeutics represents 48%,

oncology therapy ranked closely behind with 18%, followed by central nervous system (CNS)

with 15% of total market share, thereby demonstrating that they are three main therapeutics

which led the pharmaceutical market in 2010 and may also lead pharmaceutical market in the

future. This trend is in line with the trend of disease in the 21st

century.

Because of their importance in the world pharmaceutical market, these three major therapeuticsalso determine the M&A trend of big drug companies. In particular, they accounted for 70% of

Mergers & Acquisitions deals in this year, demonstrating high value of these segments.

19%

18%

15%

48%CV

Oncology

CNS

Others


11/32


11

Figure 2: M&A deals in terms of therapeutic segments

Oncology M&A deals alone accounted for 35%; nearly twice as much as CV and CNS ones,assuming that oncology therapeutic will be the most important sector in big pharmaceuticalcompanies. The dynamic behind M&A trend is consolidate the companys position in the marketin the face of patent expiration as well as expanding their portfolio. Because of fiercecompetitiveness in pharmaceutical market, M&A trend is forecasted to continue in the future.

CV, oncology and CNS are all age-related diseases. Therefore despite of being affected byworld economic recession, aging population in developed countries are the main driving force ofthe steady growth of this industry. For example, demographical statistics in USA illustrates thistrend.


12/32


12

Figure 3: US demographics in 1990 and 2005

Figure 4: The growth of US population each year

All these figures above support a fact that US population is getting older. This scenario is the

same in almost developed countries which are the main markets for pharmaceutical products.Because the more aging population, the more age-related diseases population; this is the reasonwhy CV, oncology and CNS are major therapeutics in pharmaceutical market.

In conclusion, the demographical trend in the developed market determines the disease structurewhich in turn determines the market share and M&A deals in the pharmaceutical market.Analyzing these figures is useful for pharmaceutical companies to make a long-term plan to meet

4552

78

85

0

10

20

30

40

50

60

70

80

90

1990 2005

Average age

Life expectancy

0.88%

2.50%

0.00%

0.50%

1.00%

1.50%

2.00%

2.50%

3.00%

General population The 65 years and above population

year


13/32


13

market demand. This analysis also has a profound impact on health-policy makers to establish aswell as adjust policies which are in line with this changing trend.

REFERENCES

1.GBI research (2010) Pharmaceutical Industry - 2010 Yearbook - Therapeutic MarketLandscape, Key Pipeline Drugs, Top Pharma Companies and Key Mergers and Acquisitions.The summary available at http://www.companiesandmarkets.com/Market-Report/pharmaceutical-industry-2010-yearbook-therapeutic-market-landscape,-key-pipeline-drugs,-top-pharma-companies-and-key-mergers-and-acquisitions-454324.asp ( Accessed 5January 2011)

PORTFOLIO TASK 3: INDEX NUMBER

RETAIL PRICE INDEX (RPI)

1.The composition of retail price index and how it is updated

1.1.What is RPI?

Measuring the changes in prices is always the central to every economic issue in order to set

government taxes/benefits as well as negotiate wage changes. However, how to measure these

changes correctly is one of the trickiest and the most difficult tasks.

In the UK, Retail Price Index (RPI) is monthly published by the Office for National Statistics to

measure the changes in the price of a basket of retail goods and services which a typical

household may buy (Office for National Statistics, 2010). First calculated in June 1947, RPI is

used to measured inflation in the UK for various purposes:Macro-economic indicator: For international Comparisons, Deflation of expenditure.

Income adjustment: Indexation of tax allowances, Indexation of incomes, Indexation of pensions

and benefits: Index-linked gilts and national savings.

Price adjustment: Private contracts, Regulation of utilities, other price regulation

(UK National Statistics, 2010).

1.2.RPI components

RPI is calculated by collecting a sample of prices for a selection of representative goods and

services in a range of UK retail locations (UK National Statistics, 2010, p.3). In 2010, about180,000 prices quotations have been used each month which cover 650 representative goods and

services in 150 areas in the UK. These goods are divided in 14 groups. Moreover, each group and

each item within a group is given weight which reflects the importance of that item.
http://www.companiesandmarkets.com/Market-Report/pharmaceutical-industry-2010-yearbook-therapeutic-market-landscape,-key-pipeline-drugs,-top-pharma-companies-and-key-mergers-and-acquisitions-454324.asphttp://www.companiesandmarkets.com/Market-Report/pharmaceutical-industry-2010-yearbook-therapeutic-market-landscape,-key-pipeline-drugs,-top-pharma-companies-and-key-mergers-and-acquisitions-454324.asphttp://www.companiesandmarkets.com/Market-Report/pharmaceutical-industry-2010-yearbook-therapeutic-market-landscape,-key-pipeline-drugs,-top-pharma-companies-and-key-mergers-and-acquisitions-454324.asphttp://www.companiesandmarkets.com/Market-Report/pharmaceutical-industry-2010-yearbook-therapeutic-market-landscape,-key-pipeline-drugs,-top-pharma-companies-and-key-mergers-and-acquisitions-454324.asphttp://www.companiesandmarkets.com/Market-Report/pharmaceutical-industry-2010-yearbook-therapeutic-market-landscape,-key-pipeline-drugs,-top-pharma-companies-and-key-mergers-and-acquisitions-454324.asphttp://www.companiesandmarkets.com/Market-Report/pharmaceutical-industry-2010-yearbook-therapeutic-market-landscape,-key-pipeline-drugs,-top-pharma-companies-and-key-mergers-and-acquisitions-454324.asp


14/32


14

Figure 1: RPI weights of 14 groups in 2010 (Source: ONS, 2010)

In particular, total household goods weights are 67 (which represent 6.7% in the total weights in

the pie chart above). These household goods comprise 6 components with different weights:

Figure 2: Weight component in household group (Sources ONS, 2010)

167

46

76

38157

6173

44

74

38

127

22

47 30

Food

Catering

Alcohol

Tobacco

Housing

Fuel and light

Household goods

Household services

Clothing and footwear

Personal goods and services

Motoring expenditure

Fares and other travel costs

Leisure goods

Leisure services

0

5

10

15

20

25

30 26

107

4

13

7


15/32


15

1.3.How is RPI calculated?

RPI is calculated by this formula: (Baxter, 1998)

RPI is an annually chain-linked index, in others words each years indices are chained togetherfor an index which covers several years. Within a year, RPI is a fixed quantity price index whichmeasures the changes in the cost of a fixed basket of composition, quality and quantity.

Figure 3: RPI, CPI and RPIX in 2008-2010 (Source: Office of National Statistics, 2010)

1.4. How is the composition of RPI updated? (Office for National Statistics, 2010)

The composition in the basket of goods and services each year is introduced in February

consumer price indices which are published in March. In other words, monthly price changes are


16/32


16

estimated with updated basket. This basket is annually updated based on Family expenditure

survey.

In the process of updating, some items will be added; in contrast some items will be removed to

make room for new items. Moreover, the places where items are collected and the weights are

also updated. There are some reasons to explain this change:

- Some news items are introduced because items in the existing basket cannot representadequately prices changes. For example: cereal bar is introduced to represent a type of snack

which is not covered previously in the basket.

- High-technology goods are also included to represent evolving trends. For example, blue-raydisc players were introduced in 2010 to reflect new style of DVD format.

- Some items are introduced to diversify the products ranges for established grouping where thespending is substantial. For example allergy tablets were introduced in 2010 to diversify

minor ailment remedies.

-

New items directly replace similar products which were removed from the basket becausenew items represent a greater proportion of the market in comparison with the items they

replace. For example, in 2010 electrical hair straighteners and tongs replaced hairdryers.

- Not only items are but also the places where they are collected are also updated. For exampleblue-ray disc price in 2009 was only collected over the internet but in 2010 it was also

collected in the local shops because the number of customers visiting these shops for blue-ray

disc is on the rise.

2. Discuss how useful of this index

2.1. The government as a means of controlling inflation

The RPI indicates the extent of inflation; thereby the government can depend on RPI to control

inflation by monetary policy, fiscal policy, appreciation of exchange rate, wage control, labour

market reforms supply side reform. For example, the government can set the official base rate of

interest for the economy in an aim to achieve inflation rate from 1.5% to 3.5% in a two year

period. To control inflation, it is necessary to focus on the underlying causes of inflation. If it is

the demand-pull inflation, which is excessive growth in demand leading to inflationary gap too

much money chasing too few goods, the government needs to reduce the level of total demand.

If the root cause is cost-pull inflation, the government should control production costs.

2.2. A union for wage negotiation purposes

The RPI is used by a union as a starting point of wage negotiation. Because inflation means the

fall in the value of money; therefore depending on the RPI, a union can negotiate with the

employers to raise wage for employees to compensate for the rise of the prices when the inflation


17/32


17

is positive. In contrast, when the inflation turns negative (for example in the UK in March 2009),

a union can accept put wage increases on hold or even reduce wage in order to maintain jobs

(BBC news, 2009).

2.3. A pensioner in deciding how well off he or she is likely to be next year

In the UK, the pension is adjusted in April each year in order to offset the rise of cost in living

and this adjustment is calculated according to RPI. Therefore, a pensioner may depend on the

change of RPI from September last year to the September this year to predict how much money

he or she will be received next year. For instance, the change in the RPI to September 2009 was

1.4%, so the increase in the pension from April 2010 is 0%. In contrast, change in the RPI to

September 2008 was +5%, so the increase in the pension from April 2009 was +5% because the

government ensures that basic state pension increases by at least the equivalent of RPI. From

April 2011, the Consumer Price Index (CPI) will be used to calculate state pension instead of

RPI (CSPA, 2010)

3. Comment on the accuracy of the index.

Although RPI has been widely used for a long-time, there are some flaws which make this index

less reliable.

Firstly, according to House of Commons Public accounts Committee and National Audit Office

(NAO), prices were not adequately collected each month. For example, in 1990 only 95,000

prices were recorded out of 175,000 prices collected nationally. Of 9 offices were surveyed by

NAO, 8 of them only collected from 42 -84% of the total prices and the ninth office, Camden

provided no data due to strike. Moreover, prices were not collected adequately also due to lack of

staffs and no formal training. Moreover, because 3,000 out of 10,000 household refused to

participated, making these prices under- represent, they were ethnic minorities, the very rich and

manual workers. Eventually Family Expenditure Survey (FES) distorted the RPI. Therefore,

inflation rate for given households may be substantially different from that implied by the RPI

(Anderton, 2006).

Secondly, RPI holds the weights of item in the goods basket unchanged in a certain period of

time while the price structure in this basket evolves. Therefore, it ignores the possibility of item

substitution which takes place in this period, thereby overstating truly cost-of-living index. Forexample, in the period 1976-1997, the average annual inflation calculated by PRI was 0.35%

higher than true index (6.43% and 6.28%, respectively) (Blow and Crawford, 2010).

4. Conclusion

RPI index and its variables (RPI X and PRI Y) are useful to calculated inflation, thereby being

applied to calculate pension, wage negotiation, etc. However, because of RPI accuracy, the UK


18/32


18

government determined to substitute RPI by CPI in the indexation from 2011 in some state as

well as private benefits such as pension to make a more accurate calculation.

REFERENCES

1.Anderton A. (2006) Economics Third edition. London: Pearson education.

Baxter M. (1998) The retail prices index: technical manual. London: the stationary office.

Office for National Statistics (2010) Finding RPI data. Available at

http://www.statistics.gov.uk/CCI/nugget.asp?ID=21(Accessed 26 December 2010).

2.BBC news (2009) Inflation measure turns negative. Available at

http://news.bbc.co.uk/1/hi/business/8009718.stm(Accessed 26 December 2010).

CSPA (2010) Index-liking of public sector pension. Available at http://www.cspa.co.uk/

(Accessed 26 December 2010).

3.Blow, L. and Crawford, I. (2001) The cost of living with the RPI: substitution bias in the UK

retail price index The economic Journal Vol.111, p.357-382.

4.ONS (2009) Consumer price indices a brief guide. Available at

http://www.statistics.gov.uk/downloads/theme_economy/CPI-briefguide.pdf (Accessed 26

December 2010).

5.Office for National Statistics (2010) Inflation. Available at

http://www.statistics.gov.uk/cci/nugget.asp?id=19(Accessed 26 December 2010).

UK National Statistics (2010) RPI technical manual. Available atwww.statistics.gov.uk/.../theme.../RPI_TECHNICAL_MANUAL.pdf (Accessed 26 December

2010).

PORTFOLIO TASK 4

DATA MODELLING, REGRESSION AND CORRELATION

1. Data sources

Using data in Gumtree.com website on 27 December 2010 to find out the relationship between

the age and the price of 20 second-hand cars model Toyota Aventis GS VVT-I.
http://www.statistics.gov.uk/CCI/nugget.asp?ID=21http://www.statistics.gov.uk/CCI/nugget.asp?ID=21http://news.bbc.co.uk/1/hi/business/8009718.stmhttp://news.bbc.co.uk/1/hi/business/8009718.stmhttp://www.cspa.co.uk/http://www.cspa.co.uk/http://www.statistics.gov.uk/downloads/theme_economy/CPI-briefguide.pdfhttp://www.statistics.gov.uk/downloads/theme_economy/CPI-briefguide.pdfhttp://www.statistics.gov.uk/cci/nugget.asp?id=19http://www.statistics.gov.uk/cci/nugget.asp?id=19http://www.statistics.gov.uk/.../theme.../RPI_TECHNICAL_MANUAL.pdfhttp://www.statistics.gov.uk/.../theme.../RPI_TECHNICAL_MANUAL.pdfhttp://www.statistics.gov.uk/.../theme.../RPI_TECHNICAL_MANUAL.pdfhttp://www.statistics.gov.uk/cci/nugget.asp?id=19http://www.statistics.gov.uk/downloads/theme_economy/CPI-briefguide.pdfhttp://www.cspa.co.uk/http://news.bbc.co.uk/1/hi/business/8009718.stmhttp://www.statistics.gov.uk/CCI/nugget.asp?ID=21


19/32


19

No. Age(years) Price ()

1 8.5 1,499

2 9 1,250

3 12 750

4 9 1,695

5 9 1,400

6 10 1,295

7 11 1,250

8 9.5 1,850

9 7 2,125

10 9 1,295

11 6 2,400

12 8.5 1,595

13 9.5 1,150

14 9.5 1,160

15 8 1,800

16 8 1,850

17 9 1,499

18 10 1,295

19 10 1,275

20 8 1,840

2. Data analysis

2.1. The Scatter Graph of the data

0

500

1,000

1,500

2,000

2,500

3,000

0 2 4 6 8 10 12 14

Scatter Graph

years

Price()


20/32


20

As can be seen from the scatter diagram, if we draw a closed loop around the points, this loop

looks more like an ellipse, and this ellipse points downwards, therefore there is a negative linear

relationship for all range of data.

2.2. The regression equation that would enable the price of the car to be obtained given its age.

The regression equation between the price and the age of cars is y = -257.2x+3824 (y: price, x:

age) with coefficient determination R2= 0.783 mean that 78.3% of the variation in car price is

explained by the age of the car, 21.7% of the variation is not explained.

In addition, R2= 0.783 means R = - 0.885, therefore there is a strong negative correlation

between the price and the age of a car (0.7< R < 0.99). However, this correlation does not mean

the causation, in other words the age of a car does not cause the price of the car up and down.

2.3.Using the equation obtained in (b) above and suitable examples, illustrate how the price of

a car could be found if its age was known. Within what age range is your equation valid? Why

is this so?

Because the price of the car is calculated by this equation: y= -257.2 x +3834, therefore if we

know the age of a car, we can estimate its price.

y = -257.2x + 3834.

R = 0.783

0

500

1,000

1,500

2,000

2,500

3,000

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

price and age of carsPrice ()

year


21/32


21

Age (years) Price ()

6 2290.8

6.5 2162.2

7 2033.6

7.5 1905

8 1776.4

8.5 1647.8

9 1519.2

9.5 1390.6

10 1262

10.5 1133.4

11 1004.8

11.5 876.2

12 747.6

Within age from 6 to 12 years the equation is valid because this equation is based on data which

is collected in this range, so we cannot be certain about the price of these cars which ages are

outside this range.

2.4. What proportion of the variability in price is explained by the age factor?

Because the coefficient determination R2= 0.783, therefore 78.3% on price is explained by the

age factor and 21.7% is explained by other factors.

2.5. Investigate whether other factors, such as engine size, would improve the fit of the

regression equation

If other factor, such as the mileage is added:


22/32


22

No. Price () Age (years)

Mileage

(miles)

1 1,199 8.5 106,000

2 991 9 100,000

3 695 12 156,000

4 1,695 9 90,000

5 1,400 9 160,000

6 1,295 10 114,000

7 1,250 11 130,0008 1,850 9.5 66,000

9 2,200 7 67,000

10 1,295 9 87,000

11 2,400 6 51,000

12 1,395 8.5 107,000

13 695 9.5 95,000

14 799 9.5 103,000

15 1,695 8 127,000

16 1,200 8 99,000

17 1,690 9 130,00018 1,295 10 114,000

19 1,275 10 121,000

20 1,695 8 110,000

Using Regression function in Analysis Tab in Excel 2007, the result is:

SUMMARY

OUTPUT

Regression Statistics

Multiple R 0.895556034

R Square 0.802020611

Adjusted R Square 0.778728918

Standard Error 180.7986462

Observations 20


23/32


23

ANOVA

df SS MS F

Significance

F

Regression 2 2251152 1E+06 34.4338 1.1E-06

Residual 17 555699 32688

Total 19 2806851

Coefficients

Standard

Error t Stat P-value Lower 95%

Upper

95%

Lower

95.0%

Intercept 3812.900266 286.429 13.31 2E-10 3208.59 4417.2 3208.5883

Mileage (miles) -0.002427649 0.00192

-

1.266 0.22273 -0.00647 0.0016

-

0.0064747

age -226.0766165 39.8546

-

5.673 2.8E-05 -310.163 -142

-

310.16251

The result so that the value of R square and Multiple R are improved (R2 = 0.802 compared to

0.783, multiple R= 0.896 compared to 0.885). Therefore, adding another factor such as mileage

improves the fit of regression equation.

PORTFOLIO TASK 5A

RISK AND PROBABILITY

1. Conduct a survey to find out the relationship between the colour of cars and the number

of accidents on roads.

1.1.Introduction

It is often assumed that there is a closely relationship between cars colour and the number of

accidents on roads. Up to now, a lot of research has been conducted to investigate this

correlation. A survey published in British Medical Journal in 2003 by Furness et al. revealed that

white cars were 50% more likely be involved in serious accidents than silvers ones. In addition,

according to Lardelli-Claret et al. (2002), light colour cars were less associated with car crashes

compared with cars of other colours. This report will conduct a survey in Hue Road, Hanoi, the

capital of Vietnam in 36 months, from September 2007 to August 2010 to find out the

relationship between car risks and car colours. Statistics are collected in several reports in thewebsite of Vietnam Ministry of Transportation (giaothongvantai.com.vn, 2010).

1.2. Method and result

There were 50 cases involved in car crashes in which the car occupants hospitalized or died at

that interval of time.


24/32


24

Car colour No. Proportion %

Black 12 12/50 24%

Blue 7 7/50 14%

Red 14 14/50 28%

Silver 6 6/50 12%White 4 4/50 8%

Cream 4 1/50 8%

Yellow 2 2/50 4%

Pink 1 1/50 2%

50 50/50 100%

1.3.Convert into probabilities

Car colour Probabilities

Red 0.28Blue 0.14

All others 0.58

1

2.Given that there is an accident in this road, what is the probability that the car involved

is red

Apply Bayes Theorem

P (B\A) =\()

()

Where A is the event Car is involved in accident. B is the event Cars colour is redTherefore P (A\B) = 0.03; P (B) = 0.28

C is the event Cars colour is blue and C is the event all other colours

P (A) = P (A\B) P (B) + P (A\C) P (C) + P (A\D) P (D)

= 0.03 * 0.28 + 0.015 * 0.14 + 0.023 * 0.58

= 0.02384

Hence

P (B\A) =0.280.03

0.02384

= 0.3523

Therefore given there is an accident in the road, the probability that the car involved is red is

0.3523.


25/32


25

Probability tree diagram

Prior probabilities Conditional probabilities Joint probabilities

0.28 *0.03= 0.084

Involved in an accident 0.03

Not involved in an accident

0.97 0.28* 0.97 = 0.2716

Car is red 0.28 0.14*0.015 =0.021

Car is blue Involved 0.015

0.14 Not involved

All others 0.0985 0.14 * 00985 = 0.01379

0.58 Involved 0.58* 0.023 = 0.01334

0.023

Not involved

0.977 0.58 * 0.977 = 0.56666

From the diagram tree we also calculate the probability that a car is involved in an accident:

P (A) = 0.084 + 0.021 + 0.01334 = 0.02384

Therefore the probability that the colour of the car is red given that it involved in an accident is:

P (B\A) =0.084

0.02384= 0.3523

This result coincides with the result calculated by applying Bayes theorem.

REFERENCES

1.Furness S., et al. (2003) Car colour and risk of car crash injury: population based case study

British Medical Journal Vol.327 No.7429, pp.1455-1456.


26/32


26

2.Lardelli-Claret, P. et al. (2002) Does vehicle colour Influence the Risk of being Passively

Involved in a collision?Epidemiology, Vol.13, No.6, pp.721-724.

3.Vietnam Ministry of Transportation (2010) Safety transportation. Available at

http://www.giaothongvantai.com.vn/Desktop.aspx/An-toan-giao-thong/An_toan_giao_thong/

(Accessed 20 December 2010).

PORTFOLIO TASK 5B

PROBABILITY DISTRIBUTION, NORMAL DISTRIBUTION

1.Literature review about normal distribution

1.1. Normal distribution: definition and properties

The normal distribution is a continuous probability distribution which is pattern for the

distribution of a set of data which follows a bell shaped curve.

1.2.Application in business context (NetMBA.com, 2010, p.1)

Normal distribution can be applied in many areas of business, for example:

- Modern portfolio theory commonly assumes that the returns of a diversified assetportfolio follow a normal distribution.

- In operations management, process variations often are normally distributed.- In human resource management, employee performance sometimes is considered to be

normally distributed.-


27/32


27

In this portfolio, normal distribution is applied to investigate weight of coffee jar.

2. Apply normal distribution for investigating weight of coffee jar

Using Excel we have diagram for this normal distribution

2. 1.Whats the proportion of jars are under 100 g in weight

First we calculate the Z transformation which is given by:

Z=

=

100101

1.5= 0.67

That Z is negative means 100 is on the left of the mean. Since the distribution is symmetrical

about the mean, we can ignore the sign when using normal table. Therefore we look up Z = 0.67


28/32


28

in the normal table. We get an area of 0.2514 or 25.14% which means that 25.14% of all jars

weigh fewer than 100 g.

2.2.Whats the proportion of jars are under 98 g in weight


Z=

=

98101

1.5= 2.00



in the normal table. We get an area of 0.0228 or 2.28% which means that 2.28% of all jars weigh

fewer than 98 g.

2.3.Whats the proportion of jars are under 97 g in weight


29/32


29


Z=

=

97101

1.5= 2.67



in the normal table. We get an area of 0.0038 or 0.38% which means that 0.38% of all jars weigh

fewer than 97 g.

2.4. Whats the proportion of jars are over 100 g in weight


30/32


30

Because the proportion of jars under 100 in weight is 0.2514 therefore the proportion of jars over

100 g in weight is: 1- 0.2514 = 0.7486 or 74.86% which means that 74.86% of all jars weigh

more than 100 g.

2.5. Whats the proportion of jars within 2 g of the marked weight?

Jars within 2 g of the marked weight are jars from 98 g to 102 g.

First we need to calculate the area under 98g and the area over 102g

From the result calculated above, we know 0.0228 or 2.28% of jars weigh fewer than 98 g.

Now we calculate the proportion of jars weigh more than 102 g.

Z=

=

102101

1.5= 0.67

That Z is positive means 102 are on the right of the mean. Therefore we look up Z = 0.67 in the

normal table. We get an area of 0.2514 or 25.14% which means that 25.14% of all jars weigh

more than 102 g.

Therefore the proportion of jars weigh form 98 g to 102 g is: 1- 0.2514 - 0.0228 = 0.7258 or

72.58% which means that 72.58% of all jars weigh from 98 g to 102 g.

2.6.What should the mean weight be set to in order that the probability of a jar weighing less

than 98 g is less than 0.1%?

0.1% is the same to 0.001. Look up the normal tables, the Z value for the area of 0.001 is 3.05

but because 98 is on the left of the mean, this value is negative, that is -3.05. Substituting this

value in the formula given


31/32


31

-3.05 =98

1.5

Multiplying both sides by 1.5 gives:

-4.575 = 98mean

Then adding 4.575 to both sides

0 = 102.575mean

That is: mean = 102.575 g.

2.7. Write a short note to the quality control manager summarising my results.

The result shows that: 25.14% of jars weigh fewer than 100 g,

2.28% of jars weigh fewer than 98g,

0.38% of jars weigh fewer than 97 g,

72.58% of jars weigh within 2 g of the marked weigh,

In order that the probability of a jar weighing less than 98 g is less than

0.1%, the mean weight should be set at 102.575 g.

Because prosecution only occurs when the product is underweight by more than 2%, or under 98

g; therefore if the mean weigh is 101 g, the proportion of jars weigh less than 98 g is 2.28% and

this proportion is less than 0.1% if the mean weight is set at 102.575 g.

REFERENCES

1.NetMBA.com (2010) The normal distribution. Available athttp://www.netmba.com/statistics/distribution/normal/(Accessed 30 December 2010).

2.Oakshott, L. (2006) Essential quantitative methods for business, management and financeThird edition. Basingstore: Palgrave Macmillan.
http://www.netmba.com/statistics/distribution/normal/http://www.netmba.com/statistics/distribution/normal/http://www.netmba.com/statistics/distribution/normal/


32/32


1. introduction headquartered in the uk, astrazeneca is a world’s leading biopharmaceutical...

Documents