How many orders generated the largest net profit

Reference no: EM131731771

PART: Case Study Description and Assignment Tasks

John Steed's Online Emporium (JSOE) is an online retailer that sells over 50 products to customers mainly based in Australia. Data has been obtained from JSOE's transaction system and placed into a Microsoft Access database file. The database contains: Product, Customer and Sales data.

Task A
Answer the following very specific questions by analysing JSOE data using PowerPivot. For this task you only need to submit one word or PDF document. Cut and paste the table below into a word or PDF document, which should be saved under the name: INF30004_A01_T0NN_TaskA (where NN is your team number).

1. What is your team number?

2. How many orders exist in your orders table?

3. Which product(s) appears in the largest number of sales orders?

4. Which product(s) has the largest total quantity sold figure?

5. Which product has the largest number of orders made by females?

6. What percentage of total female orders is this? (Up to 2 decimal places)?

7. Which product(s) have the largest total quantity of sales in 2012?

8. In which month of the year (2012) are sales largest for product 21, 22, 23?
(Underline the most appropriate)

9. Which customer category generated the largest number of orders?

10. How many orders generated the largest net profit? Net Profit is (selling price - cost price) * quantity sold

11. List the product names of the top 10 products that produce the largest total net profit.

Task B
Provide basic data analysis using PowerPivot. For this task you only need to submit one word or PDF document. Cut and paste the data or screen images from Pivot Sheets into a Word or PDF document, which should be saved under the name: Discussions should be supplied, which are presented and labelled in a format that is easy to read and understand.

Tara King, a production manager at JSOE has suggested that around 10% of JSOE product range should no longer be sold (dropped) by JSOE.

Which products would you suggest that Tara King consider dropping? Justify your suggestions.

Do you recommend that all/some of these products be dropped? Justify your recommendation (based on your limited knowledge of the business).

A few months ago, a column named CustValueRating was added to the customer table. The rating values are intended to reflect the 'value' of the customer to the business based on sales history. A score of 1 indicates that the customer is highly valued while a score of 4 indicates that the customer is in the group that should be least valued by the JSOE. Emma Peel, JSOE's leading sales person has doubted the validity of the values in the database.

Is Emma correct in doubting the CustValueRating values? Why?

If Emma's doubting is correct, create a new CustValueRating value and show the top 20 (or thereabouts) customers. Describe the method used to calculate new values.

Task C is to provide further data analysis using PowerPivot. These must be of between 8 & 10 trends or noteworthy observations that you notice within the data.

The analysis must be visually appealing using easy to understand PivotSheets and any data visualizations that are appropriate.
Each of the trends or observations must appear in a new worksheet.
Each worksheet must have an appropriately named tab (not Sheet1, Sheet2...).
Graphs or charts must be clearly labelled
Your Excel Workbook must have a menu that assists the user to navigate between sheets. This can be as simple as 10 buttons on a Home Sheet and a Home button on each sheet. Or it can be some other type of menu navigation if you wish.
Examples of observations could be:
Female customers never order on a Friday;

Male customers have halved in NSW over the past 3 years;
Products 4, 23, 31 and 47 have all had sudden drops in sales at exactly the same times over 3 years.
The presentation of data is important:
You must use slicers & filters that will make it easy for a user to use, read and modify;
You must include several paragraphs (in a text box or similar) describing the analysis. This might include:
Describing what you have done;
Describe why you thought the analysis would be of use to JSOE;
Findings you have observed;
Recommendations or actions that JSOE might consider based on findings.

Task D
Prepare an 800-1000 word report for Louie that outlines 1) importance and need for the ETL process; (2) potential problems that may be encountered performing ETL within the Sunshine Group; and (3) role of data stewardship in the data warehouse environment. For this task you only need to submit one word or PDF document, which should be saved under the name: INF30004_A01_T0NN_TaskD (where NN is your team number).

JSOE is only one of a number of businesses owned by The Sunshine Group. The businesses are generally operated in the Australia, New Zealand and Argentina. Some business are totally online, some are partially online with physical stores in retail shopping centres, while others have no online presence at all.

Louie De Palma, CEO of The Sunshine Group, wants to combine data from these businesses and put it into a Data Warehouse. Louie then wants to use BI tools to perform data analysis.

Data sheet

https://www.dropbox.com/s/rta7e6pgrl3o4a4/Core_Task_Sheets.rar?dl=0

Reference no: EM131731771

Questions Cloud

Identify why a patient would be prescribed androgens : Identify why a patient would be prescribed androgens. Describe the effects of testosterone and androgens on the male body

Drugs acting on the lower respiratory tract : Drugs acting on the lower Respiratory Tract, Many medications given for obstructive pulmonary disorders are given via inhaler

Null and alternative hypotheses in the researchers statement : A sample of 837 ninth-grade girls was asked how much time they spent watching music videos each week. The girls also were asked to rate how concerned.

Define four common problems of urinary system are infection : Describe the sign and symptoms of each condition

How many orders generated the largest net profit : Which customer category generated the largest number of orders - How many orders generated the largest net profit - importance and need for the ETL process

What nursing considerations are important forvthisbpatient : The patient uses an ipratropium (Atrovent) inhaler four times daily. What nursing considerations are most important forvthisbpatient. Why

Illustrate appearance of scatterplot for correlation values : Do the correlation values given by the researchers indicate weak or strong relationships between the variables? Support your answer with a sketch illustrating.

Conduct web research to include relevant statistics : Describe the correctional system (prison or jail system) in your country-of-origin. Conduct web research to include relevant statistics.

Illustrate impression management : Provide a minimum of four personal examples to illustrate impression management, social tuning, social comparisons, mindsets, intrinsic.

User Account

All Pages