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,
4.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.
5.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.
6.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.
7.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
8.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.
9.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.
10.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?

Read less