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

Attachment:- Raw Data.rar