Milestone One
A: Drawing upon the data in the data tab, create a pivot table and a pie chart that shows the total percentage of wine bought by each distributor.
The pivot table and pie chart will populate on a new tab; name this tab M1A.
State the rationale you used to create the table and pie chart.
Hint:
Create a pivot table using the data spreadsheet as its basis.
Make sure you select the select the Pivot table field list options.
B: Using a pivot table, show wine distribution by variety and distributor and provide a bar chart. The pivot table will populate in a new tab; name this tab M1B.
State the rationale you used.
C: Create a new tab named M1C. Create a table and bar chart to show revenue by distributor and wine variety.
State the rationale you used.
Hints:
We are looking at revenue generated and not profit in this problem.
Production cost data is provided in the Costs and Distances tab.
Make sure you don't mix your units of measurement (i.e., pallets, or cases, or bottles).
D: Show central tendency of the shipments to each distributor. (Use the IF Function to select the data to be used.) Show in a table.
State the rationale you used.
Hint: Do not use a pivot table or manually identify each cell to be evaluated.
| |
Mean Pallet Shipment |
Median Pallet Shipment |
Mode Pallet Shipment |
| Oakland CA |
|
|
|
| Portland OR |
|
|
|
| Riverside CA |
|
|
|
| Seattle WA |
|
|
|
E: Analyze the frequency of size of shipment sizes using a histogram with the following bin sizes (number of pallets). The histogram will generate on a new tab; name this tab M1E.
| Pallets |
| 72 |
| 48 |
| 24 |
| 18 |
| 12 |
| 6 |
3
|
1
|
State the rationale you used.
F: Create shipment histograms for Portland and Riverside using the same bin sizes (as used in Item E). The histogram will generate on a new tab; name this tab M1E.
State the rationale you used.
Hint:
Use the alphabetical sort for the Destination column. Select Data Analysis to plot the frequency of pallets shipments using the bin sizes listed for the two destinations separately.
G. Provide a summary statement below that describes the inefficiencies in the organizational sales analysis. In your response, explain why this information is important for influencing management decisions.
Attachment:- Lyons Milestone.rar