Case Double Olive Oil Ltd is a producer of high quality Olive oil The company buys raw Olives directly from large agricultural companies and refines them into Olive oil that it sells in the wholesale market The company has a maximum input capacity of 180 short tons of raw Olives every day The company cannot run at full capacity every day as it is required to shut down or reduce capacity for maintenance periods every year, and it experiences the occasional mechanical problem The facility is expected to run at 90 capacity over the year (or on average 180 x 90 162 short tons per day) Double Olive Oil is planning to purchase its supply of raw Olives from three primary growers, Supplier A, Supplier B, and Supplier C Purchase prices will not set until the orders are placed so Double Olive Oil will have to forecast purchase prices for the raw material and sales prices for the refined Olive oil The contract is written such that Double Olive Oil is only required to commit to 80 of total capacity up front Any amounts over that can be purchased only as required for the same price Historical prices for the last 15 years are in the table below (note that year 15 is the most current year) Marketing Year Olive Average Price Index $ short ton Oil Average Price Index $ short ton 1 63 7 287 8 2 72 4 455 3 112 642 2 4 120 634 2 5 154 761 3 6 122 702 7 160 911 8 237 2 1013 9 316 1167 3 10 342 8 1302 11 351 1376 12 362 1534 13 369 1307 4 14 375 1142 4 15 381 1204 4 Olive oil contains a number of fatty acids, some which are desirable in food products and others that are not One desirable fatty acid is oleic acid Double Olive Oil produces high oleic oil for the wholesale market and requires that the oleic acid content be a minimum of 77 Olive oil also contains trace amounts of iodine The market requires that that iodine content be a minimum of 0 78 and maximum of 0 88 The oleic acid and iodine content for the Olives from the three suppliers is given in the table below Supplier Oleic Acid Iodine A 74 0 85 B 73 0 73 C 79 0 93 Because the oleic acid and iodine content varies across the three suppliers, so does the price It is expected that the cost of supply from the suppliers will be a percentage of the market average price of Olives Supplier Cost as of Average Market Price of Olive A 75 B 80 C 82 The company faces an additional variable production cost of $4 short ton and an estimated fixed cost of $560,000 over the upcoming production period The company can later sell the crushed olives (after oil extraction) to livestock industry at 30 of the average market price of olive The company is asking you to provide a recommendation on the amount of raw material it should purchase from each supplier to minimize its cost of feedstock Management is also looking for an analysis on the profitability of the company in the next production cycle Suggested Approach This is a complex problem The following approach is suggested Use the historical price data set as input to a time series forecast model to generate forecasted prices for the average price of Olives and oil in the next production period Use standard measures of error to decide between a three period moving average model or an exponential smoothing model (with 0 2) Formulate a linear program to minimize the cost of raw Olives Use the average price of Olives forecasted from the previous step to determine supplier prices Perform a cost volume price analysis (review the handout entitledCost Volume Profit Analysisfor details) using the average cost per short ton average selling price per short ton You can generate an effective cost per short ton by dividing the total cost of supply (from the linear program) by the total volume (that you assumed in the linear program) You can generate an effective selling price per short ton from the expected percentage yields and the forecasted average price of Olive oil Because of the way that the contract is written, you can assume that the purchase of raw Olives is a variable cost (you only purchase what you require) Recall that the cost volume price analysis requires you to provide an algebraic statement of the revenue function and the cost function, and a break even analysis or chart determining the break even point Management Report Prepare a written management report presentation that includes, at a minimum, the following sections Purpose of the Report Description of the Problem Methodology (which would include the model formulation) Findings or Results Recommendations or Conclusions Be sure to address all relevant points, discuss any assumptions you are making, justify any modeling choices you have made (for example, the choice of time series forecast model), and highlight the following items in your report a forecast of the next production period's average price index for raw Olives and Olive oil a recommendation for the optimal purchasing strategy from the various suppliers, a cost volume profit analysis using for the recommended purchase strategy and the forecasted Olive oil sales price, a discussion of the risks and uncertainties that are faced by the company, and an analysis and opinion on the profitability of the company in the next production period (accounting for the expected profit or loss and the inherent risks uncertainties Remember that you are writing the report from the point of view of a consultant with senior management of Double Olive Oil Ltd as the intended audience (This is not for general public ) Solution This is what I did so far Exponential Smoothing Forecast Olive Oil Olive Oil Olive Oil Olive Oil Marketing Year Olive Average Price Index $ short ton Oil Average Price Index $ short ton Olive Oil Error Error Abs Error Abs Error Square Error Square Error Abs Percentage Error Abs Percentage Error 1 63 7 287 8 N A N A 2 72 4 455 63 7 287 8 8 7 167 2 8 7 167 2 75 69 27955 84 12 01657459 6144 140659 3 112 642 2 70 66 421 56 41 34 220 64 41 34 220 64 1708 9956 48682 0096 36 91071429 7580 506011 4 120 634 2 103 732 598 072 16 268 36 128 16 268 36 128 264 647824 1305 23238 13 55666667 205 8076922 5 154 761 3 116 7464 626 9744 37 2536 134 3256 37 2536 134 3256 1387 83071 18043 3668 24 19064935 2370 07314 6 122 702 146 54928 734 43488 24 54928 32 43488 24 54928 32 43488 602 667149 1052 02144 20 12236066 149 8606041 7 160 911 126 909856 708 486976 33 090144 202 513024 33 090144 202 513024 1094 95763 41011 5249 20 68134 4501 813929 8 237 2 1013 153 381971 870 497395 83 8180288 142 502605 83 8180288 142 502605 7025 46195 20306 9924 35 3364371 2004 638931 9 316 1167 3 220 436394 984 499479 95 5636058 182 800521 95 5636058 182 800521 9132 40275 33416 0305 30 24164739 2862 677158 10 342 8 1302 296 887279 1130 7399 45 9127212 171 260104 45 9127212 171 260104 2107 97796 29330 0233 13 39344258 2252 689961 11 351 1376 333 617456 1267 74798 17 3825442 108 252021 17 3825442 108 252021 302 152844 11718 5 4 952291804 851 6351756 12 362 1534 347 523491 1354 3496 14 4765088 179 650404 14 4765088 179 650404 209 569308 32274 2677 3 999035593 2103 928795 13 369 1307 4 359 104698 1498 06992 9 89530177 190 66992 9 89530177 190 669919 97 9169971 36355 0181 2 681653596 2780 711188 14 375 1142 4 367 02094 1345 53398 7 97906035 203 13398 7 97906035 203 133984 63 6654041 41263 4154 2 127749428 3611 993644 15 381 1204 4 373 404188 1183 0268 7 59581207 21 3732032 7 59581207 21 3732032 57 696361 456 813816 1 993651462 37 92874597 16 379 480838 1200 12536 24512 2183 15 87172961 2675 600402 MAE MSE MAPE Then I did Regression but I am not sure how to do the linear programming part of the question, what is that asking me to do Exponential Smoothing Forecast Olive Oil Olive Oil Olive Oil Olive Oil Marketing Year Olive Average Price Index $ short ton Oil Average Price Index $ short ton Olive Oil Error Error Abs Error Abs Error Square Error Square Error Abs Percentage Error Abs Percentage Error 1 63 7 287 8 N A N A 2 72 4 455 63 7 287 8 8 7 167 2 8 7 167 2 75 69 27955 84 12 01657459 6144 140659 3 112 642 2 70 66 421 56 41 34 220 64 41 34 220 64 1708 9956 48682 0096 36 91071429 7580 506011 4 120 634 2 103 732 598 072 16 268 36 128 16 268 36 128 264 647824 1305 23238 13 55666667 205 8076922 5 154 761 3 116 7464 626 9744 37 2536 134 3256 37 2536 134 3256 1387 83071 18043 3668 24 19064935 2370 07314 6 122 702 146 54928 734 43488 24 54928 32 43488 24 54928 32 43488 602 667149 1052 02144 20 12236066 149 8606041 7 160 911 126 909856 708 486976 33 090144 202 513024 33 090144 202 513024 1094 95763 41011 5249 20 68134 4501 813929 8 237 2 1013 153 381971 870 497395 83 8180288 142 502605 83 8180288 142 502605 7025 46195 20306 9924 35 3364371 2004 638931 9 316 1167 3 220 436394 984 499479 95 5636058 182 800521 95 5636058 182 800521 9132 40275 33416 0305 30 24164739 2862 677158 10 342 8 1302 296 887279 1130 7399 45 9127212 171 260104 45 9127212 171 260104 2107 97796 29330 0233 13 39344258 2252 689961 11 351 1376 333 617456 1267 74798 17 3825442 108 252021 17 3825442 108 252021 302 152844 11718 5 4 952291804 851 6351756 12 362 1534 347 523491 1354 3496 14 4765088 179 650404 14 4765088 179 650404 209 569308 32274 2677 3 999035593 2103 928795 13 369 1307 4 359 104698 1498 06992 9 89530177 190 66992 9 89530177 190 669919 97 9169971 36355 0181 2 681653596 2780 711188 14 375 1142 4 367 02094 1345 53398 7 97906035 203 13398 7 97906035 203 133984 63 6654041 41263 4154 2 127749428 3611 993644 15 381 1204 4 373 404188 1183 0268 7 59581207 21 3732032 7 59581207 21 3732032 57 696361 456 813816 1 993651462 37 92874597 16 379 480838 1200 12536 24512 2183 15 87172961 2675 600402 MAE MSE MAPE

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 01, 2024

Case: Double Olive Oil. Ltd. is a producer of high-quality Olive oil. The company buys raw Olives directly from large agricultural companies and refines them

Case:

Double Olive Oil. Ltd. is a producer of high-quality Olive oil. The company buys raw Olives directly from large agricultural companies and refines them into Olive oil that it sells in the wholesale market.

The company has a maximum input capacity of 180 short tons of raw Olives every day. The company cannot run at full capacity every day as it is required to shut down or reduce capacity for maintenance periods every year, and it experiences the occasional mechanical problem. The facility is expected to run at 90% capacity over the year (or on average 180 x 90% = 162 short tons per day).

Double Olive Oil is planning to purchase its supply of raw Olives from three primary growers, Supplier A, Supplier B, and Supplier C. Purchase prices will not set until the orders are placed so Double Olive Oil will have to forecast purchase prices for the raw material and sales prices for the refined Olive oil. The contract is written such that Double Olive Oil is only required to commit to 80% of total capacity up front. Any amounts over that can be purchased only as required for the same price. Historical prices for the last 15 years are in the table below (note that year 15 is the most current year).

Marketing Year	Olive Average Price Index $/short ton	Oil Average Price Index $/short ton
1	63.7	287.8
2	72.4	455
3	112	642.2
4	120	634.2
5	154	761.3
6	122	702
7	160	911
8	237.2	1013
9	316	1167.3
10	342.8	1302
11	351	1376
12	362	1534
13	369	1307.4
14	375	1142.4
15	381	1204.4

Olive oil contains a number of fatty acids, some which are desirable in food products and others that are not. One desirable fatty acid is oleic acid. Double Olive Oil produces high oleic oil for the wholesale market and requires that the oleic acid content be a minimum of 77%. Olive oil also contains trace amounts of iodine. The market requires that that iodine content be a minimum of 0.78% and maximum of 0.88%

The oleic acid and iodine content for the Olives from the three suppliers is given in the table below.

Supplier	Oleic Acid	Iodine
A	74%	0.85%
B	73%	0.73%
C	79%	0.93%

Because the oleic acid and iodine content varies across the three suppliers, so does the price. It is expected that the cost of supply from the suppliers will be a percentage of the market average price of Olives.

Supplier	Cost as % of Average Market Price of Olive
A	75%
B	80%
C	82%

The company faces an additional variable production cost of $4/short ton and an estimated fixed cost of $560,000 over the upcoming production period.

The company can later sell the crushed olives (after oil extraction) to livestock industry at 30% of the average market price of olive.

The company is asking you to provide a recommendation on the amount of raw material it should purchase from each supplier to minimize its cost of feedstock.

Management is also looking for an analysis on the profitability of the company in the next production cycle.

Suggested Approach

This is a complex problem. The following approach is suggested:

Use the historical price data set as input to a time series forecast model to generate forecasted prices for the average price of Olives and oil in the next production period. Use standard measures of error to decide between a three-period moving average model or an exponential smoothing model (with = 0.2).
Formulate a linear program to minimize the cost of raw Olives. Use the average price of Olives forecasted from the previous step to determine supplier prices.
Perform a cost-volume-price analysis (review the handout entitledCost-Volume-Profit Analysisfor details) using the average cost per short ton average selling price per short ton.
- You can generate an effective cost per short ton by dividing the total cost of supply (from the linear program) by the total volume (that you assumed in the linear program).
- You can generate an effective selling price per short ton from the expected percentage yields and the forecasted average price of Olive oil.
- Because of the way that the contract is written, you can assume that the purchase of raw Olives is a variable cost (you only purchase what you require).

Recall that the cost-volume-price analysis requires you to provide

an algebraic statement of the revenue function and the cost function, and
a break-even analysis or chart determining the break-even point

Management Report

Prepare a written management report/presentation that includes, at a minimum, the following sections:

Purpose of the Report
Description of the Problem
Methodology (which would include the model formulation)
Findings or Results
Recommendations or Conclusions

Be sure to address all relevant points, discuss any assumptions you are making, justify any modeling choices you have made (for example, the choice of time series forecast model), and highlight the following items in your report:

a forecast of the next production period's average price index for raw Olives and Olive oil.
a recommendation for the optimal purchasing strategy from the various suppliers,
a cost-volume-profit analysis using for the recommended purchase strategy and the forecasted Olive oil sales price,
a discussion of the risks and uncertainties that are faced by the company, and
an analysis and opinion on the profitability of the company in the next production period (accounting for the expected profit or loss and the inherent risks/uncertainties.

Remember that you are writing the report from the point of view of a consultant with senior management of Double Olive Oil. Ltd. as the intended audience. (This is not for general public!)

Solution

This is what I did so far:

Exponential Smoothing


			Forecast	Olive	Oil	Olive	Oil	Olive	Oil	Olive	Oil
Marketing Year	Olive Average Price Index $/short ton	Oil Average Price Index $/short ton	Olive	Oil	Error	Error	Abs. Error	Abs. Error	Square Error	Square Error	Abs. Percentage Error	Abs. Percentage Error
1	63.7	287.8	#N/A	#N/A
2	72.4	455	63.7	287.8	8.7	167.2	8.7	167.2	75.69	27955.84	12.01657459	6144.140659
3	112	642.2	70.66	421.56	41.34	220.64	41.34	220.64	1708.9956	48682.0096	36.91071429	7580.506011
4	120	634.2	103.732	598.072	16.268	36.128	16.268	36.128	264.647824	1305.23238	13.55666667	205.8076922
5	154	761.3	116.7464	626.9744	37.2536	134.3256	37.2536	134.3256	1387.83071	18043.3668	24.19064935	2370.07314
6	122	702	146.54928	734.43488	-24.54928	-32.43488	24.54928	32.43488	602.667149	1052.02144	20.12236066	149.8606041
7	160	911	126.909856	708.486976	33.090144	202.513024	33.090144	202.513024	1094.95763	41011.5249	20.68134	4501.813929
8	237.2	1013	153.381971	870.497395	83.8180288	142.502605	83.8180288	142.502605	7025.46195	20306.9924	35.3364371	2004.638931
9	316	1167.3	220.436394	984.499479	95.5636058	182.800521	95.5636058	182.800521	9132.40275	33416.0305	30.24164739	2862.677158
10	342.8	1302	296.887279	1130.7399	45.9127212	171.260104	45.9127212	171.260104	2107.97796	29330.0233	13.39344258	2252.689961
11	351	1376	333.617456	1267.74798	17.3825442	108.252021	17.3825442	108.252021	302.152844	11718.5	4.952291804	851.6351756
12	362	1534	347.523491	1354.3496	14.4765088	179.650404	14.4765088	179.650404	209.569308	32274.2677	3.999035593	2103.928795
13	369	1307.4	359.104698	1498.06992	9.89530177	-190.66992	9.89530177	190.669919	97.9169971	36355.0181	2.681653596	2780.711188
14	375	1142.4	367.02094	1345.53398	7.97906035	-203.13398	7.97906035	203.133984	63.6654041	41263.4154	2.127749428	3611.993644
15	381	1204.4	373.404188	1183.0268	7.59581207	21.3732032	7.59581207	21.3732032	57.696361	456.813816	1.993651462	37.92874597
16			379.480838	1200.12536

										24512.2183	15.87172961	2675.600402
										MAE	MSE	MAPE

Then I did Regression but I am not sure how to do the linear programming part of the question, what is that asking me to do.

Exponential Smoothing


			Forecast	Olive	Oil	Olive	Oil	Olive	Oil	Olive	Oil
Marketing Year	Olive Average Price Index $/short ton	Oil Average Price Index $/short ton	Olive	Oil	Error	Error	Abs. Error	Abs. Error	Square Error	Square Error	Abs. Percentage Error	Abs. Percentage Error
1	63.7	287.8	#N/A	#N/A
2	72.4	455	63.7	287.8	8.7	167.2	8.7	167.2	75.69	27955.84	12.01657459	6144.140659
3	112	642.2	70.66	421.56	41.34	220.64	41.34	220.64	1708.9956	48682.0096	36.91071429	7580.506011
4	120	634.2	103.732	598.072	16.268	36.128	16.268	36.128	264.647824	1305.23238	13.55666667	205.8076922
5	154	761.3	116.7464	626.9744	37.2536	134.3256	37.2536	134.3256	1387.83071	18043.3668	24.19064935	2370.07314
6	122	702	146.54928	734.43488	-24.54928	-32.43488	24.54928	32.43488	602.667149	1052.02144	20.12236066	149.8606041
7	160	911	126.909856	708.486976	33.090144	202.513024	33.090144	202.513024	1094.95763	41011.5249	20.68134	4501.813929
8	237.2	1013	153.381971	870.497395	83.8180288	142.502605	83.8180288	142.502605	7025.46195	20306.9924	35.3364371	2004.638931
9	316	1167.3	220.436394	984.499479	95.5636058	182.800521	95.5636058	182.800521	9132.40275	33416.0305	30.24164739	2862.677158
10	342.8	1302	296.887279	1130.7399	45.9127212	171.260104	45.9127212	171.260104	2107.97796	29330.0233	13.39344258	2252.689961
11	351	1376	333.617456	1267.74798	17.3825442	108.252021	17.3825442	108.252021	302.152844	11718.5	4.952291804	851.6351756
12	362	1534	347.523491	1354.3496	14.4765088	179.650404	14.4765088	179.650404	209.569308	32274.2677	3.999035593	2103.928795
13	369	1307.4	359.104698	1498.06992	9.89530177	-190.66992	9.89530177	190.669919	97.9169971	36355.0181	2.681653596	2780.711188
14	375	1142.4	367.02094	1345.53398	7.97906035	-203.13398	7.97906035	203.133984	63.6654041	41263.4154	2.127749428	3611.993644
15	381	1204.4	373.404188	1183.0268	7.59581207	21.3732032	7.59581207	21.3732032	57.696361	456.813816	1.993651462	37.92874597
16			379.480838	1200.12536

										24512.2183	15.87172961	2675.600402
										MAE	MSE	MAPE