Dazzle Jewels Ltd specialises in selling pink diamond jewellery and has been selling

Dazzle Jewels Ltd specialises in selling pink diamond jewellery and has been selling only

their proprietary pink diamonds for last 5 years. Pink diamonds were invented in their

China laboratories. Their pink diamond sales revenues were reported to have increased

over last 5 years in Singapore. The management is considering more investment in

marketing for pink diamond jewellery.

Mr. Teo C K, the CEO, has engaged you as a consultant to advise the management. Since

the official opening of their first store in 1st June 2003, they have opened five (5) stores.

Stores were located in the city and in residential estates. Dazzle Jewellery management

would like you to study their sales patterns and advise whether pink diamond jewellery

prices are going up. They would also like to identify what factors drive prices. A past

industry study indicated that caratage and design complexity influenced prices. Weight is

measured in carats. Dazzles only offer two categories of design styles; basic and complex.

The basic styles are cut by automated machines. Complex designs are produced by their

master craftsmen. A sales department report suggested that customers spent more in city

stores. Dazzle Jewellery management would like to verify if the above factors do influence

price. Factors should be defined as measurable variables for statistical analysis and

testing.

Your past studies on other jewellery distribution indicated that wealth influenced

purchases. Average housing rental data is available and has been used in past studies as

a proxy for the wealth. You have asked Dazzle Jewellery to randomly collect data on price,

year of transaction, carats, design complexity, location, and average housing rental.

Dazzle Jewellery randomly selected 100 transactions of pink diamonds from year 2012

and another 100 transactions from year 2013 from each of their 5 stores. The data is

organised in the excel file “dazzle_pink_diamonds_student_dataset.xlsx” provided.

Although some members of Dazzle Jewellery management have a good understanding of

statistical models and methods, most of the members have only a basic understanding.

Use Excel software to analyse data to write a report. Your report should include your

inferences, interpretations, conclusions and recommendations supported by Excel outputs

that validate (or not) the claims mentioned above. The report should also mention

limitations and concerns if any. An executive summary aimed at members who are not

knowledgeable in statistics should also be provided.

Variables in Excel Data:

Item Auto-generated numbers for 1,000 transaction data records

Price ($)

Price paid

Carat

Weight of the diamonds sold

Year ID

0 for Year 2012 and 1 for Year 2013

Year

Year of the transaction; 2012 or 2013

Style ID

0 for basic machine cut design styles and 1 for complex cut design

styles

Style

The style of the design; Basic or Complex

Store ID

5 stores numbered 1 to 5

Location ID

0 for residential location and 1 for city location

Location

The location of the store; in a residential or city location

Home Rental ($)

The average home rental price per square foot for that area

Your report should include the following:

Part I (15 marks)

1. For a preliminary investigation to validate if prices of pink diamond jewellery have

increased, select at least two (2) but not more than four (4) variables. Explain why you

chose these variables. Describe the data using appropriate choice of charts. At least

two (2) chart types should be presented. Explain why you chose these charts.

2. Calculate and interpret measures of location and dispersion that effectively

describe the data. Can these variables be appropriately described with the mean and

standard deviation? Explain.

3. Population mean and standard deviation are given as 0.14 and 0.04 respectively.

Using a normal distribution, identify and compute the probability that the weight of a

randomly selected pink diamond jewel sold is more than 0.18 carats. Define the

probability that the weight is exactly 0.18 carats. Explain the difference.

Part II (20 marks)

Apply a suitable hypothesis-testing approach to analyse the factors that drive price.

1. State and explain your hypothesis. Explain how regression analysis can be used to

carry out this test.

2. Identify and fit a suitable linear regression model to the data. List your variables.

3. Interpret the relevant statistics from the output generated by your Excel software.

Statistics should include the coefficient of correlation, coefficient of determination and a

hypothesis test of the coefficient of correlation.

4. Describe the relationship implied by the model you have estimated. Execute a

hypothesis test on each of the regression coefficients in your model and interpret.

5. Discuss if the assumptions of the models used are valid for the data.

Part III (15 marks)

1. Use an ANOVA test to evaluate if the prices among the 5 stores are the same in

2012 and 2013. Will the test results affect your interpretation above? Explain.

2. Determine any other statistical limitations (if any) and give an explanation of how

they could affect this analysis.

3. Describe possible improvements you would recommend including data collection

and follow-up analyses.

Part IV (10 marks)

1. Describe, discuss & interpret your findings, conclusions, assumptions, limitations and

recommendations for Dazzle Jewellery management in a 300 word executive

summary. You must write concisely.