Reference no: EM13854380

This assignment is to be completed individually. See Excel podcasts and notes before you begin the assignment, and review the notes sheet about data visualization & professional charts & graphs.

Scenario: Last week you presented some basic charts to your manager, but this week she wants some sales performance information to take to a management meeting. She asks you to work on the sales data and provide examples of how the output data might be "dressed up." Choose what you think is the best chart type to present the data for her areas of interest.

Note, when creating charts, work only with the data that is of interest for your task; that is, don't include a totals column if you're interested in clearly seeing the individual values. Be sure to use appropriate labels and formatting so your supervisor & others can understand what they are seeing when they look at your output. Copy each chart solution to the document below for each of the tasks.

Instructions: Since you are presenting the analyses to your manager and she will be using them in a meeting, you need to be concise and specific, and present your output in an extra professional format with clear titles & labels.

a. Create the necessary pivot table to find the requested results, format the output, then choose a chart type that clearly presents the output to an outside viewer.

b. Properly label your output & resulting chart. Use a logical chart type for the data you are showing and add a relevant title; numbers should be formatted currency with 2-digits, etc.

c. Save your work and name your files PS3_YourLastName (.doc and .xlsx), and upload both this completed Word doc & your Excel file to the Blackboard Assignments area for Problem Solving #3.

Hint: For these problems use a professional choice of graph or chart, with a chart title, axis labels, and readable formatted scales for the axes that will allow an outside viewer to understand what the data output is showing. Once you have your chart in place, select and view the options via the + and paint brush objects in Excel 2013, or try some of the other chart options from the menu in 2010.

[1] To give your manager a better understanding of sales force performance create a pivot chart that shows total sales revenue by sales rep (salesID), sorted high to low. Which salesID has the highest performance?

Format the sales numbers as currency, choose the best chart type and adjust your chart labels & title to look professional. You may need to try different chart types to see which does the best job of showing the data, and be sure to format and label your chart.

Insert formatted PivotChart (1) here:

[2] Create a customer sales report for the top 10 customers by total sales across all territories, and chart these results with customer names and total sales $. This chart is most effective when sorted from larges to smallest. (Hint: use the "Top 10" feature under value filters, and sort before charting.)
Insert formatted PivotChart (2) here:

[3] Create a PivotTable to filter your data by high performance bikes (UnitPrice >$2,000) for the top customer from task 2; then create a chart to show product ID, order quantity and total sales (sum of LineTotal) in the output. This will require a bit of finesse to show both quantity and $ on the same chart. Consider a combo chart, with appropriate format and labeling.

Insert formatted PivotChart (3) here:

[4] Let's look at our top selling high end bike in more detail; create a PivotTable to find how many units of ProductID 778 have been ordered for each of the territories. Sort to find the top selling territory, and chart the total order quantity for each customer in that territory. Hint: This requires two PivotTables. First, find the top selling territory for this bike, and second, see which customers of this territory ordered the bike and how many. Chart the total order quantity for each customer in that territory (include customer name & quantity ordered).
Insert formatted PivotChart (4) here:

[5] In our earlier work, we found that territory 6 has the highest sales overall across all territories. Let's see how they are doing by sales person in our high end product range (>$2000). Show the comparison in sales for SalesPerson and Product IDs, as well as sales $ in your output & chart the results.

Insert formatted PivotChart (5) here:

End of problem set #3. Upload both this completed Word doc & your Excel file to the Blackboard Assignments area.