Effective data analytics and visualisation using Excel

Assignment 1:Marks: 50(Equivalent to15% ofthe final grade)

AssignmentType:Individual

Overview

Over the past few weeks, you have gained a general understanding of using spreadsheets inbusiness.ThisassignmentwillallowyoutodemonstratewhatyoulearnedthroughaspreadsheetfilenamedBUS1001-Ass1.xlsx.

AcademicIntegrity

Bysubmitting*thispieceofwork andsigningthisdocument,Ideclarethat:

1. Theworkismy own
2. I have not previously submitted all or part of this work for assessment in any subject, unlessthe subject coordinator for the current subject (or my research supervisor, if applicable) hasgiven me written permission to reuse specific material and I have correctly referenced thematerialtakenfrommyown earlier work.
3. I have read and agree to be bound by the Statutes, Regulations and Policies of theUniversity relating to Academic Integrity available athttp://www.latrobe.edu.au/students/academic-integrity;and
4. I may be subject to student discipline processes in the event of an act of academicmisconductbymeincludinganactofplagiarismor cheating.

I further grant to the University or any third party authorised by the University(www.latrobe.edu.au/text-match) the right to reproduce and/or communicate (make available onlineorelectronicallytransmit)theworkIhavesubmittedforthepurposeofdetectingplagiarism.

AssignmentRequirements

The given BUS1001-Ass1.xlsx workbook comprises six worksheets: Data Dictionary,Order,City, Query, Sales and ProductCategory.

The Order worksheet is the main sheet containing the business data of a Brazilian E-commercecompany from 2016 to 2018. Each row in the worksheet includes information on one line in acustomer order.Therefore, an order ID may appear in multiple rows. Note that the total sales wementionhereis calculatedbased onsellingpriceandnot includetheshipping fee.

Start by exploring the workbook's contents to understand the meaning of the data, then apply dataanalyticstosupportdecision-making.

SubmitONEExcelfile,whichincludesalltheanswerstothefollowingquestions.

1. (4marks)Let'sdosomesimpleTurnthedataintheOrdersheetintoatableandwriteformulas toanswer thequestions listedintheQuery worksheet.

2. (16 marks) The company manager wants to get an insight into the sales performance ofdifferent

• (2marks)IntheSalessheet:WriteformulastofilltheCustomerCitycolumnwiththecustomercityofthecorresponding
• (2marks)IntheSalessheet:WriteformulastofilltheCustomerStateusingthe informationprovided inthe City worksheet
• (2marks)IntheSalessheet:WriteformulastofilltheTotalSalescolumnfromeach customer.
• (8 marks) In a new worksheet: Create a pivot table and chart showing the numberof customers and total sales of each state. Filter to the bottom five states that havethe least total sales only. Choose appropriate chart to make both measures visibleonone chart.
• (2 marks) Write a short paragraph (max 150 words) to describe your insight whencomparing sales performance of these five states. Identify any interestingobservations/patterns.

3. (14 marks) The manager wants to investigate further into the performance of twostates,Bahia and Riode janeiro. In anewworksheet:
   • (2 marks) Extract or copy the Total Sales data of these two states from the Salessheet into two columns, one for each state. (Hint: Apply filter and copy data of eachgroupfromthe Salessheet into anewcolumn inthe newsheet).
   • (8marks)Foreachstatestotalsaledata:
     1. (1mark)drawa
     2. (1mark)usetheDataAnalysistooltodisplayitsdescriptive

• (2 marks) write a short paragraph (max 150 words) describing your insightaboutits data distribution.

1. (2 marks) Draw boxplots to compare the sales distributions of the two states. Hideoutliers inthe plots.
2. (2 marks) Write a short paragraph (max 150 words) to describe your insight whencomparingthe sales datadistributions of thetwo states.

4. (16marks)Themanagerwantstogetaninsightintothesalesperformanceofthewatchesgifts

1. (2 marks) In the Order sheet: You can find that the Product Category column isshown in Portuguese, so to help us understand the data for analysis, write a formulato fill the Product Category English column using the information provided in theProductCategory
2. (10 marks) Create a pivot table and chart in a new sheet showing the annualsales and total number of sold items of the watches gifts category. Chooseappropriatechart tomake both measuresvisibleon the
3. (4 marks) Show the trend line (or regression model) and its equation on Q 4.bs Use the equation to forecast the annual sales of the next three years (2019-2021).Howaccurateisthecreatedregressionmodel?Basedonwhichindicator?

SubmissionGuide

Answers to all questions are presented in an Excel file, including the data. For answers writteninshortparagraphs, createatextboxnexttoyourvisualisationtowrite youranswers.

Markingrubrics

The marker in assessing your work will use the following marking guide. Please have a look tounderstandwhatyouneed tocoverforeachquestion inthisassignment.

• Fullmarksforthecorrectandwell-presented Halfofthemarkforsomethingclose.
• Toanswerquestionsthatrequirewritingaformula,youMUSThavetheformulaastheNo mark will begiven without the formula.
• Forallvisualisations(tablesandcharts),well-presentedmeans
  • Havingclearandmeaningfultitles,headers,labels,legends,and
  • Dataisformattedaccordingtotheir
  • Tables and charts are formatted nicely to see the pattern and support understandingtheinsights immediately.
• Forshortanswers,well-presentedmeansvisible,comprehensive,and