Overview
Finding out about organizational inefficiencies is the first step to improving performance. To determine what inefficiencies exist, you need to perform a data analysis. A good place to start is with sales. You have to know what data to study and how to use specific tools for data analysis. It’s important to use the IF function, pivot tables, pie charts, bar charts, and histograms. These can help you isolate specific data and organize it in a way that makes it easier to read. Studying measures of central tendency also helps you find out important information. First, you find where inefficiencies in sales exist. Then, you need to be able to talk about the effect this has on the organization.
Directions
For this assignment, you’ll use the Vinho Winery Scenario, linked in Supporting Materials, and other course resources to look at raw data sets. These data sets will explain the production, sales, and distribution of wine. You’ll need to study the different types of wine and distribution centers to figure out their financial effects on the organization’s total profits. All your analyses need to be submitted in an annotated Excel file, and each analysis needs to include your reasons for using the appropriate Excel functions.
Specifically, you must address the following rubric criteria:
1. Using a pivot table, figure out the percentage of wine varieties sold from each distribution center. Show your results in the form of a pie chart.
A. Hint: Create a pivot table using the data spreadsheet as a starting point.
2. Create a labeled bar chart that shows the amount of wine varieties sold to each distribution center.
3. Use the pivot table already created to calculate the total amount of revenue made for each distribution center. Show your results in a bar chart.
A. Hint: Production cost data is provided in the Costs & Distances tab. Make sure you don’t mix your units of measurement (i.e., pallets, cases, or bottles).
4. Use the IF function to calculate the central tendencies (mean, median, and mode) of shipment volume for each distribution center. Show your results in a table. (Do NOT use a pivot table or manually identify each cell to be evaluated.)
5. Analyze the frequency of shipment by size using a histogram. Use the following bin sizes (number of pallets): 72, 48, 24, 18, 12, 6, 3, 1.
6. Create a shipment histogram to show the distribution of shipments for Portland and Riverside. Use the same bin sizes as you did in step five.
A. Hint: Use the alphabetical sort for the destination column. Then select Data Analysis to plot the frequency of pallet shipments using the bin sizes listed for the two destinations separately.
7. Write a summary statement that describes the inefficiencies shown in the organizational sales analysis. In your response, explain why this information is important for influencing management decisions.
What to Submit
Your assignment must be submitted using the Case Study Data Set Microsoft Excel document. Use 11-point Calibri font.
For this short paper activity, you will learn about the three delays model, which explains…
Topic : Hospital adult medical surgical collaboration area a. Current Menu Analysis (5 points/5%) Analyze…
As a sales manager, you will use statistical methods to support actionable business decisions for Pastas R Us,…
Read the business intelligence articles: Getting to Know the World of Business Intelligence Business intelligence…
The behaviors of a population can put it at risk for specific health conditions. Studies…