Descriptive Analytics and Visualisation Assignment two – Trimester 2, 2016 Page 1 DEAKIN UNIVERSITY FACULTY OF BUSINESS & LAW DEAKIN BUSINESS SCHOOL DEPARTMENT OF INFORMATION SYSTEMS AND BUSINESS ANALYTICS MIS771 Descriptive Analytics and Visualisation Assignment Two Background This is an individual assignment, which requires you to analyse a given data set, interpret and draw conclusions from your analysis, and then convey your conclusions in a written report to a person with little or no knowledge of Business Analytics. Percentage of final grade 40% Due date Monday 19th September 2016 11.59pm The assignment must be submitted by the due date electronically in CloudDeakin. When submitting electronically, you must check that you have submitted the work correctly by following the instructions provided in CloudDeakin. Please note that we will NOT accept any hard copies or part of the assignment submitted after the deadline or via Email. No extensions will be considered unless a written request is submitted and negotiated with Dr Dilal Saundage prior to Thursday 15th September 2016, 5pm. Please note that assignment extensions will only be considered if you attach your draft assignment with your request for an extension. The assignment uses the file Supermart.xlsx which can be downloaded from CloudDeakin. Analysis of the data requires the use of techniques studied in both Modules 1 and 2.Descriptive Analytics and Visualisation Assignment two – Trimester 2, 2016 Page 2 Assurance of Learning This assignment assesses following Graduate Learning Outcomes and related Unit Learning Outcomes: Graduate Learning Outcome (GLO) Unit Learning Outcome (ULO) GLO1: Discipline‐specific knowledge and capabilities: appropriate to the level of study related to a discipline or profession. GLO5: Problem Solving: creating solutions to authentic (real world and ill define) problems. ULO1: Create solutions by applying data analysis and statistical techniques and skills to practical business, economic and personal decision‐making and research. ULO2: Use computer software to analyse data and understand the limitations of such software. ULO3: Deduce clear and unambiguous solutions in a form that they useful for decision making and research purposes and for communication to the wider public. ULO4: Appraise the accuracy, sources and relevance of statistical data and models. ULO5: Create and appraise solutions, reports and arguments based on statistical data and models. Feedback prior to submission Students are able to seek assistance from the teaching staff to ascertain whether the assignment conforms to submission guidelines. Feedback after submission Your assignment feedback will be returned in a rubric via CloudDeakin with an overall mark together with comments. In order to understand any areas of improvement, students are expected to refer, and compare, their answers to the suggested solutions. Case Study Supermart is one of Australia's leading supermarket chains. There are 700 stores in the chain. Originating from a family based chain of general stores, Supermart now has stores all over Australia, with the first one being established 27 years ago. In 2015 the company launched its online store to enable customers, in selected suburbs, to make their purchase online. In terms of operation, each state capital has a company office and they have significant autonomy in the individual state's operations. Further, individual store management has wide‐ranging powers about day‐to‐day operations of their stores. However, broad company planning and direction take place in the company Head Office in Melbourne. Included in the Head Office, is the Research and Analysis Department. Having compared the company's position in the market and against the competitors, the General Manager ‐ Michael Braithwaite has asked you, Ruby Rose, to conduct exploratory and descriptive analysis to gain a better understanding of Sales of Supermart.Descriptive Analytics and Visualisation Assignment two – Trimester 2, 2016 Page 3 The Data The data relates to a random sample of 150 stores in the Supermart chain. The survey is conducted every year to gather information that is not readily available from the central sources. The variables in the data table are described below: Variable Name Description Store No. Unique ID of the store Sales $m Total Sales revenue for each store for the financial year ($ million) Wages $m Total Wage and salary bill for the financial year ($million) No. Staff The number of effective full‐time staff employed on a weekly basis Av. Wage The average annual wage/salary per effective full‐time staff member. GrossProfit $m Gross Profit for each store for the financial year ($ million) Adv.$'000 Advertising and promotional expenses for the financial year ($'000) Competitors The number of competing stores in the consumer catchment area HrsTrading The total number of hours open for trading per week Sundays Open on Sundays; Close on Sunday Mng‐Gender Male store manager; Female store manager Mng‐Age Age of the store manager, years Mng‐Exp No. of years of experience in some form of junior/senior management at Supermart Mng‐Train No. of management training courses taken while employed at Supermart Union% The proportion of the staff at the store which belongs to a union Car Spaces The number of parking spaces available to the store Online Channel Whether or not the store has an online channel OnlineSales$m Sales revenue from Online Sales Channel ($ million) Basket:2015 Cost ($) of the basket of food items in each store at 1 June 2015 Basket:2016 Cost ($) of the basket of food items in each store at 1 June 2016 %Change Annual percentage increase of the cost of the basket of food items in each store In addition, time series data is available on Quarterly Sales Time Period Time Period Index Quarter Quarter Description Total Sales($m) Total Sales of Supermart in $mDescriptive Analytics and Visualisation Assignment two – Trimester 2, 2016 Page 4 Email from Michael Braithwaite To: Ruby Rose From: Michael Braithwaite – General Manager Subject: Analysis of Sales Attachment: Appendix – A (Explanatory Notes) Dear Ruby, Thank you for your earlier responses to my email as they were very insightful. Your responses got me thinking about few more issues about our financial position, especially Sales. 1. Can you provide an overall summary of Sales? 2. I presumed that our Sales differ significantly between stores. I would like a report on main factors that influence Sales. 3. Richard tells me that you are very good at model building. Are you able to build a model for Sales as I am very keen to understand what other factors influence Sales? Hopefully, your model will also provide me with an opportunity to predict Sales for a given scenario. 4. We have been tracking our quarterly Sales for a while. I would like you to develop a multiplicative time‐ series model to forecast Sales for the next 4 quarters. 5. I would also like to formalise what we have done to date so that we can repeat this exercise next year: Can you critically evaluate what we have done so far? In your opinion are we doing the right type of analysis? Are there any alternative approaches? Are there any other sources of data we can use? I look forward to reading your report. Sincerely Michael BraithwaiteDescriptive Analytics and Visualisation Assignment two – Trimester 2, 2016 Page 5 Appendix – A (Explanatory Notes) In order to prepare a reply to Michael's email, you will need to examine and analyse the dataset, Supermart.xlsx, thoroughly. The following are some guidelines to follow. Task One – Summary of Sales Only analyse Sales by itself. The importance of other variables is considered in other tasks. You should, at the very least, thoroughly investigate relevant summary measures (and their reliability) for this variable. Also, there may well be suitable tables and graphs that will illustrate, further and more clearly, other important features of sales. In your report you should comment, where relevant, on data location, central tendency, variability, shape and outliers for this variable. Reference: Module 1 – Topic 2 Task Two – Factors influencing Sales Analyse Sales of stores against other variables included in the data set. Use appropriate descriptive techniques such as cross‐tabulations, comparative summary measures, scatter diagrams to explore key relationships. In your report you should only include the most important factors that impact Sales. Reference: Module 1 – Topic 3 Task Three – Development of a multiple regression model You should follow the model building process outlined in topic 5. You are only required to consider linear relationships in the model. Each stage of developing your model should be included in your analysis. You will notice in the Supermart Excel file that there are separate worksheets (tabs) named Q3‐1, Q3‐2, etc. These are where you place each version of your model. Note that if you have undertaken more iterations of the model then add more worksheets as required. Minimum requirements for the final model:  At least two independent variables  One categorical independent variable In your written report, please provide an interpretation of all elements of the final model – (for e.g. a practical interpretation of the regression equation, description of the explanatory / predictive power/ overall strength of the models as well as the significance of each individual independent variable.) Reference: Module 2 – Topics 4 and 5 Task Four – Time Series analysis Quarterly Total Sales for Supermart from Q2, 2012 to Q1, 2016 are given in the QtrSales worksheet. Develop a multiplicative time series model to forecast Sales for the next 4 quarters (Q2, 2016 to Q1, 2017). If the observed values for those 4 quarters are as below, calculate the MAPE of the forecast. Time Period Quarter Observed 17 2016‐Q2 95 18 2016‐Q3 132 19 2016‐Q4 150 20 2017‐Q1 109Descriptive Analytics and Visualisation Assignment two – Trimester 2, 2016 Page 6 In your written report, please provide an interpretation of the final model – (for e.g. a practical interpretation of the time series model, measures of forecast accuracy, choices about exponential smoothing constant). Reference: Module 2 – Topic 6 Task Five – Critique the Business Research Approach Discuss the suitability of the general business research approach taken. In your response, include possible alternative approaches and other sources of (secondary) data. If the analysis was to be repeated in the future, would you recommend a different approach? Note that no actual analysis is required for this task. Reference: Module 1 – Topic 1 Submission The assignment consists of two parts: Analysis and Report. You are required to submit both your written report (approx. 2000 words) and analysis (in Excel).  Analysis The analysis should be submitted in the appropriate worksheets in the Excel file. Each step in the model building for task three should be included in the tabs Q3‐Correlation, Q3‐1, Q3‐2, etc. If you need more worksheets, then add them. Further instructions are included at the top of each worksheet. Before submitting your analysis make sure it is logically organised and any incorrect or unnecessary output has been removed. Marks will be penalised for poor presentation or disorganised/incorrect results. Note: Give the Excel file an appropriate name such as A2_studentID.xlsx (use a short file name while you are doing the analysis – once you complete your analysis rename the file to the format mention before).  Report The report should be written for an audience that has no, or minimal, business analytics background. You should avoid the use of technical terms. The one exception may be in task 3 and 4 as you may want to include the actual models in the report. In this report first and foremost you are required to present the insights of your analysis (tasks 1‐4) then provide a critique of the approach you adapted for this study (task 5). It is up to you how to structure and format the final report. Note: Name the report with an appropriate file name such as A2_studentID.docx.Descriptive Analytics and Visualisation Assignment two – Trimester 2, 2016 Page 7 Criteria Poor Below standard Satisfactory Good Very good Excellent Analysis Task 1 (10%) Does not use any appropriate exploratory data analysis tools. Uses irrelevant or inappropriate techniques to analyse the SALES variable and/or there are many errors in the analysis. Uses appropriate data analysis and visualisation tools to analyse the data but there are some errors in the analysis. Mostly good analysis of the SALES data using appropriate techniques but there are minor errors in the analysis. Very comprehensive analysis of the SALES data using appropriate techniques. Skilful and comprehensive analysis of the SALES data using many different techniques. Uses data visualisations/tables to produce novel insights. Analysis Task 2 (Marks: 15%) Does not use any appropriate bivariate exploratory data analysis tools. Uses irrelevant or inappropriate bivariate techniques to identify relationships and/or there are many errors in the analysis. Uses appropriate bivariate data analysis and to identify relationships but there are some errors in the analysis. Most of the important factors have been identified using appropriate techniques but there are minor errors in the analysis. All of the important factors have been identified using appropriate techniques. Skilful and comprehensive identification of all relationships using a variety of appropriate techniques. Analysis Task 3 (Marks: 20%) No correlation analysis performed and/or inappropriate model developed. Correlation analysis and regression model developed but there are many errors in the analysis. Correlation analysis identified appropriate variables and regression model was OK but there are some errors in the analysis. Correlation analysis identified important factors and model was developed in a logical fashion but there are minor errors/missing analysis. All of the important variables included (taking into account multi‐collinearity). Model developed in a logical fashion including appropriate residual analysis. Correlation and regression modelling completed in a logical and comprehensive fashion taking into consideration multi‐ collinearity, dummy variables, residual analysis, etc. Analysis Task 4 (Marks: 15%) Does not use any appropriate time series techniques. Uses irrelevant or inappropriate techniques to analyse the time series and/or there are many errors in the analysis. Time series model developed but there are some errors in the analysis. Time series model and MAPE developed but there are minor errors in the analysis. Time series and MAPE model developed correctly. Time series and MAPE model developed correctly and presented in a clear and logical fashion including visualisations.Descriptive Analytics and Visualisation Assignment two – Trimester 2, 2016 Page 8 Criteria Poor Below standard Satisfactory Good Very good Excellent Report (Marks: 40%) Does not communicate any of the main findings of the analysis in an accurate or useful way. No or poor critique of approach provided. Interpretation and communication of findings is at a basic level or does not adequately explain the main findings of the analysis. Critique of approach not explained well. Explains most of the main findings of the analysis accurately and enables reader to draw some reasonable conclusions. Critique of approach provides OK guidelines for future analysis. Provides a reasonable and accurate description of the most important features of the analysis along with appropriately qualified conclusions. Critique provides overview of potential alternatives and recommendations for fur future analysis. Provides a very detailed and accurate descriptions of the most important features of the analysis in appropriate language. Appropriately qualified conclusions are very sound and critique of approach provides good insights to alternatives and future analysis. Provides an outstanding descriptions and conclusions that are carefully considered, novel, explained in clear language and insightful. Critique provides an excellent summary of alternative approaches along with recommendations.