Spreadsheet Decision Modeling

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

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:

Student’s ID Number:

Academic Year: 2024-2025 (1446 H)

For Instructor’s Use only
Instructor’s Name:
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 – ‫مقيد‬

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

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

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
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
FGIC2019

provided that the original author
and source are credited.
Selection and Peer-review under
the responsibility of the
FGIC2019 Conference Committee.

Conference Paper

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.

An
Analytic
Hierarchy
Process
Approach 1. Introduction
Material process selection is a method to determine the most suitable material to fabricate a
in
product. Many researchers have agreed on the importance of material selection process,
Decisionespecially during the early stage of the product development phase. Determining the most
Making for suitable and appropriate material in the early stage can avoid additional cost if changes are
Material
needed to be carried out after the early stage of the product development process (Ravisankar,
Selection in Balasubramanian & Muralidharan, 2004). However, it is a difficult task with a complex
decision because various factors have to be considered during the process.
an
Automotive 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 and Integrity 2019,
KnE Social Sciences, pages 472–484. DOI 10.18502/kss.v3i22.5067

Page 472

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.

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
DOI 10.18502/kss.v3i22.5067

Page 473

FGIC2019

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.
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.
DOI 10.18502/kss.v3i22.5067

Page 474

FGIC2019

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).

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.
DOI 10.18502/kss.v3i22.5067

Page 475

FGIC2019

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 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
DOI 10.18502/kss.v3i22.5067

Page 476

FGIC2019

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.

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

DOI 10.18502/kss.v3i22.5067

Page 477

FGIC2019

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).

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
DOI 10.18502/kss.v3i22.5067

Page 478

FGIC2019

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 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.

DOI 10.18502/kss.v3i22.5067

Page 479

FGIC2019

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