Contents
Introduction..
Analysis & Discussion - Descriptive Analysis of The Data.
Analysis & Discussion - Predictive Analysis of The Data.
Analysis & Discussion - Prescriptive Analysis of The Data.
Conclusion..
Recommendations..
This report comprises of descriptive, predictive and prescriptive analysis of sales data for the online store owned by Alex and Ash Sparks.
The average gross profit, sales trend & variance gross profit etc. gives the overview of the data.
Different manufacturers and ranges of taps were compared to gain better control of the sales data
Prescriptive analysis is used to make recommendation to make strategic decisions
The below chart shows the sales trend (quarterly basis) for the online store Taps To Go
The below table gives the comparison between the average gross profit (year on year) for varying delivery charges (based upon the state to which goods are getting delivered) and fixed delivery charges
Row Labels |
Average of Gross Profit |
Average of Gross Profit - Fixed delivery |
2017 |
39.1558023 |
38.20202769 |
2018 |
39.2684982 |
38.31761972 |
2019 |
39.08891934 |
38.12639114 |
Grand Total |
39.17183309 |
38.21615715 |
The below table gives the comparison between the variance of gross profit (year on year) for varying delivery charges (based upon the state to which goods are getting delivered) and fixed delivery charges
Row Labels |
Var of Gross Profit |
Var of Gross Profit - Fixed delivery |
2017 |
409.8037717 |
400.0880571 |
2018 |
416.9291757 |
405.9415031 |
2019 |
406.2704525 |
395.7329483 |
Grand Total |
411.046197 |
400.6319065 |
The below table gives the comparison between the average gross profit for different ranges,
varying delivery charges (based upon the state to which goods are getting delivered) and fixed delivery charges
Row Labels |
Average of Gross Profit |
Average of Gross Profit - Fixed delivery |
Average of $ discount |
Basics |
19.08729185 |
18.09989837 |
11.34984755 |
Luxury |
98.04809626 |
97.0845897 |
37.09299532 |
Superior |
44.50874289 |
43.54008375 |
25.14458755 |
Value |
34.84594001 |
33.90410232 |
25.94070459 |
Grand Total |
39.17183309 |
38.21615715 |
24.32603904 |
The below table gives the values for gross profit (year on year) for different manufacturers
Row Labels |
Average of Gross Profit |
2017 |
|
Vitra |
98.00783049 |
Frederic |
45.3990172 |
Villeroy and Boch |
44.18566499 |
Dorf |
38.36157827 |
Novelli |
35.43735205 |
Caroma |
27.52765266 |
Estilo Wels |
19.12277693 |
2018 |
|
Vitra |
98.06943266 |
Frederic |
45.40715328 |
Villeroy and Boch |
44.26101431 |
Dorf |
38.38842245 |
Novelli |
35.37458178 |
Caroma |
27.47536056 |
Estilo Wels |
19.0700651 |
2019 |
|
Vitra |
98.06694392 |
Frederic |
45.41213759 |
Villeroy and Boch |
44.2198496 |
Dorf |
38.36522723 |
Novelli |
35.43079347 |
Caroma |
27.55967491 |
Estilo Wels |
19.06911539 |
Grand Total |
39.17183309 |
Multiple regression analysis is used here to develop regression model with Dependent Variable Gross Profit (y) and Independent Variables Recommended Retails Price (RRP), $ discount & Delivery
Regression Statistics |
|||||||||||
Multiple R |
0.999554773 |
||||||||||
R Square |
0.999109744 |
||||||||||
Adjusted R Square |
0.999109726 |
||||||||||
Standard Error |
0.604932733 |
||||||||||
Observations |
153675 |
||||||||||
ANOVA |
|||||||||||
|
df |
SS |
MS |
F |
Significance F |
||||||
Regression |
3 |
63110878.35 |
21036959 |
57486888 |
0 |
||||||
Residual |
153671 |
56234.92079 |
0.365944 |
||||||||
Total |
153674 |
63167113.28 |
|
||||||||
|
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
|||
Intercept |
0.063374636 |
0.007318266 |
8.659788 |
4.77E-18 |
0.049030985 |
0.077718287 |
0.049030985 |
0.077718287 |
|||
RRP |
0.107709836 |
1.28189E-05 |
8402.452 |
0 |
0.107684711 |
0.10773496 |
0.107684711 |
0.10773496 |
|||
$ discount |
0.198204997 |
0.000260963 |
759.5144 |
0 |
0.197693515 |
0.198716479 |
0.197693515 |
0.198716479 |
|||
Delivery |
1.000003076 |
0.000477778 |
2093.031 |
0 |
0.999066642 |
1.00093951 |
0.999066642 |
1.00093951 |
The regression model is given by:
Gross Profit (Y)= 0.063375 + 0.10771xRRP + 0.198205x$ discount + 1.000003xDelivery
β0 = 0.063375, β1 = 0.10771 β2 = 0.198205 & β3 = 1.000003
Prescriptive analysis is used to know the right levers to push and pull to increase sales, profitability and customer satisfaction
In synchronization with the Descriptive and predictive analysis the objective
Gross Profit (Y)= 0.063375 + 0.10771xRRP + 0.198205x$ discount + 1.000003xDelivery
We need to maximize the objective function Z=Gross Profit (Y)
Subject to the constraints
Discount =10.2
The above sales trend shows that the sales are fluctuating over the three years, the lowest sales count was 12536 in Quarter 4 (2017) and the maximum sales count was 13344 in Quarter 3 (2018).
Row Labels |
Average of Gross Profit |
Average of Gross Profit - Fixed delivery |
2017 |
39.1558023 |
38.20202769 |
2018 |
39.2684982 |
38.31761972 |
2019 |
39.08891934 |
38.12639114 |
Grand Total |
39.17183309 |
38.21615715 |
The average gross profit for varying delivery charges (based upon the state to which goods are getting delivered) is greater than the average gross profit for fixed delivery charges for all three years
Hence keeping the standard delivery charges is not good for gross profit.
Row Labels |
Var of Gross Profit |
Var of Gross Profit - Fixed delivery |
2017 |
409.8037717 |
400.0880571 |
2018 |
416.9291757 |
405.9415031 |
2019 |
406.2704525 |
395.7329483 |
Grand Total |
411.046197 |
400.6319065 |
The variance of gross profit (year on year) for varying delivery charges (based upon the state to which goods are getting delivered) is greater than the variance of gross profit for fixed delivery charges. This implies standard delivery charges will reduce the high variation in gross profit
The comparison between the average gross profit for different ranges,
varying delivery charges (based upon the state to which goods are getting delivered) and fixed delivery charges
Row Labels |
Average of Gross Profit |
Average of Gross Profit - Fixed delivery |
Average of $ discount |
Basics |
19.08729185 |
18.09989837 |
11.34984755 |
Luxury |
98.04809626 |
97.0845897 |
37.09299532 |
Superior |
44.50874289 |
43.54008375 |
25.14458755 |
Value |
34.84594001 |
33.90410232 |
25.94070459 |
Grand Total |
39.17183309 |
38.21615715 |
24.32603904 |
The below table gives the values for gross profit (year on year) for different manufacturers
Row Labels |
Average of Gross Profit |
2017 |
|
Vitra |
98.00783049 |
Frederic |
45.3990172 |
Villeroy and Boch |
44.18566499 |
Dorf |
38.36157827 |
Novelli |
35.43735205 |
Caroma |
27.52765266 |
Estilo Wels |
19.12277693 |
2018 |
|
Vitra |
98.06943266 |
Frederic |
45.40715328 |
Villeroy and Boch |
44.26101431 |
Dorf |
38.38842245 |
Novelli |
35.37458178 |
Caroma |
27.47536056 |
Estilo Wels |
19.0700651 |
2019 |
|
Vitra |
98.06694392 |
Frederic |
45.41213759 |
Villeroy and Boch |
44.2198496 |
Dorf |
38.36522723 |
Novelli |
35.43079347 |
Caroma |
27.55967491 |
Estilo Wels |
19.06911539 |
Grand Total |
39.17183309 |
This shows the average gross profit for manufacturer Vitra is the maximum and for the manufacturer Estilo Wels is the lowest
From the regression table below R2 = 0.999109744, this means that 99.91% of the variation in Y is explained by the regressors RRP, $ discount & Delivery charges
Regression Statistics |
|||||||||
Multiple R |
0.999554773 |
||||||||
R Square |
0.999109744 |
||||||||
Adjusted R Square |
0.999109726 |
||||||||
Standard Error |
0.604932733 |
||||||||
Observations |
153675 |
||||||||
Coefficients |
Standard Error |
t Stat |
P-value |
Lower 95% |
Upper 95% |
Lower 95.0% |
Upper 95.0% |
||
Intercept |
0.063374636 |
0.007318266 |
8.659788 |
4.77E-18 |
0.049030985 |
0.077718287 |
0.049030985 |
0.077718287 |
|
RRP |
0.107709836 |
1.28189E-05 |
8402.452 |
0 |
0.107684711 |
0.10773496 |
0.107684711 |
0.10773496 |
|
$ discount |
0.198204997 |
0.000260963 |
759.5144 |
0 |
0.197693515 |
0.198716479 |
0.197693515 |
0.198716479 |
|
Delivery |
1.000003076 |
0.000477778 |
2093.031 |
0 |
0.999066642 |
1.00093951 |
0.999066642 |
1.00093951 |
The regression model is given by:
Gross Profit (Y)= 0.063375 + 0.10771xRRP + 0.198205x$ discount + 1.000003xDelivery
β0 = 0.063375, β1 = 0.10771 β2 = 0.198205 & β3 = 1.000003
The above can be used for predicting future gross profit, given the regressors’ values
Considering the Regression model
Gross Profit (Y)= 0.063375 + 0.10771xRRP + 0.198205x$ discount + 1.000003xDelivery
Remember, at the center of any academic work, lies clarity and evidence. Should you need further assistance, do look up to our Management Assignment Help
Get 24x7 instant assistance whenever you need.
Get affordable prices for your every assignment.
Assure you to deliver the assignment before the deadline
Get Plagiarism and AI content free Assignment
Get direct communication with experts immediately.
Get
500 Words Free
on your assignment today
It's Time To Find The Right Expert to Prepare Your Assignment!
Do not let assignment submission deadlines stress you out. Explore our professional assignment writing services with competitive rates today!
Secure Your Assignment!