MIS775-Decision Modelling for Business Analytics

Assignment Details

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.25 in additional revenue, while adding $0.10 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 MIS775_Assignment_2. This spreadsheet also includes a template of the model for you to complete. 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.

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 should he have set aside insufficient funds 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:

1. 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 week for repaying the loan.

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

3. Stochastic treatment of random inputs, in order to explore the resulting simulated output and to summarise the risks.

Section 1: Model description, conceptual model, and assumptions

• Provide a brief overview of the model

• Include a conceptual model

Section 2: Spreadsheet-based decision model 

Design a spreadsheet model that you can use to investigate and explore Sabat’s financial situation if he takes out a loan.

Section 3: Scenario and sensitivity analysis reports

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/f05hist.xls Use the monthly rates given in the column headed Lending rates; Small business; 3-year fixed. According to the RBA, the ‘3-year fixed’ rate is the average rate charged to small businesses for residentially-secured loans. 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 4: Stochastic modelling including choice of distributions 

This section relates to Topic 8. 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.

Section 5: Simulated output distribution and risk analysis report

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.