Management Science Through Spreadsheets

Prompt

In the Plan-Do-Check- Act management cycle management plans (sets objectives, develops processes and allocates resource) that the company will execute (Do). Progress in meeting set objectives is monitored and feedback is provided (Check). Based on feedback, adjustments to the plans are made (Act). While management involves more than just measuring aspects of operation you cannot efficiency manage unless you know if objectives are being met. Checking is not a onetime 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 changes the spreadsheets do not need to be changed in order for them to calculate the results.

In the case study, you are constructing a number of spreadsheets to evaluate specific aspects of a winery and its distribution operation. Certain things such as increasing wine production significantly or lowering production costs require significant time to enact whereas making changes regarding distribution can be made fairly quickly. In the case study new management wants to understand how efficient their distribution system if functioning.

Case Study Intro

The Vinho Winery of Lodi California produces about 1 million cases of wine a year. It sells its wine wholesale to four independent wine distributors; Riverside CA, Oakland CA, Portland OR, and Seattle WA. They produce three varieties of wine; Ruby Red, Murky White, and Whole-earth Organic.

The grapes to produce the three varieties differ and their production volumes (augmented by grapes bought from other growers) must be planned at least a year in advance of being pressed into wine and the wine must be aged a year before being sold.

Vinho Winery advertises their wines in the areas surrounding their four independent wine distributors and the cost of this marketing is included in the wine production costs.

Vinho moves their wine by truck to the distributors. A full truck will consist of 24 pallets of wine totaling 2688 cases (16,128 bottles). The minimum shipment they will sell is a pallet of wine (112 cases) and they contract out delivery of the pallets unless the cost will exceed the cost of using a truck for a subsidiary that charges by the mile. To avoid having their trucks returning empty and paying for the round trip, Vihno has brokers arrange cargo to be carried on the return (Backhaul). Since little Lodi is not a major transportation destination only part of the return trip can be used (for example, the return from Seattle can be used to move cargo from Seattle to Eureka but not all the way to Lodi).

Vinho Winery was recently bought by a private equity firm and they want an assessment of current operations. Once completed, they want plans to optimize operations. You are the management consultant that will conduct the assessment and develop the plans.

You will be required to create and program spreadsheets for your analysis and conclude with summary statements. The source data (raw data) that you will draw upon for analysis is posted in Learning Module 3 and should be imported into the Excel work book that you will create. The source data should be placed in one tab in your Excel work book (at the beginning or end) and the tables you create should link to that data rather than creating copies of the data for each tab.

In Milestone 1 you will be analyzing the distribution of wines to the four distributors. The specific tasks are:

A. Determine percentage of wine by distributor (show in a pivot table and a pie chart)

B. Determine wine distribution by variety and distributor (show in a pivot table and a bar chart)

C. Determine revenue by distributor and wine variety (show data in a table and in a bar chart)

D. Show measures of Central Tendencies of the shipments to each distributor (use the IF Function to select the data to be used) show in a table

E. Analyze frequency of size of shipment sizes using a histogram

F. Create shipment histograms for Portland and Riverside

G. Summary Statement: Discuss the significance of what you found in A-F.

In Milestone 2 you will be analyzing the costs of shipping the wines to the various distributors and the impact of transport costs and state taxes on profit. The specific tasks are:

A. Calculate costs of shipping to Portland and Riverside by pallets (show in a table)

B. Revenue of Portland and Riverside minus transportation costs (show in a table)

C. Cost of production (show in a table)

D. Revenue minus Transport & Production (show in a table)

E. Profit after state taxes (show in a table)

F. Summary, explain how central tendency in regard to shipment sizes impacted the bottom line

In Milestone 3; management has asked a question and you will program spreadsheets using Excel Solver to answer the questions. Run Excel Solver to determine the solution and show the limits and sensitively reports. In addition you will provide a summary statement providing your assessment as to whether the changes being examines are worth examining further. The question is:

1) Management wants you to determine the optimum speed that the trucks should travel when going to the distributors in order to reduce Round Trip truck times to the four destinations (one trip to each) using the following assumptions and constraints:

a. That the Cost per Mile provided in the Background is based on truck/driver costs and fuel. Semi trucks get about 6.5 mpg at 55.6 mph (speed used in this scenario) and the mpg will decrease about 0.14 mpg per MPH over 55.6 MPH.

b. The cost of diesel for this scenario is $2.95 gallon

c. If we ask the trucks to go faster we will need to pay for the additional fuel cost

d. Management is will to consider allowing fuel costs to $6,000 if significant time savings can be realized

e. Management has imposed a limit of 14.4 mph increase on average trucks speeds

Data

Destination	Pallets	Total Cases	Cases red	Cases white	Cases organic	Week of Order	Pallets (Bins)
Oakland CA	48	5376	2648.00	2648.00	80	1	72
Portland OR	48	5376	2438.00	2438.00	500	1	48
Portland OR	48	5376	2438.00	2438.00	500	1	24
Riverside CA	48	5376	2673.00	2673.00	30	1	18
Seattle WA	48	5376	2638.00	2638.00	100	1	12
Seattle WA	48	5376	2638.00	2638.00	100	1	6
Oakland CA	28	3136	1518.00	1518.00	100	1	3
Oakland CA	28	3136	1518.00	1518.00	100	1	1
Riverside CA	28	3136	1512.00	1512.00	112	1
Oakland CA	24	2688	1288.00	1288.00	112	1
Oakland CA	24	2688	1324.00	1324.00	40	1
Oakland CA	24	2688	1288.00	1288.00	112	1
Oakland CA	24	2688	1324.00	1324.00	40	1
Oakland CA	24	2688	1294.00	1294.00	100	1
Riverside CA	24	2688	1288.00	1288.00	112	1
Riverside CA	24	2688	1288.00	1288.00	112	1
Riverside CA	24	2688	1329.00	1329.00	30	1
Riverside CA	24	2688	1288.00	1288.00	112	1
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	1294.00	100	1
Oakland CA	22	2464	1192.00	1192.00	80	1
Riverside CA	22	2464	1176.00	1176.00	112	1
Riverside CA	22	2464	1176.00	1176.00	112	1
Riverside CA	22	2464	1202.00	1202.00	60	1
Seattle WA	22	2464	1120.00	1120.00	224	1
Seattle WA	22	2464	1176.00	1176.00	112	1
Portland OR	12	1344	422.00	422.00	500	1
Portland OR	12	1344	616.00	616.00	112	1
Portland OR	11	1232	560.00	560.00	112	1
Portland OR	11	1232	560.00	560.00	112	1
Portland OR	11	1232	491.00	491.00	250	1
Oakland CA	3	336	112.00	112.00	112	1
Oakland CA	3	336	112.00	112.00	112	1
Oakland CA	3	336	118.00	118.00	100	1
Portland OR	3	336	93.00	93.00	150	1
Portland OR	3	336	168.00	168.00	0	1
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
Riverside CA	3	336	112.00	112.00	112	1
Seattle WA	3	336	163.00	163.00	10	1
Seattle WA	3	336	56.00	56.00	224	1
Seattle WA	3	336	112.00	112.00	112	1
Seattle WA	3	336	56.00	56.00	224	1
Oakland CA	1	112	0.00	0.00	112	1
Portland OR	1	112	-69.00	-69.00	250	1
Seattle WA	1	112	53.00	53.00	6	1
Seattle WA	1	112	6.00	6.00	100	1
Seattle WA	1	112	51.00	51.00	10	1
Seattle WA	1	112	0.00	0.00	112	1
Oakland CA	48	5376	2588.00	2588.00	200	2
Oakland CA	48	5376	2638.00	2638.00	100	2
Oakland CA	48	5376	2632.00	2632.00	112	2
Oakland CA	48	5376	2638.00	2638.00	100	2
Oakland CA	48	5376	2632.00	2632.00	112	2
Portland OR	48	5376	2338.00	2338.00	700	2
Seattle WA	48	5376	2632.00	2632.00	112	2
Seattle WA	48	5376	2576.00	2576.00	224	2
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	1304.00	80	2
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	1304.00	1304.00	80	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	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	1232.00	224	2
Seattle WA	24	2688	1294.00	1294.00	100	2
Seattle WA	24	2688	1288.00	1288.00	112	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	1339.00	1339.00	10	2
Seattle WA	24	2688	1304.00	1304.00	80	2
Portland OR	22	2464	1182.00	1182.00	100	2
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	622.00	100	2
Portland OR	12	1344	616.00	616.00	112	2
Portland OR	11	1232	491.00	491.00	250	2
Portland OR	11	1232	601.00	601.00	30	2
Portland OR	3	336	163.00	163.00	10	2
Portland OR	3	336	43.00	43.00	250	2
Portland OR	3	336	148.00	148.00	40	2
Portland OR	3	336	43.00	43.00	250	2
Riverside CA	3	336	112.00	112.00	112	2
Riverside CA	3	336	153.00	153.00	30	2
Oakland CA	1	112	26.00	26.00	60	2
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
Riverside CA	48	5376	2632.00	2632.00	112	3
Seattle WA	28	3136	1518.00	1518.00	100	3
Oakland CA	24	2688	1219.00	1219.00	250	3
Riverside CA	24	2688	1219.00	1219.00	250	3
Riverside CA	24	2688	1314.00	1314.00	60	3
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	547.00	547.00	250	3
Portland OR	12	1344	642.00	642.00	60	3
Portland OR	12	1344	547.00	547.00	250	3
Riverside CA	3	336	153.00	153.00	30	3
Riverside CA	1	112	41.00	41.00	30	3
Riverside CA	1	112	16.00	16.00	80	3
Portland OR	72	8064	3732.00	3732.00	600	4
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	1538.00	60	4
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
Riverside CA	24	2688	1288.00	1288.00	112	4
Oakland CA	22	2464	1171.00	1171.00	122	4
Oakland CA	20	2240	1090.00	1090.00	60	4
Portland OR	12	1344	572.00	572.00	200	4
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	1229.00	230	5
Seattle WA	1	112	31.00	31.00	50	5
Oakland CA	48	5376	2538.00	2538.00	300	6
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	143.00	50	6
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	2658.00	60	7
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
Oakland CA	28	3136	1512.00	1512.00	112	7
Riverside CA	28	3136	1512.00	1512.00	112	7
Seattle WA	28	3136	1456.00	1456.00	224	7
Seattle WA	28	3136	1518.00	1518.00	100	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	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
Portland OR	24	2688	1288.00	1288.00	112	8
Riverside CA	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	1125.00	214	9
Portland OR	22	2464	1182.00	1182.00	100	9
Seattle WA	22	2464	1197.00	1197.00	70	9
Seattle WA	22	2464	1176.00	1176.00	112	9
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
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
Portland OR	1	112	0.00	0.00	112	9
Riverside CA	1	112	36.00	36.00	40	9
Riverside CA	48	5376	2488.00	2488.00	400	9
Portland OR	24	2688	1319.00	1319.00	50	9
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	1194.00	300	10
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	138.00	60	11
Oakland CA	28	3136	1563.00	1563.00	10	12
Riverside CA	24	2688	1344.00	1344.00	0	12
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	2563.00	250	13
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	1548.00	40	15
Oakland CA	24	2688	1288.00	1288.00	112	15
Portland OR	24	2688	1144.00	1144.00	400	15
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	1107.00	250	18
Portland OR	3	336	112.00	112.00	112	18
Portland OR	72	8064	3532.00	3532.00	1000	19
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	1212.00	40	20
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	1553.00	30	24
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	1528.00	1528.00	80	26
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	2683.00	10	29
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	1227.00	10	34
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	657.00	30	39
Oakland CA	1	112	13.00	13.00	86	39
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	3832.00	400	44
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	2668.00	40	45
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
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
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
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
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	2663.00	50	47
Riverside CA	48	5376	2688.00	2688.00	0	47
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
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
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
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	128.00	80	51
Portland OR	72	8064	3582.00	3582.00	900	52
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 in a FTL	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%	$240.00	$480.00	$20.00	$0.03	0.3
Portland OR	620	$2.15	$1,333.00	30%	$399.90	$1,732.90	$72.20	$0.11	2.5
Seattle WA	800	$2.15	$1,720.00	30%	$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	Cost per bottle
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.50	$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 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 Data tab
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
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 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
In your response, be sure to use the histogram located in the data analysis tool pack add on.
State the rationale you used
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
State the rationale you used
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).
State the rationale you used
Size of shipment in pallets	1	3	6	12	18	24	48	72
Tranport cost to Portland
cost per pallet
Frequency
Cost of shipments
Size of shipment in pallets	1	3	6	12	18	24	48	72
Tranport cost to Riverside
cost per pallet
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.
State the rationale you used
Red	White	Organic	Total
Portland
Riverside
C: Create a table that calculates Gross Profit and Gross Profit as a percentage of revenue
Gross Profit = Revenue (from Milestone One) minus Transport (from part A) & Production (from part B) from Portland and Riverside
State the rationale you used
Total revenue	Transport	Production	Gross profit	%GP/R
Portland
Riverside
Gross Profit Total For Each Variety of Wine by DC
DC	Wine Type	Revenue	Transport	COGS	Gross profit	%GP/R
Portland	Red
White
Organic
Gross Profit Total For Each Variety of Wine by DC
DC	Wine Type	Revenue	Transport	COGS	Gross profit	%GP/R
Riverside	Red
White
Organic
D: Create a table that calculates Gross Profit (from part C) minus state taxes (from the Costs&Distances tab)
State the rationale you used
State tax rate	Gross Profit	State Tax	Profit After
Portland
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 based on types of grapes:
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.