I present a cost-volume-profit analysis and a break-even analysis for your organization. You are purchasing the raw sunflower seeds from the three producers A, B and C. The quality of the Sunflower seeds of A, B and C are different with different percentages of fatty acid and iodine content. Oleic acid a type of fatty acid is a very important factor in the quality of Sunflower Oil. The higher the quantity of oleic acid the better is the quality of Sunflower oil. Another important constituent of Sunflower seeds is iodine. The lower the quantity of iodine, the better the quality of Sunflower oil. Thus a balance between oleic acid and iodine has to be done.

The sunflower seeds are crushed to express the oils.  The oil is directly sold to the wholesale market. The by-product of the crushing is mash. The mash produced is sold as animal feed.

Here first a Forecasting has been done. The purpose of the forecast analysis was to predict the future price / short ton of Sunflower seeds. For the forecast analysis a 15 year data was used. The methods for forecasting was a three period moving average and exponential smoothing (a = 0.2). 

Next a linear model has been developed to calculate the quantity of sunflower seeds that should be mixed to get the proper quality of sunflower oil. Since the sunflower oil should contain a minimum  of 77% of oleic acid and between 0.78 to 0.88% of iodine. With the help of the linear model I developed a ratio in which the sunflower seeds, of the supplier’s A, B and C should be mixed to get the proper quality of sunflower oil.

The ratio found from the linear model was used in the cost-volume-profit analysis. First the revenue of your organization was found. The factor’s considered was fixed cost, variable cost and the mix of the purchase price of sunflower seeds. The purpose of the cost-volume-profit analysis is to optimize the cost and volume of the sunflower seeds being purchased from the three organizations.

A break-even analysis has also been done. And thus the profitability of your company has also been calculated.

I present also a discussion on the risks and uncertainties.

The case at hand assumes that you will be purchasing the sunflower seeds from the three suppliers A, B and C. Before you purchase the seeds the price of the seeds for the next season is to be forecast. Hence the first problem is to forecast the price of sunflower seeds.

The seeds will have to be purchased from three organizations since the quality of your product depends on a mix of the sunflower seeds of the three suppliers. Thus a ratio of the mix of the sunflower seeds from the three suppliers is to be calculated. The second problem is to find the ratio in which the sunflower seeds, is to be mixed. This also defines the ratio in which the sunflower seeds, is to be purchased from the three suppliers.

The price of the sunflower seeds, of the three suppliers, varies as a percentage of the average market price (which is also the forecasted price). Third problem is to calculate a detailed cost-volume-profit analysis. From the cost-volume-profit analysis a break-even analysis has also to be calculated.

Last some insights into the risks and uncertainties is being discussed.

In order to generate a model I first considered the standard error of forecast price for the sunflower seed, oil and mash. I compared the standard error of the moving average model (three period) and the exponential smoothing model (with a = 0.2). The mean absolute error (MAE) of the forecast was considered. Since the MAE of the moving average method was lower for Sunflower seed hence for all the three variables the moving average method was used.

Next a linear model was used to calculate the quantity of sunflower seed to be bought from each supplier. This was necessitated since the quality factors of sunflower oil depends on Oleic Acid (a fatty acid) and iodine. Supplier B’s seeds had a high quantity of both Oleic Acid and iodine both. While both supplier’s A and C supplied seeds with lower quantity of oleic acid than required. The iodine content of supplier A was very high and that of supplier C was much lower than required. Thus a mix of all the three will have to be used.

Table 1: A comparative study of ingredients with price

We calculated the amount of sunflower seed in the final mix as P, Q and R amounts from supplier A, B and C respectively.

Thus the linear model used was

0.72P + 0.82Q + 0.65R ≥ 0.77

0.0095P + 0.0085Q + 0.0072R ≥ 0.0078

0.0095P + 0.0085Q + 0.0072R ≥ 0.0088

The decision variable used was to minimize the cost of input (z).

Thus z = 388.45P + 457Q + 411.3R was used as the decision variable.

The result from the decision variable was used for the cost-volume-price analysis.

The break-even analysis was also done.

The analysis of the forecast value of the sunflower seeds, mash and oil are presented below.

Table 2: Forecast Analysis

Since the Moving average error for sunflower seed is lower hence the forecast price for sunflower seed, oil and mash using the MAE method has been used.

Hence the average forecast price of sunflower seed is $ 457.00.

Thus the average forecast price of sunflower price of the three suppliers is:

Table 3: Forecast price of the three suppliers

Using the above forecast price and quality on oleic acid and iodine present in sunflowers seeds of the three suppliers the ratio in which the sunflower seeds is to be mixed has been calculated. The linear method has been used to find the ratio. The ratio in which the sunflower seeds has to be mixed is:

Table 4: Ratio of mix of the sunflower seeds

From the above ratio of the mix, the forecasted quantity of the sales, the requirement / year, the cost-volume-profit analysis is done.

Accordingly I present the cost-volume-profit analysis

Hence I can say that the expected income / short ton will be $ 61.25 taking into consideration that you will be using 150 short tons / day.

The break-even analysis is also calculated. The break-even analysis shows that the for break-even you will need to get 34145 short tons / year or 94 short tons / day.    

Figure 1 : Graph of Break-even analysis.

Since you can run at 90% capacity hence the break-even value for 90% of your capacity is 104 short tons / day or 37939 short tons / year.

The above calculations have been done taking into consideration that you will be utilizing 150 short ton of sunflower seeds / per day.  The first risk considered is that the quality of sunflower seeds of the three suppliers A, B and C are different. All the above calculations are based on the data provided regarding the quality of the sunflower seeds. If the quality of the sunflower seeds vary then the calculations will not be true. The second risk is that for every 1 short ton of sunflower seeds you get 0.3 sunflower oil and 0.7 sunflower mash. The cost of sunflower oil is more and that of sunflower mash is less. The calculations as well as the break-even analysis have been based on the fact that the whole produce of sunflower oil and mash will be sold. If the ratio in which the sunflower oil and sunflower mash is not sold then the revenue of your organization will change. Since the price of sunflower oil is very high hence if the required quantity of sunflower is not sold then the revenue will dip.

It can be concluded that the quality of sunflower seeds is very important factor. It is important for the three suppliers to maintain their quality. Taking into consideration that the quality of sunflower seeds will be maintained I have suggested an optimum purchasing ratio from the three suppliers. I have also analyzed the cost-volume-profit (CVP) ratio. From the CVP I have calculated the break-even.  The break-even takes into account that you will be able to sell the whole produce of oil and mash produced from the seeds. The price of oil is much higher than that of the mash. Hence if you are not able to sell the whole quantity of sunflower oil you will not be able to generate revenue. Thus the break-even will not be valid.

At 90% optimum capacity the break-even will increase by 10%.  At your maximum input capacity of 150 short tons / day you can expect to have revenue of $ 61.25 / short tons. 

Anderson, D. R., Sweeney, D. J., & Williams, T. A. (2014). Modern Business Statistics with Microsoft Excel. Cengage Learning.

Brennan, R., Canning, L., & McDowell, R. (2014). Business-to-Business Marketing. SAGE Publications.

Brown, S., Bessant, J. R., & Lamming, R. (2013). Strategic Operations Management. Routledge.

Cafferky, M., & Wentworth, J. (2014). Breakeven Analysis: The Definitive Guide to Cost-Volume-Profit Analysis, Second Edition. Business Expert Press.

Camm, J. D., Cochran, J. J., Fry, M. J., Ohlmann, J. W., & Anderson, D. R. (2014). Essentials of Business Analytics. Cengage Learning.

Carlberg, C. (2012). Predictive Analytics: Microsoft Excel. Pearson Education.

Carlberg, C. (2016). Excel Sales Forecasting For Dummies. Wiley.

Ferrari, A., & Russo, M. (2013). Microsoft Excel 2013 Building Data Models with PowerPivot: Building Data Models with PowerPivot. Pearson Education.

Ficken, F. A. (2015). The Simplex Method of Linear Programming. Dover Publications.

Force, E. M., & Salas, J. J. (2015). Sunflower: Chemistry, Production, Processing, and Utilization. American Oil Chemists’ Society.

Gottlieb, I. (2013). Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS). Wiley.

Hillier, F. S., & Lieberman, G. J. (2015). Introduction to Operations Research. McGraw-Hill.

Jelen, B. (2013a). Excel 2013 Charts and Graphs. Pearson Education.

Jelen, B. (2013b). Excel 2013 In Depth. Pearson Education.

Kolman, B., Beck, R. E., & Rheinboldt, W. (2014). Elementary Linear Programming with Applications. Elsevier Science.

Mowen, M. M., Hansen, D. R., & Heitger, D. L. (2013). Cornerstones of Managerial Accounting. Cengage Learning.

 Ord, K., & Fildes, R. (2013). Principles of Business Forecasting. Cengage Learning.

Ragsdale, C. (2014). Spreadsheet Modeling and Decision Analysis: A Practical Introduction to Business Analytics. Cengage Learning.

Salkind, N. J. (2015). Excel Statistics: A Quick Guide. SAGE Publications.

Warren, C. S., Reeve, J. M., & Duchac, J. (2015). Financial & Managerial Accounting. Cengage Learning.