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
Why Work with Us
Top Quality and Well-Researched Papers
We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.
Professional and Experienced Academic Writers
We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.
Free Unlimited Revisions
If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.
Prompt Delivery and 100% Money-Back-Guarantee
All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.
Original & Confidential
We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.
24/7 Customer Support
Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.
No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.
Admission Essays & Business Writing Help
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.