MIS775 \u2013 Decision Modelling for Business Analytics \u2013 Trimester 1 2023 Assessment Task 2 \u2013 A spreadsheet-based decision model \u2013 Individual
\nDUE DATE: Wednesday, 24th May, by 8:00pm (Melbourne time)
\nPERCENTAGE OF FINAL GRADE: 30%
\nWORD COUNT: 1500 Maximum number of words Description Purpose
\nThis 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.
\nContext\/Scenario
\nThree 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.
\nSabat 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.
\nSpecific Requirements
\nThis 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 \u2013 one MS Excel spreadsheet and one MS PowerPoint file.
\nThe assignment has five sections:
\nSpreadsheet-based decision model
\nScenario and sensitivity analysis
\nStochastic modelling including justification for the choice of distributions
\nSimulated distribution for each output and risk analysis report
\nOverall presentation
\nYou are to assume the following:
\nThe term of the loan is limited to one, two, or three years.
\nEqual 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.
\nSabat 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.
\nThere is no option for making extra repayments on the loan.
\nAssume that Sabat has no other income to rely on other than his business to meet a repayment.
\nSabat is correct in his belief regarding the additional revenue generated and increased costs associated with taking out a loan.
\nYour 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.
\nThe minimum requirements of the decision model are:
\nAbility 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.
\nAbility 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)
\nStochastic treatment of random inputs, in order to explore the resulting simulated output and to summarise the risks.
\nYour submission will be assessed across the following five sections. The requirements of each section are detailed above.
\nSection 1: Spreadsheet-based decision model (Files: MS PowerPoint & Excel; 5 marks)
\nDesign a spreadsheet model that you can use to investigate and explore Sabat\u2019s financial situation if he takes out a loan.
\nAt minimum, the model should include the following:
\nFixed inputs
\no Utilities
\no Shop Leasing o Insurance
\no Phone& Internet
\no Regular maintenance of coffee machine
\no Regular maintenance of cooking appliances
\no Sabat\u2019s wage
\no Total staff wages
\nStochastic inputs
\no Interest rate
\no Sales revenue
\no Costs
\nCoffee supplies
\nBaking supplies
\nGeneral consumables
\nAd hoc maintenance
\nOther costs
\n? Decision variables
\no Loan size
\no Term of the loan
\no Percentage of net profit to be set aside each four-week period for repaying the loan
\nCalculated variables over a four-week period: o Total costs
\no Net profit (i.e. sales revenue \u2013 total costs)
\no Money set aside for meeting loan repayment o Required repayment on the loan
\nOutput variables at the end of a four-week period:
\no Is Sabat in default of the loan agreement? (Yes\/No)
\no Amount of repayment outstanding (Note: this will be zero if Sabat has set aside sufficient funds to meet the loan repayment).
\nSection 2: Scenario and sensitivity analysis (Files: MS PowerPoint & Excel; 5 marks)
\nThis 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).
\nConsider 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.
\nSection 3: Stochastic modelling including choice of distributions (Files: MS PowerPoint & Excel; 5 marks)
\nThis 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.
\nTopic 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.
\nFor each of the remaining stochastic inputs, you will need to determine appropriate distributions.
\nSection 4: Simulated output distribution and risk analysis report (Files: MS PowerPoint & Excel; 5 marks)
\nThis 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.
\nSection 5: Overall presentation (File: MS PowerPoint; 5 marks) See the Assignment 2 Rubric at the end of this document.
\nYour MS PowerPoint document should be a standalone document containing no more than 40 slides. It should include:
\nA brief description of the model (maximum 100 words).
\nThe conceptual model and assumptions behind the decision model.
\nThe decision model copied from the spreadsheet.
\nDifferent scenarios for each stochastic input, and a discussion of the consequences.
\nSummary of the sensitivity analysis of the outputs to each of the decision variables.
\nSummary of stochastic modelling including choice of distributions.
\nRisk analysis report based on the simulation modelling in order to quantify the risks associated with meeting the loan commitments.
\nLearning Outcomes
\nThis 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.
\nThe learning outcomes that are aligned to this assessment task are:
\nUnit Learning Outcomes (ULOs)\tGraduate Learning Outcomes (GLOs)
\nULO1\tConceptualise, formulate and represent a business problem as a decision model\tGLO1: Discipline-specific knowledge and capabilities: appropriate to the level of study related to a discipline or profession
\nULO3\tInterpret and analyse the results and evaluate the sensitivity of solutions to the assumptions of the decision models\tGLO4: Critical thinking: evaluating information using critical and analytical thinking and judgment
\nSubmission
\nYou 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, \u2018Jones_Barry_123456789_ABC123.pptx\u2019. Apply the same naming convention with an xlsx extension when uploading your MS Excel spreadsheet.
\nWhen submitting electronically, you must check that you have submitted the work correctly by following the instructions provided in CloudDeakin. Please note that any assignment or part of an assignment submitted after the deadline without an approved extension or via email will NOT be accepted.
\nSubmitting a hard copy of this assignment is not required. You must keep a backup copy of every assignment
\nyou submit until the marked assignment has been returned to you. In the unl<\/p>\n
___________________________<\/p>\n
Design a spreadsheet model<\/p>\n
The spreadsheet model should include the following:<\/p>\n
Fixed inputs:
\nUtilities
\nShop leasing
\nInsurance
\nPhone & internet
\nRegular maintenance of coffee machine
\nRegular maintenance of cooking appliances
\nSabat’s wage
\nTotal staff wages
\nStochastic inputs:
\nInterest rate
\nSales revenue
\nCosts
\nCoffee supplies
\nBaking supplies
\nGeneral consumables
\nAd hoc maintenance
\nOther costs
\nDecision variables:
\nLoan size
\nTerm of the loan
\nPercentage of net profit to be set aside each four-week period for repaying the loan
\nCalculated variables over a four-week period:
\nTotal costs
\nNet profit (i.e. sales revenue \u2013 total costs)
\nMoney set aside for meeting loan repayment
\nRequired repayment on the loan
\nOutput variables at the end of a four-week period:
\nIs Sabat in default of the loan agreement? (Yes\/No)
\nAmount of repayment outstanding<\/p>\n
Scenario and sensitivity analysis<\/p>\n
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.<\/p>\n
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.<\/p>\n
Stochastic modelling including choice of distributions<\/p>\n
In this section, you will 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.<\/p>\n
Simulated output distribution and risk analysis report<\/p>\n
In this section, you will undertake a risk analysis based on simulation modelling, in order to quantify the risks associated with meeting the loan commitments.<\/p>\n
Overall presentation<\/p>\n
Your MS PowerPoint document should be a standalone document containing no more than 40 slides. It should include:<\/p>\n
A brief description of the model (maximum 100 words).
\nThe conceptual model and assumptions behind the decision model.
\nThe decision model copied from the spreadsheet.
\nDifferent scenarios for each stochastic input, and a discussion of the consequences.
\nSummary of the sensitivity analysis of the outputs to each of the decision variables.
\nSummary of stochastic modelling including choice of distributions.
\nRisk analysis report based on the simulation modelling in order to quantify the risks associated with meeting the loan commitments.<\/p>\n","protected":false},"excerpt":{"rendered":"
MIS775 \u2013 Decision Modelling for Business Analytics \u2013 Trimester 1 2023 Assessment Task 2 \u2013 A spreadsheet-based decision model \u2013 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 & […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6740,7741],"tags":[],"class_list":["post-122546","post","type-post","status-publish","format-standard","hentry","category-studbay","category-studybay"],"_links":{"self":[{"href":"https:\/\/www.colapapers.com\/assessments\/wp-json\/wp\/v2\/posts\/122546","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.colapapers.com\/assessments\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.colapapers.com\/assessments\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.colapapers.com\/assessments\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.colapapers.com\/assessments\/wp-json\/wp\/v2\/comments?post=122546"}],"version-history":[{"count":1,"href":"https:\/\/www.colapapers.com\/assessments\/wp-json\/wp\/v2\/posts\/122546\/revisions"}],"predecessor-version":[{"id":122547,"href":"https:\/\/www.colapapers.com\/assessments\/wp-json\/wp\/v2\/posts\/122546\/revisions\/122547"}],"wp:attachment":[{"href":"https:\/\/www.colapapers.com\/assessments\/wp-json\/wp\/v2\/media?parent=122546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.colapapers.com\/assessments\/wp-json\/wp\/v2\/categories?post=122546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.colapapers.com\/assessments\/wp-json\/wp\/v2\/tags?post=122546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}