I want Power Point presentation for Data Analysis using Excel solver ?

Data analysis using excel solver

Overview

This is a report on data analysis for 100% quality, a fictitious airline company. The data analysis was performed using Excel solver an upgraded analytic tool from Microsoft excels, a statistical package from Microsoft words. Microsoft excel solver is an easy to use statistical package that offers steps by step guide on how to perform a majority of statistical operations. It uses inbuilt computerized formulas to resolve complex mathematical calculations and is hence recommended for advanced programmers, statistician, and data analyst. The tool also offers options for arranging and organizing data into tabular forms. It can either be accessed and used on cloud based applications or downloaded.

The source of data that was used for analysis was from a quantitative survey that was done by one of the big data companies in the world. The company has been undergoing a rapid transformation aimed at streamlining its operation model from an MTC to an MTO as well as adopting a smart kitchen strategy. This was done with an aim of improving customer retention, cutting on the amount of overtime pay, as most of its target employees have transitioned from non-exempt to exempt, as well as ensuring that the various contentions raised by unions were adding up to realistic productivity results. The data was collected through sending structured questionnaires and the sample was selected through systematic sampling method. The various records mined by the excel application spanned an entire 1000 records from different airline companies.

This paper summarizes the dataset output with an aim of exemplifying how the variables are correlated. More also, the paper offers a brief explanation of the analytic tools, illustrating in depth its advantage and limitation while at the same time offering alternative methods of performing a similar data analysis.

History of excel solver

Excel solver was developed by frontline solvers to excel with an aim of resolving the problem of optimization and providing solutions or finding values for variables involved in decision making. (de Levie, 2004)The application can be downloaded as an inbuilt add-on compatible excel and windows program. The target model is that which maximizes or minimizes or constrains any objective cell value.

For excel solver to deliver optimum results several factors must be realized first as they serve as the fundamental premise. The data set must be arranged well, and the number of formulas, constraints and decision variables must be determined. In addition, the statistical relationship must be determined. This can either be non -linear or linear. Integers that constrain the model should also be considered as the affect the value of output.

In summary, excel solver has the ability to perform a number of analysis such as prescriptive and predictive statistics, descriptive statistics, can perform data mining, simulation, and optimization, among several others such as decision trees. Essentially, the excel add-ons solve a number of functions that could not be achieved in other software such as linear, nonsmooth and non-linear problems. The software also comes at hand for non-business analytics looking for a deployable application to perform complex statistical simulations.

Benefits and limitations of excel solver as a data analytic tools

The data tool is very effective in transforming big raw data into actionable and intelligent data hence enabling any business analyst to make meaningful predictions from its output(Stergiou, 2016). The excel solver software is very easy to use and can perform a lot of calculations using shorter times.

The information arrived from using the excel solver data analytic tool is quite invaluable. Excel solver ability to perform both descriptive analyses as well as business analytics at the same time is its key strength. Statistical simulation software that only performs one of these can pose a serious limitation to data analyst because both tools are needed if an analyst is to derive a holistic meaning from the output. The descriptive analysis also called business intelligence provides information as it was and like it and does not attempt to generate meaningful prediction from the data set while business analysis or predictive analysis focuses on using dataset output to predict what might and will happen. This is an important aspect of the excel solver which makes it a very reliable software for performing advanced analytics. The tool however

Review of the data

Performing data analysis on a big set of data is entirely unimaginable as the spreadsheets are not made with an ability to handle such an amount of data. Excel solver, however, is a good option for focusing data set from a large and voluminous record while avoiding the other daunting alternative of having to write lambda functions codes in software such as python. The analysis was on over 1000 records of data on the airline industry and analysis was done to help in predicting prevailing labor, and demand and supply statistics with an aim of putting up an actionable white paper.

Data exploration with excel solver

The analysis was only interested in labor factors as they touch on the airline industry. However, to arrive at the necessary records, the sampling of data had to be done. This simplified by the matter as it restricted the analysis to the important aspects that were the subject of the study leaving a sample of 30,000.

Data classification

Data classification is basically categorizing a data set or observations into pre-established classes based on a set of defined variables. The data set was originally classified using logistic linear regression and classification trees. However, the Excel solver which uses an XLMiner supports four other classification methods. These are the neural network classification method, the naïve Bayes and discriminant analysis and K-nearest neighbor classification method. The choice of linear regression classification was justified by the fact that the set of data mined for this analysis was dichotomous in that for labor union membership among airline staffs the answer was either yes or no. Using excel solver to perform a logistic linear regression on voluminous records of data to identify whether staffs were overworked or not and whether they were members to any union or not is a very easy task that is performed in a fraction of a second by the analytic tools.

Decision tree

The aim of classification trees is to generate data into clear and explicable rules that can be employed or translated into a query language or SQL. Classification trees for the data set were arrived by executing a chaid algorithm; this algorithm provides a rationale of splitting the independent variable. The aim of the trees was to establish the quantitative and explanatory variables for the set of data. (Rokach & Maimon, 2008)By performing a simple output excel solver generates a table with columns such as nodes-value, parent node, family trees and split values, the values as well as range. The results are then simulated in terms of trees for ease of categorization and interpretations. The predefined split values for the data were a membership to unions or not and for working hours whether the particular employee was working outside the acceptable threshold of over 40 hours a week.

Alternative techniques

Big data is the type of data that have the capacity to break excel. It is advisable to opt for analytical packages that support voluminous data for fast and quick results. There are numerous data intelligence and prediction tools that one can choose from. Whatever the data tool or analytical method a data scientist opt for, the need to retain data integrity to the point that the data team can say that they cannot do better is imperative. When performing classification, it is also important to go for a classification method that provides the best of explanatory value.

Summary of results

The data analysis showed that a majority of workers belong to labor organizations besides the fact that the work within the average range of working hours which is forty hours per week. This implies that the constraints and maximum models are within the acceptable threshold. The industry, however, needs to maximize on working hours for its staffs as there was a large outlier whereby a majority of staffs in senior management position were working for hours less than 30 hours. The industry hence needs to focus on fair work practices among its staffs by asking realistic questions such as how to motivate their employee in the workplace to achieve more productivity and efficiency.

A summary of descriptive statistics

References

De Levie, R. (2004). Advanced Excel for scientific data analysis. Oxford University Press. ISBN 0-19-515275-1.

Rokach, L., & Maimon, O. (2008). Data mining with decision trees: theory and applications. World Scientific Pub Co Inc. ISBN 978-9812771711.

Stergiou, C. (2016). "Recent advances delivered by Mobile Cloud Computing and Internet of Things for Big Data applications: a survey. International Journal of Network Management, VOl 4 1-18.