Posted: March 3rd, 2020

Mgmt. Sci Thru Spreadsheets essay

1
QSO 320 Final Project Guidelines and Rubric
Overview
Data analysis and communication of data to stakeholders are key factors in effective management. You cannot efficiently manage unless you know if objectives
are being met. You can use data and spreadsheets to assess areas of concern and evaluate if progress is being made for key objectives. The process of reviewing
the data is not a one-time event; it must be repeated frequently or be continuous. This is where spreadsheets that are linked to sources of data being collected
provide an advantage. As the input data values change, the spreadsheets do not need to be changed in order for them to calculate the results. You can use the
data to evaluate the efficiencies or inefficiencies of key objectives.
In the plan-do-check-act management cycle, management plans a set of objectives, develops processes, and allocates resources that the company will execute
(or do). Progress in meeting set objectives is monitored, and feedback is provided (the check). Based on feedback, adjustments to the plans are made (the act).
The final project for this course is the creation of an analysis report. The final product represents your ability to logically drill down large amounts of raw data to
produce useful reports that can be utilized to ensure optimal allocation of resources to maximize profitability. You will construct a report that includes your
analysis of sales, costs, and profit to develop an overall understanding of performance that can be used for stakeholder decision making. The project is divided
into two milestones, which will be submitted at various points throughout the course to scaffold learning and ensure quality final submissions. These milestones
will be submitted in Modules Three and Five. The final product will be submitted in Module Seven.
In this assignment, you will demonstrate your mastery of the following course outcomes:
 QSO-320-01: Utilize basic functionality of spreadsheet software for constructing effective databases that evaluate organizational decisions
 QSO-320-02: Conduct descriptive and inferential statistical analyses of raw data using spreadsheet software for informing processes and operations
 QSO-320-03: Conduct optimization analyses of raw data using spreadsheet software for reaching solutions that meet organizational objectives
 QSO-320-04: Present business data analysis using spreadsheet software that clearly communicates specific information to key stakeholders
Prompt
In the case study, you are constructing a number of spreadsheets to evaluate specific aspects of a winery and its distribution operation. Certain factors, such as
increasing wine production significantly or lowering production costs, require significant time to enact. However, making changes regarding distribution can be
made fairly quickly using spreadsheets. In the case study, new management wants to understand how efficient their distribution system is functioning. You will
analyze the data and present the data in a way that can be clearly communicated to the key stakeholders. Use the following documents for your analysis report:
 Case Study
 Case Study Data Set
2
Specifically, you must address the critical elements listed below. Most of the critical elements align with a particular course outcome (shown in brackets).
I. Organizational Sales: For the first part of your final project, you will 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 include a rationale.
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. [QSO-320-01]
B. Generate a labeled bar chart that illustrates the sum of wine varieties sold to each distribution center. [QSO-320-02]
C. Using a pivot table, calculate the total amount of revenue generated for each distribution center. Illustrate your results on a bar chart. [QSO320-01]
D. Using the IF function, calculate the central tendencies (the mean, median, and mode) of shipment volume for each distribution center. Illustrate
your results in a table. [QSO-320-02]
E. Analyze frequency of shipments by size using a histogram. [QSO-320-02]
F. Create a shipment histogram to show the distribution of shipments for Portland and Riverside. [QSO-320-01]
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. [QSO-320-04]
II. Organizational Cost and Profit: For the next part of your final project, use your findings and raw data from the previous section to dive deeper into types
of wine and distribution centers. You will need to analyze these factors to determine average costs and profits. All of your analyses need to be submitted
in an annotated excel file and include a rationale.
A. Calculate costs of shipping to Portland and Riverside by pallets and frequency. Illustrate your results in a table. [QSO-320-02]
B. Calculate the cost of production for the wine varieties sold in Portland and Riverside. Illustrate your results in a table. [QSO-320-02]
C. Generate a labeled table that illustrates gross profit for each variety of wine for each distribution center. Explain why this information is
important for informing operation efficiencies. [QSO-320-04]
D. Generate a labeled table that shows the profit after state taxes. [QSO-320-04]
E. Provide a summary statement that describes the inefficiencies in the organizational cost and profit analysis, and explain why this information is
important for influencing management decisions. [QSO-320-04]
III. Optimizing Performance: Finally, you will determine an optimal solution that will maximize the organization’s objectives. You will need to consider the
level of sensitivity and uncertainty of alternative solutions in supporting your optimal solution. The analyses need to be submitted in an annotated excel
file and include a rationale.
A. Determine the values of the constraints to be used to generate the target number when running Solver. [QSO-320-03]
B. Using Solver, calculate the level of sensitivity of decision variables and describe the significance of the report. [QSO-320-03]
C. Using Solver, calculate the limits of decision variables and describe the significance of the report. [QSO-320-03]
3
D. Online assignment help tutors – Discuss solutions that will maximize profits for the organization based on the Solver analysis. [QSO-320-04]
Milestones
Milestone One: Organization Sales
In Module Three, you will submit a draft of the Organizational Sales section of the final project. This milestone will be graded with the Milestone One Rubric.
Milestone Two: Organizational Cost and Profit
In Module Five, you will submit a draft of the Organizational Cost and Profit section of the final project. This milestone will be graded with the Milestone Two
Rubric.
Final Submission: Analysis Report
In Module Seven, you will submit your final project. It should be a complete, polished artifact containing all of the critical elements of the final product. It should
reflect the incorporation of feedback gained throughout the course. This submission will be graded with the Final Project Rubric.
Final Project Rubric
Guidelines for Submission: Your analysis report should be submitted as an annotated Excel file. All spreadsheet, table, and chart functions must be created
within the analysis report. Cutting and pasting spreadsheets, tables, or charts in from another source will result in your work being evaluated as “Not Evident,”
as it is important to demonstrate that you utilized the spreadsheet functionality. Spreadsheets and tables must link to the information provided in the Data tab.
The rationale used for each task must be provided in the same tab where the task is addressed.
Critical Elements Exemplary Proficient Needs Improvement Not Evident Value
Organizational Sales:
Wine Varieties
[QSO-320-01]
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%)
8
4
Critical Elements Exemplary Proficient Needs Improvement Not Evident Value
Organizational Sales:
Distribution Center
[QSO-320-02]
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%)
6
Organizational Sales:
Revenue
[QSO-320-01]
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%)
8
Organizational Sales:
Central Tendencies
[QSO-320-02]
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%)
6
Organizational Sales:
Shipments by Size
[QSO-320-02]
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%)
6
5
Critical Elements Exemplary Proficient Needs Improvement Not Evident Value
Organizational Sales:
Distribution of Shipments
[QSO-320-01]
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%)
8
Organizational Sales
Sales Analysis
[QSO-320-04]
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%)
4
Organizational Cost and
Profit:
Cost of Shipping
[QSO-320-02]
Meets “Proficient” criteria
and demonstrates an
insightful rationale for
appropriately employed
Excel functions (100%)
Calculates costs of
shipping to Portland and
Riverside by pallets and
frequency and illustrates
results in a table (85%)
Calculates costs of shipping
to Portland and Riverside
by pallets and frequency,
but does not illustrate
results in a table, or results
contain inaccuracies or
rationale is not provided
(55%)
Does not calculate costs of
shipping (0%)
4
Organizational Cost and
Profit:
Cost of Production
[QSO-320-02]
Meets “Proficient” criteria
and demonstrates an
insightful rationale for
appropriately employed
Excel functions (100%)
Calculates the cost of
production for the wine
varieties sold in Portland
and Riverside and
illustrates results in a
table (85%)
Calculates the cost of
production for the wine
varieties sold in Portland
and Riverside, but does not
illustrate results in a table,
or results contain
inaccuracies or a rationale
is not provided (55%)
Does not calculate the cost
of production (0%)
4
6
Critical Elements Exemplary Proficient Needs Improvement Not Evident Value
Organizational Cost and
Profit:
Gross Profit
[QSO-320-04]
Meets “Proficient” criteria
and explanation is detailed
and well-conceived (100%)
Generates a labeled table
that illustrates gross profit
for each variety of wine
for each distribution
center, explaining why
this information is
important for informing
operation efficiencies
(85%)
Generates a labeled table
that illustrates gross profit
for each variety of wine for
each distribution center,
but does not provide an
explanation, or explanation
is cursory, contains
inaccuracies, or lacks
justification (55%)
Does not illustrate gross
profit through the use of a
labeled table (0%)
4
Organizational Cost and
Profit:
State Taxes
[QSO-320-04]
Generates a labeled table
that shows the profit after
state taxes (100%)
Generates a labeled table,
but table contains
inaccuracies (55%)
Does not generate a
labeled table (0%)
4
Organizational Cost and
Profit:
Cost and Profit Analysis
[QSO-320-04]
Meets “Proficient” criteria
and demonstrates a
complex grasp of how cost
and profit analyses
influence managerial
decisions (100%)
Provides a summary
statement that describes
the inefficiencies in the
organizational cost and
profit analysis and
explains why this
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 cost
and profit analysis (0%)
4
Optimizing Performance:
Constraints
[QSO-320-03]
Meets “Proficient” criteria
and demonstrates an
insightful rationale for
appropriately employed
Excel functions (100%)
Determines the values of
the constraints to be used
to generate the target
number when running
Solver (85%)
Determines the values of
the constraints to be used
to generate the target
number when running
Solver, but response
contains inaccuracies or a
rationale is not provided
(55%)
Does not determine the
values of the constraints
(0%)
8
7
Critical Elements Exemplary Proficient Needs Improvement Not Evident Value
Optimizing Performance:
Sensitivity
[QSO-320-03]
Meets “Proficient” criteria
and demonstrates an
insightful rationale for
appropriately employed
Excel functions (100%)
Uses Solver to calculate
the level of sensitivity of
decision variables and
describes the significance
of the report (85%)
Uses Solver to calculate the
level of sensitivity of
decision variables and
describes the significance
of the report, but the
response contains
inaccuracies, or the
description is cursory or
missing key components,
or a rationale is not
provided (55%)
Does not use Solver to
calculate the level of
sensitivity of decision
variables and does not
describe the significance of
the report (0%)
8
Optimizing Performance:
Limits
[QSO-320-03]
Meets “Proficient” criteria
and demonstrates an
insightful rationale for
appropriately employed
Excel functions (100%)
Uses Solver to calculate
the limits of decision
variables and describe the
significance of the report
(85%)
Uses Solver to calculate the
limits of decision variables
and describe the
significance of the report,
but the response contains
inaccuracies, or the
description is cursory or
missing key components,
or a rationale is not
provided (55%)
Does not use Solver to
calculate the limits of
decision variables and does
not describe the
significance of the report
(0%)
8
Optimizing Performance:
Solutions
[QSO-320-04]
Meets “Proficient” criteria
and discussion
demonstrates a
sophisticated awareness of
the needed solutions based
on the needs of the
organization (100%)
Online assignment help tutors – Discusses solutions that
will maximize profits for
the organization based on
the Solver analysis (85%)
Online assignment help tutors – Discusses solutions that
will maximize profits for
the organization based on
the Solver analysis, but
response lacks clarity or
solutions are illogical (55%)
Does not discuss solutions
for the organization (0%)
4
Articulation of Response Submission is free of errors
related to citations,
grammar, spelling, syntax,
and organization and is
presented in a professional
and easy-to-read 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%

Order | Check Discount