Create necessary pivot table to find the requested results

Assignment Help Basic Computer Science
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.

Reference no: EM13854380

Questions Cloud

Headings for assignment-character evaluation : My Character (Name the character you are going to discuss and who they are - i.e.  character from a movie, person in the media, person who is historical)
Transition into announcing the topic : Once you have the body of the speech organized and planned, you must also add your introduction and conclusion.  With your introduction, you must first gain the attention of your audience, then transition into announcing the topic, which of course..
Write paragraph about that arabic luxury brand : The paragraph about that arabic luxury brand like Elie Saab and Reem Acra they are famous but they're marketing offline
Additional emphasis on internal controls : Sarbanes Oxley Act of 2002 (SOZ) placed additional emphasis on internal controls.  What section of SOX is specifically applicable to internal controls.  Why do you think it does?  What is the history of this?  What are the benefits or disadvantages o..
Create necessary pivot table to find the requested results : Create the necessary pivot table to find the requested results
Draw a free body diagram for the combined passenger : a. Draw a free body diagram for the combined passenger and chair. b. Develop an equation for the angle θif the motorized shaft rotates at a constant angular speed ω. Assume d and L are known constants.
Amusement park ride : An amusement park ride is shown in Fig. 1. It has a motorized vertical shaft with twoattached cables. The cables join at a seat which holds a seated passenger. The combinedseat and passenger have a weight of 1050 N. The seat swings in a horizo..
How independent models of inventory management are utilized : Be sure to do an analysis of how independent and dependent models of inventory management are utilized in the firm's decision making process and how it alters them to respond to sudden changes in the market demand for its product.
Different ways that media works : Each of the previous assignments in this class has asked you to critically engage with media - by examining your own personal relationship, outside representations, or capitalist structures - and think about the different ways that media works to ..

Reviews

Write a Review

 

Basic Computer Science Questions & Answers

  Steps to diagnose application changed from original state

Write down steps would you take in diagnosing the application which has been changed from its original state? Describe the answer.

  Write the definition of the function min and a program

write the definition of the function min and a program to test this function. Add the function max to the class arrayListType to return the largest element of the list. Also, write the definition of the function max and a program to test this func..

  What is your opinion of the criteria used

Based on your understanding of how AES was chosen, what is your opinion of the criteria used?

  Describe the inherent design issues across hci environments

Compare and contrast the design and development processes in HCI. Describe legal, societal, and ethical issues in HCI design. Describe the inherent design issues across HCI environments.

  Write a complete main method that would print your last name

Suppose your name was George Gershwin. Write a complete main method that would print your last name, followed by a comma, followed by a space and your first name. Question 2 Declare a variable named myMenu suitable for holding references to Menu o..

  What value is the log function sensitive

If x~y(both have values close to each other but not same), then we would expect some cancellation in computing log(x)-log(y). on the other hand, log(x)-log(y)=log(x/y), and the latter involves no cancellation. Does this mean that computing log..

  Why is ciso participation in business continuity planning

Title: Why is CISO participation in business continuity planning important? Business Continuity Planning (BCP) is a critical business process which requires the participation of managers from all parts of the enterprise.

  Swimlane-hypothesis space

Assignment need to be done. It is about swimlane. I am attaching document and example of how it suppose to be done.

  Write the windows cli commands that will clear the screen

Write the Windows CLI commands that will Clear the screen; Turn off Command echo; and display the current IP address, Subnet Mask, and Default Gateway

  How do i put three numbers in ascending order in ocaml

how do i put three numbers in ascending order in ocaml?

  What interface does an application need to use

What interface does an application need to use if it wants to get updates on the current position of the mouse cursor? How does the program get the x,y coordinates of the mouse cursor?

  Create an array of five shippedorder objects

Create 4 Order class fields: order number, customer name, quantity ordered, and total price. Create public accessors for each field except total price.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd