Everyone should read the below information carefully.
For the second assessment tsak, you are required to present manual on how to use Excel for data visualisation It is a good idea to work on your manuals as you are completing the weekly Excel and Power BI tasks, and the case study assessment tasks. These manuals can be used in the future as a reference in your future careers.
Where/when:
Excel manual - submit via Turnitin link on Moodle
Weighting: 25% of your final grade.
Format: Keep in mind that your manuals should be something you can take with you to a future job, so these will be best presented as a Word document or PDF (or similar).
All completed files must be uploaded to their co
esponding Turnitin drop box. It is your responsibility to check that your upload has been successful. If you upload a blank file, or a file that we are unable to read, your submission will not be marked. You will be able to upload to the Turnitin link multiple times before the submission date (if you wish). We encourage you to upload a 'practice submission' before the due date so that you can check that all content you uploaded can be seen in Turnitin. Be aware that, if you have submitted a file prior to the due date/time, you will NOT be able to re-upload another document after the due date/time.
Please read the Assessment Criteria document which provides more finer details of what to include and a marking guide for both the self-reflection and manuals.
Word count: Equivalent to 1,000 words
The manuals can comprise of both images and texts.
Do I need to reference my manuals?
The manuals are created from your own learnings, so no referencing is needed.
However, if you refer to some information you have gathered that is not your own thought or idea, an appropriate in-text citation and reference is required.
Content
(10 marks)
The manual is a comprehensive user guide to the software, which includes instructions on all methods and skills learnt and utilised throughout the unit. The manual demonstrates excellent knowledge of software use, including how and when to use certain features of the software.
(8.5 – 10 marks)
Quality and
Usability of Manual
(5 marks)
The manual is well-written with minimal to no e
ors in spelling and grammar. It demonstrates excellent usability with detailed, easy-to-follow instructions, and many helpful figures/images to guide the reader through the software. The manual has a logical flow and hierarchy, and contains a table of contents for easy searchability. (4.5 – 5 marks)
BUSINESS INTELLIGENCE AND BIG DATA
EXCEL WORKSHEET 1
Companies rely on data to make important business decisions. However, data comes in a range of unusable or poorly formatted outputs. Small business or community organisations, in particular, may use very manual processes to generate and store data. Being able to take various datasets and manipulate them easily in Excel is an important skill as a manager. For these exercises, small datasets will be used, but the same skills and knowledge will apply when using larger datasets.
Skills:
· Import data into a manageable format in Excel
· Sort and order data
· Clean-up data that has been generated from multiple sources
· Create spreadsheets that can be viewed or printed easily
Activity 1 (30 minutes): Use the basics of Excel using the data found in the file:
Question 1. Do some research into all the file formats that can be imported into Excel. Create a table of the differences between each.
Question 2. Import the data into Excel so that each new value is a new cell and create a spreadsheet that has the following features:
· Entire values can be seen
· The column headings and the customers’ names are always visible even when scrolling
· The email addresses are hidden
· Only records without a Canadian email address are shown
· Records where the customer whose address is an Apartment are shaded red
Class Discussion XXXXXXXXXXminutes): Share Answers
Be ready to share your screen and share your answers with the class. If you finish early, work on your custom Excel manual.
Activity 2 (30 minutes): Use Excel to clean data using the file:
Question 1. Imagine you have asked one of your staff members for a list of all customers over the last 6 months. After a couple of days, they email you the file above, commenting that they copied the data from a number of databases, including QR check-in registers, online orders, sales records databases and the company’s CRM. Open the data in Excel. As a manager, what data source should they have used?
Question 2. Remove duplicate values and note down how many were removed.
Question 3. Your manager asks you to split the list of customers into four groups by surname: A-F, G-M, N-S and T-Z and assign your 4 sales reps to call the customers on their list. Is this fair?
Question 4: Does the data need any clean-up? i.e. are there any issues with the data?
Class Discussion XXXXXXXXXXminutes): Share Answers
Be ready to share your screen and share your answers with the class. If you finish early, work on your custom Excel manual.
Data sets-
Customer names –
Jane Jarvis
Nichole O'conno
Wing Fry
Tallulah Mcneil
Thaddeus Ba
on
Evelyn Gi
s
Jane Jarvis
Wyatt Henson
Randall Cooley
Bree Pruitt
Da
el Davis
Evelyn Gi
s
Colette Mullen
Hall Mcconnell
John Lane
Dorothy Sharpe
Nichole O'conno
Craig Whitake
Sawyer Bradshaw
Karleigh Garza
Stone Schneide
Lila Schwartz
Lucas Campos
Mufutau Rice
Silas Kinney
Chiquita Cole
Hakeem Stein
Lara Langley
Serina Potts
Conan Ochoa
Ishmael Gonzalez
Nichole OConno
Kitra Blevins
Preston Ingram
Eagan Horne
Colt White
Carlos Bartlett
Adrienne Bush
Declan Joyne
Ila Camacho
Noble Velez
Shaine Hartman
Philip Rasmussen
Callum Anderson
Evelyn Gi
s
Ja
od Holden
Keaton Hendrix
Tatiana Austin
Dorian Lara
Amena Glove
Phoebe Harmon
Jesse Pruitt
Alan Holland
Heidi Romero
Minerva Cook
Ingrid Tucke
Leroy Clark
Carlos Vincent
Tanner Le-Blanc
Naomi Mu
ay
Evelyn Gi
s
Sage Ayers
Minerva Cook
Sawyer Bradshaw
Tatiana Austin
Lila Schwartz
Jack O
Roary Vincent
Edan Travis
Kai Miranda
Randall Cooley
Halla Benjamin
Evelyn Gi
s
Laith Ro
ins
Hop Leon
Evelyn Gi
s
Austin Mann
Ezekiel Evans
Wayne Petersen
Evelyn Gi
s
Oleg Rivas
Li
y Fitzgerald
Evelyn Gi
s
Evelyn Gi
s
Leila Miranda
Uta O
Kai May
Evelyn Gi
s
April Dorsey
Evelyn Gi
s
Amery Frank
Felicia Coleman
Roary Vincent
Evelyn Gi
s
Evelyn Gi
s
Heidi Vance
Evelyn Gi
s
Sylvester Blackwell
Victor Fleming
Evelyn Gi
s
Ba
y Weiss
Wylie Fulle
Preston Ingram
Evelyn Gi
s
Upton Sykes
Felix Donaldson
Justin White
Vivian Tanne
Alexander Hogan
Preston Banks
Karly Neal
Yoshi Espinoza
Iris Juarez
Noble Velez
Thane Craft
Ahmed Mccray
Peter Valenzuela
Judah Ayala
Phelan Espinoza
Tanner Le-blanc
Evelyn Gi
s
Derek Odom
Evelyn Gi
s
Guy Mack
Lynn Webste
Naomi Mu
ay
Evelyn Gi
s
Kaitlin Shepard
Anne Blackwell
Emerson O'donnell
Colt White
Evelyn Gi
s
Philip Rasmussen
Tanner Leblanc
Ira Tate
Sample Data set
"name phone email address alphanumeric"
"Adrienne Bush XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXMolestie Road JWM44DJL9QQ"
"Tallulah Mcneil XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXAc Rd. YMN87LUV1VX"
"Leila Miranda XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXDui Avenue VMC67CLT6KU"
"Keaton Hendrix XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXScelerisque Avenue XCQ60FVV1EC"
"Oleg Rivas XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXDui. Road HNN39JDL1FU"
"Thaddeus Ba
on XXXXXXXXXX XXXXXXXXXX "" XXXXXXXXXXAt, St."" KQD15GYS8MU"
"Dorian Lara XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXUltrices St. DBJ86WHX7KR"
"Carlos Bartlett XXXXXXXXXX XXXXXXXXXX "" XXXXXXXXXXMontes, Av."" NSZ86PEN5BM"
"Ila Camacho XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXVel Rd. QTQ14EYO1FJ"
"Heidi Vance XXXXXXXXXX XXXXXXXXXX ""P.O. Box 550, 9923 Nulla. Road"" NWV77YNO2DG"
"Lucas Campos XXXXXXXXXX XXXXXXXXXX 5940 Eget Rd. VLT21JBC8NF"
"Ezekiel Evans XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXNec Street LTH21HRP8QJ"
"Declan Joyner XXXXXXXXXX XXXXXXXXXX 6725 Donec St. KYM57OTO1PI"
"Conan Ochoa XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXInteger Road FYS87VED2DL"
"Edan Travis XXXXXXXXXX XXXXXXXXXX ""P.O. Box 544, 3118 Quam Road"" GVM29UCT5XY"
"Alan Holland XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXUrna Rd. HHC11TXW8EM"
"Felix Donaldson XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXLuctus. Rd. OBY84WRB7YK"
"Ahmed Mccray XXXXXXXXXX XXXXXXXXXX ""P.O. Box 481, 754 Eu Road"" JZO71BKR2VG"
"Judah Ayala XXXXXXXXXX XXXXXXXXXX ""Ap # XXXXXXXXXXLorem, Avenue"" QTU63WYI9GH"
"Wayne Petersen XXXXXXXXXX XXXXXXXXXX 9954 Sem Rd. NYZ23BCI3OH"
"Austin Mann XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXIpsum. St. MNJ71USP8ZV"
"Thane Craft XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXSodales Av. LVT30QBF1JY"
"Victor Fleming XXXXXXXXXX XXXXXXXXXX 2227 Adipiscing Avenue QOM61ELW4CT"
"Shaine Hartman XXXXXXXXXX XXXXXXXXXX ""P.O. Box 753, 5688 Ultricies St."" SBD28QSK4PF"
"Uta O
XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXJusto Rd. NRH91BTS4UI"
"Karleigh Garza XXXXXXXXXX XXXXXXXXXX ""P.O. Box 422, 8861 Malesuada. Rd."" KQO84GSQ4MK"
"Sawyer Bradshaw XXXXXXXXXX XXXXXXXXXX ""P.O. Box 749, 8206 Rhoncus. St."" ZOX18WWP9NF"
"Colt White XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXMauris St. EGQ93VTV4XM"
"Noble Velez XXXXXXXXXX XXXXXXXXXX ""P.O. Box 975, 5149 Quis, Ave"" XMM79MTL9GU"
"Jane Jarvis XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXDictum. St. WZW32TFD4OW"
"Philip Rasmussen XXXXXXXXXX XXXXXXXXXX ""P.O. Box 853, 283 Gravida Street"" EXR47NBB9LF"
"Lila Schwartz XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXSodales Avenue VEG56RFU2NC"
"Nichole O'connor XXXXXXXXXX XXXXXXXXXX 2962 Risus Avenue XUV01NNP8PG"
"Naomi Mu
ay XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXErat Av. JAD65YQP1JR"
"Preston Ingram XXXXXXXXXX XXXXXXXXXX "" XXXXXXXXXXLeo, Street"" ZSO07KEW5YO"
"Roary Vincent XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXHymenaeos. Rd. QNJ16CQW1UX"
"Randall Cooley XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXIn Avenue AWI28HEV8US"
"Tatiana Austin XXXXXXXXXX XXXXXXXXXX ""P.O. Box 837, 1268 Sapien, Av."" SCL32QEB7NO"
"Minerva Cook XXXXXXXXXX XXXXXXXXXX ""Ap # XXXXXXXXXXIn, St."" QRQ12UVH4PW"
"Carlos Vincent XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXNullam Av. OGY51CVO3YZ"
"Phoebe Harmon XXXXXXXXXX XXXXXXXXXX ""P.O. Box 668, 6541 Duis St."" WXU62BQX8VA"
"Ingrid Tucker XXXXXXXXXX XXXXXXXXXX "" XXXXXXXXXXUltrices, Ave"" FRL49WDV3OG"
"Kai May XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXVestibulum Rd. BSR64VRS2RZ"
"Hall Mcconnell XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXTellus Ave DIH26EOQ7SJ"
"Jesse Pruitt XXXXXXXXXX XXXXXXXXXX 5335 Pede Rd. GBR15FGT5CL"
"Alexander Hogan XXXXXXXXXX XXXXXXXXXX ""P.O. Box 548, 7105 Ridiculus Street"" ATX93FLD2ZI"
"Vivian Tanner XXXXXXXXXX XXXXXXXXXX ""Ap # XXXXXXXXXXOrci, St."" SCR49XRP4FE"
"Hop Leon XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXAc St. FQC55QUP3KQ"
"Ba
y Weiss XXXXXXXXXX XXXXXXXXXX 9368 Massa Rd. VRO58BJS5LE"
"Amena Glover XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXPhasellus Road IQD76PUJ6UZ"
"Sage Ayers XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXNulla. Av. XCR92CSW5UW"
"Mufutau Rice XXXXXXXXXX XXXXXXXXXX ""4806 Montes, Street"" JLO82NXH4CV"
"Peter Valenzuela XXXXXXXXXX XXXXXXXXXX ""2794 Vitae, Ave"" XHO56SBE7TU"
"Halla Benjamin XXXXXXXXXX XXXXXXXXXX ""P.O. Box 286, 6460 Nulla St."" QFG86NKV3DX"
"Preston Banks XXXXXXXXXX XXXXXXXXXX 7054 Id Avenue UJT15CEO8HB"
"Laith Ro
ins XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXLorem St. NNL74QZH8XN"
"Emerson O'donnell XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXUt Av. UJJ27VAY5CX"
"Lara Langley XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXNibh Avenue JBM71GAN5RO"
"Kai Miranda XXXXXXXXXX XXXXXXXXXX ""P.O. Box 571, 6908 Vitae Street"" XPK41ZEX5OE"
"Wylie Fuller XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXLuctus. Av. IVZ82GUC5MA"
"Anne Blackwell XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXLacinia Street ZGS11KLM8RE"
"Yoshi Espinoza XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXSed Avenue NRX51WDB6XY"
"Justin White XXXXXXXXXX XXXXXXXXXX 3238 Mauris St. DAM18IXJ6UJ"
"April Dorsey XXXXXXXXXX XXXXXXXXXX 5951 Risus. Ave ZRD85KFM8TU"
"Stone Schneider XXXXXXXXXX XXXXXXXXXX 5833 Curabitur St. KKV38EMM5GB"
"Chiquita Cole XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXFermentum Road MKL58UOA2WG"
"Eagan Horne XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXNec Avenue RHV58IXS7CD"
"Kitra Blevins XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXPosuere Av. PIY15TVE6LW"
"Felicia Coleman XXXXXXXXXX XXXXXXXXXX 7845 Dignissim Road ODI81MDX4OS"
"Iris Juarez XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXDui Rd. GHM14VWS4SD"
"Sylvester Blackwell XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXElementum Rd. RDE36KGF0BG"
"Karly Neal XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXEt Road HEH06UMT4DM"
"Colette Mullen XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXCommodo Av. JBY45RQV3IE"
"Craig Whitaker XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXNulla Avenue RCG71LZN5GY"
"Heidi Romero XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXAc Street KLB53CTW4WT"
"Li
y Fitzgerald XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXAugue Avenue PAR65WHB3MC"
"Ja
od Holden XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXNisi Road KXN23PIE1KM"
"Derek Odom XXXXXXXXXX XXXXXXXXXX ""P.O. Box 427, 5651 Malesuada Ave"" ZTN52XFI2GC"
"Upton Sykes XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXEgestas Rd. SPZ78LCA7CG"
"Ishmael Gonzalez XXXXXXXXXX XXXXXXXXXX ""3826 Placerat, Rd."" OHJ29YMR7NV"
"Tanner Leblanc XXXXXXXXXX XXXXXXXXXX ""P.O. Box 191, 7406 Ultricies Avenue"" IYR15GFT3SS"
"Jack O
XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXNeque. Av. NET62OUG5WM"
"Ira Tate XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXUltricies St. UGZ12FMZ5MO"
"Wyatt Henson XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXAliquam St. RJE16YUS3WJ"
"Serina Potts XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXQuis Road MXV01UUL2DU"
"Phelan Espinoza XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXOrnare Road UMR05ICU6NW"
"Lynn Webster XXXXXXXXXX XXXXXXXXXX 1626 Phasellus Road GTC13PXY7UY"
"Callum Anderson XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXPede Ave FDW00SKD1WO"
"Guy Mack XXXXXXXXXX XXXXXXXXXX ""P.O. Box 184, 3462 Aliquam St."" WMS81IUR7BC"
"Leroy Clark XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXSem Avenue DDO27PVP8RJ"
"Dorothy Sharpe XXXXXXXXXX XXXXXXXXXX "" XXXXXXXXXXDolor, Av."" TCS79DRV5CB"
"Hakeem Stein XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXTempor Av. BHO58YTJ8NO"
"Amery Frank XXXXXXXXXX XXXXXXXXXX ""P.O. Box 361, 3666 Posuere, Av."" OJQ47RKJ8BY"
"Bree Pruitt XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXAmet St. PHC85FQV3EY"
"Evelyn Gi
s XXXXXXXXXX XXXXXXXXXX ""P.O. Box 887, 114 Penatibus Rd."" DEY86SIB6XK"
"Da
el Davis XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXRutrum Ave GND51HSR4YX"
"John Lane XXXXXXXXXX XXXXXXXXXX Ap # XXXXXXXXXXDiam Ave QUC93JJC5IW"
"Silas Kinney XXXXXXXXXX XXXXXXXXXX XXXXXXXXXXQuisque St. BPI35VYT7LL"
"Wing Fry XXXXXXXXXX XXXXXXXXXX ""P.O. Box 654, 8383 Etiam Avenue"" BVL54KTQ5AK"
"Kaitlin Shepard XXXXXXXXXX XXXXXXXXXX 1826 Posuere Street XTS80SGM7LR"
BUSINESS INTELLIGENCE AND BIG DATA
EXCEL WORKSHEET 2
Excel is not just a database. In fact it is quite poor as a stand-alone database. Instead its power is in its ability to make calculations and outputs using a variety of numerical and logical formulas. By using formulas, one can turn data into meaningful information and draw specific answers, trends and outputs as required for a particular business purpose. For this exercise, a larger dataset will be used when compared to last week, in order to ensure that you cannot “cheat” and use manual processes for deriving the answers!
Skills:
· Use a range of formulas to interpret data
· Fix unusual data so it is ready for interpreting
· Use a sequence of formulas that add value to the dataset
Activity 1 (60 minutes): Use the basics of Excel using the data found in the file:
Question 1. Repeat the basic steps from last class by:
a) Freezing the top row;
) Fitting row widths to text;
c) Applying filters to each column;
and share your screen. Once you have the okay from the lecturer, you can proceed.
Question 2. Pretend that you have been given this data and need to tell your manager the sales figures for the quarter. After opening the dataset, you notice the SALES figures have been calculated inco
ectly and instead need to show QUANTITY ORDERED multiplied by PRICE OF EACH. Fix this mistake and calculate the total sales for the year.
Question 3. In presenting your annual report, you decide to present the following table. Fill it in.
QUARTER
TOTAL # OF SALES
AVERAGE PRICE / SALE
TOTAL REVENUE
1
2
3
4
Question 4: When calculating the values, what cells did you use to display the answers? Did you add rows at the bottom of the data, or add columns to the right, or start a new ta
ed sheet? Discuss the pros and cons of each of these 3 alternatives.
Class Discussion XXXXXXXXXXminutes): Share Answers
Be ready to share your screen and