Business Analytics and Data Intelligence: XYZ Yoghurt Linear Program

This project applies linear programming to analyze the operations of XYZ Yoghurt Company, focusing on both the optimization of yoghurt varieties' production and the selection of new yoghurt-producing machines. The analysis begins with defining decision variables, formulating objective functions to maximize profit, and establishing constraints based on resource availability (milk, sugar, and machine capacity). The model determines the optimal number of cups for each yoghurt variety (plain, single flavor, and mixed flavor) to produce per machine per hour, maximizing the hourly profit. The second part of the project focuses on optimizing the selection of new machines (Type A and Type B) to replace the existing ones, considering power consumption, de-clogging costs, and maintenance costs, to minimize the overall weekly maintenance costs. The results provide actionable recommendations for XYZ Yoghurt, including optimal production quantities and the ideal mix of new machines to purchase, ultimately aiming to improve the company's market position and profitability. The project includes detailed model implementations, results tables, sensitivity analyses, and recommendations for the company's strategic decisions.

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
Contents
INTRODUCTION.......................................................................................................................................3
PROBLEM DEFINITION...........................................................................................................................4
MODEL DESIGNS.....................................................................................................................................5
MODEL DESIGN FOR AMOUNT OF YOGHURT VARITIES’ PRODUCED....................................5
FORMULATION OF LINEAR PROGRAMMING PROBLEM........................................................5
DEFINITION OF DECISION VARIABLES......................................................................................6
DEFINITION OF OBJECTIVE FUNCTION......................................................................................7
DEFINITION CONSTRAINS.............................................................................................................7
MODEL IMPLIMENTATION............................................................................................................7
RESULTS............................................................................................................................................9
MODEL DESIGN FOR YOGHURT PRODUCING MACHINES.......................................................10
FORMULATION OF LINEAR PROGRAMMING PROBLEM......................................................10
DEFINITION OF DECISION VARIABLES....................................................................................11
DEFINITION OF OBJECTIVE FUNCTION....................................................................................11
DEFINITION CONSTRAINS...........................................................................................................11
MODEL IMPLIMENTATION..........................................................................................................12
RESULTS..........................................................................................................................................13
RECOMMENDATIONS AND CONCLUSIONS.....................................................................................14
REFERENCES..........................................................................................................................................16
APPENDICES: EXCEL OUTPUT............................................................................................................18
2

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
INTRODUCTION
Two main types of factors affect company performances; external and internal. External
factors refer to the aspects of business that a company cannot control such as price of raw
materials and tax rates (Kiechel, 2010; Besanko, Dranove, & Shanley, 2012). Internal factors on
the other hand refer to aspects of business that a company can control such as production rate
(Gamble, Thompson, Strickland, & John, 2010; Tang, 2014).
In order to maintain or improve profitability, a company has to manipulate the internal
factors, since these are the only factors within their control. The manipulation of the of the
internal factors is also essential in reducing the operation costs and thereby avoiding the negative
impacts of the external factors on a company’s performance (Sirajuddin, Ibrahim, & Jamali,
2017). Maintaining or improving profitability and reducing cost of operations are vital elements
of any company that intends to continue doing business (Laudon & Guercio, 2014).
Statistical data analysis refers to the application of mathematical concepts in drawing of
inferences and conclusions about attributes of a dataset (Witten, 2011; Oscar, 2009). Statistical
data analysis is an important tool in the solving of business problems (Galit, Peter, Inbal, Patel,
& Kenneth, 2018; Karolin & Schrape, 2018). Through statistical data analysis, alternatives are
made available for decision makers in business for optimal results (Vicenc, 2017; Shaffer, 2011).
Linear programming is an example of a statistical data analysis method.
Linear programming is a statistical data analysis method that is used in determination of
optimal conditions for processes (Everitt & Skrondal, 2010; Barbara & Susan, 2014). Optimal
conditions may either refer to conditions that maximize an aspect of a process or conditions the
3

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
minimize an aspect of a process (Freedman, 2009). In business terms, optimal conditions would
refer to either conditions that maximize profit or conditions that minimize cost (Aalst & Wil,
2011).
The XYZ Yoghurt Company is a yoghurt producing company based in Melbourne,
Australia. The company produces three main types of yoghurt varieties; plain yoghurt, single
flavor yoghurt and mixed flavor yoghurt. Due to increased competition as a result of new market
entries, the XYZ Yoghurt Company board is interested in maintaining and improving the
company’s position in the market by exploring internal options for improving its performance.
This research paper applies the use of the linear programming statistical analysis method
in analyzing the XYZ Yoghurt Company’s operations data. Through the analysis, inferences are
drawn to provide the company’s board with actionable recommendations for maintaining the
company’s position in the market.
PROBLEM DEFINITION
Despite XYZ Yoghurt Company being an established company, the new market entries
have resulted in an increased competition in the market by offering competitive market entry
prices. The company is therefore interested in exploring internal options that would enable it to
maintain its market position until the competitive market entry prices are abandoned by the new
market entries. Thereafter, the company is interested in implementing strategies for improving it
market position
4

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
In order to maintain its market position, the company intends to improve the efficiency of
the yoghurt varieties’ production until the competitive market entry prices are abandoned by the
new market entries. Through the optimizing of the yoghurt varieties’ production operation per
machine, the company aims at maintaining and improving the profit margins.
In order to improve its market position, the company intends to replace all its yoghurt
producing machines with two new models. The optimization of the new yoghurt producing
machines’ operations is aimed at regulating the maintenance cost margins for the new model
machines. The optimization of the yoghurt producing machines’ operations will involve
determining the number of each of the two models to be brought in to replace the old machines.
MODEL DESIGNS
MODEL DESIGN FOR AMOUNT OF YOGHURT VARITIES’ PRODUCED
FORMULATION OF LINEAR PROGRAMMING PROBLEM
The XYZ Yoghurt Company has 16 machines for producing the three varieties of
yoghurt; plain yoghurt, single flavor yoghurt and mixed flavor yoghurt. The company strictly
produces its yoghurt in 500ml quantities (cups) as a product differentiation technique illustrated
by the company’s product tagline “YOUR BETTER HALF”. The company operates for a total
of 8 hours daily with a minimum of 50 cups of each variety per machine production in every
hour. The profits per cup of yoghurt are $0.3, $0.5 and $0.7 for plain yoghurt, single flavor
yoghurt and mixed flavor yoghurt respectively.
5

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
The plain yoghurt requires 400ml of milk and 5.7 grams of sugar to produce. The single
flavor yoghurt requires 370ml and 6.4 grams of sugar to produce. The mixed flavor yoghurt
requires 250ml of milk and 7.8 grams of sugar to produce. The XYZ Yoghurt Company has a
budget for 10240 liters of milk and 11 kilograms of sugar daily. This is as illustrated in Table 1:
Linear Programming Problem Summary below:
Table 1: Linear Programming Problem Summary
Plain Yoghurt Single Flavor Mixed Flavor
Amount of Milk Per
Cup (in liters)
0.4 0.37 0.25
Amount of Sugar Per
Cup (in Kilograms)
0.0057 0.0064 0.0078
Profit per Cup
Produced (in dollars)
0.3 0.5 0.7
DEFINITION OF DECISION VARIABLES
The decision variables for the linear programming problem given in Table 1: Linear
Programming Problem Summary above are as given in Table 2: Decision Variables Definition
below:
Table 2: Decision Variables Definition
Yoghurt Varieties No. of Cups Produced per Hour per Machine
Plain Yoghurt x
6

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
Single Flavor Yoghurt y
Mixed Flavor Yoghurt z
DEFINITION OF OBJECTIVE FUNCTION
The objective function for the linear programming given in Table 1: Linear Programming
Problem Summary above is as follows:
Ma xProfit =0.3 x +0.5 y +0.7 z
DEFINITION OF CONSTRAINS
The constrains for the linear programming given in Table 1: Linear Programming
Problem Summary above are as follows:
1. x ≥ 50 , y ≥ 50 , z ≥50
2. 0.4 x +0.37 y +0.25 z ≤ 80
3. 0.0057 x+0.0064 y+ 0.0078 z ≤ 1.375
MODEL IMPLIMENTATION
Table 3: Linear Programming Summary Output (yoghurt varieties’ production operation
per machine) below gives the output of the linear programming statistical analysis of the yoghurt
7

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
varieties’ production operation per machine. Table 4: Summary Output (Sensitivity Analysis)
gives the sensitivity analysis summary output.
Table 3: Linear Programming Summary Output (yoghurt varieties’ production operation per machine)
Plain Yoghurt Single Flavor
Yoghurt
Mixed Flavor
Yoghurt Total Profit
Cups Produced Per
Hour 50 50 98.71794872
Profit Per Cup 0.3 0.5 0.7 109.1025641
Constrains Production Requirements Per Cup Used Available
Amount of Milk 0.4 0.37 0.25 63.17948718 80
Amount of Sugar 0.0057 0.0064 0.0078 1.375 1.375
Table 4: Summary Output (Sensitivity Analysis)
From Table 3: Linear Programming Summary Output (yoghurt varieties’ production
operation per machine) above, we observe that the optimal number of cups of plain yoghurt
8

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
produced by a machine per hour is 50, the optimal number of cups of single flavor yoghurt
produced per hour by a machine is 50 and the optimal number of cups of mixed flavor yoghurt
produced by a machine per hour is 99. The optimal production process will use 63.18 liters of the
80 liters available for each machine per hour. Also, the optimal production process will use the
entire 1.375 kilograms of sugar available for each machine per hour. This can also be observed
from Table 4: Summary Output (Sensitivity Analysis).
RESULTS
Table 5: Optimal Results below gives the results of the optimization of the yoghurt
varieties’ production operation per machine.
Table 5: Optimal Results
Yoghurt Varieties No. of Cups Produced per
Hour per Machine
Optimal No. of Cups
Produced per Hour per
Machine
Plain Yoghurt x 50
Single Flavor Yoghurt y 50
Mixed Flavor Yoghurt z 99
The optimal objective function is given as below:
109.3=0.3 (50 )+ 0.5 (50 )+ 0.7( 99)
9

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
From the optimal objective function above, the maximized profits per hour per machine
in $109.3.
MODEL DESIGN FOR YOGHURT PRODUCING MACHINES
FORMULATION OF LINEAR PROGRAMMING PROBLEM
The XYZ Yoghurt Company plans to replace all 16 current machines for producing the
three varieties of yoghurt; plain yoghurt, single flavor yoghurt and mixed flavor yoghurt. The
company will replace the current machines with two new model of yoghurt producing machines;
Type A and Type B. Each Type A machine uses 150 kilowatts of power per week while each
Type B machine uses 275 kilowatts of power per week. Type A machines each require weekly
de-clogging that costs $40 while Type B machines each require weekly de-clogging that costs
$28. The machines also require weekly maintenance for proper functioning, the cost of
maintenance of each Type A machine is $42 while the cost of maintenance of each Type B
machine is $49.
The weekly budget of XYZ Yoghurt Company is such that it has allocated money for
4640 kilowatts of power for the machines. The budget also allocates a weekly expenditure of
$480 on de-clogging. This is illustrated in Table 6: Linear Programming Problem Summary
below:
Table 6: Linear Programming Problem Summary
Type A Machine Type B Machine
10

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
Power Consumption (in
kilowatts)
150 275
De-clogging Cost (in dollars) 40 28
Cost of Maintenance (in
dollars)
42 49
DEFINITION OF DECISION VARIABLES
The decision variables for the linear programming problem given in Table 6: Linear
Programming Problem Summary above are as given in Table 7: Decision Variables Definition
below:
Table 7: Decision Variables Definition
Machine Type No. of Machines
Type A v
Type B w
DEFINITION OF OBJECTIVE FUNCTION
The objective function for the linear programming given in Table 6: Linear Programming
Problem Summary above is as follows:
Mi nMaintenance Cost=42 v +49 w
11

BUSINESS ANALYTICS AND DATA INTELLIGENCE (LINEAR PROGRAMMING OF
XYZ YOGHURT OPERATIONS)
DEFINITION OF CONSTRAINS
The constrains for the linear programming given in Table 6: Linear Programming
Problem Summary are as given below:
1. v +w=16
2. 150 v +275 w ≤ 4640
3. 40 v +28 w ≤ 480
MODEL IMPLIMENTATION
Table 8: Linear Programming Summary Output (yoghurt producing machines operations)
below gives the output of the linear programming statistical analysis of the yoghurt producing
machines’ operations. Table 9: Summary Output (Sensitivity Analysis) gives the sensitivity
analysis summary output.
Table 8: Linear Programming Summary Output (yoghurt producing machines operations)
Type A Machine Type B Machine Total Cost
No. of Machines 7 7
Cost of
Maintenance 42 49 637
Constrains Operating Requirements Used Available
Power Consumption 150 275 2975 4640
De-clogging Cost 40 28 476 480
12