Management science through spreadsheets | Operations Management homework help




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

Calculate the price
Make an order in advance and get the best price
Pages (550 words)
*Price with a welcome 15% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know how difficult it is to be a student these days. That's why our prices are one of the most affordable on the market, and there are no hidden fees.

Instead, we offer bonuses, discounts, and free services to make your experience outstanding.
How it works
Receive a 100% original paper that will pass Turnitin from a top essay writing service
step 1
Upload your instructions
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
Pro service tips
How to get the most out of your experience with Australia Assessments
One writer throughout the entire course
If you like the writer, you can hire them again. Just copy & paste their ID on the order form ("Preferred Writer's ID" field). This way, your vocabulary will be uniform, and the writer will be aware of your needs.
The same paper from different writers
You can order essay or any other work from two different writers to choose the best one or give another version to a friend. This can be done through the add-on "Same paper from another writer."
Copy of sources used by the writer
Our college essay writers work with ScienceDirect and other databases. They can send you articles or materials used in PDF or through screenshots. Just tick the "Copy of sources" field on the order form.
See why 20k+ students have chosen us as their sole writing assistance provider
Check out the latest reviews and opinions submitted by real customers worldwide and make an informed decision.
Excellent work.
Customer 453187, July 5th, 2022
thank you so much
Customer 453933, January 25th, 2020
Thanks so much!!!
Customer 454087, June 24th, 2020
Human Resources Management (HRM)
Good work.
Customer 463125, July 4th, 2022
English 101
Good one.
Customer 453625, May 25th, 2022
Business and administrative studies
Excellent work!
Customer 452719, June 11th, 2022
Good work.
Customer 453707, March 27th, 2022
Excellent work!
Customer 463469, October 17th, 2022
good job
Customer 456821, April 4th, 2022
poor service
Customer 453627, January 24th, 2020
Nice job.
Customer 452441, April 6th, 2022
Good job
Customer 456821, November 2nd, 2022
Customer reviews in total
Current satisfaction rate
3 pages
Average paper length
Customers referred by a friend
15% OFF your first order
Use a coupon FIRST15 and enjoy expert help with any task at the most affordable price.
Claim my 15% OFF Order in Chat