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.
 
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

IBM EXAM

1. With _________________, the seller pays the actual freight charges and bills every purchase with an identical, flat freight charge.

  a.  

zone pricing

  b. basing-point pricing
  c. uniform delivered pricing
  d. freight absorption pricing
  e. FOB origin pricing

2. ABC Company is hoping to increase its product sales immediately.  Its best option is to use:

  a. sales promotion
  b. marketing research
  c. advertising
  d. publicity
  e. personal selling

3.A medium’s ability to reach a precisely defined market is its:

  a. market targeting
  b. noise filtering ability
  c. geographic selectivity
  d. cost per exposure
  e. audience selectivity

 

4. Denny owned a small company which just started selling a new product.  To try and win favor from some retailers who carried competing products, Denny offered them higher margins on his products than on his competitors’.  This is known as:

  a. Trade promotion
  b. Sales promotion
  c. A pull strategy
  d. Trade partnership
  e. Consumer promotion

 

Question 5    查不到,但是感觉是第四个,受众广?

One of the main advantages of television advertising is:

Television can be high cost but has higher impact, it is more engaging and that makes it easier to demonstrate the products. It also provides wide coverage, which is particularly appealing for nationwide product launches.

  a. the lack of commercial clutter
  b. that it is cheap
  c. its captive audience
  d. ability to reach wide and diverse audience
  e. its ability to convey complex messages

Question 6

Why do many brands with a large market share spend proportionally less on advertising compared to brands with small market share in most industries?

  a. Certain industries have a practice of spending a low amount of dollars
  b. They cannot afford to spend more
  c. The firms with large market share do not need to advertise much
  d. Advertising will not stimulate their sales
  e. Because they need a lower proportion to get the same amount of absolute advertising expenses

 

Question 7

Slatescape sells crushed slate to be used as a decorative, permanent mulch to keep weeds out of flower beds.  Slatescape has decided to put a majority of its promotion budget into personal selling and promotion to lawn and garden supply stores and large discount stores such as Wal-Mart.  This new strategy is aimed towards:

  a. creating a push strategy
  b. increasing consumers’ preference of its product
  c. increasing the use of manufacturer rebates
  d. creating a pull strategy
  e. increasing awareness among consumers

Question 8

ABC Enterprises is trying to spur growth in sales in the short term.  Their best bet for this project is to focus on:

  a. personal selling
  b. advertising
  c. publicity
  d. social media
  e. sales promotion

Question 9 When entering a market for a high cost product, using the strategy of setting your price high so that you attract less price-sensitive buyers and thus earn money via high margins, not volume, is known as what type of pricing:

 

a. Market skimming

 

b. Mark-up

c. Profit maximization

 

d. Dollar sales maximization

 

e. Market penetration

 

Question 10

For salespeople, the process of screening out poor customers is called:

  a. Selection
  b. Pre-approach
  c. Prospecting
  d. Qualifying
  e. Approach

Question 11

Heinz is contemplating introducing its ketchup in countries where the product is rarely used.  So, this is a rather novel product. Heinz has to rely primarily on _____ advertising.

  a. reminder
  b. repetitive
  c. persuasive
  d. top of the mind
  e. informative

Question 12

_____________ is the process of finding hidden patterns and relationships in the customer data stored in the data warehouse.

  a. Customer profiling
  b. Cluster analysis
  c. Correlation analysis
 

  c. is a model for reaching promotional goals that propels consumers along the stages of consumer involvement process

 

 

d. Regression analysis
  e. Data mining

 

Data Mining

used to find hidden patterns and relationships in the customer data stored in the data warehouse

对

Question 13

Jaguar has created a new car for a limited time, and is priced at $400,000.  Jaguar is aiming to:

  a. get some market share of super luxury car markets
  b. maximize the company’s unit sales
  c. maximize the company’s market share in the car industry
  d. Can be any of the above
  e. maximize the company’s dollar sales

 

2 points   

 

Question 14

The AIDA concept:

  a. is a plan based on the effectiveness of sales promotion
  b. demonstrates that buyers go through nine stages on the way to making a decision
     
  d. each individual promotional message should get the customers to the purchase stage ?
  e. is a model effectively showing that advertising is somewhat of a random process

Question 15

Social media is:

  a. primarily a medium to promote products and services
  b. Internet based services that facilitate conversations and social interaction
  c. always a useful avenue for marketers
  d. meant to be a way for marketers to get more individual level data
  e. any website aimed at marketing to consumers

Question 16

A company has found that sales response to its advertising expenses has been rather low.  What does this suggest?

  a. No definitive statement can be made because the company may not know what point of sales-advertising curve does it lie on
  b. It should stop spending on advertising since it is not showing good results anyway
  c. It should spend less because the sales response will get worse at higher level of advertising
  d. It should spend more because the sales response will improve at higher level of advertising
  e. Advertising does not have any effect on sales

Question 17

An organization is using _________________ when it sets its prices so that its revenues are as large as possible compared to its costs.

  a. dollar sales maximization
  b. unit sales maximization
  c. cost based pricing
  d. profit maximization
  e. market share maximization

 

Question 18

In a CRM environment, ________________ is defined as the informal process of collecting customer information through customer contacts and feedback on product and service performance.

  a. data mining
  b. leveraging
  c. interaction
  d. learn

ing

  e. knowledge management

 

Question 19

_____ refers to the way the advertisement message is conveyed.

  a. Campaign evaluation
  b. Message execution
  c. None of the above
  d. Identifying product benefits
  e. Evaluating the appeal of competing advertisements

Question 20

With a (n) _______________ media schedule, the marketer may alternate between no advertising and heavy advertising every other month to achieve a greater impact for the same budget.

  a. bursting
  b. intermittent
  c. pulsing
  d. flighted
  e. periodic

 

Question 21

A consumer goods company wants to reach its market, but feels that its consumers are often distracted by all the media clutter.  If this is true, it should consider:

  a. advertising
  b. product placement
  c. press release
  d. personal selling
  e. Internet

Question 22

Point-of-purchase displays in grocery stores are mostly targeted at creating:

  a. knowledge
  b. action
  c. preference
  d. awareness
  e. liking

Question 23

Dick’s Sporting Goods collects a vast amount of data through its website, direct mailings, and retail stores.  To be useful, all of these data would be centralized in a(n):

  a. data warehouse
  b. central server
  c. data bank
  d. data mart
  e. data cluster

Question 24

The Comcast Triple Play package includes cable television, Internet, and telephone service for a price significantly lower than the cost of the three services priced separately.  This is an example of _______________.

  a. option pricing
  b. quantity discount pricing
  c. price bundling
  d. two-part pricing
  e. loss leader pricing

 

Question 25

Using consumers to develop and market products is called:

  a. crowdfunding
  b. co-creation
  c. closed innovation
  d. crowdsourcing
  e. brainstorming

Question 26

If you are trying to appeal to the mass markets, the most effective promotional method is:

  a. Direct marketing
  b. Public relations
  c. Promotion
  d. Publicity
  e. Advertising

Question 27

Personal selling:

  a. is a good substitute for advertising
  b. is more effective than advertising in the early stages of the purchase process
  c. is more effective than advertising in the later stages of the purchase process

Personal Selling (Expensive; face-to-face; high impact)

Can be very effective

Companies may rely on advertising to do initial work of informing the customers but use personal selling in the later stages

 

  d. serves the same purpose as advertising
  e. is less expensive than advertising

Question 28

Persuasion becomes the primary promotion goal when entering which stage of the product life cycle?

  a. Maturity
  b. Introduction
  c. Growth
  d. Persuasion is always a good idea
  e. Decline

 

Question 29

A price-skimming strategy would be very appropriate for which of the following?

  a. A high technology solar automobile
  b. A mass market cereal
  c. A new comic book series with an obviously gay hero
  d. A new brand of bottled water
  e. Low wattage light bulbs

Question 30

The main function of a promotional strategy is to:

  a. create a strong brand image in the marketplace
  b. enhance the value of the brand so the customers are willing to pay a higher price
  c. find a niche in the marketplace for the firm and its products
  d. convince its customers that a firm’s products offer advantages over those of its competition
  e. create brand awareness in the marketplace

Question 31

A website that allows individuals to connect with friends, peers, and business associates is called a:

  a. social networking site
  b. media sharing site
  c. virtual world
  d. social news site
  e. microblog

Question 32

Companies can buy space on the folding trays in front of every seat on airplanes to inform travelers about their products.  Use of this kind of promotion would be considered:

  a. public relations
  b. publicity
  c. sales promotion
  d. personal selling
  e.

 

advertising

Question 33

Which of the following would imply demand would be elastic?

  a. high involvement product
  b. nondurable product
  c. many substitute products
  d. difficult to evaluate quality
  e. price is low relative to purchasing power

 

Question 34

ABC Company is trying to build its brand image.  If so, it should probably not focus on:

  a. Sales promotion
  b. Publicity
  c. Personal selling

(not sure)

  d. Press releases
  e. Advertising

uestion 35

Instead of sending artfully arranged flower bouquets, an entrepreneur has developed fresh fruit bouquets and is marketing them under the name Edible Arrangements.  The premium fruit is cut to mimic flowers, arranged in a basket, and carefully delivered to the recipient’s home. One of the challenges is convincing customers about the appeal of this novelty product.  Thus, Edible Arrangements will be using _____________ advertising to promote its fruit bouquets.

  a. comparative
  b. pioneering
  c. competitive
  d. institutional
  e. demonstration

Question 36

The American Plastics Council wants to stimulate more consumer demand for products that are packaged in plastic.  The ads will be run in consumer magazines and will hopefully cause customers to ask retailers to carry more products packaged in plastic.  The American Plastics Council is:

  a. using publicity to fight the negative image associated with the use of plastics
  b. using a push strategy
  c. using a pull strategy
  d. focusing on recycling plastic packaging
  e. increasing secondary demand for plastics

uestion 37 _____ measures the number of different target consumers who are exposed to a commercial at least once. a. Reach b. Audience selectivity c. Impact d. Frequency e. Cost per exposure Question 38

Which of the following is an example of owned media?

  a. The display advertisement that FitBody purchased on the local newspaper’s Web site
  b. An e-mail sent by Garrett to the manager of FitBody, asking about what types of workout programs the gym offers
  c. The Facebook page created by FitBody to let members know about special events at the gym
  d. The tweet that Winnie—who works out regularly at FitBody—sent to her followers about how much weight she’s lost since she’s been working out
  e. The personal blog “Feeling the Burn,” maintained by Andy, who is one of the members of FitBody

39. The situation in which there are so many buyers and sellers that each has little control on price is known as:

  a. Pure Monopoly
  b. Uncontrolled Competition
  c. Monopolistic Competition
  d. Pure Competition
  e. Oligopolistic Competition

 

40. After establishing a listening platform, the next step for an organization should be to:

  a. implement measurement tools to determine the benefits of social media
  b. develop a list of objectives for its social media team to accomplish
  c. identify potential consumers
  d. attempt to develop a unique social media platform to generate buzz about the brand
  e. decide which tools and platforms to use as part of its social media strategy

Question 41

A (n) _______________ media schedule combines continuous scheduling throughout the year with a flighted schedule during the best sales periods.

  a. flighted
  b. periodic
  c. intermittent
  d. bursting
  e. Pulsing

 

Question 42

Price elasticity refers to:

  a. how much price will change in response to demand
  b. how much demand will change with a given change in price
  c. how much cost can be recovered at a given price
  d. how much price will change in response to supply
  e. how much supply will change in response to price

Question 43

Why do some marketers offer rebates instead of price reductions to induce short-term sales?

  a. A price reduction of this type would likely be an example of price discrimination.
  b. Rebates result in brand-loyal consumers.
  c. Rebates are good at enticing purchases, but most consumers never bother to redeem them.
  d. Consumers find rebates more appealing.
  e. Rebates are more convenient for consumers.

 

Question 44

A print ad for Real Simple magazine shows a typical scene of a group of women sitting on a sofa, and casually chatting.  Which executional style is being used in this ad?

  a. humor
  b. testimony
  c. slice-of-life
  d. demonstration
  e. mood or image

Question 45

Which advertising is particularly suited for smaller competitors moving up to the more upscale markets?

  a. demonstration
  b. competitive
  c. pioneering
  d. comparative
  e. institutional

Question 46

When Delta Airlines raises or lowers its prices on its Atlanta to Chicago route, other airlines tend to make the same changes in their pricing.  This is an example of ______________ pricing.

  a. market share
  b. predatory
  c. cost-plus
  d. status quo
  e. target return

uestion 47

When Microsoft introduced its Zune MP3 player, some people thought it would capture the MP3 player market by pricing its product so low that a smaller competitor, like the Apple iPod, would be unable to compete and have to quit the market.  If Microsoft had used this approach, it would be guilty of _________________.

  a. product dumping
  b. price discrimination
  c. price fixing
  d. predatory pricing
  e. unfair trade practices

Question 48

South Africa’s Competition Commission accused South African Airways of conspiring with its partner, Germany’s Lufthansa, to set high prices on flights between Johannesburg and Frankfurt.  As a result, the two airlines were charged with:

  a. premium pricing
  b. price discrimination
  c. price fixing
  d. price skimming
  e. predatory pricing

Question 49

Detrol, a drug for an overactive bladder, runs a television ad where a female restroom sign jumps off the door and convinces a patient (without naming any of the competitors) that Detrol is the best solution available.  This is an example of a (n) _____ ad.

  a. competitive
  b. demonstration
  c. pioneering
  d. institutional
  e. comparative

Question 50

The use of trade promotion by a manufacturer is aimed towards:

  a. giving incentives to the final consumers to buy its product
  b. creating a pull strategy
  c. increasing awareness among consumers
  d. creating a push strategy
  e. increasing consumers’ preference of its product

 

 

 
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

Operations Management Report For Markeaton Park

0

 

 

Module Assessment 2

Module Title: Applied Operations Management

Module Code: 5LO507

 

 

 

 

1

 

The purpose of an Assignment is for students to be able to demonstrate that they have successfully achieved the module Learning Outcomes

On successful completion of the module, students will be able to: 1. Understand the operations function as a mechanism for competitive advantage. 2. Recognise the need to maintain and improve operations to meet demanding quality

standards and customer expectations 3. Understand the role of design, planning, control and improvement in the operations

function, and be able to apply this knowledge to practical workplace/case study situations

 

This is the Assessment guidance as produced in your Module Handbook.

Coursework 2: 70% (Learning Objective 1 and 3) Individual Assignment: Management Report on a real-world project, which provides a detailed theoretical and applied rationale/justification to support the outcomes backed up by recent scholarly books, articles, etc.

Individual, written 3000 words (+/- 10%) assignment which will include the review and evaluation of operations function and recommendations for improvement.

OV E R V I E W In Coursework 1 (CW 1) you have applied your knowledge to the case study/scenario presented. CW1 provided you with an opportunity to understand the application of theoretical knowledge to real-life scenarios. The case/scenario was presented in a structured manner with information/data provided. The expectation was that you will apply specific theoretical knowledge to the provided scenarios.

In Coursework 2 (CW 2) you are presented with a real-life case where you are provided with basic information about the rationale, purpose and scope of the study subject. You are expected to apply the breadth of your knowledge obtained throughout this course and conduct both the online and in-field research to further expand your understanding of the operational aspect of the case presented. In result, you will provide a comprehensive analysis with academic underpinning and recommendations. It is expected that you will spend a total of 30 hours on this research. This time could include, but is not limited to: • Assignment briefing • Q & A session regarding the task, with client/ tutors • Site Visits • Competitor analysis • Skills analysis (Skills that you have developed during this module and specifically by doing CW2)

You should structure your thoughts in a logical manner and present them professionally in a written document. Your answers should not exceed a total of 3000 Word limit (+/- 10%). Word limit excludes Table, Figures and charts.

D E A D L I N E F O R S U B M I S S I O N CW 2 Individual written work must be submitted via Turnitin by 8th May 2020 (05:00 PM) Marked work will be available to view with comments through Turnitin in 3 weeks after the submission date.

 

 

 

2

 

Case Study: The Markeaton Park Project Markeaton Park (including the Mundy Play Centre) is Derby’s most visited and heavily used park and one of the most visited destinations in the East Midlands area, with a wide range of recreational and sporting facilities and attractions. In recent years, the Craft Village has gained a major uplift and all the units are occupied by different businesses. The Community Room is used as a base for a range of events and can be hired. Bookings have already been taken for two art exhibitions this year along with workshops and other public and private training and conferences. Park’s management has applied for Brown Signage at the road for the Craft Village, so more people driving nearby will know and could potentially visit.

The park retains a good reputation as a place to visit and attracts around 1.6 million visits annually while hosting over 100 different events throughout a year. Some of the key events are: • One of the biggest Park-Run in all of the East-Midlands. It attracts over 600 participants weekly. • An Outdoor Theatre & Cinema programme offers something for everyone and the 4 nights of

cinema was a sell-out in 2019 after adding an extra night. • Formerly known as “The Rose Garden” this area has been adopted by an organisation known as

‘Making Spaces’. They provide health and social care services. This is now called “Capability Garden”, created for people living with dementia, their families and carers.

Operational Aspects/Challenges: Park’s operations management span from range of different processes/functions such as maintenance of grounds and facilities, ensuring the seasonality factor on available activities for people, managing the layout of the park, smooth flow, ensuring for the craft village space to be occupied in effective way, engaging with community, capacity management, events planning and management, etc. Some of the key current and upcoming challenges for the park are, but are not limited to: 1. A-38 will be undergoing major improvements starting 2021 and is expected to complete

in 4 years. Since the park has one of the major access points through A38, the management is concerned as to,

• How to keep the Park busy and accessible? • How to ensure that Visitor’s number would not decline but rather increase? • What will be the impact of this on the businesses in Craft Village and how to

avoid/manage this impact? 2. Park has limited budget overall and thus limited budget for Marketing. What possible

ways the management can increase the Park’s visibility and engage with people of all ages and abilities?

3. Park’s management would also like to expand their knowledge as to how people access the park (bike, cycle, walk, car) and what they think about the park, such as its accessibility, facilities, and how they can be improved.

4. Park wants to attract people of all ages and abilities, for this purpose are current facilities, and attractions good enough or do they need improvement? If so what and to what outcome?

 

Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite

 

 

3

 

5. The slow decline in horticulture standards and features such as loss of herbaceous borders and bedding, indicating a reduced level of care by the Council.

Some of the continued challenges for Markeaton Park are, • To increase visitor numbers throughout the year. • To increase the income that can be used to reinvest in the park in improved

maintenance and management and provision of a higher quality of facilities. • To protect and improve the parks historic and natural fabric.

 

Please note that the above-mentioned challenges/issues are just a few to trigger your analytical thinking. You must not limit yourself to these only but utilise the knowledge gained through the course and apply different models (such as Value Chain analysis, ITO or IPO model, etc.)

A Way Forward? The Council submitted a successful bid to the Heritage Lottery Fund under the Parks for People programme http://www.hlf.org.uk/Pages/Home.aspx. The award was for a grant of around £2.4 million towards a project costing around £3.4 million and was confirmed in December 2012. The Council, therefore, spent the money on improving various aspects of the Park. Details of Markeaton Park and the improvements proposed can be found at http://www.derby.gov.uk/environment-and-planning/parks-and-open-spaces/markeaton- park/ Following this success, the Council will continue to look at other funding streams to finance future improvements.

The Council have done well to obtain past funding – only around 20% of applications for funding actually succeed. Thus, with future funding, it has to make sure that it creates a successful and sustainable leisure facility for the future.

One of the key outcomes is to increase the amount of revenue funding generated by the park that can be used to support improvements to the park’s management, maintenance and infrastructure. There are three existing important sources of revenue: car parking, the craft village rents and income from the pay to use facilities in Mundy Play Centre.

Background Information Detailed Information about Markeaton Park and the Mundy Play Centre can be found on http://www.derby.gov.uk/environment-and-planning/parks-and-open- spaces/markeaton-park/ Useful Web-sites Derby City Council website: http://www.derby.gov.uk/environment-and-planning/parks- and-open-spaces/markeaton-park/

Google Map: http://maps.google.com/maps/ms?msid=211224912365347794408.00047c6d7cb6aaad270 d6&msa=0&ll=52.934465,-1.502037&spn=0.009351,0.019205

Mundy Play Centre: http://www.derby.gov.uk/environment-and-planning/parks-and-open- spaces/markeaton-park/mundy-play-centre/

 

Greta Gridinaite
Greta Gridinaite
Greta Gridinaite
Greta Gridinaite

 

 

4

 

Survey Results from 2009

 

 

 

1. How often do you visit Markeaton Park?

121

546

1099

654

178 136 99 74 0

200 400 600 800

1000 1200

Almost every day

At least once a week

About once a month

Once in 6

months

Once a year

Less than

once a year

Never No answer

Frequency of visits

N o.

o f r

es po

nd en

ts

2. If you visit, how long would you usually stay?

275

1603

699

170 144 17

0

500

1000

1500

2000

Less than 1 hour

1-2 hours 2-4 hours All day No answer More than 1 answer

Length of visit

N o.

o f r

es po

nd en

ts

3. How do you get to Markeaton Park?

879

103

1542

82

149 147 6

Foot Cycle Car Public Transport More than 1 answer No answer Missing

 

 

 

5

 

 

4. Who would you go to Markeaton Park with?

2281

1356

492 551 101 237

0

500

1000

1500

2000

2500

Family Friends Dog On your own

Team/Club No answer

Categories of visitors

N o.

o f r

es po

nd en

ts

5. What do you tend to come to Markeaton Park for?

1566

1881

454

506

575

782

1064

981

793

1143

66

1366

913

121

641

196

266

72

1156

1349

839

394

403

234

43

104

0 500 1000 1500 2000

Relaxing

Walking

Cycling

Dog walking

Exercising

Meet Friends

Events

Picnics

Wildlife/Nature

Feed ducks

Fishing

Mundy Play Centre

Paddling Pool

Wheeled Sports

Pitch and Putt

Play Tennis

Play Football

Play Cricket

Craft Village

Café/Kiosk

Steam train

Road Train

Rowing Boats

Watch/use model boats

Other

No answer

A ct

iv iti

es

No. of respondents

These activites are removed now and new ones are introduced such as Foot Golf and Disk Golf.

 

 

 

6

 

 

 

 

11. Do you live in Derby?

2650

175

Yes No

13. How old are you?

158 102

1087 898

578

6 81

0 200 400 600 800

1000 1200

0 – 15 16 – 24 25 – 44 45 – 64 65+ More than 1

answer

No answer

Age ranges

N o.

o f r

es po

nd en

ts

Q15. Do you consider yourself to have any long- term illness, health problems or disability, which

limits daily activities or the work you can do?

376

2365

157

0

500

1000

1500

2000

2500

Yes No No answ er

Respondent response

No . o

f r es

po nd

en ts

 

 

 

7

 

Your Assignment You will produce an Individual Management Report for the Managers of Markeaton Park about certain key operational aspects of the park and project. Your focus must be on providing knowledge about the current operations of the park and suggestions to improve some of these. In particular, Section 1 (see below) is designed for you to provide the Managers of the park with knowledge about how the park currently operates and its analysis. On the other hand, in Section 2 you will provide suggestions regarding how various aspects of the park can be improved and to what outcome. Your analysis and plans need to be underpinned by relevant ‘operations theory’ to support your arguments.

1. As an initial step, you are required to investigate and discuss various operational aspects of Markeaton Park. To do this, you will: a) Identify and discuss what you may consider the most important:

• Inputs (including the transforming and transformed resources), • Transformational operation/process, • Outputs (pure products, a mixture of products and services, pure services) of the park’s

operations b) Conduct the value chain analysis and define the importance/impact of different

stakeholders. c) Identify and briefly discuss the effectiveness of the capacity planning strategy that you

consider Managers of the Markeaton Park may be following d) Identify and briefly discuss the effectiveness of the Markeaton Park layout

As part of this section, and indeed the whole assignment, you must carry out some actual field research to support the ideas presented. One important piece of information currently lacking is quantitative information on where people access the park, how they move around it, congregate and use particular areas of the park. You are required to collect some data about this in a systematic way. You are encouraged to gather this information by visiting, observing and collecting data in groups*. It would be really useful if this data could be collated so that you get a good picture of how the Park is used, How the data is used is up to individuals concerned, but you must all be involved in this. (40 % marks) *NOTE: Group work is for data collection ONLY. Analysis, development of suggestion and writing the assignment is all individual work.

2. You are required to make recommendations to improve the operations of Mundy Play Centre, Craft Village, and Markeaton Park. These recommendations could include, but are not limited to, improvements and addition of facilities, activities, and attractions. Your proposals must generate positive income for the Park. (Do not spend time writing about the things that are being done. Explore new attraction and approaches, which are not covered in their existing plans.) Your recommendations should also address the challenges mentioned in the case study description. (40 % marks)

 

3. Professional presentation and format – appropriate academic citations, references, etc. (10% marks)

 

4. Provide a 250 words description about your Skills Development during this module and specifically by undertaking the Course Work 2. (10% marks)

 

 

 

8

 

Submission Guidance Please make sure to submit your work on-time through Turnitin submission point. No late submission will be accepted. Visit www.derby.ac.uk/esub for guidance on electronic submission of assignments. Support/ Feedback During your seminars/tutorials you will receive helpful and formative Feedback (oral) from a range of stakeholders:

• from your tutors • from your fellow group members • from your Module Leader

Assessment Criteria

In completing the assessments, you need to demonstrate all of the following skills:

 Application of academic concepts to practical situations

 Critical awareness and analysis of the ideas that you explore

 Data Analysis: Demonstrates skill in data analysis and use of relevant tools

 Problem Identification: Demonstrates an understanding of Problem Definition and

reflective analysis of research.

 Synthesis and presentation of key ideas

 Evaluation of complex matters

 Integration of ideas and practice in a cross-disciplinary manner

 Presentation of key concepts/ideas Plagiarism and Collusion You are reminded that plagiarism and collusion are considered serious offences and contravene University of Derby regulations. If your work is found to be plagiarized, or it has been found that you have colluded this is likely to result in failing the minimum standards and learning outcomes within the module and further action could be taken. If you are unsure of the University regulations it is your responsibility to familiarise yourself with them. The regulations can be found in the University of Derby Academic Regulations (3R’s) at www.derby.ac.uk/academic-regulations Remember: All coursework should be submitted on time. There are no opportunities to submit work as late. Even if you submit an EEC request you should submit the coursework you have already completed to that date by the deadline. The only exception is if a student has a support plan and agreed extra time to complete work.

 

 

 

 

9

 

Undergraduate marking scale This scale applies to Levels 3, 4, 5 and 6 in the University Credit Framework. The descriptors are typical characteristics of the standard of work associated with each range of marks. The descriptors are illustrative and for guidance only. They are not comprehensive.

% Mark Mark Descriptors

70- 100%

Excellent Outstanding; high to very high standard; a high level of critical analysis and evaluation, incisive original thinking; commendable originality; exceptionally well researched; high quality presentation; exceptional clarity of ideas; excellent coherence and logic. Trivial or very minor errors. For the highest marks (90 – 100%): an exceptional standard of work illustrating thorough and in-depth understanding, communicated with exceptional authority.

Fi rs

t

60-69% Very good A very good standard; a very good level of critical analysis and evaluation; significant originality; well researched; a very good standard of presentation; commendable clarity of ideas; thoughtful and effective presentation; very good sense of coherence and logic; minor errors only. S

ec on

d D

iv 1

 

50-59% Good A good standard; a fairly good level of critical analysis and evaluation; some evidence of original thinking or originality; quite well researched; a good standard of presentation; ideas generally clear and coherent, some evidence of misunderstandings; some deficiencies in presentation. Se

co nd

D

iv 2

 

40-49% Satisfactory A sound standard of work; a fair level of critical analysis and evaluation; little evidence of original thinking or originality; adequately researched; a sound standard of presentation; ideas fairly clear and coherent, some significant misunderstandings and errors; some weakness in style or presentation but satisfactory overall.

Th ir

d

35-39% Unsatisfactory Overall marginally unsatisfactory; some sound aspects but some of the following weaknesses are evident; inadequate critical analysis and evaluation; little evidence of originality; not well researched; standard of presentation unacceptable; ideas unclear and incoherent; some significant errors and misunderstandings. Marginal fail.

M ar

gi na

l Fa

il

21-34% Poor Below the pass standard; a poor critical analysis and evaluation; virtually no evidence of originality; poorly researched; presentation unacceptable and not up to graduate standard; ideas confused and incoherent, some serious misunderstandings and errors. A clear fail, short of pass standard.

 

1-20% Very poor Well below the pass standard, with many serious errors. Standard of presentation totally unacceptable, incoherent and may be severely under- length. No evidence of evaluation or application. A very clear fail, well short of the pass standard.

Fa il NS Non-submission

No work has been submitted.

Z Academic offence notation Applies to proven instances of academic offence.

 

 

  • Module Assessment 2
  • Module Title: Applied Operations Management
    • Assessment Criteria
    • You are reminded that plagiarism and collusion are considered serious offences and contravene University of Derby regulations. If your work is found to be plagiarized, or it has been found that you have colluded this is likely to result in failing th…
 
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

Tableau Analysis And Presentation

Please get to tableau software from below link. Its free!

Please see attached file.

1st document is about tableau and need to follow each steps to complete whole lession.

2nd document is .excel which is a part of the steps mentioned in 1st document

1. Open Tableau. 2. Open the Microsoft Excel Worksheet titled Car Sales. 3. Click on the Data Source Tab. 4. Take a screenshot of the Data Source Tab showing the data. Make sure to save ALL

screenshots on a separate Word Docx. The screenshots must be turned in also. 5. Click on Sheet 1. 6. Go to the Tables section on the left and drag & drop “Sales Rep” to the Columns area on

top. 7. Go to the Table section on the left and drag & drop “Units” to the Rows area on top. 8. Take a screenshot of Sheet 1 showing the Bar Chart. 9. At this point, who is the top Sales Rep? Approximately how many sales does this Rep

have? 10. Go to the Tables section on the left and drag & drop “Region” to the Columns area on top

so that it is placed before “Sales Rep.” 11. At this point, who are the top Sales Reps in each region? 12. Click on Sheet 1 and rename it Car Sales Data_yourname 13. Take a screenshot of the renamed sheet with your name and the Bar Chart. 14. Go to the Rows area on top click the drop-down box on SUM(Units) and click Remove. 15. Take a screenshot of the Data_yourname sheet with SUM(Units) removed. 16. Go to the Table section on the left and drag & drop “Unit Price” to the Rows area on top. 17. Take a screenshot of the Bar Graph with the Data_yourname sheet showing the sums of

each Sales Rep. 18. On the right of the screen under Show Me, click on Highlight Tables. This will convert

the Bar Graph to a table shows total sales. 19. Take a screenshot of the table showing total sales. 20. At this point, who is the top Sales Rep? What is the total of their sales? 21. Undo this action by clicking the back arrow under Data on top 22. In the middle of the screen under Marks, click on Color and choose a new color for your

Bar Chart. 23. Go to File at the top of the screen and click Export As PowerPoint. Keep “Include This

View” and click Export. 24. Save as CarSales Assignment_Yourname. 25. Write a 350-Word paper that explains to your boss about the data in this export. In this

write-up explain the following: A. Sales Rep who sold the most units. B. Total sales of the leading Sales Rep. C. The top Sales Rep in each region will a 3% bonus. The top Sales Rep in all regions

will get a 5% bonus. Who are these Sales Reps and what are their bonuses? D. The type of car that sold the most. E. The type of car that sold the least. F. The region with most units sold. G. Most profitable region.

 
"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"