Management Question

‫المملكة العربية السعودية‬
‫وزارة التعليم‬
‫الجامعة السعودية اإللكترونية‬

Kingdom of Saudi Arabia
Ministry of Education
Saudi Electronic University

College of Administrative and Financial Sciences

Assignment-2
MGT425-Spreadsheet Decision Modeling
Due Date: 22/03/2025 @ 23:59 (End of Week 9)
Course Name: Spreadsheet Decision
Modeling
Course Code: MGT425

Student’s Name:

Semester: Second

CRN: 23745

Student’s ID Number:

Academic Year: 2024-2025 (1446 H)

For Instructor’s Use only
Instructor’s Name: Dr. Shahid Husain
Students’ Grade: Marks Obtained / Out of 10 Level of Marks: High/Middle/Low
General Instructions – PLEASE READ THEM CAREFULLY
•
•
•
•
•
•
•
•

Restricted – ‫مقيد‬

The Assignment must be submitted on Blackboard (WORD format only) via
allocated folder.
Assignments submitted through email will not be accepted.
Students are advised to make their work clear and well presented; marks may be
reduced for poor presentation. This includes filling your information on the cover
page.
Students must mention question number clearly in their answer.
Late submission will NOT be accepted.
Avoid plagiarism, the work should be in your own words, copying from students
or other resources without proper referencing will result in ZERO marks. No
exceptions.
All answered must be typed using Times New Roman (size 12, double-spaced)
font. No pictures containing text will be accepted and will be considered
plagiarism).
Submissions without this cover page will NOT be accepted.

Course Learning Outcomes-Covered
Course Learning Outcomes (CLOs)

Question

Find some structured ways of dealing with complex managerial
decision problems.
Explain simple decision models and management science ideas
that provide powerful and (often surprising) qualitative insight
about large spectrum of managerial problems.
Demonstrate the tools for deciding when and which decision
models to use for specific problems.
Build an understanding of the kind of problems that is tackled
using Spreadsheet Modeling and decision analysis.

Question-1

Aligned (PLOs)
MGT.K.1
(1.1)
MGT.K.3
(1.2)
MGT.S.1
(2.1)
MGT.V.1
(3.1)

Question-3

Question-4
Question-2

Assignment Instructions:
Assignment Questions: (Marks 10)
PART-A:
Business Analysis using Spreadsheets (Critical Thinking)
Question 1: In context of Business Analysis using Spreadsheets, Discuss the various
categories of Business Analysis that are required for detailed analysis by Business Analysts
(250-300 words) (2.5 Marks)..

PART-B: (Case Study)
• Log in to Saudi Digital Library (SDL) via University’s website
• On first page of SDL, choose “English Databases”
• From the list find and click on EBSCO database.
• In the Search Bar of EBSCO find the following article:
Title: “An Analytic Hierarchy Process Approach in Decision-Making for Material
Selection in an Automotive Company: A Case Study”
Author: Cheng Jack Kie, Ahmed Khalif, Norhana Mohd Aripin, Rafiuddin Mohd Yunus.
Date: August 18, 2019

Read the above case study and answer the following Questions:
2. Discuss the Analytic Hierarchy Process that can assist decision-makers and
continuous improvement managers. (250-300 words) 2.5 Marks.

Restricted – ‫مقيد‬

3. What is the importance of employee involvement in process of continuous
improvement to be successful in manufacturing companies? (250-300 words) 2.5
Marks

4. Discuss the four criteria that are the most important and needed to be considered
when selecting the material to use in the fabrication process. (250-300 words) 2.5
Marks

Answers:
1
2
3
4

Restricted – ‫مقيد‬

FGIC2019
FGIC 2nd Conference on Governance and Integrity 2019
Volume 2019

Conference Paper

An Analytic Hierarchy Process Approach in
Decision-Making for Material Selection in an
Automotive Company: A Case Study
Cheng Jack Kie, Ahmed Khalif Hassan, Norhana Mohd Aripin, and Rafiuddin
Mohd Yunus
Faculty of Industrial Management, Universiti Malaysia Pahang, Lebuhraya Tun Razak, 26300
Gambang, Kuantan, Pahang

Abstract

Corresponding Author:
Cheng Jack Kie
[email protected]
Received: 5 August 2019
Accepted: 14 August 2019
Published: 18 August 2019
Publishing services provided by
Knowledge E

This study is an approach to investigate and to choose the suitable material for the
fabrication of tools trolley to ensure the good quality of the product. The project team
of an automotive manufacturing company is planning to fabricate 100 sets of tools
trolley in the assembly shop. This study was developed to describe an approach based
on Analytic Hierarchy Process (AHP) that can assist decision-makers and continuous
improvement engineers in determining the most suitable material to be employed in
fabrication process at the early stage of the product development to reduce the cost.
The selected main criteria are Material Strength, Material Cost, Procurement Lead Time
and Duration of Fabrication Process while the four materials that will be considered in
this study are Aluminium, Steel Tube, and Square Tube. Finally, the results show that
Square Tube is recommended as the most suitable material for the in-house tools for
trolley fabrication.

Keywords: analytic hierarchy process, decision-making, continuous improvement,
fabrication process.

Cheng Jack Kie et al. This
article is distributed under the
terms of the Creative Commons
Attribution License, which
permits unrestricted use and

1. Introduction

redistribution provided that the
original author and source are
credited.
Selection and Peer-review under

Material process selection is a method to determine the most suitable material to
fabricate a product. Many researchers have agreed on the importance of material

the responsibility of the

selection process, especially during the early stage of the product development phase.

FGIC2019 Conference

Determining the most suitable and appropriate material in the early stage can avoid

Committee.

additional cost if changes are needed to be carried out after the early stage of the
product development process (Ravisankar, Balasubramanian & Muralidharan, 2004).
However, it is a difficult task with a complex decision because various factors have to
be considered during the process.
Analytic Hierarchy Process (AHP) is a tool that can be used at the conceptual
design stage in the product development process (Hambali et al., 2010; Subramanian &

How to cite this article: Cheng Jack Kie, Ahmed Khalif Hassan, Norhana Mohd Aripin, and Rafiuddin Mohd Yunus, (2019), “An Analytic Hierarchy
Process Approach in Decision-Making for Material Selection in an Automotive Company: A Case Study” in FGIC 2nd Conference on Governance Page 472
and Integrity 2019, KnE Social Sciences, pages 472–484. DOI 10.18502/kss.v3i22.5067

FGIC2019

Ramanathan, 2012). According to Vaidya and Kumar (2006), AHP is widely implemented
for selection and evaluation based decision-making, usually in the area of manufacturing, engineering, healthcare, education, and many more. AHP has been used to
solve multi-criteria decision-making problems based on experience and skills of the
experts by determining the factors that impacted the decision process (Subramanian
& Ramanathan, 2012). The tools trolley which acts to transport tools and small parts
safety is generally made from few materials such as stainless steel, carbon steel,
aluminum, iron, and copper. Each material has different material strength, material lead
time, and the price of the material can be very expensive to manipulate the cost. In
the fabrication process, there are many processes involved with different amounts of
costs of material and equipment, quality of material, and fabricating time (Kalpakjian
and Schmid, 2014). In an automotive manufacturing industry, the fabrication process
gives the Continuous Improvement (CI) Engineers different types of problems, where
the selection of appropriate material is one of the critical issues. By doing this study,
the problem faced by the engineers is solved using AHP. This technique will assist in
determining the most appropriate material to fabricate the tools trolley, which will meet
the product’s specifications and requirements. Thus, the main focus of this study is to
explore the potential use of AHP in assisting CI projects to evaluate and determine
the most appropriate material for producing tools trolley in an automotive company.
Besides that, this paper briefly reviews the concepts and applications of multiple
criterion decision analysis.
This paper is organized into five sections where after the broad introduction was firstly
discussed in Section 1. The literature of past studies related to AHP and Continuous
Improvement are presented in Section 2. Next, the chosen methodology, which is AHP,
will be elaborated in Section 3 while Section 4 encompassed results and discussion.
Then, a conclusion with the point of discussion on limitations and suggestion for future
studies are provided in the last section of this paper.

2. Literature Review
In order to make a good decision, the decision-maker must be able to first define
the problem, the need, and purpose of the decision, then using this information to
develop criteria that can be used to evaluate the potential alternative actions to take.
The beauty of Analytic hierarchy process and continuous improvement are discussed
in the following section, respectively.

DOI 10.18502/kss.v3i22.5067

Page 473

FGIC2019

2.1. Analytic hierarchy process
Dweiri and Al-Oqla (2006) mentioned that the Analytic Hierarchy Process (AHP) is one
of the multi-criteria decision-making tools that incorporated the behavior of its decisionmaker in the decision model. Professor Thomas L. Saaty developed AHP techniques in
the 1970s to improve the decision-making process when multiple criteria are involved
in the process. Since then, the method is widely used, refined, and studied. AHP
technique is one of the most commonly used multi-criteria decision methods in decision
making (Subramanian & Ramanathan, 2012). The main flexibility of this method is AHP
considered a systematic approach that includes both the tangible and intangible factors
and finally provides a structured solution to problems in the industries.
The advantages of AHP method is the technique uses both qualitative data collected
from judgment values which based on experience and intuition apart from quantitative
data of a problem (Subramanian & Ramanathan, 2012; Vaidya & Kumar, 2006). Besides
that, the application of AHP allows the investigated problems to be broken down
hierarchically where a set of criteria will be arranged in a hierarchy order so that it can
be evaluated subjectively based on the importance according to scores or weights. To
develop an AHP model, there are three important phases which are problem structuring,
judgments comparison, and analyzing priorities. In the structuring phase, a decisionmaking model is developed and then is transposed to a hierarchy form. Then, for each
alternative obtained will be evaluated according to the criterion’s weight in the judgment
phase.
A hierarchy can be used to study the interaction of its components and how these
interactions impact the whole system. Therefore a hierarchy is one form of abstraction
or representation of a system’s structure (Hambali et al., 2010). Hierarchies work by
separating the reality of human thinking into several sets and subsets. The decision
making alternatives can be rated once weights are assigned to the developed hierarchy.
Weights are assigned through expert comparison using judgment scale. These scales
are usually ranged from 1 (equally preferred) to 7 (extremely preferred). These numerical
values represent the intensity of the alternatives compared to criteria.
Due to the mathematical elements used in AHP, researchers are keen to adopt
the technique (Dweiri & Al-Oqla, 2006; Hambali et al., 2008). With the properties of
using multi-level objectives, criteria, sub-criteria, and alternatives, AHP is suitable to
be used to solve decision problem. Through pairwise comparison, data are obtained
using weightage of the importance of the criteria and the alternatives in terms of each
decision criteria.

DOI 10.18502/kss.v3i22.5067

Page 474

FGIC2019

AHP is also commonly applied in task selection where the method is used not
to find the correct answer but to aid decision-makers finding the best answer. Not
only for academic studies, but AHP is also widely used in organizations, especially
for an organization to explore their strategies and their competitors (Vaidya & Kumar,
2006). AHP is suitable to be used to groups of decision-makers who shared common
objectives, worked in a cooperative environment and of the same status.

2.2. Continuous improvement (CI)
Currently, the implementation of sustainable improvement is gaining increase attention (Bhasin, 2008; Hassini, Surti & Searcy, 2012). With that, several guidelines were
developed to support continuous improvement implementation (Sundar, Balaji & Kumar,
2014). Strategic Management, Kaizen, Six Sigma, and Total Quality Management are
some of the well-known methods used in continuous improvement (Garcia, Rivera &
Iniesta, 2013). Each of these methods uses different tools for improvement.
One way for the continuous improvement to be successful, there is a need to include
staff involvement. With that, Total Quality Management adopts tools and plan of doing,
check, act approach (Moeuf et al., 2016) that are capable of integrating learning culture
to drive organization change (Amirteimoori, Despotis & Kordrostami, 2014; Moeuf et al.,
2016). On the other hand, Six Sigma approach aims at reducing variability in organizational processes through the defining, measuring, analyzing, improving and controlling
improvement cycle are used to support this approach (Garcia, Rivera & Iniesta, 2013). As
for Kaizen, this tool adopted scenario that allows continuous improvement in personal,
family, social, and work-life (Anand & Kodali, 2008) which aimed to change for the better
(Bhasin, 2008; Gupta & Jain, 2013). However, there are researchers that mentioned that
Kaizen is not only a continuous improvement tool, but it also serves as the means
and result of human and non-human resources management in the pursuit of business
excellence (Hassini, Surti & Searcy, 2012).
As such, a vast literature argues that characteristically the tools that support Kaizen
are process-oriented and human-based, as Kaizen is incremental, continuous, and
participatory (Anand & Kodali, 2008; Moeuf et al., 2016; Zhang et al., 2012). Therefore,
Kaizen, as a continuous improvement tool, stressed that efforts of all people involved
in the organization are important to achieve the improvements that can contribute to
the achievement of superior results (Hassini, Surti & Searcy, 2012; Sundar, Balaji &
Kumar, 2014), while understanding management as the maintenance and improvement
of working standards (Amirteimoori, Despotis & Kordrostami, 2014).
DOI 10.18502/kss.v3i22.5067

Page 475

FGIC2019

3. Methodology
This case study was done in an automotive manufacturing company in Pahang. Moving
forward towards the lean manufacturing concept, the company is encouraging continuous improvement projects and activities. In a lean manufacturing concept, reducing
waste and increasing value-adding operation time is the main target. To reduce the
waste of walking in the production time, fabrication of tools trolley was proposed to
increase the efficiency rate. The project team is planning to fabricate 100 sets of tools
trolley (shown in Figure 1) in the assembly shop for the used in the assembly line.

Figure 1: Tools Trolley in an Assembly Shop (Source: Authors’ own work).

As the company, in-house Continuous Improvement (CI) Workshop has the capacity of
fabricating the trolley. Therefore CI-engineers need to plan on the design and choose
the correct material for the trolley. All fabrication tools and machine such as cutting
machine, tightening tools, welding machines, and measuring devices are available in
the workshop. The material for the fabrication must be strong to withstand the weight of
the tools, equipment, and some fittings parts. Project lead time is short. Therefore the
procurement and fabrication lead time must be minimized to ensure the project completion is on schedule. Material cost should also be within the budget allocation. The data
collection phase is crucial in any research. Several aspects come into play in the data
DOI 10.18502/kss.v3i22.5067

Page 476

FGIC2019

collection process. The three most crucial aspects are the cost of the selected data
collection method, the accuracy of data collected, and the efficiency of data collection.
In this study, data were collected via structured, face-to-face interviews. The interview
session was conducted with the participation of Project Engineers, Project Leaders,
Continuous Improvement Operators, and Assembly Operators. All the participants are
involved in the tools trolley project. A pilot test to validate the questionnaires was
conducted with two Managers, and some amendments have been made based on the
given feedback. Besides that, the researcher also reviewed a few products’ catalogs
that describe in details about each material specification to developed comprehensive
criteria and alternatives. Information collected from the participants via interview is
gathered to determine which criteria are the most important in deciding which material
to select for tools trolley fabrication. With the information gathered, the AHP method
can be performed. AHP is a decision-making tool that involves structuring criteria into a
hierarchy and the relative importance of these criteria is then assessed. Alternatives for
each criterion are compared, that relies on the judgment of the interviewed participants.
An overall ranking scale of the alternatives is determined.
The AHP selection method follows the following steps. Firstly, define the objective
of selection, follows by developing a hierarchical framework based on the collected
information on criteria and alternatives, construction of pairwise comparison set matrix,
calculation of preferences against criteria of pairwise comparison, ranking the criteria,
developing an overall priority ranking, performing a consistency check on the result
and finally selection of the best alternatives.

4. Results and Discussion
The main purpose of this study is selecting the most suitable material for the fabrication
process of the tool trolley in order to produce a good quality product. From the interview
conducted with the Project Engineers, Project Leaders, Continuous Improvement Operators, and Assembly Operators, information on criteria and alternatives are successfully
gathered. The selected main criteria are Material Strength, Material Cost, Procurement
Lead Time, and Duration of Fabrication Process. According to the participants, these
four criteria are the most important and needed to be considered when considering
which material to use in the fabrication process of the tool trolley. As for the alternatives,
Aluminium, Steel Tube, and Square Tube are chosen by the participants as the potential
materials that can be considered in order to construct the tool trolley. The defined
objective, four different criteria, and three possible alternatives are shown in Figure 2.
DOI 10.18502/kss.v3i22.5067

Page 477

FGIC2019

Figure 2: Hierarchy Structure of the Criteria (Source: Authors’ work).

4.1. Pairwise comparison
The score of each criterion was calculated using pairwise comparison. The decision
was done by comparing two alternatives against one criterion, and then the indicated
preferences will be recorded. The pairwise comparison scale measurement that was
used is shown in Table 1.
Table 1: Pairwise Comparison Scale.
Preference Level

Numeric Value

Equally Preferred

1

Moderately Preferred

2

Strongly Preferred

3

Very strongly Preferred

4

Extremely Preferred

5

Source: Taylor (2019)

Table 3 depicted the pairwise comparison of alternatives (Aluminium, Steel Tube, and
Square Tube) against criteria (Material Strength, Material Cost, Procurement Lead Time
and Fabrication Lead Time). The data collected are based on the expert judgment of
the participants during the interview.

Figure 3: Result for All Criterion (Source: Authors’ own work).

DOI 10.18502/kss.v3i22.5067

Page 478

FGIC2019

4.2. Calculation of preferences
The next step in AHP is to prioritize the decision alternatives within each criterion.
This will assist the CI-engineers in determining the most suitable material based on the
criteria provided. Preference score result and normalized matrix with row average were
calculated as shown in Figure 4.
From Figure 4, the calculated results showed that for the criteria of Material Strength,
Square Tube is the most preferred alternative with the score of 0.67, followed by
Aluminium and Steel Tube with the score of 0.23 and 0.10 respectively. For the second
criteria, Procurement Lead Time, Square Tube scored the highest at 0.57, followed by
Steel Tube with 0.33 and Aluminium with 0.09. The third criteria, Material Cost shows
that the participants agreed that Square Tube with the score 0.47 is the most costeffective as compare to Steel Tube and Aluminium with the score of 0.43 and 0.10. For
the Fabrication Lead Time, as the last criteria are chosen by the participant, Aluminium
was ranked first with 0.62, followed by Steel Tube and Square Tube with 0.28 and
0.096 respectively.

Figure 4: Comparison of Criteria against Alternatives Normalize Matrix (Source: Authors’ own work).

After determining the preference of materials against the chosen four criteria, next
is to determine among the four criteria, which criteria is the most important to the least
important where these criteria will be the major reason which material will be chosen for
the fabrication of tools trolley. With the relative importance or weight of the criteria used
to rank the criteria from the most important the least important, the result of the ranking
and normalize matrix of the chosen four criteria are shown in Figure 5. Results in Figure
5 indicated that among the four criteria, Fabrication Lead Time with the score of 0.44 is
considered the most important criteria in determining which material to choose for tool
DOI 10.18502/kss.v3i22.5067

Page 479

FGIC2019

trolley fabrication. The second most important criteria are the Material Cost (score of
0.24). Material Strength and Procurement Lead Time scored 0.19 and 0.13, respectively.

Figure 5: Criteria Normalize Matrix (Source: Authors’ own work).

4.3. Overall ranking
As the preference of materials (alternative) against the chosen four criteria (Figure 4)
and the preferable criteria (Figure 5) are determined, the next step is to determine given
all the preferences calculation and ranking, ultimately which material should be chosen
to fabricate the tools trolley. Hence, an overall score of each criterion is computed by
multiplying the values in the criteria preference vector by the preceding criteria matrix
and summing the products as in Figure 6.
Figure 6 showed the overall ranking of the three materials. Based on the result,
Square Tube ranked the highest with 0.36 as compared to Aluminium with a score of
0.35 and Steel Tube with a score of 0.29. As Square Tube scored the highest, Square
Tube should be selected as the most suitable material for the in-house tools trolley
fabrication, followed by Aluminium and Steel Tube.

4.4. Consistency check
The last step in AHP is to check the level of consistency of the developed pairwise
comparison matrixes so that the results are reliable and can be recommended to
the management for decision making. The degree of consistency for the pairwise
DOI 10.18502/kss.v3i22.5067

Page 480

FGIC2019

Figure 6: AHP Overall Ranking (Source: Authors’ own work).

comparison in the decision criteria matrix is determined by computing the ratio of
Consistency Index (CI) to Random Index (RI).
CI/RI = 0.06593/0.90 = 0.07326
In this case study, the consistency result is 0.07326, which is
Purchase answer to see full
attachment