MIS775 - Decision Modelling for Business Analytics - Mr Sabat Urda Case Study - Bussiness Analytics Assignment Help

Assignment Task:

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

MIS775 - Decision Modelling for Business Analytics

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. 

Your submission will be assessed across these six sections for a total unit mark of 30%: 

Section 1: Model description, conceptual model, and assumptions

• Provide a brief overview of the model 

• Include a conceptual model • Note any relevant assumptions. 

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. 

The model includes the following: 

• Fixed inputs 

1. Utilities 
2. Leasing 
3. Insurance 
4. Phone & Internet 
5. Regular maintenance of coffee machine 
6. Regular maintenance of cooking appliances 
7. Sabat’s wage o Total staff wages 

• Stochastic inputs 

1. Interest rate
2. Sales revenue 
3. Costs 
4. Coffee supplies 

• Decision variables 

1. Loan size 
2. Term of the loan 
3. Percentage of net profit to be set aside each week for repaying the loan 

• Calculated variables over four-week period: 

Total costs 

1. Net profit (i.e. sales revenue – total costs) 
2. Money set aside for meeting loan repayment 
3. Required repayment on the loan 

• Output variables at the end of a four-week period: 

1. Is Sabat in default of the loan agreement? (Yes/No) 
2. Amount of repayment outstanding 

 

MIS775 - Decision Modelling for Business Analytics 

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

Section 6: Overall presentation 

The PowerPoint presentation should form the content of a report that includes: 

1. A brief description of the model  

2. The conceptual model and assumptions behind the decision model. 

3. The decision model copied from the spreadsheet. 

4. The best and worst case scenarios for the data provided, and a discussion of the consequences. 

5. Summary report of the sensitivity analysis of inputs. 

6. Risk analysis report based on the simulation modelling along with a summary of input parameters  and distributions used.

 

This MIS775 - Business Analytics Assignment has been solved by our Business Analytics Experts at onlineassignmentbank. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.