Decision Modelling for Business Analytics | Desklib

Learn about decision modelling for business analytics with Desklib's online library. This presentation covers the base model, decision model, scenario analysis, sensitivity analysis, risk analysis, output summary and recommendations for MIS775 (Trimester 1, 2018). The presentation also includes a conceptual model, frequency distribution, best and worst case scenarios, sensitivity analysis and recommendations.

DECISION MODELLING FOR
BUSINESS ANALYTICS
DECISION MODELS FOR BUSINESS ANALYTICS
MIS775 (TRIMESTER 1, 2018)
PREPARED BY-(GROUP )

TABLE OF CONTENTS
 Description of the base model
 Bubble diagram and assumptions of the base model
 Decision Model
 Scenario Analysis
 Sensitivity Analysis
 Risk Analysis
 Output summary
 Recommendations

BRIEF
DESCRIPTION
Home Mortgage loan depends on monthly
take home salary. The net take home salary
component is taken into account for
eligibility of loan amount.
Number of borrowers were also considered
for the loan eligibility criterion.
The conceptual model describes the four
segment variables, fixed inputs, calculated
variables, stochastic and decision variables.
The interest rate was taken from earlier
knowledge and was simulated for different
options.
Home loan amount was taken as fixed and
user input variable, where maximum loan
capacity was calculated.

CONCEPTUAL MODEL
Gas
MaintenanceWaste
Removal
Cable
Phone
Electricity
Insurance
Council
Rates
Medical
Clothing
Cable TV
Internet
Others
Max Monthly
Payment
Loan Tenure
Total Interest Paid
Actual tenure of
Payment (Years)
Loan Amount
Based on Max
Monthly Payment
Future Value of
the Loan
Take Home
Salary
Total
Monthly
Cost
Anuual Interest
Rate
Term of
Mortgage
Water &
Sewer
Fixed Factors
Decision
Variable
Stochastic
Variable
Calculated
Variable
LEGEND

CONCEPTUAL
MODEL
Home loan amount depends on the net
monthly instalment.
Monthly instalment depends on monthly
salary and monthly expenditure.
Interest of the home loan initially was
taken as 4% in the model, later to be
simulated.
Stochastic Variables were the interest
rate and term of mortgage .
Actual tenure of the loan and total
interest paid were calculated.
Future value of the loan was evaluated
to find total interest payable.
The final loan tenure depend on all the
above factors.

DECISION MODEL
FIXED INPUTS
 There are 14 monthly factors for expenditure.
 Number of Borrowers was kept as a variable. The input
value was either 1 or 2.
 The monthly expenditure is the total of all fixed
expenditure.
 Monthly salary of an individual was taken as $3500
DECISION VARIABLES
 Total Monthly cost and max monthly payment were two
decision variables of the model.
Number of Borrowers 2
Take Home MonthlySalary $ 3,500.00
Fixed Inputs
Phone $ 128.00
Electricity $ 134.00
Gas $ 147.00
Water and sewer $ 183.00
Cable $ 171.00
Waste removal $ 78.00
Maintenance or repairs $ 54.00
Insurance $ 157.00
Medical $ 197.00
Clothing $ 102.00
Others $ 52.00
Council Rates $ 66.00
Cable TV $ 118.00
Internet $ 82.00
Decision Variables
Total Monthly Cost $ 1,669.00
Max Monthly Payment Based on Income $ 1,971.00
Financing (Stochastic)
Term of Mortgage (years) 20
Annual Interest Rate 4.00%
Calculated Variables
Total Interest Paid $ 1,47,781.92
Actual tenure of Payment (Years) 20.00
Future Value of the Loan $ 4,73,040.00
Loan Amount Based on Max Monthly Payment $ 3,25,258.08

DECISION MODEL
STOCASTIC VARIABLES
 Maximum tenure of the loan duration was taken as 20
years and later simulated.
 Annual interest rate was initially 4% and later
simulated within a a range of 3% to 10%
CALCULATED VARIABLES
 The calculated total interest payable was $1,47,781.92
for base model.
 The total payable cost is calculated is $4,73,040.00.
 The total loan amount affordable was calculated as $
3,25,258.08.
Number of Borrowers 2
Take Home MonthlySalary $ 3,500.00
Fixed Inputs
Phone $ 128.00
Electricity $ 134.00
Gas $ 147.00
Water and sewer $ 183.00
Cable $ 171.00
Waste removal $ 78.00
Maintenance or repairs $ 54.00
Insurance $ 157.00
Medical $ 197.00
Clothing $ 102.00
Others $ 52.00
Council Rates $ 66.00
Cable TV $ 118.00
Internet $ 82.00
Decision Variables
Total Monthly Cost $ 1,669.00
Max Monthly Payment Based on Income $ 1,971.00
Financing (Stochastic)
Term of Mortgage (years) 20
Annual Interest Rate 4.00%
Calculated Variables
Total Interest Paid $ 1,47,781.92
Actual tenure of Payment (Years) 20.00
Future Value of the Loan $ 4,73,040.00
Loan Amount Based on Max Monthly Payment $ 3,25,258.08

FREQUENCY
DISTRIBUTION
MEAN : 7.36%
MODE : 8.00%
MINIMUM : 4.0%
MAXIMUM : 10.0%
1.00%
1.50%
2.00%
2.50%
3.00%
3.50%
4.00%
4.50%
5.00%
5.50%
6.00%
6.50%
7.00%
7.50%
8.00%
8.50%
9.00%
9.50%
10.00%
0
2
4
6
8
10
12
14
Frequency Distribution of Interest Rate
Freq

FREQUENCY
DISTRIBUTION
 Normal distribution was chosen as the probability
distribution model.
 The expected frequency numbers were calculated for
interest loan rate distribution, and the chi test value is
calculated.
 Interest is a continuous data and hence normal
distribution is used for the frequency distribution.
 The results pointed out some outlier values for interest
movement.
 Market volatility was the probable reason for this.
 By observing the above frequency distribution graph, it
is concluded that interest movement rate was almost
normal with some outlier values.
1.00%
1.50%
2.00%
2.50%
3.00%
3.50%
4.00%
4.50%
5.00%
5.50%
6.00%
6.50%
7.00%
7.50%
8.00%
8.50%
9.00%
9.50%
10.00%
10.50%
11.00%
0
2
4
6
8
10
12
14
Observed and Expected Interest Rate
Freq Expected Freq

BEST AND WORST
CASE SCENARIO
Scenario Summary
Current Values: Scenario 4
Changing Cells:
$B$29 4.00% 5.00%
Result Cells:
$B$33 13.76 15.07
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
Scenario Summary
Current Values: Scenario 1 Scenario 2 Scenario 3
Changing Cells:
$B$3 7506 7506 7506 7506
$B$23 $ 6,191.00 $ 3,500.00 $ 6,191.00 $ 6,191.00
$B$22 $ 1,647.00 $ 1,647.00 $ 2,000.00 $ 1,647.00
$B$29 0.04 0.04 0.04 0.05
Result Cells:
$B$33 20.00 20.00 20.00 20.00
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.

Case Scenario
Scenarios
 In scenario 1 increase in take home
salary increased the loan affordability
without change in loan tenure.
 In scenario 2 increase in monthly cost
is assessed.
 In scenario 3 increase in interest rate
without keeping loan amount fixed is
assessed.
 The change in interest rate yielded
increased loan tenure. Loan amount
was kept fixed at $2,50,000. For 4%
interest rate the loan tenure was
13.76 years whereas 5% interest
tenure increased to 15.06 years.

SENSITIVITY ANALYSIS
The above sensitivity analysis conducted, shows that if costs of staffing and
the room tariff at the partner hotel are raised, we would experience a drastic
drop in the profits.

RISK ANALYSIS
The greatest risk is associated with the
monthly cost and interest rate of home
loan.
Increase in monthly expenditure will
deter the loan amount eligibility. Keeping
the loan amount fixed the interest rate will
go high.
 For a fixed loan amount the increase in
interest rate will cause higher monthly
payment.
 Max monthly payment is optimized at
$1971 for 20 years of loan, keeping
salary as fixed.

OUTPUT SUMMARY
The average interest simulated rate
is 9.29%.
The minimum interest rate is
14.29%; which is a possibility that
could occur during high market
volatile situation and inflation.
The standard deviation was of $
2.6%.
Actual tenure of Payment (Years)
Mean 9.291
Standard Error 0.369
Median 8.089
Standard Deviation 2.606
Sample Variance 6.790
Kurtosis -1.176
Skewness 0.585
Range 8.478
Minimum 5.810
Maximum 14.289
Sum 464.535
Count 50

RECOMMENDATIONs
 Increase number of borrowers to
increase loan eligibility.
 Reduction in monthly cost to
increase monthly payment amount
so that tenure of loan decreases..
 Avail low interest rates when rates is
lowered by banks due to volatility in
market.
 Higher initial down payment to
reduce loan tenure.

CONTRIBUTION

THANK YOU