Posted: May 19th, 2023

MIS775 – Decision Modelling for Business Analytics

MIS775 – Decision Modelling for Business Analytics – Trimester 1 2023 Assessment Task 2 – A spreadsheet-based decision model – Individual
DUE DATE: Wednesday, 24th May, by 8:00pm (Melbourne time)
PERCENTAGE OF FINAL GRADE: 30%
WORD COUNT: 1500 Maximum number of words Description Purpose
This assignment task is aligned to the learning outcomes GLO1 & ULO1 and skills GLO4 & ULO3 required to build complex decision models and use advanced quantitative modelling techniques, such as optimisation, to analyse and develop solutions to business problems. By completing this task, you will develop your skills in conceptualising, formulating and representing a business problem as a spreadsheet-based decision model, developing a business decision model using MS Excel, and undertaking scenario analysis, stochastic modelling, and risk analysis based on simulation modelling.
Context/Scenario
Three years ago Mr Sabat Urda purchased a lively and colourful Coffee Roastery and Bakery premises in the Melbourne Botanical Gardens, and now wants to apply for a small business loan in order to expand his business. He believes that for every dollar he borrows, it will return $1.30 in additional revenue, while adding $0.15 to his total costs. He has therefore asked you, as his business advisor, to develop a spreadsheet-based decision model and analyse the financial risks he might face if he takes out a loan.
Sabat has provided you with historical financial data for his premises, covering costs and sales revenue each week over three years – see Excel spreadsheet T12023 MIS775_Assignment_2. This spreadsheet also includes a template of the model for you to follow. At a minimum, the model should allow Sabat to input the size of the loan, the term of the loan, and the percentage of net profit that he will set aside each week for meeting the loan repayments.
Specific Requirements
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 in a MS PowerPoint file. 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 PowerPoint file.
The assignment has five sections:
Spreadsheet-based decision model
Scenario and sensitivity analysis
Stochastic modelling including justification for the choice of distributions
Simulated distribution for each output and risk analysis report
Overall presentation
You are to assume the following:
The term of the loan is limited to one, two, or three years.
Equal loan repayments are made every four weeks, covering both principal and interest. The repayments must be met immediately following the end of each four-week period. Otherwise, Sabat will be deemed in default of the loan agreement.
Sabat plans to set aside a fixed percentage of his profit in each four-week period and use that money to cover the loan repayment due at the end of each period.
There is no option for making extra repayments on the loan.
Assume that Sabat has no other income to rely on other than his business to meet a repayment.
Sabat is correct in his belief regarding the additional revenue generated and increased costs associated with taking out a loan.
Your decision model needs to take into account the costs of running the business and the sales revenue in order to determine the profit generated in each four-week period. You will use it to explore the risks associated with Sabat taking the loan under various scenarios of your choosing.
The minimum requirements of the decision model are:
Ability to explore decision options relating to the size of the loan, the loan term, and the percentage of profit to be set aside each four-week period for repaying the loan.
Ability to calculate outputs such as whether Sabat is in default of the loan agreement (i.e. whether he has set aside insufficient funds to cover a repayment)
Stochastic treatment of random inputs, in order to explore the resulting simulated output and to summarise the risks.
Your submission will be assessed across the following five sections. The requirements of each section are detailed above.
Section 1: Spreadsheet-based decision model (Files: MS PowerPoint & Excel; 5 marks)
Design a spreadsheet model that you can use to investigate and explore Sabat’s financial situation if he takes out a loan.
At minimum, the model should include the following:
Fixed inputs
o Utilities
o Shop Leasing o Insurance
o Phone& Internet
o Regular maintenance of coffee machine
o Regular maintenance of cooking appliances
o Sabat’s wage
o Total staff wages
Stochastic inputs
o Interest rate
o Sales revenue
o Costs
Coffee supplies
Baking supplies
General consumables
Ad hoc maintenance
Other costs
? Decision variables
o Loan size
o Term of the loan
o Percentage of net profit to be set aside each four-week period for repaying the loan
Calculated variables over a four-week period: o Total costs
o Net profit (i.e. sales revenue – total costs)
o Money set aside for meeting loan repayment o Required repayment on the loan
Output variables at the end of a four-week period:
o Is Sabat in default of the loan agreement? (Yes/No)
o Amount of repayment outstanding (Note: this will be zero if Sabat has set aside sufficient funds to meet the loan repayment).
Section 2: Scenario and sensitivity analysis (Files: MS PowerPoint & Excel; 5 marks)
This section relates to Topic 7. In this section use averages from the historical data as best guesses for sales revenue and cost components. Base the interest rate on the published RBA rates. The rates can be found at https://www.rba.gov.au/statistics/tables/xls/f07hist.xls Use the monthly rates given in the column headed Lending rates; Business finance; New loans funded in the month; Small business; Fixed-rate (Column N).
Consider different scenarios for each stochastic input and examine the impact on the outputs. Also consider the sensitivity of the outputs to each of the decision variables.
Section 3: Stochastic modelling including choice of distributions (Files: MS PowerPoint & Excel; 5 marks)
This section relates to Topics 8 and 9. Undertake stochastic modelling where each of the seven stochastic inputs are now random. This will require you to analyse the historical data and fit an appropriate distribution to each of the stochastic inputs.
Topic 9 Fitting_distributions spreadsheet includes the analysis and result for Coffee supplies. You can therefore assume findings given there without having to repeat the analysis in your submission.
For each of the remaining stochastic inputs, you will need to determine appropriate distributions.
Section 4: Simulated output distribution and risk analysis report (Files: MS PowerPoint & Excel; 5 marks)
This section relates to Topic 9. This requires you to undertake a risk analysis based on simulation modelling, in order to quantify the risks associated with meeting the loan commitments.
Section 5: Overall presentation (File: MS PowerPoint; 5 marks) See the Assignment 2 Rubric at the end of this document.
Your MS PowerPoint document should be a standalone document containing no more than 40 slides. It should include:
A brief description of the model (maximum 100 words).
The conceptual model and assumptions behind the decision model.
The decision model copied from the spreadsheet.
Different scenarios for each stochastic input, and a discussion of the consequences.
Homework help – Summary of the sensitivity analysis of the outputs to each of the decision variables.
Homework help – Summary of stochastic modelling including choice of distributions.
Risk analysis report based on the simulation modelling in order to quantify the risks associated with meeting the loan commitments.
Learning Outcomes
This task allows you to demonstrate your achievement towards the Unit Learning Outcomes (ULOs) which have been aligned to the Deakin Graduate Learning Outcomes (GLOs). Deakin GLOs describe the knowledge and capabilities graduates acquire and can demonstrate on completion of their course. This assessment task is an important tool in determining your achievement of the ULOs. If you do not demonstrate achievement of the ULOs you will not be successful in this unit. You are advised to familiarise yourself with these ULOs and GLOs as they will inform you on what you are expected to demonstrate for successful completion of this unit.
The learning outcomes that are aligned to this assessment task are:
Unit Learning Outcomes (ULOs) Graduate Learning Outcomes (GLOs)
ULO1 Conceptualise, formulate and represent a business problem as a decision model GLO1: Discipline-specific knowledge and capabilities: appropriate to the level of study related to a discipline or profession
ULO3 Interpret and analyse the results and evaluate the sensitivity of solutions to the assumptions of the decision models GLO4: Critical thinking: evaluating information using critical and analytical thinking and judgment
Submission
You must submit your assignment in the Assignment Dropbox in the unit CloudDeakin site on or before the due date. When uploading your assignment, name your document using the following syntax: surname_your first name_your Deakin student ID number_[unitcode].pptx. For example, ‘Jones_Barry_123456789_ABC123.pptx’. Apply the same naming convention with an xlsx extension when uploading your MS Excel spreadsheet.
When submitting electronically, you must check that you have submitted the work correctly by following the instructions provided in CloudDeakin. I need help writing my essay – research paper note that any assignment or part of an assignment submitted after the deadline without an approved extension or via email will NOT be accepted.
Submitting a hard copy of this assignment is not required. You must keep a backup copy of every assignment
you submit until the marked assignment has been returned to you. In the unl

_________________________________________

Design a spreadsheet model. The spreadsheet model should include the following:

Fixed inputs:
Utilities
Shop leasing
Insurance
Phone and internet
Regular maintenance of coffee machine
Regular maintenance of cooking appliances
Sabat’s wage
Total staff wages
Stochastic inputs:
Interest rate
Sales revenue
Costs:
Coffee supplies
Baking supplies
General consumables
Ad hoc maintenance
Other costs
Decision variables:
Loan size
Term of the loan
Percentage of net profit to be set aside each four-week period for repaying the loan
Calculated variables over a four-week period:
Total costs
Net profit (i.e. sales revenue – total costs)
Money set aside for meeting loan repayment
Required repayment on the loan
Output variables at the end of a four-week period:
Is Sabat in default of the loan agreement? (Yes/No)
Amount of repayment outstanding
Perform scenario and sensitivity analysis. In this section, you will use averages from the historical data as best guesses for sales revenue and cost components. You will also base the interest rate on the published RBA rates. You will then consider different scenarios for each stochastic input and examine the impact on the outputs. You will also consider the sensitivity of the outputs to each of the decision variables.

Undertake stochastic modelling. In this section, you will make each of the seven stochastic inputs random. This will require you to analyze the historical data and fit an appropriate distribution to each of the stochastic inputs. You can use the results from the Fitting_distributions spreadsheet to help you with this.

Perform a risk analysis. In this section, you will use simulation modelling to quantify the risks associated with meeting the loan commitments. You will do this by running the simulation 10,000 times and then calculating the probability of Sabat defaulting on the loan.

Create a presentation. Your presentation should include the following:

A brief description of the model (maximum 100 words).
The conceptual model and assumptions behind the decision model.
The decision model copied from the spreadsheet.
Different scenarios for each stochastic input, and a discussion of the consequences.
Homework help – Summary of the sensitivity analysis of the outputs to each of the decision variables.
Homework help – Summary of stochastic modelling including choice of distributions.
Risk analysis report based on the simulation modelling in order to quantify the risks associated with meeting the loan commitments.

Order | Check Discount