Assignment Details:
You are required to develop an MS Excel based decision model that can be used to investigate and explore decisions and risks relating to taking a mortgage loan to purchase a property. The model needs to be generic enough to cater for single or multiple ownership (e.g. a couple purchasing using a joint mortgage loan) and enable the decision makerto explore the size the loan that is viable/manageable within different scenarios of income, living expenses, loan amount, deposit made on the loan, repayment amount, other financial commitments etc., the amount of interest paid and understand the risks associated with meeting the loan commitments.
The decision model must be realistic and easy to use. The level of complexity modelled, for example the choice of deterministic vs. stochastic inputs, input distributions, time units modelled etc., is left to your discretion. However the model must enable the user to input the following living expenses and loan details/options. You may add other inputs that you consider to be relevant.
- Council Rates
- Utilities (Electricity& Gas &Water)
- Telephone/mobile/Internet
- House Insurance
- Maintenance
- Groceries
- Medical
- Cloths
- Holidays
- Transport
- Other
There is no data provided. You are required to create a fictitious client and demonstrate the utility of the decision model using real data where available (e.g. mortgage interest rates, public transport, petrol) and create data where it is not (e.g. salaries, living expenses).
MIS775 DECISION MODELS FOR BUSINESS ANALYTICS
TRIMESTER 1, 2016
The minimum requirements of the decision model are:
1. Ability to enter loan details, income, costs and expenses to explore decision options relating to the loan amount and repayments to calculate outputs such as total interest paid and duration of loan.
2. Ability to understand the impact of variation to the loan interest rate, income, salaries and cost of living over the duration of the loan.
3. Stochastic treatment of some of the inputs to explore resulting simulated output and summarise risks.
The PowerPoint presentation should form the content of a report that includes:
1. A brief description of the model (maximum 100 words)
2. A bubble diagram or similar capturing the conceptual model used and assumptions behind the decision model (Using Analytica can add some bonus points).
3. Summary report of the sensitivity analysis of inputs.
4. The best and worst case scenarios (for the data you have used) and a brief discussion of the consequences.
5. Risk analysis report corresponding to the simulation modelling along with summary of input parameters/distributions used.
File Marks*
Conceptual model as a bubble diagram + assumptions PowerPoint 15%
Decision model Excel 15%
Sensitivity analysis reports Excel & PowerPoint 10%
Scenario analysis report Excel & PowerPoint 20%
Stochastic model including choice of distributions Excel & PowerPoint 20%
Simulated output distribution and percentile analysis Excel 10%
Risk analysis report Excel & PowerPoint 10%
* Total unit mark for assignment 1 is 20%.