TourneSol Canada: Raw Material Purchase Analysis & Profitability Forecast

Scenario

Scenario

TourneSol Canada, Ltd. is a producer of high quality sunflower oil. The company buys raw sunflower seeds directly from large agricultural companies, and refines the seeds into sunflower oil that it sells in the wholesale market. As a by-product, the company also produces sunflower mash (a paste made from the remains of crushed sunflower seeds) that it sells into the market as base product for animal feed.

The company has a maximum input capacity of 150 short tons of raw sunflower seeds every day (or 54,750 short tons per year). Of course 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 150 x 90% = 135 short tons per day).

TourneSol is planning to purchase its supply of raw sunflower seeds from three primary growers, Supplier A, Supplier B, and Supplier C. Purchase prices will not set until the orders are actually placed so TourneSol will have to forecast purchase prices for the raw material and sales prices for the refined sunflower oil and mash. The contract is written such that TourneSol is only required to commit to 70% 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).

Historical Price Data
Marketing Year	Seed Average Price Index $/short ton	Oil Average Price Index $/short ton	Mash Average Price Index $/short ton
1	127.7	317.8	63
2	192.4	465	87
3	242	662.2	105
4	242	668.2	111
5	274	791.3	124
6	242	732	108
7	290	951	134
8	347.2	1123	153
9	436	1297.3	193
10	422.8	1312	187
11	466	1416	193
12	582	1664	247
13	508	1317.4	242
14	428	1182.4	197
15	434	1334.4	210

Sunflower 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. TourneSol produces high oleic oil for the wholesale market, and requires that the oleic acid content be a minimum of 77%. Sunflower 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 sunflower seeds from the three suppliers is given in the table below.

Supplier	Oleic Acid	Iodine
A	72%	0.95%
B	82%	0.85%
C	65%	0.72%

For all three suppliers, it is expected that the average yield of oil from the seeds is 30%.  There is no net loss of material, so the yield of mash from the same supply is expected to be 70%.

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 seeds.

Supplier	Cost as % of Average Market Price of Seed
A	85%
B	100%
C	90%

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

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 fairly complex problem. The following approach is suggested:

Use the historical price data set as input to a time series forecast model in order to generate forecasted prices for the average price of sunflower seeds, oil, and mash 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). Use the type of model for all three time series forecasts. That is, if you decide to use the moving average model, use a three-period moving average model to fit the relevant data for all three series. Don’t use the moving average for one time series and the exponential smoothing model for another time series.

Formulate a linear program to minimize the cost of raw sunflower seeds.  Use the average price of seeds forecasted from the previous step in order to determine supplier prices.

Perform a cost-volume-price analysis (review the handout entitled Cost- volume- profit Amalysis( please read the last couple of pages) for details using the average cost per short ton average selling price per short ton

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

a.an algebraic statement of the revenue function and the cost function,

b.a detailed break-even chart that includes lines for the revenue and for the total cost, fixed cost, and variable cost (a total of four lines), and
c.a calculation break-even point expressed in number of short tons and percent of capacity.

Scenario

When it comes to product production and business, it is always prudent to monitor every step of production as well as be able to:

1. Minimize the cost of production
2. Forecast the market behavior

• And, trace the performance of the company

The purpose of this report is to analyze the performance of TourneSol Company using historical data so as to be able to forecast factors such as average purchase prices of the production inputs in the coming year i.e. marketing year 16. In addition be able to offer recommendation as to which supplier to purchase from which % of input so as to minimize the feedstock cost. The paper uses the Anderson et al (2015) descriptive, predictive and analytics text for application in excel solver where applicable to enable data-driven decision analysis and making.

In the production of Oil by the company, the maximum amount of iodine required is 0.88% and the minimum is 0.78% while the minimum Oleic acid that should be in the product should be 77%. Each supplier has a different kind of raw material that has different concentrations of the required contents. The task is to determine at which percentage of raw material the company should purchase from each supplier to ensure minimum cost of feedstock while attaining the required content for quality oil.

In the preparation of cost-volume-profit data, entries such as taxes are obtained from government source so as to enable an almost real-life business situation (they are subject to change).

Forecasting

The method used for forecasting in this report is the exponential smoothening. According to Otext (2017), “…all future forecasts are equal to the last observed value.”

Hence:

yT+h|T=yT

Where h= 1, 2, 3… which is the naïve method that supposes that only the latest observation is the most important. In exponential forecasting each forecast is solved through applying weighted averages such that they decrease in an exponential manner as the historical data gets older (Makridakis and Hibon, 2013).

yT+1|T=αyT+α(1−α)yT−1+α(1−α)2yT−2+?

According to Casey (2018) the triple exponential smoothening takes the form:

I_t = Β x_t/SS_t + (1-Β)I_t-L+m

Where:

• x = observation,
• SS = smoothed observation,
• B = trend factor
• I = seasonal index,
• F = forecast m periods ahead,
• t = time period.
  Forecasting data

The forecasting data is extracted from the company’s historical data for the past 15 marketing years with the 15^th year being the current year.

Linear Programming

In linear programming, we have 2 variables i.e. x and y with 3 constraints such that:

Where X denotes amount of Iodine present in each supplier’s raw material and Y is the amount of Oleic acid available.

So as to obtain the ratios that the company has to purchase from each supplier to minimize the feedstock cost.

From the initial problem, the least amount of iodine that should be available in the product is 0.78% while that of Oleic acid is 77%. Supplier C has 0.72% iodine and 65% Oleic acid, contents that are less than the least required by the company’s product.

Forecasting

Figure 1

Figure 2

Figure 3

Figure 4

Figure 5

Figure 6

Price forecasting

From the results above, the forecasted average price for raw sunflower seed in the marketing year 16 is $428 (figure 1), while that of oil is $1245 (figure 2) and that of mash is $195 (figure 3). The forecast results indicate an increase in all of the inputs per short ton where raw sunflower seed record an increment of $1, mash increase by $4 and sunflower oil increase by $2.

Suggested Approach

Cost-volume-profit analysis 

In order to ensure that the company produces the recommended quality products for the market while reducing the variable costs as well as feedstock costs, it would be suitable to of all the $50,174,851 (figure 4) cost worth of supplies that is to be made to consider rationing the purchases between suppliers A, B, and C such that each supplier makes their supply with the following proportions:

	Optimum Volume ratio to be purchased
A	25.33197
B	29.80232
C	26.82209

 As such, the suppliers will supply goods worth:

A- $16,594,191.8

B- $19,522,578.6

C -  $17,570,320.8

Given the above strategy, the company will then be able to make a profit of $11,913,777.20

If the company supplies a total volume of 60000 tons in year 16 at an average sales unit price of $749.1202 and $ 11,195,358.72 if it supplies total volume of 56,000 tons, however, if the company runs at a capacity of 90% producing almost 48,000 tons, it will be able to make sales worth $ 35,957,760.00 and make a profit of $ 9,758,521.76 after all deductions are made which include taxes and other production costs.

Risks and uncertainties

The above figures are from an ideal production environment during the marketing year in everyday operations; however, the company has to face variances in most of their departments. Factors such as industrial actions, environmental hazards, change in government policies, termination of contracts by suppliers etcetera may cause a shift in the sales-profit graph such that negative effects will lead to the shift of the profit graph to the right as well as the sales graph leading to a decrease in both assuming the fixed costs and variable costs remain constant. The above risks and uncertainties are likely to cause wavering in the profits projected hence it would be crucial to account for them in the regression model so as to project up to what percentage they are likely to affect the company performance. 

Analysis and opinion on the profitability

Despite a slight increase in the input prices, given the projected 90% running capacity of the company producing an estimated 150 short tons a day the company is set to make profits of up to: $8,321,684.80 if the company produces at least 40,000 short tons a year. Therefore the company will be able to cover all the fixed costs, varying costs such as government taxes and insurance. The projected increase in profits is largely accounted by the optimal purchase from all the three suppliers hence ensuring minimal feedstock cost.

Conclusion

In conclusion, the company’s projected performance is dependent on a wide range of factors both internal and external i.e. those beyond the company’s control. However after considering internal factors in an ideal condition and the already known external such as government taxes, the company is set for a relatively good production year 16.

To ensure better projection of the company’s performance, the following three recommendations are made for consideration to the executive:

1. The executive should seek suppliers whose raw inputs have relatively higher concentrations of Oleic acid, this will ensure minimal requirement of many suppliers
2. The company should consider insuring against risks that may lead to bad losses since it is not always that risks can be included in a forecasting model hence may go undetected

• The company should purchasing more plant power so as to increase the production capacity of the company to at least 60,000 short tons a year so as to increase both sales and profits.

Anderson, D. R., Sweeney, D. J., Williams, T. A., Camm, J. D., Cochran, J. J., Fry, M., &

Ohlmann, J. (2015). Quantitative methods for business. (13th ed.). Mason, OH: Cengage

Learning.

Otext. (2017). Forecasting : Principles and Practice. Retrieved from:

https://otexts.org/fpp2/ses.html 

Makridakis, S. & Hibon, M. (2013). Accuracy of Forecasting: An Empirical Investigation. The

European Institute of Business Administration, 142(2), pp 97-145

Casey,M. (2017). Difference between simple exponential moving averages. Retrieved from:

https://www.investopedia.com/ask/answers/difference-between-simple-exponential-moving-average/