BEA 674 Data and Business Decision Making Report

1. Describe the scope of your study (i.e. what entities, time periods etc. the study covers, and the source of your data.). Also include any key caveats.

2. Expand upon the performance characterisation in the Executive Summary and effectively communicate how key measure(s) performed not only overall, but also for broken down by important categories.

Answer:

Introduction

The given dataset shows all the sales related information of Star Solar Company. The company has three product offerings –

1. Basic which includes solar array and invertor
2. Standard which includes solar array, battery storage, and invertor

• Premium which offers solar array, invertor, and premium battery.

Each product has six different size variations and the power of the products ranges from 7.5kW to 20kW. There are four salesperson Lee, Raj, Mary, and Vlad. The Install variable shows three types of installation - ground array, sinlge storey roof array, multi-storey roof array. The overall sales is quite good and it can be better by making strategic plannings. The existing sale is quite high for products of the standard range (Burns, Bushand and Sinha 2014).

The scope for analysis is to use the measures Regression and Pivot tables. The regression lines show the prediction of the dependent variable Contribution based on Quote, Cost, and Cont% variables. A single linear regression line has been created for each of these quantitative variables (Hsiao 2014). The remaining four categorical variables have been treated using the pivot tables where the pivot tables show the sum of the contribution and the percentage of contribution for Product, Install, Salesperson, and Power. The problem taken into account in this paper is to analyse how the sales can be increased and more revenue can be generated by analyzing the given variables. It is an important step to analyse the sales of any company to help the company to achieve more sales (Einav and Levin 2014). 

Body

The study of this business case has the scope to analyse the sales data on the basis of the cost, quote, and percentage contribution of the products (Woodside 2013). The Pivot table has been used here to show the relationship of Contribution with other categorical variables Product, Install, and Salesperson. The reason behind choosing the Pivot table is that it is one of the most powerful tool to use for data summarisation (Slezà et al. 2014). Besides, it analyses a large amount of data in an efficient way. In the calculation, firstly the pivot table has been created and then the respective pie-charts contribution have been inserted to show the percentage graphically.

To insert the pivot table, first the desired columns of the data are selected. Then from the “Insert” tab, the “PivoteTable” command should be clicked. Then the Create Pivot Table dialog box will be appeared where the range of the dataset will be shown. Then “OK” should be clicked by selecting the desired settings. Then a blank PivotTable along with a field list will come. This field list will be filled up according to the requirement. The selected data fields will be calculated and summarized by the Pivot tables (Kämpgen and Harth 2014).

Pivot table for Product and Contribution

The below table shows the Pivot table where the maximum sum value of the contribution is for Standard (1835210) and the minimum sum is for Premium (350290).

The below pie-chart shows that the Premium products contribute minimum (12%) to the sum of the contribution and the Standard product quality contributes 62% which is the maximum percentage.

Pivot table for Salesperson and Contribution

The pivot table below shows the maximum sum of contribution is for Mary (782130) and minimum is contributed by Vlad (707060). Thus it can be said that the contribution to sales will be increased if the company works with Mary more. 

The pie-chart below shows of Salesperson and Contribution, Mary contributes the maximum percentage of contribution (26.61%). The other three salespersons Lee, Raj, and Vlad provides almost equal amount of contribution to the sales. 

Pivot table for Install and Contribution

According to the pivot table below, the single story contributes maximum for the sum of the contribution (2131350) and the minimum is due to ground installation (230110). The pivot table shows that the installation of sigle storey roof array will help in increasing the contribution towards sales. 

The pie-chart is showing the maximum percentage of contribution (72.59%) for single story and the minimum is for the ground story (7.83%). 

Pivot table for Power and Contribution

According to the pivot table, the maximum sum contribution is for 17.5 kW power (697700) and the minimum contribution is for 7.5 kW power (118990). The sum of contribution indicates that if the product works on 17.5 kW power then the contribution will be maximised

The pie chart below shows the minimum percentage of contribution for 7.5 kW (4.05%) and the maximum is for 17.5kW (23.74%). 

Opportunities

In the above graphs and charts, the analysis of the contribution of sales on the basis of four categorical variables. The required analysis was restricted to three variables only. The other variables can be treated by using other statistical tools like fitting regression lines (Park 2015) and calculating the coefficients of determination (Banerjee, Carlin and Gelfand 2014). From that fitting one can also create residual plots and normal probability plots. The residual plot will analyse the appropriateness of the linear regression model on the basis of the random dispersion of the data points along the horizontal axis. Therefore, the interpretation can be drawn on the sales that if the values of the independent variables are more randomly dispersed then the fitting of the regression model is good (Schmuller 2013). The value of coefficient of determination can be taken into account; higher the value of R², better the fitting. The normal probability plot graphically shows whether the dataset is approximately normally distributed or not (Siegel 2016).

Issues

The sample size is quite large which is sufficient to perform efficient statistical analysis (Hastie 2017). However, if more predictors were available in the dataset, then, more statistical analysis could be performed. Another significant issue lies in the nature of the data. The given dataset is a secondary data. Therefore, there may be presence of biasedness of the authority of the data source who has collected the data. The secondary data put certain constraints such as the accuracy of the data and methodology of data collection. The given dataset also contains qualitative data and for qualitative data, the interpretation after certain time period can bring challenges and unauthenticity (Clarke and Cossette 2016).

Conclusion

The above report has discussed the analysis of the qualitative data only. From the above report, it can be concluded that the sales data has been analysed pretty well using the powerful Excel tool Pivot table. From all the pivot tables illustrated above, it can be concluded that, the sales of the Star Solar Company can be increased if the company considers the maximum contribution of each of the variables- Product, Installation, Salesperson, and Power. The business will increase its sales if Standard product offerings are used; if Single storey roof array installation is used more; if the company engages Salesperson Mary more, and if 17.5 kW power is used more. The intensive statistical analysis can be considered for further analysis of the data like fitting regression line plotting normal probability plot, and residual plot. Also, the SWOT analysis (acronym for Strength, Weakness, Opportunity, and Threats) will be very useful to find out the external and internal aspect of the business that are beneficial to increases the sales and increasing the overall revenue. The true costs and the structure of profitability should be understood properly in order to enhance the decision making and business management. The linear will be the most efficient statistical measure to analyse the sales. However, the components of descriptive statistics are fairly useful to high-level statistical analysis. The standard deviation represents the amount of deviation of the data which helps to analyse how the sales or the contribution will vary. Apart from calculating the regression line and plotting the data, one can simply calculate the descriptive statistics from the Data Analysis Tool Pak in Excel to check the confidence limits, standard deviation, variance and the largest and the smallest values of any particular variable. These measures are useful to figure out valuable information about the data set which can be further used to evaluate various statistical interpretations.

References

Banerjee, S., Carlin, B.P. and Gelfand, A.E., 2014. Hierarchical modeling and analysis for spatial data. Crc Press.

Burns, A.C., Bush, R.F. and Sinha, N., 2014. Marketing research (Vol. 7). Boston, MA, USA: Pearson.

Clarke, S.P. and Cossette, S., 2016. Secondary analysis: Theoretical, methodological, and practical considerations. Canadian Journal of Nursing Research Archive, 32(3).

Einav, L. and Levin, J., 2014. The data revolution and economic analysis. Innovation Policy and the Economy, 14(1), pp.1-24.

Hastie, T.J., 2017. Generalized additive models. In Statistical models in S (pp. 249-307). Routledge.

Hsiao, C., 2014. Analysis of panel data (No. 54). Cambridge university press.

Kämpgen, B. and Harth, A., 2014, May. OLAP4LD–A framework for building analysis applications over governmental statistics. In European Semantic Web Conference (pp. 389-394). Springer, Cham.

Park, H.M., 2015. Linear regression models for panel data using SAS, Stata, LIMDEP, and SPSS.

Schmuller, J., 2013. Statistical analysis with excel for dummies. John wiley & sons.

Siegel, E., 2016. Predictive analytics: The power to predict who will click, buy, lie, or die. John Wiley & Sons Incorporated.

SlezÃ, P., Bokes, P., Pavol, N.Ã. and WaczulÃkovÃ, I., 2014. Microsoft Excel add-in for the statistical analysis of contingency tables. International Journal for Innovation Education and Research, 2(5), pp.90-100.

Woodside, A.G., 2013. Moving beyond multiple regression analysis to algorithms: Calling for adoption of a paradigm shift from symmetric to asymmetric thinking in data analysis and crafting theory.