Mad Dog Craft Beer Sales Data Analysis and Forecasting

Desklib provides solved assignments and past papers to help students succeed.

MIS771 ASSIGNMENT_2
Task 5 Technical Report
Student name:
Id:

Contents
Introduction......................................................................................................................................4
Task 1 – Summarizing Dependent Variables..................................................................................5
Task 2.1 Identifying relevant factors that may influence quantity ordered.....................................7
Task 2.2 – Model building (estimating quantity ordered).............................................................10
a)................................................................................................................................................10
b)................................................................................................................................................12
Task 2.3 Interaction effect.............................................................................................................14
Task 3.1 Model building (likelihood of recommending Mad Dog Craft Beer).............................16
Task 3.2 and 3.3 – Calculating predicted probabilities, Visualizing and interpreting predicted
probabilities...................................................................................................................................17
Task 4 Forecasting production.......................................................................................................21
Conclusion.....................................................................................................................................24

List of tables:
Table 1: Vriable table......................................................................................................................5
Table 2: selected data for task 1......................................................................................................6
Table 3: Anova two factor with replication...................................................................................10
Table 4: Anova Summary..............................................................................................................11
Table 5: Covariance table..............................................................................................................12
Table 6: Regression summary.......................................................................................................15
Table 7: Data sample.....................................................................................................................17
Table 8: Pale Ale sale data.............................................................................................................18
Table 9: Forecast summary............................................................................................................23
List of figures:
Figure 1: Loyalty VS Quality..........................................................................................................6
Figure 2: Shipping Speed and Order Quantity................................................................................7
Figure 3: Quality and order quantity...............................................................................................8
Figure 4: Brand Image and Order Quantity.....................................................................................8
Figure 5: Shipping Cost and Order Quality.....................................................................................9
Figure 6: Flex price and Order Quantity..........................................................................................9
Figure 7: Regression model for order quantity and quality...........................................................14
Figure 8: Regression 1...................................................................................................................16
Figure 9: Regression summary 2...................................................................................................16
Figure 10: Data forecast for order quantity...................................................................................18
Figure 11: Pale Ale forecast..........................................................................................................20
Figure 12: Chart to display the pattern of quarterly sale of pale ale..............................................21
Figure 13: Predicted sale...............................................................................................................22

Introduction
The assignment presented here is for the completion of the task 5: technical report of the
Assignment 2. The report is made for the sales data and its visualization and presentation using
the excel. The company Mad Dog Craft Beer is one of the growing companies of Australia, the
company deals in the micro-brewery company in the last 15 years. The company has the major
dealing area in Victoria and Melbourne and is experiencing more growth in the recent years. The
company is having the enhancement in brewing sales and it is recorded to be 3 million
liters/year. In order to maintain the trust of consumers in the company and the drinks, the
company wants to forecast the sales of the coming years. The company Mad Dog Craft Beer
wants Beautiful Data company to visualize the coming sales and predict the data possibilities.

Task 1 – Summarizing Dependent Variables
The following are the variables described in the excel and data collected in three different parts:
Table 1: Vriable table
S. No. ID name Description Measuring Scale
1. Loyalty The loyalty defines the total time
for which the customer has been
buying beers from the company.
No of years
2. Cust_Type Type of customer that is purchasing
beer from Mad Dog Craft Beer
0: beer shops and 1:
restaurants and pubs
3. Dist_Channel The method through which the Mad
Dog customers are provided with
beer.
1: directly and 0: sales
representative
4. Region The area in which the beer from the
Mad Dog Craft Beer is supplied.
1: outside Melbourne
area and 0: Melbourne
5. Quality The quality of beer that is supplied
to the customers.
1: poor quality 10:
excellent quality
6. SM_Presence Presence of brand name on social
media.
1: no presence
10: active
participation
7. Brand_Image The image of the company in the
market.
1: negative
10: positive/influential
8. Advert Insights of Mad Dog Craft Beer's
advertising operations
1: worst 10: excellent
9. Order_Fulfillment Insights of billing and ordering 1: worst 10: excellent
10. Comp_Pricing The level of pricing to which the
company offers competitive
pricings
1: worst 10: excellent
11. Order_Qty The quantity of the beer ordered by
the different types of the customers.
Thousand litre
12. Recommend The agreement of the company
supplier on referring to company to
others.
0: not recommendable
1: recommendable
13. Quarter The financial quarter of year -
14. Ale_Product The complete amount of the pale
production
Litres
15. Flex_Price The willingness of the company to
negotiate with the price range
1: not flexible and 10:
flexible
16. Shipping_Speed The time taken by the company to
ship the products to the suppliers.
1: Very slow and 10:
very fast
17. Year The description of the particular
financial year.
-

18. Shipping_Cost The charges of shipping imposed
on the customer.
1: Not Affordable 10:
affordable
For the description of the dependent variable, the two variables decided are the Loyalty and
Quality, they both variables are dependendent on each other. The variable Loyalty is an
dependent variable, that depends upon the quality of the supplied product by Mad Dog Craft
Beer to the customer.
6646-VRFOL
3460-TJBWI
5917-HBSDW
5685-IIXLY
5671-UUNXD
0956-ACVZC
2325-NBPZG
4250-ZBWLV
4482-FTFFX
8859-DZTGQ
0440-MOGPM
0020-JDNXP
3752-CQSJI
5025-GOOKI
0
2
4
6
8
10
12
14
16
Loyalty VS Quality
Series1 Loyalty Quality
Figure 1: Loyalty VS Quality
The following table is used for the display of the graph and dependency of loyalty on quality:
Table 2: selected data for task 1

Task 2.1 Identifying relevant factors that may influence quantity ordered
The following are the factors that influence the quantity of food ordered:
1. Quality
2. Brand Image
3. Flex Price
4. Shipping Speed
5. Shipping Cost
The data of some considerable products was utilized in producing the following graphs:
6646-
VRFOL 3460-
TJBWI 5917-
HBSDW 5685-
IIXLY 5671-
UUNXD 0956-
ACVZC 2325-
NBPZG 4250-
ZBWLV 4482-
FTFFX 8859-
DZTGQ
0.0
1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0
Ship
ping
_Sp
eed
Shipping Speed and Order Quantity
Shipping_Speed Order_Qty
Figure 2: Shipping Speed and Order Quantity
The graph above displays the dependency of order quantity on the shipping speed. This was
inspected that the shipping speed and order quantity varied directly proportionally to each other.

6646-
VRFOL 3460-
TJBWI 5917-
HBSDW 5685-
IIXLY 5671-
UUNXD 0956-
ACVZC 2325-
NBPZG 4250-
ZBWLV 4482-
FTFFX 8859-
DZTGQ
0.0
1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0
10.0
Quality and order quantity
Quality Order_Qty
Figure 3: Quality and order quantity
The order quality and the order quantity, both are somehow working as a constant function and
their variation rise and fall in same quantity (approx..).
6646-
VRFOL 3460-
TJBWI 5917-
HBSDW 5685-IIXLY 5671-
UUNXD 0956-
ACVZC 2325-
NBPZG 4250-
ZBWLV 4482-
FTFFX 8859-
DZTGQ
0.0
1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0
10.0
Brand image and Order Quantity
Brand_Image Order_Qty
Figure 4: Brand Image and Order Quantity
The brand image plays and important influencing factor on order quality. The brand image and
order quantity are multipliable in variation amount.

6646-VRFOL
3460-TJBWI
5917-HBSDW
5685-IIXLY
5671-UUNXD
0956-ACVZC
2325-NBPZG
4250-ZBWLV
4482-FTFFX
8859-DZTGQ
0.0 2.0 4.0 6.0 8.0 10.0 12.0 14.0 16.0
Shipping Cost and Order Quantity
Shipping_Speed Order_Qty
Figure 5: Shipping Cost and Order Quality
The shipping cost and order quality are dependent on each other and are inversely proposal as the
less the shipping price the more is the order quantity.
6646-
VRFOL 3460-
TJBWI 5917-
HBSDW 5685-
IIXLY 5671-
UUNXD 0956-
ACVZC 2325-
NBPZG 4250-
ZBWLV 4482-
FTFFX 8859-
DZTGQ
0.0
2.0
4.0
6.0
8.0
10.0
12.0
14.0
16.0
Flex Price and Order Quantity
Flex_Price Order_Qty
Figure 6: Flex price and Order Quantity
The flex price and order quality are co dependent variables and their growth depend directly
proportional to each other. The more bargain a customer receives, the more quantity of order is
placed.

Task 2.2 – Model building (estimating quantity ordered)
a)
For the completion of the task, various data analysis process was conducted to bring out various results. Firstly the Anova Two factor
with replication was performed and the results are as following:
Table 3: Anova two factor with replication
SUMMA
RY
Loyal
ty
Cust_Ty
pe
Regi
on
Dist_Chan
nel
Qualit
y
SM_Prese
nce
Advert Brand_Im
age
Comp_Pric
ing
Order_Fulfill
ment
Flex_Pri
ce
6646-
VRFOL
Count 16 16 16 16 16 16 16 16 16 16 16
Sum 136 7 10 7 121.6 59.9 61.8 81.7 117.6 67.1 73.9
Average 8.5 0.4375 0.625 0.4375 7.6 3.74375 3.8625 5.10625 7.35 4.19375 4.61875
Variance 18.8 0.2625 0.25 0.2625 1.8666
67
0.613292 1.1491
67
1.035292 2.208 0.664625 1.12429
2
Total
Count 16 16 16 16 16 16 16 16 16 16 16
Sum 136 7 10 7 121.6 59.9 61.8 81.7 117.6 67.1 73.9
Average 8.5 0.4375 0.625 0.4375 7.6 3.74375 3.8625 5.10625 7.35 4.19375 4.61875
Variance 18.8 0.2625 0.25 0.2625 1.8666
67
0.613292 1.1491
67
1.035292 2.208 0.664625 1.12429
2
Shipping_Sp
eed
Shipping_C
ost
Order_
Qty
Recomme
nd
Total
16 16 16 16 240

63.1 88.4 122.8 7 1024.9
3.94375 5.525 7.675 0.4375 4.2704
17
0.665292 1.971333 0.70466
7
0.2625 9.3866
94
16 16 16 16
63.1 88.4 122.8 7
3.94375 5.525 7.675 0.4375
0.665292 1.971333 0.70466
7
0.2625
Table 4: Anova Summary
ANOVA
Source of
Variation
SS df MS F P-
value
F crit
Sample 0 0 65535 65535 #NUM! #NUM!
Columns 1765.81
8
14 126.1299 59.4202
4
2.13E-
67
1.735948
Interaction 5.68E-14 0 65535 65535 #NUM! #NUM!
Within 477.601
9
225 2.122675
Total 2243.42 239
(Carlberg, 2014)

b)
After the Anova Two factor with replication, the covariance analysis was conducted and the following are the results generated from
that:
Table 5: Covariance table
Ro
w 1
Ro
w 2
Ro
w 3
Ro
w 4
Ro
w 5
Ro
w 6
Ro
w 7
Ro
w 8
Ro
w 9
Ro
w
10
Ro
w
11
Ro
w
12
Ro
w
13
Ro
w
14
Ro
w
15
Ro
w
16
Ro
w
17
Ro
w
18
Ro
w
19
Ro
w
20
Ro
w 1
2.2
5
Ro
w 2
9 36
Ro
w 3
9 36 36
Ro
w 4
9.7
5
39 39 42.
25
Ro
w 5
6.7
5
27 27 29.
25
20.
25
Ro
w 6
5.2
5
21 21 22.
75
15.
75
12.
25
Ro
w 7
0.7
5
3 3 3.2
5
2.2
5
1.7
5
0.2
5
Ro
w 8
6.7
5
27 27 29.
25
20.
25
15.
75
2.2
5
20.
25
Ro
w 9
4.5 18 18 19.
5
13.
5
10.
5
1.5 13.
5
9
Ro
w
10
6.7
5
27 27 29.
25
20.
25
15.
75
2.2
5
20.
25
13.
5
20.2
5
Ro
w
7.5 30 30 32.
5
22.
5
17.
5
2.5 22.
5
15 22.5 25