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. |