Posted: September 1st, 2023

Mgmt Sci Thru Spreasheets essay

QSO 320 Milestone One Guidelines and Rubric
Overview: Uncovering organizational inefficiencies is the first step to optimizing performance. In order 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 analyze as well as how to use specific tools for data analysis. Using the
IF function, pivot tables, pie charts, bar charts, and histograms can help you isolate and organize specific data in a way that makes it easier to read. Studying
measures of central tendency can also help reveal important information. After you discover where inefficiencies in sales exist, you need to be able to articulate
the impact this has on the organization.
Prompt: For this assignment, you will use the Vinho Winery Case Study and other course resources to review raw data sets that summarize the production, sales,
and distribution of wine. You will need to analyze the various types of wine and different distribution centers to determine their financial impacts on the
organization’s total revenue. All of your analyses need to be submitted in an annotated Excel file, and each analysis needs to include a rationale.
Specifically, the following critical elements must be addressed:
A. Using a pivot table, determine the percentage of wine varieties sold from each distribution center. Illustrate your results in the form of a pie chart. Hint:
Create a pivot table using the data spreadsheet as its basis.
B. Generate a labeled bar chart that illustrates the sum of wine varieties sold to each distribution center.
C. Using a pivot table, calculate the total amount of revenue generated for each distribution center. Illustrate your results on a bar chart. Hints: Production
cost data is provided in the Costs and Distances tab. Make sure you don’t mix your units of measurement (i.e., pallets, cases, or bottles).
D. Using the IF function, calculate the central tendencies (mean, median, and mode) of shipment volume for each distribution center. Illustrate your results
in a table. (Do NOT use a pivot table or manually identify each cell to be evaluated.)
E. 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.
F. Create a shipment histogram to show the distribution of shipments for Portland and Riverside. Use the same bin sizes as you did in Part E. Hint: Use the
alphabetical sort for the destination column, and select Data Analysis to plot the frequency of pallet shipments using the bin sizes listed for the two
destinations separately.
G. Provide a summary statement that describes the inefficiencies in the organizational sales analysis. In your response, explain why this information is
important for influencing management decisions.
Rubric
Guidelines for Submission: Your assignment must be submitted using the Case Study Data Set Microsoft Excel document. Use 11-point Calibri font.
Critical Elements Exemplary Proficient Needs Improvement Not Evident Value
Wine Varieties Meets “Proficient”
criteria and
demonstrates an
insightful rationale for
appropriately employed
Excel functions (100%)
Uses a pivot table to
determine the percentage
of wine varieties sold from
each distribution center
and illustrates results in
the pie chart (85%)
Uses a pivot table to
determine the percentage
of wine varieties sold from
each distribution center,
but does not illustrate
results in the pie chart, or
response contains
inaccuracies or is missing
key information (55%)
Does not determine the
percentage of wine
varieties sold from each
distribution center (0%)
16
Distribution Center Meets “Proficient”
criteria and
demonstrates an
insightful rationale for
appropriately employed
Excel functions (100%)
Generates a labeled bar
chart that illustrates the
sum of wine varieties sold
to each distribution center
(85%)
Generates a labeled bar
chart that illustrates the
sum of wine varieties sold
to each distribution
center, but bar chart
contains inaccuracies or is
missing key information,
or a rationale is not
provided (55%)
Does not generate a
labeled bar chart that
illustrate the sum of wine
varieties sold to each
distribution center (0%)
12
Revenue Meets “Proficient”
criteria and
demonstrates an
insightful rationale for
appropriately employed
Excel functions (100%)
Uses a pivot table to
calculate the total amount
of revenue for each
distribution center and
illustrates results in a bar
chart (85%)
Uses a pivot table to
calculate the total amount
of revenue for each
distribution center, but
does not illustrate results
in a bar chart, or response
contains inaccuracies or is
missing key information,
or a rationale is not
provided (55%)
Does not calculate the
total amount of revenue
for each distribution
center (0%)
16
Critical Elements Exemplary Proficient Needs Improvement Not Evident Value
Central Tendencies Meets “Proficient”
criteria and
demonstrates an
insightful rationale for
appropriately employed
Excel functions (100%)
Uses the IF function to
calculate the central
tendencies of shipment
volume for each
distribution center and
illustrates the results in a
table (85%)
Uses the IF function to
calculate the central
tendencies of shipment
volume for each
distribution center, but
does not illustrate the
results in a table, or
response contains
inaccuracies or is missing
key information, or a
rationale is not provided
(55%)
Does not calculate the
central tendencies (0%)
12
Size of Shipment Analyzes the frequency of
the shipment by size using
a histogram (100%)
Analyzes the frequency of
the shipment by size using
a histogram, but response
contains inaccuracies or a
rationale is not provided
(55%)
Does not analyze the
frequency of the shipment
by size (0%)
12
Distribution of Shipment Creates a shipment
histogram to show the
distribution of shipments
for Portland and Riverside
(100%)
Creates a shipment
histogram to show the
distribution of shipments
to Portland and Riverside,
but response contains
inaccuracies or a rationale
is not provided (55%)
Does not create a
shipment histogram to
show the distribution of
shipments for Portland
and Riverside (0%)
16
Sales Analysis Meets “Proficient”
criteria and response
demonstrates a
sophisticated awareness
of how the inefficiencies
impact managerial
decision making (100%)
Provides a summary
statement that describes
the inefficiencies in the
organizational sales
analysis and explains why
information is important
for influencing decisions
(85%)
Provides a summary
statement, but the
statement description is
cursory, contains
inaccuracies, or lacks
justification (55%)
Does not provide a
summary statement that
describes the
inefficiencies in the
organizational sales
analysis (0%)
10
Critical Elements Exemplary Proficient Needs Improvement Not Evident Value
Articulation of Response Submission is free of
errors related to
citations, grammar,
spelling, syntax, and
organization and is
presented in a
professional and easy-toread format (100%)
Submission has no major
errors related to citations,
grammar, spelling, syntax,
or organization (85%)
Submission has major
errors related to citations,
grammar, spelling, syntax,
or organization that
negatively impact
readability and
articulation of main ideas
(55%)
Submission has critical
errors related to citations,
grammar, spelling, syntax,
or organization that
prevent understanding of
ideas (0%)
6
Total 100%