DEAKIN BUSINESS SCHOOL
DEPARTMENT OF INFORMATION SYSTEMS AND BUSINESS ANALYTICS
MIS275 Decision Analytics
Assignment Two: A spreadsheet-based decision model
Background
This is an individual assignment. The modelling work should be submitted online in the Assignment Folder as a single MS Excel file with the required information in clearly labelled separate worksheets. In addition, you are also required to submit a report that summarises your models and results. Your report must be in MS Word file format. Any other file format, such as pdf, is NOT acceptable and will not be marked. In summary, two files should be submitted – one MS Excel spreadsheet and one MS Word file.
The assignment has six sections:
1. Model description, conceptual model, and assumptions
2. Spreadsheet-based decision model
3. Scenario analysis
4. Spreadsheet-based stochastic decision model including justification for the choice of distribution 5. Simulated distribution for each output and a risk analysis
6. Report.
The requirements of each section are detailed below. The breakdown of marks (total is 30) is given in the Assignment 2 Rubric at the end of this document.
Percentage of final grade 30%
Due date Thursday 30 September 2021 at 8pm AEST
The assignment must be submitted by the due date electronically in CloudDeakin. When submitting electronically, check that you have submitted the work correctly by following the instructions. Please note that we will NOT accept any assignment or part of the assignment submitted after the deadline or via Email.
Any request for an extension must be negotiated at least one week prior to the above deadline by email. Deakin policy for late submission: 5% will be deducted from the 30 marks allocated to this assessment task for each day or part day that the assessment is late, up to five days. Penalties also apply on weekend days and public holidays. When work is submitted more than five days after the due date, the task will not be marked, and the student will receive 0% for the task.
Assurance of Learning
This assignment assesses following Graduate Learning Outcomes and related Unit Learning Outcomes:
Graduate Learning Outcome (GLO) Unit Learning Outcome (ULO)
GLO1: Discipline-specific knowledge and capabilities: appropriate to the level of studyrelated to a discipline or profession. GLO3: Digital literacy: using technologies to find,use and disseminate information
GLO4: Critical thinking: evaluating information using critical and analytical thinking and judgment ULO1: Apply decision models optimisation techniques toconceptualise and
represent a business scenario
ULO2: Apply optimisation software tools to find optimaldecisions for a given business scenario
ULO3: Interpret and analyse the results; investigate the assumptions of the decision model
Feedback
Prior to submission
Students are able to seek assistance from the teaching staff to ascertain whether the assignment conforms tosubmission guidelines. Please post your questions on CloudDeakin’s discussion forum for Assignment 2.
After submission
Your assignment feedback will be returned in the rubric via CloudDeakin with an overall mark and comments.
Assignment Details:
You are required to develop a spreadsheet-based decision model that can be used to investigate and explore decisions and risks relating to taking a business loan (e.g. for investment) for a small-to-medium sized business. The model needs to be generic enough to enable the decision maker to explore the size of the loan that is viable/manageable within different scenarios of income, cost, expenses, loan amount, deposit made on the loan, repayment amount, other financial commitments, the amount of interest paid etc. 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, etc., is left to your discretion. However, the model must enable the user to input the following business cost/expenses and loan details/options.
• Utilities (Electricity& Gas &Water)
• Telephone/mobile
• Insurance
• Maintenance
• Paid salaries
• Other
No data is provided. You are required to create a fictitious business and demonstrate the utility of the decision model using real data where available (e.g. interest rates) and create data where it is not (e.g. income, business expenses).
Note that Net Profit = Sales revenue – Total variable costs – Fixed costs – Overheads.
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, costs, and expenses over the duration of the loan.
3. Stochastic treatment of some of the inputs to explore resulting simulated output and summarize risks.
Your submission will be assessed across these six sections for a total unit mark of 30%:
Section 1: Model description, conceptual model, and assumptions (File: MS Word; 5 marks)
• Provide a brief overview of the model
• Include a conceptual model
• Note any relevant assumptions.
Section 2: Spreadsheet-based decision model (Files: MS Word & Excel; 5 marks)
Design a spreadsheet-based decision model. All cells displaying fixed inputs, stochastic inputs, decision variables, calculated variables and output variables.
Section 3: Scenario analysis (Files: MS Word & Excel; 5 marks)
This section relates to Topic 7. Use Excel’s scenario analysis and investigate the impact that different scenarios for each stochastic input on the outputs. The choice of best and worst scenarios is yours.
Section 4: Stochastic variables including justification for the choice of distribution (Files: MS Word & Excel; 5 marks)
This section relates to Topic 8. This will require you to justify your choice of distribution for each of the stochastic inputs.
Section 5: Simulated output distribution and a risk analysis (Files: MS Word & Excel; 5 marks)
This section relates to Topic 9. This requires you to examine the simulated distributions for each output, and undertake a risk analysis based on simulation modelling, in order to quantify the risks associated with problem.
Section 6: Report (File: MS Word; 5 marks) Word limit: No more than 1500
The report should be a standalone document. It should include:
1. A brief description of the model (maximum 100 words) 2. The conceptual model and any assumptions behind the model.
3. The decision model copied from the spreadsheet.
4. The best- and worst-case scenarios chosen, and a discussion of the consequences.
5. Justification for the choice of probability distribution.
6. Risk analysis report based on the simulation modelling.
Rubric for Assignment 2
Performance Levels
Criteria YET TO ACHIEVE MINIMUM STANDARD MEETS STANDARD EXCEEDS STANDARD Format
Poor (0-49) Satisfactory (50-59) Good (60-69) Very good (70-79) Excellent (80-100)
Model description, conceptual model, and assumptions ULO1/GLO1
Total: 5 marks 0 Conceptual model is inappropriate or not clearly outlined
0 – 1.4 Marks 1.5 A vague conceptual model is given, with several modelling errors
1.5 – 2.4 Marks 2.5
An appropriate conceptual model is given. Relationships are mostly correctly specified
2.5 – 2.9 Marks 3
A clear conceptual model is given. Relationships are mostly correctly specified
3.0 – 3.4 Marks 3.5
A very clear conceptual model is given. All components are
modelled correctly in a clear way
3.5 – 3.9 Marks 5
A very clear conceptual model is given. All components are modelled correctly in a clear and innovative fashion
4 – 5 Marks MS Word report
Decision model
ULO1,3/GLO1,4
Total: 5 marks 0 Decision model is incomplete with some formulas or values missing
0 – 1.4 Marks 1.5 Decision model is complete. All stochastic inputs can only display averages
1.5 – 2.4 Marks 2.5
Decision model is complete. Some stochastic inputs can only display fixed values
2.5 – 2.9 Marks 3
Decision model is complete. All stochastic inputs display appropriate random values. There are errors in fixed inputs or calculated values
3.0 – 3.4 Marks 3.5
Decision model is complete. All stochastic inputs display appropriate random values. Only errors are in some formulas for outputs
3.5 – 3.9 Marks 5
Decision model is complete. All stochastic inputs display appropriate random values. All formulas in the models are correct
4 – 5 Marks MS Word report
&
Excel spreadsheet
Scenario analysis
ULO1,2,3/GLO1,3,4
Total: 5 marks 0 Scenario analysis report is not included or is inappropriate
0 – 1.4 Marks 1.5
Some possible scenario analyses are presented, but not clearly
1.5 – 2.4 Marks 2.5 Most of the important scenarios are considered for the analysis
2.5 – 2.9 Marks 3 Most of the important scenarios are considered for the analysis and elaboration on them is presented clearly
3.0 – 3.4 Marks 3.5
All of the main scenarios are considered, justified in a clear way, and presented in a compelling fashion
3.5 – 3.9 Marks 5
All scenarios are considered, fully justified in a clear way, and presented in a compelling fashion
4 – 5 Marks MS Word report
&
Excel spreadsheet
Stochastic decision model including choice of distribution
ULO1,2,3/GLO1,3,4
Total: 5 marks 0
Stochastic modelling is not included or inappropriate
0 – 1.4 Marks 1.5
Stochastic modelling is poorly implemented and analysed
1.5 – 2.4 Marks 2.5
Some effective stochastic modelling is used with poor choice of distribution
2.5 – 2.9 Marks 3
Some effective stochastic modelling is used with some elaboration on distribution
3.0 – 3.4 Marks 3.5
Stochastic modelling is used effectively. Choice of distribution is justified
3.5 – 3.9 Marks 5
Stochastic modelling is used effectively.
Choice of distribution is fully justified
4 – 5 Marks MS Word report
&
Excel spreadsheet
Simulated output distribution and risk analysis report ULO1,2,3/GLO1,3,4
Total: 5 marks 0 Simulated output and risk analysis are not included or are inappropriate
0 – 1.4 Marks 1.5 Unclear simulation outputs are presented. Unclear analysis of the effect of input risks
1.5 – 2.4 Marks 2.5
Some analysis of simulation output is presented. It considers some relevant stochastic inputs, with some discussion of their effect on output
2.5 – 2.9 Marks 3 Reasonable analysis of simulation outputs is presented. It considers some of the main stochastic inputs, with some discussion of their effect on output
3.0 – 3.4 Marks 3.5
Clear and reasonable analysis of simulation output. A comprehensive consideration of the main stochastic inputs, with some discussion of their effect on output
3.5 – 3.9 Marks 5
Clear and comprehensive analysis of simulation output is provided with comprehensive consideration of the main stochastic inputs, and discussion of their effect on output
4 – 5 Marks MS Word report
&
Excel spreadsheet
Report
ULO3/GLO4
Total: 5 marks 0 Report is not included or is inappropriate
0 – 1.4 Marks 1.5 Report is not a standalone document, requiring review of spreadsheet to understand results on which report is based. Displays a general lack of clarity or logic in interpretation or analysis of results
1.5 – 2.4 Marks 2.5
Report is largely a standalone document, though may require a review of the spreadsheet to help understand some points. Some areas lack clarity or logic in interpretation or analysis of results, or there are a few errors or omissions
2.5 – 2.9 Marks 3 Report is a completely standalone document.
A clear and logical report is provided across all areas, where results of analysis have been satisfactorily examined and accurately interpreted.
Some areas of the report display a lack of depth in understanding
3.0 – 3.4 Marks 3.5 Report is a completely standalone document.
A clear and logical report is provided across all areas, where results of analysis have been examined, interpreted and described accurately, precisely and concisely. Report displays a understanding across all areas
3.5 – 3.9 Marks 5
Report is a completely standalone document.
A clear and logical report is provided across all areas, where results of analysis have been examined, interpreted and described accurately, precisely and concisely. Report displays understanding across all areas.
Report concludes with some key insights
4 – 5 Marks MS Word report
Total marks 30 (30%)
MIS275 – Decision Analytics Assignment 2 – Trimester 2, 2021 Page 5
