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.

Summary of the sensitivity analysis of the outputs to each of the decision variables.

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. 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.

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 & 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

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.

Stochastic modelling including choice of distributions

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.

Simulated output distribution and risk analysis report

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.

Overall presentation

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.

Summary of the sensitivity analysis of the outputs to each of the decision variables.

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.