Data Analytics and Visualization for Demand Forecasting: A Case Study

This case study employs data analytics and visualization techniques to analyze the demand for Pale Ale beer produced by Mad Dog Craft Beer, an Australian micro-brewery. The study examines customer loyalty, distribution channels, and perceptions to forecast demand and supply. It includes summary statistics, correlation analysis, regression modeling to predict order quantity, and the effects of interaction variables. The analysis identifies significant variables influencing order quantity, such as quality, brand image, and shipping cost, and develops a refined regression model for improved prediction accuracy. The case study also explores the likelihood of customers recommending the beer and includes a time series analysis for forecasting future production needs. The findings provide actionable insights for Mad Dog Craft Beer to optimize its production and meet increasing demand.

Running head: A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
A Case Study Using Data Analytics and Visualization
Name of the Student:
Name of the University:
Author Note:

1A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
Table of Contents
Introduction................................................................................................................................3
Main Body..................................................................................................................................4
Task 1 Summary of Variable, Ordered Quantity and Recommend...........................................4
Task 2.1 Correlation between the ordered quantity and other independent variables...............4
Task 2.2 Modelling in Order to Predict Order Quantity............................................................5
Initial Model to Estimate the Order quantity.........................................................................5
New Model to Estimate the Order Quantity..........................................................................7
Task 2.3 Effects of Introducing Interaction Variable.................................................................8
Task 3.1 Continuation of Predictive Modelling.........................................................................9
Task 3.2 Probability.................................................................................................................10
Task 3.3 Time Series Analysis.................................................................................................10
Conclusion................................................................................................................................10
Appendices...............................................................................................................................12
Task 1.......................................................................................................................................12
Table 1: Summary Statistics for Order Quantity and Recommended..................................12
Figure 1: Distribution of Order Quantity.............................................................................12
Figure 2: Distribution of Recommended..............................................................................13
Task 2.1....................................................................................................................................13
Table 2: Correlation Coefficient for the Variables with Order Quantity.............................13
Task 2.2a..................................................................................................................................14
Table 3: Initial Regression Model........................................................................................14

2A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
Task 2.2b..................................................................................................................................15
Table 4: Final Regression Model.........................................................................................15
Task 2.3....................................................................................................................................15
Table 5: Interaction Analysis...............................................................................................15
Task 3.1....................................................................................................................................16
Table 6: Likelihood of Recommending (a)..........................................................................16
Table 7: Accuracy of the model...........................................................................................16
Table 8: Overall Model Fit...................................................................................................16
Task 3.2....................................................................................................................................17
Table 9: Log-Likelihood and the Probability of Recommending MDCB...........................17
Task 3.3....................................................................................................................................17
Figure 3: Forecasting of the Pale Ale Production................................................................17

3A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
Introduction
Australia based micro-brewery company Mad Dog Craft Beer produce the Pale Ale
Beer majorly and mainly operates in Melbourne and the Victoria. It has experience of less
than years in the brewing ale. The company sales its product in two way, wither directly to
the consumers and indirectly through the sales representatives of pubs, restaurants, bottle
shops and the bars. Despite of its limited operations, this company is growing faster from the
last two years. The company has reported that it is going to increase the brewing capacity by
3 million liters per annum. They assume that they can meet the increasing demand of the pale
alee beer. Mad Dog Craft Beer, now, wants to shift in business climate within next 5 years.
So, before moving the business the management team of the company wants to investigate
and examine the relation with the customer base. Moreover the company wants a formal
procedure to forecast accurate demand and supply to adjust the production needs. Basically,
the management needs to do the analysis of the relation with the customer and wants to
forecast the demand and supply of the market to manage the production.
The analysis needs the data on the parameters, the loyalty of the customer, type,
region, rating of the company according to the customers and the level consumption of the
products produced by customers of the Mad Dog Craft Beer. BEAUTIFUL-DATA has
collected the data by conducting a large -scale survey focusing on the clients Mad Dog Craft
Beer. The collected data are added with the extra information stored in Mad Dog Craft Beer’s
data store. The survey collects the data on 200 observations on the three types of information.
The information is about the loyalty, distribution channel, customer’s perception about the
company and the ordered quantity and the relationship with company. The excel file contains
the data on the variables that are ordered quantity, quality, advertising, brand image, order
fulfillment, shipping speed and shipping cost.

4A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
Main Body
Task 1 Summary of Variable, Ordered Quantity and Recommend
The table 1 presents the summary statistics for the variable ordered quantity and
recommend. The 2nd column shows presents the statistics for the ordered quantity. The mean
value is 7.665 with the variation of 0.893 that means the sales amount of the beer per
consumer of the Mad Dog Craft Beer is 7665 with the variation of 893 bottles. The minimum
number of consumption of the beer is 4300 bottles and the maximum number of the
consumption of the beer is 9900 bottles by an individual consumer in a single year. The total
number of consumption by the observed 200 consumers of the Mad Dog Craft Beer’s beer is
1533000. The median and the mode value of the ordered bottles of beer is 7600 and 7200
bottles respectively. The mean, median and the mode values are not very different to each
other. Therefore, it can be concluded that the ordered quantity of beer bottles is normally
distributed. Moreover, the normality of the ordered quantity is supported by the histogram
presented in the figure 1.
The variable recommend is a binary variable where it presents the value 0 and 1. The
variables is used to present the customers view on beer of Mad Dog Craft Beer that they will
recommend it or not. The figure 2, presents the bar diagram for the particular variable and
shows that 101 customers out of 200 that means 50.5% customers prefer to recommend the
beer of Mad Dog Craft Beer. This also implies that the difference between recommending
and not recommending the Mad Dog Craft Beer is only 1% which is very low.
Task 2.1 Correlation between the ordered quantity and other independent variables
There are 9 important variables on which data are collected and may have the major
impact on the ordered quantity of the beer of the Mad Dog Craft Beer. So, to check
significant relation between the dependent and the independent variables the correlation

5A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
between the dependent and independent variable is obtained. Table 2 presents the correlation
coefficients. The higher the value of the correlation, implies the stronger relationship. The
table shows the positive values for all the independent variable except the variable
competitive pricing and price flexibility. The correlation coefficient for the competitive
pricing and ordered quantity is -0.21771 and for the price flexibility and ordered quantity is -
0.00284. The negative values presents no significant relation between the two variables. The
variables with negative correlation are excluded from the model. However, the strength of the
relationship varies for all the variables as the coefficients are different for each corresponding
variables. The correlation for the quality and shipping speed is 0.433372 and 0.425082
respectively and for the shipping cost is 0.504413 which present the strongest relation to the
ordered quantity among other independent variables. The correlation for the social media
presence and the advertising is 0.235189 and 0.237038 respectively that indicate the weaker
relation with the order quantity among other independent variables. There are two more
variables that shows the moderate strength of relation that are brand image and order and
billing with the correlation coefficient 0.33805 and 0.314591. Hence, it is concluded that the
significantly identified variables that influence the ordered quantity of the beer of Mad Dog
Craft Beer, are quality, social media presence, advertising, brand image, order and billing,
shipping speed and shipping cost.
Task 2.2 Modelling in Order to Predict Order Quantity
Initial Model to Estimate the Order quantity
The construction of model includes the above mentioned variables that are identified
as significant variable by checking the correlation with the dependent variable that can be
influenced by the independent variables. Using these 7 seven variables in the multiple linear
regression model the prediction can be done for the order quantity of beer of Mad Dog Craft
Beer. The model estimated model is presented as below:

6A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
Order Quanity=3.033+(0.277∗Quality)−(0.1 56∗SM Presence)−(0.018∗Advert)+(0..322∗Brand I
Table 3 presents the regression result where the 7 identified variables are regressed on
the order quantity. Table 3 shows the slope coefficients of the independent variables which
are used to for the above estimated regression equation. Now, the significant coefficients of
the variables at 5% significance level that is the p-value of the coefficients of the
corresponding variables are less than 0.05, are quality, brand image and shipping speed.
Moreover, the intercept term is also significant at 5% significance level. The insignificant
variables are social media presence, advertising, order and billing and shipping cost as the p-
values are 0.119, 0.738, 0.071 and 0.203 respectively that are less than 0.005. The variables
that are statistically significant at 5% significance level have positive influence on the order
quantity as the coefficients are positive. Moreover, the coefficients also presents the amount
of impact that can be interpreted individually. The coefficient of quality is 0.277 which
indicates that one unit change in quality significantly raises the order by 277 bottles at 5%
significance level. The coefficient of brand image is 0.322 which indicates that one unit
change in quality significantly raises the order by 322 bottles at 5% significance level. The
coefficient of shipping cost is 0.257 which indicates that one unit change in shipping cost
significantly raises the order by 257 bottles at 5% significance level.
The goodness of fit is presented by the adjusted R2 in case of multivariate linear
regression model. The adjusted R2 for the model is 0.454 which is presented in the table 3
that contains the regression result. The adjusted R2 states that the model is predictable by the
seven, variables with 45.4% accuracy. The F-statistics is F (7, 192) = 24.604 with P-value
0.000 which is less than 0.05 that indicates there is enough evidence to conclude that the
model is better fit than a model without the independent variables. Simply, the independent
variables in the model improves the fit.

7A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
New Model to Estimate the Order Quantity
The new model excludes the insignificant variables from thee model as it is known
that the exclusion of insignificant and irrelevant variables improves the fitness of the model.
The new model consists only the significant variables in order to improve the model. The
significant variables that are included in the new model are social media presence,
advertisement, order and billing and the delivery cost or shipping cost. The estimated new
model can be presented by the following equation where the estimated coefficients of the
corresponding variables are used that are presented in the table 4:
Order Quanity=2.924+( 0.268∗Quality )−( 0.220∗Brand Image)−(0.2 7 3∗Shipping Cost )
Table 4 presents the regression result for the new model to predict the order
quantity. From table 4, the adjusted R2 of the model is equal to 0.457 that means the model
can be predicted by 44.9% accuracy. More preciously, the independent variables can describe
the order quantity at 44.9% accuracy level. The F-statistics is F (3,192) = 55.003 with the P-
value 0.000 which is less than the 0.05. This indicates that there is enough evidence to
conclude that the model is better fit than a model without the independent variables. Simply,
the independent variables in the model improves the fitness of the model. In the new model,
the coefficient of quality is 0.268 with 0.000 p-value which indicates that one unit change in
quality significantly raises the order by 268 bottles at 5% significance level. The coefficient
of brand image is 0.220 with 0.000 p-value which indicates that one unit change in quality
significantly raises the order by 220 bottles at 5% significance level. The coefficient of
shipping cost is 0.273 with the P-value 0.000 which indicates that one unit change in shipping
cost significantly raises the order by 273 bottles at 5% significance level.

8A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
From the above discussion, it is clear that the company should consider the views of
customers only on quality, brand image and shipping cost. This is the best option to predict
the order quantity accurately.
Task 2.3 Effects of Introducing Interaction Variable
To test the Todd’s assumption that there exists an interaction effect of brand image on
the relation between the quality and the order quantity, the interaction variable is introduced.
The interaction variable is derived by multiplying the quality and the brand image. The model
that analyses the interaction effect, consists three variables, brand image, quality and the
interaction variable. Table 5 presents the result of the regression of the interaction model.
From the table, following estimated model is represented:
Order Quanity=0.5011+ ( 0.6911∗Quality ) +(0.8643∗Brand Image)−(0.0686∗Quality∗Brand Image
The table 5 shows the p-value for the intercept term and coefficient of the
corresponding variables. The p-value of the intercept term is 0.7447 that is higher than 0.05.
This indicates that the intercept term is not statistically significant. In the interaction model,
the coefficient of quality is 0.6911 with 0.0003 p-value which indicates that one unit change
in quality significantly raises the order by 6911 bottles at 5% significance level. The
coefficient of brand image is 0.8643 with 0.0016 p-value which indicates that one unit change
in quality significantly raises the order by 864 bottles at 5% significance level. The
coefficient of interaction variable is -0.0686 with the P-value 0.0387 which indicates that one
unit change in shipping cost significantly reduces the order by 69 bottles at 5% significance
level.
Task 3.1 Continuation of Predictive Modelling
To estimate the probability of recommending the beer of Mad Dog Craft Beer by the
influential variables distribution channel, quality, brand image and shipping speed which are

9A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
selected by Todd. The table 6 presents the regression result. The estimated model is presented
below:
log ( P
1−P )=−13.278+ ( 0.968∗Distribution Channel ) + ( 0.654∗Quality ) + ( 0.621∗Brand Image ) +( 1.15
The estimated coefficient of distribution channel is 0.968 with the p-value 0.010
which indicates that one unit change in distribution channel increases the log-likelihood of
recommending the beer of Mad Dog Craft Beer at 5% significance level. The estimated
coefficient of quality is 0.654 with the p-value 0.000 which indicates that one unit change in
quality increases the log-likelihood of recommending the beer of Mad Dog Craft Beer at 5%
significance level. The estimated coefficient of brand image is 0.621 with the p-value 0.001
which indicates that one unit change in brand image increases the log-likelihood of
recommending the beer of Mad Dog Craft Beer at 5% significance level. The estimated
coefficient of shipping speed is 1.159 with the p-value 0.000 which indicates that one unit
change in shipping speed increases the log-likelihood of recommending the beer of Mad Dog
Craft Beer at 5% significance level.
The estimated probability of recommending the beer of Mad Dog Craft Beer is
P= 1
1+e−13.278+0.968∗ Dist Cannel +0.654∗Quality +0.621∗Brand Image +1.159∗ShippingSpeed
Table 8 presents the various R2 values like pseudo R2 which is equal to 0.312, Cox
and Shell R2 which is equal to 0.351 and the Naelkerke’s R2 which is equal to 0.468
respectively. The model to recommend the beer of Mad Dog Craft Beer is higher with the p-
value 0.001.

10A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
Task 3.2 Probability
The table 9 presents the probability of recommending the beer of Mad Dog Craft Beer
for the given values of the incorporated variables in the model. The difference between the
probability of recommending the beer of Mad Dog Craft Beer sold by sales representative
and direct purchase while the quality is scaled at 10 and the brand image is positive, is
(0.998-0.995) = 0.003. Similarly, the difference between the probability of recommending the
beer of Mad Dog Craft Beer sold by sales representative and direct purchase while the quality
is scaled at 10 and the brand image is neutral, is (0.897-0958) = -0.061. The difference
between the probabilities of recommending the beer of Mad Dog Craft Beer sold by sales
representative and direct purchase while the quality is scaled at 10 and the brand image is
negative, is (0.420-0.656) = 0.230. The above differences states indicates that if the shipping
speed is fixed at 5 scale and the quality is at 10 scale then the probability of getting
recommendation is higher from the consumers who are purchasing the beer from sales
representative by considering the negative or neutral brand image.
Task 3.3 Time Series Analysis
The prediction of the production of beer of Mad Dog Craft Beer for the future can be
done by the help of the following estimated model:
P roduction ( litres )=1111.39+16.39∗Time
The result is presented in the figure 3. The next 4 quarters will experience the
production of beer by 1767.03, 1783.42, 1799.82 and 1816.21 litres.
Conclusion
The variable ordered quantity is normally distributed as the histogram is replicated as bell
shaped. The variable has the properties to be used in the linear regression to give proper
statistical results. The order quantity can be measured by only the variables that are

11A CASE STUDY USING DATA ANALYTICS AND VISUALIZATION
statistically significant. The linear multivariate regression analysis helps to conclude that the
significant variables are quality, brand image and shipping cost. Hence, these variables
should get the attention while predicating the order quantity of beer of Mad Dog Craft Beer.
The interaction variable significantly reduces the order quantity by 69 bottles if there is one
unit change in the value of the interaction variable. The shipping speed is fixed at 5 scale and
the quality is at 10 scale then the probability of getting recommendation is higher from the
consumers who are purchasing the beer from sales representative by considering the negative
or neutral brand image.