Analyzing Customer Spending & Machine Repair with Quantitative Methods

This assignment provides a comprehensive quantitative business analysis focusing on customer spending patterns and machine repair operations. It includes step-by-step illustrations for identifying high-spending customers using Euclidean distance and KNN algorithms, along with predictions based on customer variables. The analysis also examines machine repair data, identifying modal repair times and recommending price analysis using CART regression trees. Furthermore, the assignment covers missing data imputation techniques, descriptive statistics for blood protein levels, and the application of multiple linear regression models to predict diabetes risk based on age, gender, weight, and lifestyle. Finally, it includes linear programming for optimization problems solved using Excel Solver, providing a detailed approach to quantitative decision-making in a business context. Desklib offers more solved assignments and study resources for students.

ANALYSIS 1
Quantitative Business Analysis
Name of Author
Name of Class
Name of Professor
Name of School
State and City
Date

ANALYSIS 2
Section B
5a. Writing the step by step illustration on how to find a customer who spent more than $1000, we will
be needed to fist fill in the empty cell in the ages variable using above function in excel. There are five
predictor variables to be used and the variable to be predicted, the target variable is amount spent and
the actual predictor variables are; Age (blanks means we do not know their age), Gender (Male is 1),
Family size, Membership (with membership is 1), Discount card type (0 means no card and there are
three discount cards).
Steps
1. After the filling of the missing cells, there is a creation of an additional variable called the
magnitude variable which shows the amounts which are less than $1000 and those that are
more than $1000.
2. From here we are supposed to find the Euclidean distance between the customer details that
we are to find his amounts and the actual predictor variable that is there in the data given for all
the variables.
3. The results will surely be in different values.
4. An individual can decide to number then ascending order to have a view of what exactly there is
in terms of the distances that each amount spent on goods are.
5. The final one is the KNN algorithm and in this case, there will be up to 500 Ks, an event column
will be created to show which K- Value will show a more than value or a less than value.
6. Then there is the prosperity column that divides event column by the K column and this brings
back a percentage. If the percentage is lower than 50% in this case then the customer spent less
than $1000 and if the percentage is equal to or more than 50% then the customer spent $1000
(Vandhana, S., & Anuradha, J. (2018, June)
5b. In this case, there are variables of a customer and the value that this customer spent is to be
determined by. In excel the actual variables to be predicted are;
The steps that are above are to be followed and the actual result that will come back, in this case, is that
the customer with the variables that are given above will spend an amount that is less than $1000 and
between $672 and $999.
6a.from the analysis asked to be conducted by the manager it is evident to see that most machines are
repaired for 10 hours each since this is the modal value while the mean value for the time of machine
repair is
The average numbers of months after which the machines are brought in for repair are averagely 5.6
with the modal number at 4 (Clayton, 2016).

ANALYSIS 3
From the pie charts below, it is evident to see which number of the machine was repaired more than the
other, who among the three took in more orders than the other and which time were most repairs
done.
From the above electrical machine was repaired the most,
From above we have Bob taking in more orders and James taking in the least number of orders.

ANALYSIS 4
From being above the most time repairs were done was in the morning.
c) The variable that I would recommend was the price charged for each and every machine repaired,
either mechanical or electrical. The method used for analysis that would predict the price of a future
machine that is brought for repair is CART Regression trees.
Part 1
7a. the approach applied to the filling of the missing entries in the blood type column variable in the
dataset was the file using the above function.
Steps
1. Go to the find and replace in excel and this is as below;
2. The next step is going to the go to special and click on that and click on blanks and the Ok as
bellow;

ANALYSIS 5
This will then highlight the missing cells. Click the equal sign into an empty cell then the control key and
then the up arrow and finally control and enter. The value that was above this cell will then be, fills on
the next cell. This can be done for all the remaining cells (Vieira, Soares & Sousa, 2017).
7d. the total protein is not declining by age rather it keeps on fluctuating as shown below

ANALYSIS 6
7e. the best visualization models are as below;
The above descriptive statistics table shows the statistical constants for blood protein level that is
related to the dataset (Opie, 2019).
Looking at the blood groups above one can see which specifically has a larger percentage in the dataset.
Part 2
7a Looking at the diabetic data set, the actual analysis that can be used to predict an individual’s rate of
conducting diabetes is the multiple linear regression model as this model offers one dependent variable

ANALYSIS 7
and multiple independent variables and in our case, there are several variables that can act as predictor
variables to predict the dependent variable which is age, weight and the gender.
7b. after developing a multiple linear regression model, the equation will be;
Y= -27.22729212 + 0.804006829X1 + 0.386562418X2 + -5.23431886X3 + -0.602133263X4.
Y is the value to be predicted whereas the values that are coefficients of X1, X2, X3, X4, the independent
variables and therefore they multiply the predictor variables. The variables stand for X1 (age), X2
(gender), X3 (weight), X4 (life style) (Supapo, Santiago and Pacis, 2017).
7c. using the model above the value for 59-year-old man living in a small town with 72 kg weight will be
Y = -27.22729212 + 0.804006829*(59) + 0.386562418*(0) + -5.23431886*(79) + -0.602133263*(1) = -
363.72.
8b. the value that should be invested is 46% of the salary.
9a. linear programing model
Maximize 65X1 + 48X2
Constraints
10X1 + 14X2 <= 1600
45X1 + 30 X2 <= 10
55X1 + 70X2 <= 14
X1 >= 0, X2>= 0.
9b the values of the solution are
x1 = 0.186666667
x2 = 0.053333333
The maximum value is Maximize 14.69333333.
All of the above solution is done using solver in excel (Sanchez and Herrera, 2016).
9c. new model is
Maximize 65X1 + 48X2
Constraints
9X1 + 13.6X2 <= 1600

ANALYSIS 8
45X1 + 30 X2 <= 10
55X1 + 70X2 <= 14
X1 >= 0, X2>= 0

ANALYSIS 9
References
Clayton, T. (2016).
Master Excel: Sharing Your Work, Charts and Graphing (Volume 3).
Opie, C., 2019.
USING EXCEL/SPSS IN YOUR RESEARCH. Getting Started in Your Educational Research:
Design, Data Production and Analysis, p.309.
Sanchez, L.C. and Herrera, J., 2016.
The solution to the multiple products transportation problem: linear
programming optimization with Excel Solver. IEEE Latin America Transactions, 14(2), pp.1018-1023.
Supapo, K.R.M., Santiago, R.V.M. and Pacis, M.C., 2017, December.
Electric load demand forecasting for
Aborlan-Narra-Quezon distribution grid in Palawan using multiple linear regression. In 2017IEEE 9th
International Conference on Humanoid, Nanotechnology, Information Technology, Communication and
Control, Environment and Management (HNICEM) (pp. 1-6). IEEE.
Vandhana, S., & Anuradha, J. (2018, June
). Dengue Prediction Using Hierarchical Clustering Methods. In
International Conference on Design Science Research in Information Systems and Technology (pp. 157-
168). Springer, Cham.
Vieira, A., Soares, N., & Sousa, S. D. (2017, December).
Implementing the balanced scorecard in excel for
small and medium enterprises. In 2017 IEEE International Conference on Industrial Engineering and
Engineering Management (IEEM) (pp. 2361-2365). IEEE.