QSO Milestone 2
I need help with this assignment.
QSO 3
20
Milestone Two Guidelines and Rubric
Overview: Once you’ve uncovered inefficiencies in sales, you can take a look at average costs and profits to determine where inefficiencies lie in these areas.
Again, it is important to know what data to analyze and what tools can be used to do so. It is also important to know how to describe the organizational impacts
of the inefficiencies.
Prompt: In this assignment, you will use your findings and raw data from Milestone One 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 each analysis needs to
include a rationale.
Specifically, the following critical elements must be addressed:
A. Calculate costs of shipping to Portland and Riverside by pallets and frequency. Illustrate your results in a table. Use the bin sizes from Milestone One,
Part E.
B. Calculate the cost of production for the wine varieties sold in Portland and Riverside. Illustrate your results in a table.
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.
D. Generate a labeled table that shows the profit after state taxes. For Portland, use a tax rate of 6.6% and for Riverside, use 8.8%.
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.
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
Costs of Shipping 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%)
20
https://learn.snhu.edu/d2l/lor/viewer/view.d2l?ou=6606&loIdentId=21606
Critical Elements Exemplary Proficient Needs Improvement Not Evident Value
Cost of Production 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%)
20
Gross Profit 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%)
20
State Taxes 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%)
20
Cost and Profit
Analysis
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%)
1
5
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%)
5
Total 100%
>Data
Cases
. 0
2648.00 48 6
.00
2 8.00 0
1 48 2438.00 2438.00 500 1 CA
48 5376 2673.00 1 48 5376 2638.00 0
1 1 6
18.00
8.00
100 1 1518.00 1518.00 100 1 1512.00 2
1 1288.00 1 4.00
1 Oakland CA 24 2688 1288.00 1288.00 112 1 4.00
4.00
100 1 Riverside CA 24 2688 1288.00 1288.00 112 1 1329.00 30 1 Riverside CA 24 2688 1288.00 1288.00 112 1 1294.00 100 1 4
2.00
80 1 112 1 Riverside CA 22 2464 1176.00 1176.00 112 1 2.00
1 1 4
2.00
422.00 500 1 .00
112 1 2
560.00 112 1 Portland OR 11 1232 560.00 560.00 112 1 1.00
491.00 0
1 6
112.00 112 1 Oakland CA 3 112.00 112.00 112 1 118.00 100 1 93.00 1 168.00 0 1 Riverside CA 3 336 112.00 112.00 112 1 163.00 10 1 56.00 224 1 Seattle WA 3 336 56.00 56.00 224 1 -69.00 1 53.00 6 1 51.00 10 1 2588.00 2 2632.00 112 2 Oakland CA 48 5376 2638.00 2638.00 100 2 2338.00 0
2 2576.00 224 2 1304.00 80 2 Oakland CA 24 2688 1304.00 1304.00 80 2 Riverside CA 24 2688 1329.00 1329.00 30 2 1232.00 224 2 Seattle WA 24 2688 1288.00 1288.00 112 2 .00
10 2 1182.00 100 2 622.00 100 2 616.00 112 2 601.00 30 2 43.00 250 2 8.00
40 2 Portland OR 3 336 43.00 43.00 250 2 153.00 30 2 26.00 60 2 Riverside CA 48 5376 2632.00 2632.00 112 3 9.00
250 3 1314.00 60 3 .00
547.00 250 3 642.00 60 3 Portland OR 12 1344 547.00 547.00 250 3 .00
30 3 3732.00 4 1538.00 60 4 Riverside CA 24 2688 1288.00 1288.00 112 4 1171.00 4 1090.00 60 4 572.00 200 4 1229.00 5 31.00 50 5 2538.00 6 143.00 50 6 2658.00 60 7 Oakland CA 28 3136 1512.00 1512.00 112 7 6.00
224 7 Oakland CA 24 2688 1294.00 1294.00 100 7 Portland OR 24 2688 1288.00 1288.00 112 8 Riverside CA 24 2688 1288.00 1288.00 112 8 1125.00 9 1197.00 70 9 Seattle WA 22 2464 1176.00 1176.00 112 9 Oakland CA 1 112 16.00 16.00 80 9 Portland OR 1 112 0.00 0.00 112 9 36.00 40 9 2488.00 9 1319.00 50 9 1194.00 300 10 138.00 60 11 1563.00 10 12 1344.00 0 12 2563.00 250 13 1548.00 40 15 1144.00 400 15 1107.00 250 18 32.00
19 1212.00 40 20 1553.00 30 24 8.00
1528.00 80 26 2683.00 10 29 1227.00 10 34 657.00 30 39 13.00 39 3832.00 400 44 2668.00 40 45 Seattle WA 28 3136 1518.00 1518.00 100 45 Portland OR 24 2688 1288.00 1288.00 112 45 Oakland CA 3 336 148.00 148.00 40 45 Riverside CA 1 112 0.00 0.00 112 45 2663.00 50 47 2688.00 0 47 Riverside CA 24 2688 1288.00 1288.00 112 50 Seattle WA 24 2688 1288.00 1288.00 112 50 Oakland CA 22 2464 1192.00 1192.00 80 50 128.00 80 51 3582.00 52 in a FTL
$240.00 $2.15 $2.15 30% Cost per bottle 0
to create the table and pie chart
:
State the rationale you used cost data is provided in the Data tab
State the rationale you used 72 State the rationale you used State the rationale you used State the rationale you used 1 3 6 12 18 24 48 72 Size of shipment in pallets 1 3 6 12 18 24 48 72 cost per pallet State the rationale you used and Gross Profit as a percentage of revenue
(from Milestone One) minus (from part A) & Production (from part B) from Portland and Riverside
State the rationale you used Transport Production Portland Revenue Transport Gross profit %GP/R DC Wine Type Revenue Transport COGS Gross profit %GP/R White State the rationale you used Gross Profit Portland
2
Destination
Pallets
Total
Cases red
Cases white
Cases organic
Week of Order
Pallets (Bins)
Oakland
CA
4
8
5
3
7
6
26
48
0
80
1
72
Portland
OR
5
37
24
38
43
50
48
Portland OR
5376
24
Riverside
2673.00
30
18
Seattle WA
2638.00
10
12
Seattle WA 48 5376 2638.00 2638.00
100
6
Oakland CA
28
3
13
15
1
51
3
Oakland CA 28
31
36
1
Riverside CA 28
3136
1512.00
11
Oakland CA 24
2688
1288.00
112
Oakland CA 24 2688
1
32
1324.00
40
Oakland CA 24 2688 1324.00 1324.00 40 1 Oakland CA 24 2688
12
9
1
29
Riverside CA 24 2688 1288.00 1288.00 112 1
Riverside CA 24 2688
1329.00
Riverside CA 24 2688 1288.00 1288.00 112 1
Riverside CA 24 2688 1329.00 1329.00 30 1 Seattle WA 24 2688
1294.00
Oakland CA
22
2
46
1
19
1192.00
Riverside CA 22
2464
1
17
6.00
1176.00
Riverside CA 22 2464
1
20
1202.00
60
Seattle WA 22 2464
112
0.00
1120.00
224
Seattle WA 22 2464 1176.00 1176.00 112 1
Portland OR 12
1
34
42
Portland OR 12
13
44
6
16
6
16.00
Portland OR 11
1
23
560.00
Portland OR 11 1232
49
25
Oakland CA 3
33
112.00
336
Oakland CA 3 336
118.00
Portland OR 3 336
93.00
150
Portland OR 3 336
168.00
Portland OR 3 336 112.00 112.00 112 1
Portland OR 3 336 118.00 118.00 100 1
Riverside CA 3 336 112.00 112.00 112 1
Seattle WA 3 336
163.00
Seattle WA 3 336
56.00
Seattle WA 3 336 112.00 112.00 112 1
Oakland CA 1 112 0.00 0.00 112 1
Portland OR 1 112
-69.00
250
Seattle WA 1 112
53.00
Seattle WA 1 112 6.00 6.00 100 1
Seattle WA 1 112
51.00
Seattle WA 1 112 0.00 0.00 112 1
Oakland CA 48 5376
2588.00
200
Oakland CA 48 5376 2638.00 2638.00 100 2
Oakland CA 48 5376
2632.00
Oakland CA 48 5376 2632.00 2632.00 112 2 Portland OR 48 5376
2338.00
70
Seattle WA 48 5376 2632.00 2632.00 112 2
Seattle WA 48 5376
2576.00
Oakland CA 28 3136 1512.00 1512.00 112 2
Riverside CA 28 3136 1512.00 1512.00 112 2
Riverside CA 28 3136 1518.00 1518.00 100 2
Oakland CA 24 2688
1304.00
Oakland CA 24 2688 1294.00 1294.00 100 2
Oakland CA 24 2688 1288.00 1288.00 112 2
Oakland CA 24 2688 1324.00 1324.00 40 2
Oakland CA 24 2688 1288.00 1288.00 112 2 Portland OR 24 2688 1294.00 1294.00 100 2
Riverside CA 24 2688 1329.00 1329.00 30 2
Riverside CA 24 2688 1288.00 1288.00 112 2
Riverside CA 24 2688 1329.00 1329.00 30 2
Riverside CA 24 2688 1288.00 1288.00 112 2
Riverside CA 24 2688 1288.00 1288.00 112 2
Seattle WA 24 2688 1288.00 1288.00 112 2
Seattle WA 24 2688
1232.00
Seattle WA 24 2688 1294.00 1294.00 100 2
Seattle WA 24 2688 1232.00 1232.00 224 2
Seattle WA 24 2688 1232.00 1232.00 224 2
Seattle WA 24 2688 1294.00 1294.00 100 2 Seattle WA 24 2688
13
39
1339.00
Seattle WA 24 2688 1304.00 1304.00 80 2
Portland OR 22 2464
1182.00
Riverside CA 22 2464 1192.00 1192.00 80 2
Riverside CA 22 2464 1176.00 1176.00 112 2
Riverside CA 22 2464 1202.00 1202.00 60 2
Portland OR 12 1344
622.00
Portland OR 12 1344
616.00
Portland OR 11 1232 491.00 491.00 250 2
Portland OR 11 1232
601.00
Portland OR 3 336 163.00 163.00 10 2
Portland OR 3 336
43.00
Portland OR 3 336
14
148.00
Riverside CA 3 336 112.00 112.00 112 2
Riverside CA 3 336
153.00
Oakland CA 1 112
26.00
Oakland CA 1 112 16.00 16.00 80 2
Portland OR 1 112 26.00 26.00 60 2
Portland OR 1 112 51.00 51.00 10 2
Seattle WA 1 112 26.00 26.00 60 2
Portland OR 48 5376 2638.00 2638.00 100 3
Riverside CA 48 5376 2632.00 2632.00 112 3
Seattle WA 28 3136 1518.00 1518.00 100 3
Oakland CA 24 2688
1
21
1219.00
Riverside CA 24 2688 1219.00 1219.00 250 3
Riverside CA 24 2688
1314.00
Riverside CA 24 2688 1288.00 1288.00 112 3
Riverside CA 22 2464 1182.00 1182.00 100 3
Portland OR 12 1344 616.00 616.00 112 3
Portland OR 12 1344
5
47
Portland OR 12 1344
642.00
Riverside CA 3 336 153.00 153.00 30 3
Riverside CA 1 112
41
41.00
Riverside CA 1 112 16.00 16.00 80 3
Portland OR 72
8064
3732.00
600
Riverside CA 48 5376 2673.00 2673.00 30 4
Riverside CA 48 5376 2632.00 2632.00 112 4
Riverside CA 28 3136
1538.00
Portland OR 24 2688 1288.00 1288.00 112 4
Riverside CA 24 2688 1288.00 1288.00 112 4
Riverside CA 24 2688 1314.00 1314.00 60 4
Oakland CA 22 2464
1171.00
122
Oakland CA 20
2240
1090.00
Portland OR 12 1344
572.00
Portland OR 12 1344 547.00 547.00 250 4
Seattle WA 3 336 118.00 118.00 100 4
Portland OR 1 112 6.00 6.00 100 4
Riverside CA 1 112 51.00 51.00 10 4
Riverside CA 1 112 0.00 0.00 112 4
Portland OR 48 5376 2638.00 2638.00 100 5
Oakland CA 24 2688 1339.00 1339.00 10 5
Riverside CA 24 2688
1229.00
230
Seattle WA 1 112
31.00
Oakland CA 48 5376
2538.00
300
Seattle WA 24 2688 1219.00 1219.00 250 6
Riverside CA 22 2464 1192.00 1192.00 80 6
Portland OR 3 336
143.00
Seattle WA 28 3136 1538.00 1538.00 60 7
Oakland CA 24 2688 1294.00 1294.00 100 7
Riverside CA 24 2688 1294.00 1294.00 100 7
Portland OR 12 1344 547.00 547.00 250 7
Riverside CA 48 5376
2658.00
Seattle WA 48 5376 2576.00 2576.00 224 7
Seattle WA 48 5376 2632.00 2632.00 112 7
Oakland CA 28 3136 1512.00 1512.00 112 7
Riverside CA 28 3136 1512.00 1512.00 112 7
Seattle WA 28 3136
1
45
1456.00
Seattle WA 28 3136 1518.00 1518.00 100 7
Oakland CA 24 2688 1304.00 1304.00 80 7
Oakland CA 24 2688 1304.00 1304.00 80 7
Oakland CA 24 2688 1294.00 1294.00 100 7 Oakland CA 24 2688 1294.00 1294.00 100 8
Oakland CA 24 2688 1324.00 1324.00 40 8
Oakland CA 24 2688 1288.00 1288.00 112 8
Portland OR 24 2688 1294.00 1294.00 100 8
Portland OR 24 2688 1288.00 1288.00 112 8
Riverside CA 24 2688 1288.00 1288.00 112 8
Riverside CA 24 2688 1294.00 1294.00 100 8
Seattle WA 24 2688 1339.00 1339.00 10 8
Seattle WA 24 2688 1294.00 1294.00 100 8
Seattle WA 24 2688 1288.00 1288.00 112 8
Seattle WA 24 2688 1288.00 1288.00 112 9
Oakland CA 22 2464
1125.00
214
Portland OR 22 2464 1182.00 1182.00 100 9
Seattle WA 22 2464
1197.00
Seattle WA 22 2464 1176.00 1176.00 112 9
Seattle WA 22 2464 1176.00 1176.00 112 9 Portland OR 12 1344 547.00 547.00 250 9
Oakland CA 3 336 112.00 112.00 112 9
Riverside CA 3 336 112.00 112.00 112 9
Oakland CA 1 112 16.00 16.00 80 9
Portland OR 1 112 26.00 26.00 60 9
Portland OR 1 112 0.00 0.00 112 9
Riverside CA 1 112
36.00
Riverside CA 48 5376
2488.00
400
Portland OR 24 2688
1319.00
Seattle WA 24 2688 1324.00 1324.00 40 9
Oakland CA 3 336 118.00 118.00 100 9
Portland OR 28 3136 1512.00 1512.00 112 10
Oakland CA 24 2688 1288.00 1288.00 112 10
Riverside CA 24 2688
1194.00
Seattle WA 1 112 36.00 36.00 40 10
Oakland CA 48 5376 2632.00 2632.00 112 11
Seattle WA 24 2688 1288.00 1288.00 112 11
Riverside CA 22 2464 1176.00 1176.00 112 11
Portland OR 3 336
138.00
Oakland CA 28 3136
1563.00
Riverside CA 24 2688
1344.00
Seattle WA 22 2464 1182.00 1182.00 100 12
Portland OR 1 112 0.00 0.00 112 12
Riverside CA 48 5376
2563.00
Seattle WA 24 2688 1304.00 1304.00 80 13
Portland OR 12 1344 616.00 616.00 112 13
Oakland CA 3 336 118.00 118.00 100 13
Oakland CA 48 5376 2576.00 2576.00 224 14
Seattle WA 24 2688 1288.00 1288.00 112 14
Riverside CA 22 2464 1182.00 1182.00 100 14
Portland OR 3 336 153.00 153.00 30 14
Seattle WA 28 3136
1548.00
Oakland CA 24 2688 1288.00 1288.00 112 15
Portland OR 24 2688
1144.00
Riverside CA 24 2688 1288.00 1288.00 112 15
Oakland CA 22 2464 1176.00 1176.00 112 16
Portland OR 12 1344 642.00 642.00 60 16
Seattle WA 3 336 112.00 112.00 112 16
Riverside CA 1 112 26.00 26.00 60 16
Portland OR 28 3136 1512.00 1512.00 112 17
Oakland CA 24 2688 1339.00 1339.00 10 17
Riverside CA 24 2688 1288.00 1288.00 112 17
Seattle WA 1 112 31.00 31.00 50 17
Oakland CA 48 5376 2638.00 2638.00 100 18
Seattle WA 24 2688 1304.00 1304.00 80 18
Riverside CA 22 2464
1107.00
Portland OR 3 336 112.00 112.00 112 18
Portland OR 72 8064
35
3532.00
1000
Seattle WA 28 3136 1512.00 1512.00 112 19
Oakland CA 24 2688 1232.00 1232.00 224 19
Riverside CA 24 2688 1294.00 1294.00 100 19
Oakland CA 22 2464 1176.00 1176.00 112 20
Seattle WA 22 2464
1212.00
Portland OR 1 112 0.00 0.00 112 20
Riverside CA 1 112 0.00 0.00 112 20
Riverside CA 48 5376 2632.00 2632.00 112 21
Portland OR 24 2688 1314.00 1314.00 60 21
Seattle WA 24 2688 1288.00 1288.00 112 21
Oakland CA 3 336 112.00 112.00 112 21
Oakland CA 24 2688 1339.00 1339.00 10 22
Riverside CA 24 2688 1344.00 1344.00 0 22
Seattle WA 22 2464 1182.00 1182.00 100 22
Portland OR 3 336 112.00 112.00 112 22
Riverside CA 48 5376 2563.00 2563.00 250 23
Seattle WA 24 2688 1304.00 1304.00 80 23
Oakland CA 3 336 118.00 118.00 100 23
Portland OR 3 336 112.00 112.00 112 23
Portland OR 28 3136
1553.00
Oakland CA 24 2688 1232.00 1232.00 224 24
Riverside CA 24 2688 1294.00 1294.00 100 24
Seattle WA 1 112 16.00 16.00 80 24
Oakland CA 48 5376 2638.00 2638.00 100 25
Seattle WA 24 2688 1294.00 1294.00 100 25
Riverside CA 22 2464 1176.00 1176.00 112 25
Portland OR 3 336 43.00 43.00 250 25
Oakland CA 28 3136
1
52
Riverside CA 24 2688 1288.00 1288.00 112 26
Seattle WA 22 2464 1120.00 1120.00 224 26
Portland OR 1 112 6.00 6.00 100 26
Riverside CA 48 5376 2673.00 2673.00 30
27
Portland OR 24 2688 1288.00 1288.00 112 27
Seattle WA 24 2688 1288.00 1288.00 112 27
Oakland CA 3 336 112.00 112.00 112 27
Oakland CA 24 2688 1324.00 1324.00 40 28
Portland OR 24 2688 1288.00 1288.00 112 28
Riverside CA 24 2688 1288.00 1288.00 112 28
Seattle WA 1 112 0.00 0.00 112 28
Seattle WA 48 5376
2683.00
Portland OR 22 2464 1232.00 1232.00 0 29
Riverside CA 22 2464 1202.00 1202.00 60 29
Oakland CA 1 112 0.00 0.00 112 29
Oakland CA 24 2688 1294.00 1294.00 100 30
Seattle WA 24 2688 1294.00 1294.00 100 30
Portland OR 3 336 43.00 43.00 250 30
Riverside CA 3 336 112.00 112.00 112 30
Oakland CA 28 3136 1528.00 1528.00 80 31
Portland OR 24 2688 1294.00 1294.00 100 31
Riverside CA 24 2688 1288.00 1288.00 112 31
Seattle WA 24 2688 1232.00 1232.00 224 31
Seattle WA 48 5376 2632.00 2632.00 112 32
Riverside CA 28 3136 1553.00 1553.00 30 32
Portland OR 22 2464 1176.00 1176.00 112 32
Oakland CA 1 112 0.00 0.00 112 32
Oakland CA 24 2688 1324.00 1324.00 40 33
Seattle WA 24 2688 1288.00 1288.00 112 33
Portland OR 3 336 112.00 112.00 112 33
Riverside CA 3 336 112.00 112.00 112 33
Oakland CA 28 3136 1512.00 1512.00 112 34
Riverside CA 24 2688 1314.00 1314.00 60 34
Seattle WA 22 2464
1227.00
Portland OR 1 112 56.00 56.00 0 34
Portland OR 48 5376 2563.00 2563.00 250 35
Oakland CA 22 2464 1182.00 1182.00 100 35
Seattle WA 3 336 118.00 118.00 100 35
Riverside CA 1 112 26.00 26.00 60 35
Oakland CA 24 2688 1304.00 1304.00 80 36
Portland OR 24 2688 1294.00 1294.00 100 36
Riverside CA 24 2688 1288.00 1288.00 112 36
Seattle WA 24 2688 1232.00 1232.00 224 36
Portland OR 72 8064 3532.00 3532.00 1000 37
Riverside CA 28 3136 1553.00 1553.00 30 37
Oakland CA 22 2464 1176.00 1176.00 112 37
Seattle WA 3 336 118.00 118.00 100 37
Oakland CA 24 2688 1294.00 1294.00 100 38
Portland OR 24 2688 1288.00 1288.00 112 38
Riverside CA 24 2688 1219.00 1219.00 250 38
Seattle WA 24 2688 1304.00 1304.00 80 38
Seattle WA 48 5376 2632.00 2632.00 112 39
Riverside CA 28 3136 1518.00 1518.00 100 39
Portland OR 12 1344
657.00
Oakland CA 1 112
13.00
86
Seattle WA 28 3136 1548.00 1548.00 40 40
Oakland CA 24 2688 1288.00 1288.00 112 40
Portland OR 12 1344 616.00 616.00 112 40
Riverside CA 3 336 112.00 112.00 112 40
Portland OR 72 8064 3732.00 3732.00 600 41
Seattle WA 48 5376 2632.00 2632.00 112 41
Oakland CA 22 2464 1176.00 1176.00 112 41
Riverside CA 1 112 16.00 16.00 80 41
Oakland CA 24 2688 1339.00 1339.00 10 42
Portland OR 24 2688 1288.00 1288.00 112 42
Seattle WA 24 2688 1294.00 1294.00 100 42
Riverside CA 3 336 168.00 168.00 0 42
Oakland CA 28 3136 1518.00 1518.00 100 43
Riverside CA 24 2688 1219.00 1219.00 250 43
Seattle WA 22 2464 1192.00 1192.00 80 43
Portland OR 1 112 0.00 0.00 112 43
Portland OR 72 8064
3832.00
Riverside CA 48 5376 2638.00 2638.00 100 44
Seattle WA 24 2688 1288.00 1288.00 112 44
Oakland CA 3 336 56.00 56.00 224 44
Oakland CA 48 5376 2576.00 2576.00 224 45
Portland OR 48 5376 2632.00 2632.00 112 45
Seattle WA 48 5376
2668.00
Oakland CA 28 3136 1512.00 1512.00 112 45
Oakland CA 28 3136 1548.00 1548.00 40 45
Riverside CA 28 3136 1512.00 1512.00 112 45
Seattle WA 28 3136 1456.00 1456.00 224 45
Seattle WA 28 3136 1518.00 1518.00 100 45
Oakland CA 24 2688 1288.00 1288.00 112 45
Portland OR 24 2688 1288.00 1288.00 112 45
Portland OR 24 2688 1288.00 1288.00 112 45 Riverside CA 24 2688 1288.00 1288.00 112 45
Seattle WA 24 2688 1339.00 1339.00 10 45
Oakland CA 22 2464 1176.00 1176.00 112 45
Oakland CA 22 2464 1192.00 1192.00 80 45
Portland OR 22 2464 1176.00 1176.00 112 45
Seattle WA 22 2464 1176.00 1176.00 112 45
Oakland CA 3 336 148.00 148.00 40 45
Portland OR 3 336 153.00 153.00 30 45
Oakland CA 1 112 0.00 0.00 112 45
Portland OR 1 112 6.00 6.00 100 45
Portland OR 1 112 56.00 56.00 0 45
Riverside CA 1 112 41.00 41.00 30 45
Riverside CA 1 112 0.00 0.00 112 45
Oakland CA 24 2688 1288.00 1288.00 112 46
Portland OR 24 2688 1314.00 1314.00 60 46
Seattle WA 22 2464 1176.00 1176.00 112 46
Riverside CA 3 336 112.00 112.00 112 46
Portland OR 48 5376
2663.00
Riverside CA 48 5376
2688.00
Portland OR 24 2688 1288.00 1288.00 112 47
Riverside CA 24 2688 1288.00 1288.00 112 47
Seattle WA 24 2688 1294.00 1294.00 100 47
Seattle WA 24 2688 1339.00 1339.00 10 47
Oakland CA 3 336 163.00 163.00 10 47
Portland OR 48 5376 2632.00 2632.00 112 48
Oakland CA 24 2688 1294.00 1294.00 100 48
Riverside CA 24 2688 1219.00 1219.00 250 48
Seattle WA 24 2688 1304.00 1304.00 80 48
Oakland CA 48 5376 2576.00 2576.00 224 49
Seattle WA 48 5376 2632.00 2632.00 112 49
Oakland CA 24 2688 1288.00 1288.00 112 49
Seattle WA 24 2688 1288.00 1288.00 112 49
Riverside CA 22 2464 1182.00 1182.00 100 49
Portland OR 3 336 153.00 153.00 30 49
Riverside CA 3 336 138.00 138.00 60 49
Oakland CA 28 3136 1518.00 1518.00 100 50
Seattle WA 28 3136 1518.00 1518.00 100 50
Oakland CA 24 2688 1294.00 1294.00 100 50
Portland OR 24 2688 1288.00 1288.00 112 50
Riverside CA 24 2688 1288.00 1288.00 112 50
Seattle WA 24 2688 1288.00 1288.00 112 50
Seattle WA 24 2688 1288.00 1288.00 112 50 Oakland CA 22 2464 1176.00 1176.00 112 50
Oakland CA 22 2464 1192.00 1192.00 80 50
Riverside CA 22 2464 1176.00 1176.00 112 50
Seattle WA 22 2464 1182.00 1182.00 100 50
Seattle WA 22 2464 1120.00 1120.00 224 50
Seattle WA 22 2464 1227.00 1227.00 10 50
Seattle WA 3 336 112.00 112.00 112 50
Oakland CA 1 112 36.00 36.00 40 50
Portland OR 1 112 31.00 31.00 50 50
Riverside CA 48 5376 2632.00 2632.00 112 51
Portland OR 24 2688 1294.00 1294.00 100 51
Seattle WA 24 2688 1232.00 1232.00 224 51
Oakland CA 3 336
128.00
Portland OR 72 8064
3582.00
900
Seattle WA 48 5376 2632.00 2632.00 112 52
Oakland CA 24 2688 1288.00 1288.00 112 52
Riverside CA 24 2688 1329.00 1329.00 30 52
8728
988288
Costs&Distances
Destination
Miles
Truck Cost per mile
Fronthaul cost
% Deadhead return
Return to Lodi costs
Total FTL Shipping cost
Cost per pallet in a FTL
Cost per bottle
RT time/days
Riverside CA
410
$2.15
$881.50
50%
$440.75
$1,322.25
$55.09
$0.08
1.6
Oakland CA 80
$3.00
$240.00
100%
$480.00
$20.00
$0.03
0.3
Portland OR
620
$1,333.00
30%
$399.90
$1,732.90
$72.20
$0.11
2.5
Seattle WA
800
$1,720.00
$516.00
$2,236.00
$93.17
$0.14
3.2
1 pallets = 112 cases
FTL = 24 pallets
1 Case = 6 bottles
Destination
Cost to ship one pallet
Riverside CA
$500.00
$0.74
Oakland CA
$200.00
$0.30
Portland OR
$600.00
$0.89
Seattle WA
$800.00
$1.19
bottle
Red
bottle
White
bottle
Organic
case Red
case White
case Organic
Whole sale price
$
7.5
$8.00
$12.00
$45.00
$48.00
$72.00
Product cost
$2.40
$3.40
$6.30
$14.40
$20.40
$37.80
State taxes
Based on Profit
CA
8.8%
OR
6.6%
M1 A-E
Milestone 1
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
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
Hints:
We are looking at revenue generated and not profit in this problem
Production
Make sure you don’t mix your units of measurement (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
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 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
48
24
18
12
6
3
1
In your response, be sure to use the histogram located in the data analysis tool pack add on.
F: Create shipment histograms for Portland and Riverside using the same Bin sizes (as used in E). The histogram will generate on a new tab; name this tab M1E
Hint
Use alphabetical sort for 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.
M2 A-E
Milestone 2
A: Create a spreadsheets that calculate the costs of shipping to Portland and Riverside by pallets based on the frequency distribution used in the Histograms used in Milestone 1 (Link the cost data to the data in the Costs&Distances tab).
Size of shipment in pallets
Tranport cost to Portland
cost per pallet
Frequency
Cost of shipments
Tranport cost to Riverside
Frequency
Cost of shipments
B: Create a table that calculates the production costs of the wines sold to Portand and Riverside. Hints: Link the cost data to the data in the Costs&Distances tab. Use a pivot table and some additional programing.
Red White Organic Total
Portland
Riverside
C: Create a table that calculates
Gross Profit
Gross Profit =
Revenue
Transport
Total revenue
Gross profit
%GP/R
Riverside
Gross Profit Total For Each Variety of Wine by
DC
DC
Wine Type
COGS
Portland Red
White
Organic
Gross Profit Total For Each Variety of Wine by DC
Riverside Red
Organic
D: Create a table that calculates Gross Profit (from part C) minus state taxes (from the Costs&Distances tab)
State tax rate
State Tax
Profit After
Riverside
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.
FS A-B
III. Optimizing Performance
For the Lodi Winery, you have been asked by management to examine the data collected and analyzed in the previous modules. The objective is for you to help management decide on the right mix of wine bottles to sell based on newly derived profit information while considering the limitations of the particular types of grapes available for production.
While doing more research on wine production, you realize that it takes 3.5 pounds of grapes to make a bottle of wine. In addition, you already were provided the price per bottle that the distributors are paying for each variety of wine:
Price for Red Wine ($)
Price for White Wine ($)
Price for Organic Wine ($)
7.5 8 12
After discussing wine production with the operations manager, you also learn that the wineries that supply the grapes to produce the above types of wine can produce up to a total of 200,000 pounds of grapes for a six-month supply of wine bottles for the three markets, with the following expected distribution constraints based on types of grapes. Note that current market demand will not support more than the below constraints for each type:
Red wine ceiling
22,000 bottles
White wine ceiling
24,000 bottles
Organic wine ceiling
12,000 bottles
Note that the production cost per bottle remains the same as before, that is, 32% of sales or revenue for red wine, 42.5% of sales for white wine, and 52.5% for organic wine. With additional information you have gathered, you are now ready to determine the optimum production mix to maximize profit.