Information Systems in Organisations

Lab Assignment 3: Business Analytics

This Lab Assignment is worth 10 marks

Due by Week 10 - Friday 11.55 pm, 17 October 2014

Business Analytics using Spreadsheets

This semester ISO has been covering information systems in organisations and how the use of those systems can contribute to an organisation’s competitive advantage and decision making through the use of various technologies. One way is to do business analyse of the transaction data that has been collected. Business analytics involves the extensive use of:

• data;
• statistical and quantitative analysis methods;
• models of various types;
• various ways of illustrating finds; and
• fact-based management for problem solving.

For this lab assignment you will be using MS Windows based MS Excel 2007/2010 for data analysis (Excel is part of Microsoft Office 2010 found in UC labs). The exercise will require the development and use of PivotTables, PivotCharts, and analysis. You may need to do some research and then apply the knowledge gained on how to do PivotTables and PivotCharts. To assist in how to do PivotTables, MS Excel 2010 Plug_In_T3_2013.pdf has been provided. The data in this pdf file is NOTthe same as in the base data downloaded from the unit Moodle Web site that must be used. You must use allthe data found in the data file.

In this assignment you will use a spreadsheet to model and design a solution to consolidate sales data in different forms in order to provide a report to management. Thus the information shown must be in summary form and only what is needed. Marks will be deducted for not following instructions. Only a MS Excel 2007 or 2010 (UC lab standards) file will be accepted.

Lab Assignment #3

For Lab Assignment 3, you are expected to create several worksheets using PivotTables and related PivotCharts. You must use PivotTables and the results must show that PivotTables have been used. You MUST use all the data found in the “base data” file (all 175 rows – that includes the headings) in all questions. All worksheets must be easy to read, formatted, easy for any user to follow, and follow instructions. For each PivotTable it must address the question with the result showing the sufficient amount of data (too detailed is insufficient) required for a good decision making solution.

1. The first MS Excel worksheet must be your completed coversheet, with your full name, student id, tutor’s name (spelled correctly), day and time of tutorial;
2. The second MS Excel worksheet must contain all the base data;
3. Create a PivotTable, using MS Excel 2007or 2010, in a new worksheet and call that worksheet ‘SA Sales A. Holiday’. Read “PivotTables” section of the MS Excel 2010 Plug_In_T3_2013.pdf file to better understand how to create a PivotTable. Then in your PivotTable ensure that it only displays the sales information for the following:

Ø in the region of SA

Ø Amelia Holiday

Ø for the magazine Better Homes and Garden

Ø for all months

1. Create a PivotChart bar chart to graphically display the results from the PivotTable you created in Question 3 above. The chart must provide information for users to easily read and understand what the chart is intended to communicate. The PivotChart must be on the same worksheet as the PivotTable and cannot cover-up any of the PivotTable information.

1. Next create another PivotTable in a new worksheet and call it “Sales Summary Business Weekly”. Read the instructions in “PivotTables” section of the MS Excel 2010 Plug_In_T3_2013.pdf file to understand how to create a PivotTable. Ensure this PivotTable only displays the sales information as follows:

Ø all Sales Reps

Ø for all months

Ø in all Regions

Ø for Business Review Weekly

1. Create a clustered column PivotChart to graphically display the results from the PivotTable you created in Question 5. The chart must provide sufficient information for the user to easily read and understand what the chart is telling them. The PivotChart should be on the same worksheet as the PivotTable.

1. Create a PivotTable about the magazines in a new worksheet and call it “Second Quarter Sales”. Ensure the PivotTable displays only the information as follows:

1. all Sales Reps

2. in all regions

3. for all magazines

4. for July and August

1. Create a PivotChart to graphically display the results from the PivotTable you created in Question 7. You select the most appropriate information providing chart type. The chart must provide sufficient information for the user to easily read and understand exactly what the chart is telling them, this includes charts size, headings, and legends etc... The PivotChart can be on the same or on a different worksheet than the PivotTable.

1. Create a PivotChart (call the tab “Compares May June July Aug” that compares May, June July and August sales for all magazines, all regions, and all sales representatives.

1. Reflect on the PivotTables and PivotCharts you have created and answer the following questions:

a) Does using a chart make any difference in all cases or is the use of figures better in some cases but not others? Consider the first chart when answering this question.

b) Does any of the PivotCharts show which magazine is the bestselling today (based on the given data) and if so why? If not why not and what would be needed to get this information?

c) Does any of the PivotCharts clearly display who is the best performing sales rep (based on the given data) and why that person or persons is considered the best performing sales representative? If this information is not shown, recommend how this would be done.

d) What are the strengths and weaknesses of displaying data a graphical format like the PivotCharts? Relate you answer to one or more of your PivotCharts.

e) Based on the PivotTables and PivotCharts done in questions 5 through 9 above, what two managerial decisions might be made recommended for the fourth quarter (Oct, Nov, and Dec) of the 2014 financial year?

Some terms herein:

Each tab is referred to as a: Sheet or Worksheet or a Spreadsheet

All the tabs together are referred to as a: Workbook

The entire file is a spreadsheet file.

Submission

For this exercise, you are to submit a single MS Excel 2007 or 2010 file (upload to Moodle). Include your assumptions and other text within the Excel file in a separate worksheet. The spreadsheet file name must be as follows: Student ID_first name_surname Lab 3 (for example u123456_Moby_Whale Lab 3. This is an individual assignment. Please ensure it is all your own work.

No assignment questions will be responded to after the close of business on Thursday.

Marking Criteria

The following criteria will be used as a guideline for marking lab reports:

Innovative, impressive, accurate solutions applied to all parts of the assignment – HD

Clear and accurate answers with appropriate data subject material – DI

Demonstrated clear understanding of the subject material, accurate complete solutions – CR

Questions answered mostly correct some user readability issues – P

Significant confusion, incorrect answers did not follow instructions – Fail

Alpha Geek

(8)

Status NEW Posted 29 May 2017 10:05 AM My Price 25.00

-----------

Attachments

file 1496052481-181275_1_solution.xlsx preview (387 words )

Sa-----------les----------- Re-----------pRe-----------gio-----------nMo-----------nth-----------Sal-----------eMa-----------gaz-----------ine-----------Ala-----------mei-----------da,----------- Da-----------vid-----------NTB-----------ett-----------er -----------Hom-----------es -----------and----------- Ga-----------rde-----------nNS-----------WBu-----------sin-----------ess----------- Re-----------vie-----------w W-----------eek-----------lyG-----------ard-----------eni-----------ng -----------Aus-----------tra-----------lia-----------WAN-----------ati-----------ona-----------l G-----------eog-----------rap-----------hic-----------Who----------- We-----------ekl-----------yHo-----------lid-----------ay,----------- Am-----------eli-----------aSA-----------Jon-----------es,----------- Br-----------adS-----------ain-----------t J-----------ame-----------s, -----------Phi-----------lli-----------pSh-----------riv-----------er,----------- Na-----------ncy-----------Smi-----------th,----------- Mi-----------cha-----------elS-----------tep-----------hen-----------son-----------, R-----------ach-----------elW-----------ort-----------hin-----------gto-----------n, -----------Pet-----------erF-----------ull----------- Na-----------meS-----------tud-----------ent----------- ID-----------Tut-----------or'-----------s N-----------ame-----------Day----------- an-----------d T-----------ime-----------Gra-----------nd -----------Tot-----------alS-----------um -----------of -----------Sal-----------eSA----------- To-----------tal-----------Mar-----------chM-----------ayj-----------une-----------Sal-----------es -----------Rep-----------res-----------ent-----------ati-----------ves-----------Jan-----------uar-----------yFe-----------bru-----------ary-----------Apr-----------ilJ-----------une-----------Jul-----------yAu-----------gus-----------tNS-----------W T-----------ota-----------lNT----------- To-----------tal-----------WA -----------Tot-----------ala-----------ugu-----------stB-----------ett-----------er

Not Rated(0)

• Buy Answer