MINISTRY OF EDUCATION AND TRAINING
UEH UNIVERSITY – UEH COLLEGE OF BUSINESS
SCHOOL OF INTERNATIONAL BUSINESS - MARKETING
&
FINAL ESSAY
SUBJECT: MANAGEMENT SCIENCE
MAI NGUYỄN KHÁNH TRÂN
Ho Chi Minh City, December 20th 2021.
UEH UNIVERSITY
UEH COLLEGE OF BUSINESS
SCHOOL OF INTERNATIONAL BUSINESS - MARKETING
&
FINAL ESSAY:
MANAGEMENT SCIENCE
Subject : Management Science
Lecturer : Nguyễn Thị Hồng Thu
Student Name : Mai Nguyễn Khánh Trân
Student ID: : 31201026018
Class - Batch : IBC07 - 46
Major : International Business
Ho Chi Minh City, December 20th 2021.
ACKNOWLEDGEMENT
First and foremost, I would like to express my sincere appreciation to Mrs. Nguyen Thi Hong
Thu. You have provided me with a great deal of attention, assistance, and direction during my
studies in Management Science. As a lecturer, you have assisted us in acquiring a greater
understanding of Management Science so that I may apply what I have learned to analyze an
issue in my daily life and gain a deeper understanding of the subject.
Even though I put my best effort into this essay, there may still be some inaccuracies. I'm
looking to receive feedback from you that helps me to improve.
Management Science, I feel, will be highly valuable luggage for us on our future journey. I
would like to express my gratitude one more and wish you health, happiness, and success.
1
ENDORSEMENT
I declare that this essay is entirely my own works. The essay's contents and conclusions are
original, not copied from other units or individuals. If any of the foregoing statements are
false, I will accept full responsibility in front of Ms. Nguyen Thi Hong Thu.
2
COMMENTS OF THE LECTURER
3
TABLE OF CONTENTS
1. LINEAR PROGRAMMING ………………………………………………………. 6
a. Formulate a linear programming model and write down the mathematical model
for this problem …………………………………………………………………….. 6
b. Solve this problem using QM and SOLVER …………………………………… 7
c. Create a sensitivity report ……………………………………………………….. 12
d. Explain the shadow price, reduced cost and the allowable range (increase and
13
decrease) …………………………………………………………………………….
15
2. DECISION MAKING ………………………………………………………………
18
3. FORECASTING …………………………………………………………………….
18
a. Using averaging forecasting method, calculate the forecast …………………….
b. Using 3-month moving average forecasting method (n=3), calculate the forecast
20
…………………………………………………………………………..
21
c. Using last-value forecasting method, calculate the forecast …………………….
d. Explain 3 methods of forecast. Which one is better and more accurate according
22
to you? You can explain however you want …………………………………….
4
TABLE OF FIGURES
Figure 1.1. Enter data to the Excel Spreadsheet ……………………………………………… 8
Figure 1.2. Determind and enter the constraint ………………………………………………. 8
Figure 1.3. Determine and enter the objective ………………………………………………... 9
Figure 1.4. Set up Solver Parameters ………………………………………………………….. 10
Figure 1.5. The final results for solving Inner Problem …………………………………….. 10
Figure 1.6. Set up the data for Linear Programming ………………………………………... 11
Figure 1.7. Change variables, constraints and enter data ………………………………….. 11
Figure 1.8. The final result for solving Inner Problem ……………………………………… 12
Figure 1.9. Create sensitivity report …………………………………………………………… 12
Figure 1.10. Reduced cost in Sensitivity Report ……………………………………………… 14
Figure 2.1. Set up the data to draw decision tree …………………………………………….. 16
Figure 2.2. A window for drawing Decision tree on Qm for Windows ……………………. 16
Figure 2.3. Forming a decision tree ……………………………………………………………. 16
Figure 2.4. Forming a decision tree ……………………………………………………………. 17
Figure 2.5. Forming a decision tree ……………………………………………………………. 17
Figure 2.6. The final solution for Petrolimex problem ………………………………………. 17
Figure 3.1. The Excel spreadsheet for the Averaging Method ……………………………… 19
Figure 3.2. The Excel spreadsheet for the Moving Average Method ………………………. 20
Figure 3.3. The Excel spreadsheet for the Last-value Method ……………………………… 21
Figure 3.4. MAD and MSE comparion of three methods ……………………………………. 23
5
MAIN TEXT
1. LINEAR PROGRAMMING.
Problem Imagination:
INNER is a spa that specializes in skin care and consultations for skin therapy. It has six
available skin specialists, as well as eight current and former customers in need of care and
therapy. As each specialist has their own set of abilities and experience, the center charges
different hourly rates for its services. Also, each specialist's skill is better suited to one
customer's skin condition than another, and some customers prefer one specialist over
another. The specialist's suitability for each customer's condition is rated on a 5-point scale,
with 1 is the worst and 5 is the best. The following table shows each specialist's rating for
each customer, as well as the hours available to the specialist, the agreed-upon hours and
budget to care for each customer:
Customer
Hourly Available
Specialist 1 2 3 4 5 6 7 8
wage hours
A $155 3 3 5 5 3 2 3 3 550
B $145 3 3 2 5 5 5 3 2 610
C $165 2 1 3 3 2 1 4 3 500
D $300 2 3 1 1 2 2 5 1 400
E $280 3 1 1 2 2 1 2 3 700
F $150 3 5 3 2 3 5 4 3 860
Agreed-
500 240 420 475 350 460 300 210
upon Hours
Agreed-
upon budget 100 80 110 90 65 85 50 55
(x1000 USD)
The center wants to know how many hours to assign each specialist to each customer in order
to best utilize their skill while meeting clients needs.
a. Formulate a linear programming model and write down the mathematical
model for this problem.
Variables:
Let Xi = The number of hours assigned each specialist to each customer (With X = (A; B; C;
D; E; F) and i = [1;8]).
Objective: Maximize the total suitability of specialists for customers means maximize the
total rating:
Total rating point = (3A1+3A2+5A3+5A4+3A5+2A6+3A7+3A8) +
(3B1+3B2+2B3+5B4+5B5+5B6+3B7+2B8) + (2C1+C2+3C3+3C4+2C5+C6+4C7+3C8) +
6
(2D1+3D2+D3+D4+2D5+2D6+5D7+D8) + (3E1+E2+E3+2E4+2E5+E6+2E7+3E8) +
(3F1+5F2+3F3+2F4+3F5+5F6+4F7+3F8)
Constraints:
• The hours of each specialist spent on a customer must equal or smaller than their
available hours.
Consultant A: A1+A2+A3+A4+A5+A6+A7+A8 ≤ 550.
Consultant B: B1+B2+B3+B4+B5+B6+B7+B8 ≤ 610.
Consultant C: C1+C2+C3+C4+C5+C6+C7+C8 ≤ 500.
Consultant D: D1+D2+D3+D4+Đ5+D6+D7+D8 ≤ 400.
Consultant E: E1+E2+E3+E4+E5+E6+E7+E8 ≤ 700.
Consultant F: F1+F2+F3+F4+F5+F6+F7+F8 ≤ 860.
• The total hours of each customer must equal to their agreed-upon hours.
Customer 1: A1+B1+C1+D1+E1+F1 = 500.
Customer 2: A2+B2+C2+D2+E2+F2 = 240.
Customer 3: A3+B3+C3+D3+E3+F3 = 420.
Customer 4: A4+B4+C4+D4+E4+F4 = 475.
Customer 5: A5+B5+C5+D5+E5+F5 = 350.
Customer 6: A6+B6+C6+D6+E6+F6 = 460.
Customer 7: A7+B7+C7+D7+E7+F7 = 300.
Customer 8: A8+B8+C8+D8+E8+F8 = 210.
• The total cost spent on each customer must equal or smaller than their agreed-upon
budget.
Customer 1: 155A1+145B1+165C1+300D1+280E1+150F1 ≤ 100.000 ($).
Customer 2: 155A2+145B2+165C2+300D2+280E2+150F2 ≤ 80.000.
Customer 3: 155A3+145B3+165C3+300D3+280E3+150F3 ≤ 110.000.
Customer 4: 155A4+145B4+165C4+300D4+280E4+150F4 ≤ 90.000.
Customer 5: 155A5+145B5+165C5+300D5+280E5+150F5 ≤ 65.000.
Customer 6: 155A6+145B6+165C6+300D6+280E6+150F6 ≤ 85.000.
Customer 7: 155A7+145B7+165C7+300D7+280E7+150F7 ≤ 50.000.
Customer 8: 155A8+145B8+165C8+300D8+280E8+150F8 ≤ 55.000.
b. Solve this problem using QM and SOLVER.
SOLVER:
Step 1: Open Excel and enter the data.
- Rating for each specialist for each customer (C5:J10).
- Hourly Wage (K5:K10).
7
- Available hours of each specialist (M14:M19).
- Agreed-upon Hours (C20:J20).
- Maximum budget for each customer as agreed-upon (C24:J24).
Figure 1.1. Enter data to the Excel Spreadsheet
- Determine variable cells: The decision to be made (how many hours to assign each
specialist to each customer) is shown in C14:J19.
Step 2: Determine the constraints.
- There are 03 constraints needed to solve this problem:
+ The hours of each specialist spent on a customer must equal or smaller than their
available hours.
+ The total hours of each customer must equal to their agreed-upon hours.
+ The total cost spent on each customer must equal or smaller than their agreed-upon
budget.
Figure 1.2. Determind and enter the constraint
- Hour per Specialist:
8
+ Specialist A: K14 =SUM(C14:J14)
+ Specialist B: K15 =SUM(C15:J15)
+ Specialist C: K16 =SUM(C16:J16)
+ Specialist D: K17 =SUM(C17:J17)
+ Specialist E: K18 =SUM(C18:J18)
+ Specialist F: K19 =SUM(C19:J19)
- Hour per Customer:
+ Customer 1: C20 =SUM(C14:C19)
+ Customer 2: D20 =SUM(D14:D19)
+ Customer 3: E20 =SUM(E14:E19)
+ Customer 4: F20 =SUM(F14:F19)
+ Customer 5: G20 =SUM(G14:G19)
+ Customer 6: H20 =SUM(H14:H19)
+ Customer 7: I20 =SUM(I14:I19)
+ Customer 8: J20 =SUM(J14:J19)
- Cost per Customer:
+ Customer 1: C24 =SUMPRODUCT(C14:C19,K5:K10)
+ Customer 2: D24 =SUMPRODUCT(D14:D19,K5:K10)
+ Customer 3: E24 =SUMPRODUCT(E14:E19,K5:K10)
+ Customer 4: F24 =SUMPRODUCT(F14:F19,K5:K10)
+ Customer 5: G24 =SUMPRODUCT(G14:G19,K5:K10)
+ Customer 6: H24 =SUMPRODUCT(H14:H19,K5:K10)
+ Customer 7: I24 =SUMPRODUCT(I14:I19,K5:K10)
+ Customer 8: J24 =SUMPRODUCT(J14:J19,K5:K10)
Step 3: Determine the objective.
The objective for this problem is to maximize the total suitability of specialists for customers
which means maximize the total rating point.
9
Figure 1.3. Determine and enter the objective
- Rating Point per Specialist:
+ Specialist A: N14 =SUMPRODUCT(C14:J14,C5:J5)
+ Specialist B: N15 =SUMPRODUCT(C15:J15,C6:J6)
+ Specialist C: N16 =SUMPRODUCT(C16:J16,C7:J7)
+ Specialist D: N17 =SUMPRODUCT(C17:J17,C8:J8)
+ Specialist E: N18 =SUMPRODUCT(C18:J18,C9:J9)
+ Specialist F: N19 =SUMPRODUCT(C19:J19,C10:J10)
- Total Rating Point: N26 =SUM(N14:N19)
Step 4: Use SOLVER to solve the problem.
Choose Data → Solver → Set up Solver Parameters.
Figure 1.4. Set up Solver Parameters
Then, click Solve and the results will appear on the spreadsheet as below.
10
Figure 1.5. The final results for solving Inner Problem
QM FOR WINDOWS:
Step 1: Open QM for Window. On module tree, choose Module → Linear Programming →
Set up the data for Linear Programming as below.
Figure 1.6. Set up the data for Linear Programming
Step 2: Change the name of variables and constraints, the enter the data.
Variables: A1, B1, …, F1, A2, B2, …F2,… ,A8, B8, …, F8.
- A, B, …, F are the specialists
- 1, 2, …, 8 are the customers.
Constraints:
- Constraint 1 to Constraint 6: Specialist A to Specialist F.
- Constraint 7 to Constraint 14: C1 to C8 (Hour per Customer)
- Constraint 15 to Constraint 22: C1 to C8 (Cost per Customer)
Figure 1.7. Change variables, constraints and enter data.
Step 3: Click Solve or Solution to see the results.
11
Figure 1.8. The final result for solving Inner Problem
c. Create a sensitivity report.
Once again choose Solver in Excel to solve the problem. When the Solver Result Windows
appear, choose Sensitivity and OK.
Figure 1.9. Create sensitivity report
Then we have the Sensitivity Report as below.
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$K$14 A Hour per Specialist 550 2.769230769 550 81.2962963 85.66022544
$K$15 B Hour per Specialist 610 2.769230769 610 81.2962963 85.66022544
$K$16 C Hour per Specialist 500 0.769230769 500 81.2962963 85.66022544
$K$17 D Hour per Specialist 75.61965812 -3.10649E-16 400 1E+30 324.3803419
$K$18 E Hour per Specialist 359.3803419 0 700 1E+30 340.6196581
$K$19 F Hour per Specialist 860 2 860 71.91595442 168.0840456
Hour per Customer
$C$20 500 -1.307692308 500 78.03902116 33.38955026
Customer
$D$20 Hour per Customer 240 3 240 110.7086895 71.91595442
12
$E$20 Hour per Customer 420 2.230769231 420 85.66022544 81.2962963
$F$20 Hour per Customer 475 2.230769231 475 85.66022544 81.2962963
$G$20 Hour per Customer 350 2.230769231 350 85.66022544 81.2962963
$H$20 Hour per Customer 460 3 460 106.6666667 71.91595442
$I$20 Hour per Customer 300 1.068376068 300 3.03030303 36.58333333
$J$20 Hour per Customer 210 1.127090301 210 35.18187831 13.57142857
$C$24 Cost per Customer = 100000 0.015384615 100000 9349.074074 21850.92593
$D$24 Cost per Customer = 46787.39316 0 80000 1E+30 33212.60684
$E$24 Cost per Customer = 65950 0 110000 1E+30 44050
$F$24 Cost per Customer = 71025 -1.3344E-18 90000 1E+30 18975
$G$24 Cost per Customer = 50750 0 65000 1E+30 14250
$H$24 Cost per Customer = 69000 0 85000 1E+30 16000
$I$24 Cost per Customer = 50000 0.013105413 50000 10975 500
$J$24 Cost per Customer = 55000 0.006688963 55000 3800 9850.925926
d. Explain the shadow price, reduced cost and the allowable range (increase and
decrease).
Shadow price:
Hour per Specialist:
- The shadow price of A Hours per Specialist and B Hours per Specialist (hours used by
specialist A/B) is 2.769230769. It means that if a one-hour change is made in the number of
hours used by specialist A or B, there will be a change of $2.769230769 in Total Cost.
- The shadow price of C Hours per Specialist (hours used by specialist C) is 0.769230769. It
means that if a one-hour change is made in the number of hours used by specialist C, there
will be a change of $0.769230769 in Total Cost.
- The shadow price of D Hours per Specialist and E Hours per Specialist (hours used by
specialist D/E) is 0. It means that if a one-hour change is made in the number of hours used
by specialist D or E, there will be no change in Total Cost.
- The shadow price of F Hours per Specialist (hours used by specialist F) is 2. It means that if
a one-hour change is made in the number of hours used by specialist E, there will be a change
of $2 in Total Cost.
Hour per Customer:
- The shadow price of the Hour per Customer of customer 2, 6 is 3. It means that if a one-
hour change is made in the Hour per Customer of one of these customers, there will be a
change of $3 in Total Cost.
- The shadow price of the Hour per Customer of customer 3, 4, 5 is 2.230769231. It means
that if a one-hour change is made in the Hour per Customer of one of these customers, there
will be a change of $2.230769231 in Total Cost.
13
- The shadow price of the Hour per Customer of customer 7 is 1.068376068. It means that if a
one-hour change is made in the Hour per Customer of one of these customers, there will be a
change of $1.068376068 in Total Cost.
- The shadow price of the Hour per Customer of customer 8 is 1.127090301. It means that if a
one-hour change is made in the Hour per Customer of one of these customers, there will be a
change of $1.127090301 in Total Cost.
Cost per Customer:
- The shadow price of Cost per Customer of project 1 is 0.015384615. It means that if a one
dollar change is made in the Cost per Customer of one of these project, there will be a change
of $0.015384615 in Total Contract Cost.
- The shadow price of Cost per Customer of project 2, 3, 4, 5, 6 is 0. It means that if a one
dollar change is made in the Cost per Customer of one of these project, there will be no
change in Total Contract Cost.
- The shadow price of Cost per Customer of project 7 is 0.013105413. It means that if a one
dollar change is made in the Cost per Customer of one of these project, there will be a change
of $0.013105413 in Total Cost.
- The shadow price of Cost per Customer of project 8 is 0.006688963. It means that if a one
dollar change is made in the Cost per Customer of one of these project, there will be a change
of $0.006688963 in Total Cost.
Reduced cost: The reduced cost for a variable is nonzero only when the variable’s value
equal to its upper or lower bound at optimal solution. The reduced cost measures the change
in the objective function’s value per unit increase in the variable’s value. Some example
analysis below are more detailed.
- The dual value for Specialist A to care
for customer 5 is -2, which means if we
were tighten the lower bound on this
variable (move it from 0 to 1), the Total
Cost would decrease by 2.
- The dual value for Specialist D to care
for customer 6 is -1, which means if we
were tighten the lower bound on this
variable (move it from 0 to 1), the Total
Cost would decrease by 1.
14
Figure 1.10. Reduced cost in Sensitivity
Report
Allowable range: Suppose that there are changes in the data, the allowable range would help
the center to determine whether those changes affect the shadow price and the solution or not.
Details are as some example below.
- Example 1: Hourly wage of specialist A increase from $155 to $220 and hourly wage of
specialist D decrease from $300 to $250.
Percentage of allowable increase=100 ( 220−155155 ) ≈ 41,93 %
Percentage of allowable decrease=100 ( 300250− 250 ) ≈ 16,67 %
∑ ¿58,6 % <100 %
→ So, the shadow price and the solution won’t change.
- Example 2: Hourly wage of specialist B decrease from $145 to $100 and hourly wage of
specialist F increase from $150 to $320.
Percentage of allowable increase=100 ( 145145− 100 ) ≈ 31,03%
Percentage of allowable decrease=100 ( 320150− 150 ) ≈113,3 %
∑ ¿144,33 % >100 %
→ So, the shadow price and the solution will change.
2. DECISION MAKING.
Petrolimex Gas station are soon going to open a new dealership. They have 3 offers: from a
local gas company, from a provider and from a big gas corporation. The success of each type
of dealership will depend on how much gasoline is going to be available during the next few
years.
Fill the profit of each type of dealership, giving the availability of gas data to the following
payoff table (unit: million VND). Draw a decision tree to help Petrolimex choose what’s best
for the profit.
Dealership Gasoline Shortage Gasoline Surplus
Local gas company 350,000 200,000
15
Provider -100,000 700,000
Corporation 150,000 220,000
Prior probability (%) 60% 40%
Step 1: Open QM for Windows. On module tree, choose Module → Decision Analysis →
New + Decision Trees (Graphical) → Set up the data as below.
Figure 2.1. Set up the data to draw decision tree
Step 2: Then click OK. The initial node will appears on the screen (node 1) is the decision
node.
Figure 2.2. A window for drawing Decision tree on Qm for Windows
Step 3: As there are 3 options, set 3 for the number of branches and click “Add n branches”.
The decision tree will appear as below.
16
Figure 2.3. Forming a decision tree
Step 4: Rename the 3 decisions as the options given (Local company, Provider and Big
corporation). Then add 2 branches for the state of nature for each node (Shortage - 60% and
Surplus - 40%).
Figure 2.4. Forming a decision tree
Step 5: Add the probability and profit for each event node as the data in the payoff table. The
decision tree will be as below.
Figure 2.5. Forming a decision tree
Step 6: Click Solutions and the results will appear.
17
Figure 2.6. The final solution for Petrolimex problem
The result has given the EMV (Expected Monetary Value) for each decision. The blue branch
is the suggested decision. It means that to maximize the profits, Petrolimex should choose
option form Local gas company, which has the highest EMV of 290,000 million VND.
3. FORECASTING.
The monthly demand of a water bottle extracted from a supermarket data is as the following
table.
Month Demand
1 22
2 24
3 21
4 27
5 23
6 25
7 21
8 24
9 22
10 28
11 25
12 29
To know the reliability of each forecast, we need to calculate the Forecasting Error, Mean
Absolute Deviation (MAD), and Mean Square Error (MSE).
- Forecasting Error = |Demand – Forecasting demand| .
- MAD =
∑ of Forecasting errors
Number of forecasts
18
- MSE =
∑ of square of Forecasting errors
Number of forecasts
a. Using averaging forecasting method, calculate the forecast.
In this method, the forecasting demand of the next month is the average of all previous
months since the first month is calculated.
As the number of water bottles cannot be a non-integer or a negative integer, the results are
rounded to the nearest integers. In Excel, the data are painted in blue, the results are painted
in orange.
Figure 3.1. The Excel spreadsheet for the Averaging Method
- Averaging Forecast:
+ D5 =C4
+ D6 =AVERAGE(C4:C5)
+ D7 =AVERAGE(C4:C6)
+ D8 =AVERAGE(C4:C7)
+ D9 =AVERAGE(C4:C8)
+ D10 =AVERAGE(C4:C9)
+ D11 =AVERAGE(C4:C10)
+ D12 =AVERAGE(C4:C11)
+ D13 =AVERAGE(C4:C12)
+ D14 =AVERAGE(C4:C13)
+ D15 =AVERAGE(C4:C14)
+ D16 =AVERAGE(C4:C15)
- Forecasting error:
+ E5 =ABS(C5-D5)
+ E6 =ABS(C6-D6)
19
+ E7 =ABS(C7-D7)
+ E8 =ABS(C8-D8)
+ E9 =ABS(C9-D9)
+ E10 =ABS(C10-D10)
+ E11 =ABS(C11-D11)
+ E12 =ABS(C12-D12)
+ E13 =ABS(C13-D13)
+ E14 =ABS(C14-D14)
+ E15 =ABS(C15-D15)
- MAD = H5 =AVERAGE(E5:E15)
- MSE = H9 ==SUMSQ(E5:E15)/COUNT(E5:E15)
b. Using 3-month moving average forecasting method (n=3), calculate the
forecast.
In this method, the forecasting demand of the next month is the average of 3 previous recent
months (n=3)
As the last method, the number of water bottles cannot be a non-integer or a negative integer,
the results are rounded to the nearest integers.
Figure 3.2. The Excel spreadsheet for the Moving Average Method
- Forecasting Demand:
+ D7 =AVERAGE(C4:C6)
+ D8 =AVERAGE(C5:C7)
+ D9 =AVERAGE(C6:C8)
+ D10 =AVERAGE(C7:C9)
+ D11 =AVERAGE(C8:C10)
+ D12 =AVERAGE(C9:C11)
+ D13 =AVERAGE(C10:C12)
20
+ D14 =AVERAGE(C11:C13)
+ D15 =AVERAGE(C12:C14)
+ D16 =AVERAGE(C13:C15)
- Forecasting error:
+ E7 =ABS(C7-D7)
+ E8 =ABS(C8-D8)
+ E9 =ABS(C9-D9)
+ E10 =ABS(C10-D10)
+ E11 =ABS(C11-D11)
+ E12 =ABS(C12-D12)
+ E13 =ABS(C13-D13)
+ E14 =ABS(C14-D14)
+ E15 =ABS(C15-D15)
- MAD = H5 =AVERAGE(E7:E15)
- MSE = H9 ==SUMSQ(E7:E15)/COUNT(E7:E15)
c. Using last-value forecasting method, calculate the forecast.
In this method, the forecasting demand of the next month is the demand of the last month.
Also, the number of water bottles cannot be a non-integer or a negative integer, the results are
rounded to the nearest integers.
Figure 3.3. The Excel spreadsheet for the Last-value Method
- Averaging Forecast:
+ D5 =C4
+ D6 =C5
+ D7 =C6
+ D8 =C7
21
+ D9 =C8
+ D10 =C9
+ D11 =C10
+ D12 =C11
+ D13 =C12
+ D14 =C13
+ D15 =C14
+ D16 =C15
+ D17 =C16
- Forecasting error:
+ E5 =ABS(C5-D5)
+ E6 =ABS(C6-D6)
+ E7 =ABS(C7-D7)
+ E8 =ABS(C8-D8)
+ E9 =ABS(C9-D9)
+ E10 =ABS(C10-D10)
+ E11 =ABS(C11-D11)
+ E12 =ABS(C12-D12)
+ E13 =ABS(C13-D13)
+ E14 =ABS(C14-D14)
+ E15 =ABS(C15-D15)
- MAD = H5 =AVERAGE(E5:E15)
- MSE = H9 ==SUMSQ(E5:E15)/COUNT(E5:E15)
d. Explain 3 methods of forecast. Which one is better and more accurate
according to you? You can explain however you want.
Explain:
Averaging Forecasting Method:
- This method uses all the data points in the time series and simply average these points. The
forecast of the next month is the average of all previous months since the first month is
calculated.
Forecast = Average of all data to date
D m −1 + Dm −2 +...+ Dm − n
- Formula: Dm =
n
In which:
D is the demand
m is the forecasting month
22
n is the number of all the months before m since the first month is calculated
Moving Average Forecast Method:
- In this method, forecast for the next month is the average of the monthly sales for the most
recent months. The number of months being used varies in different problems.
D m −1 + Dm −2 +...+ Dm − n
- Formula: Dm =
n
In which:
D is the demand
m is the forecasting month
n is the number of months being used
Last-value Forecasting Method:
- This method is also known as the naive method. It uses the last month’s demand as the
forecast for the next month’s demand.
- Formula: Dm = D m − 1
In which:
D is the demand
m is the forecasting month
Comparison:
To measure the reliability of the forecast, we calculate MAD (Mean Absolute Deviation) and
MSE (Mean Square Error). The lower MAD and MSE are, the more reliable the forecasts are.
We compare the results of MAD and MSE in each method to see which one is the best and
the most accurate.
Figure 3.4. MAD and MSE comparison of three methods
As the calculation from Excel showing above, the MAD and MSE in the Averaging
Forecasting method are the lowest, which indicates that the forecasts in this method is the
most reliable.
However, Averaging Forecasting is not always the best method to apply in forecasting. In
fact, the best method depends on each case and each situation.
23
--------
24
