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.