MIS775-Decision Modelling for Business Analytics - Management Assignment Help

Assignment Task

Task

Assignment Details:

Three years ago, Emma Thompson purchased a cafe in the Melbourne Botanical Gardens, and now wants you to develop a spreadsheet-based decision model that can be used to investigate and explore decisions and risks relating to taking a small business loan (e.g., for investment). The model needs to be generic enough to enable Emma to explore the size of the loan that is viable/manageable within different scenarios of income, cost, expenses, loan amount, deposit made on the loan, repayment amount, other financial commitments, the amount of interest paid etc. and understand the risks associated with meeting the loan commitments.

The decision model must be realistic and easy to use. The level of complexity modelled, for example the choice of deterministic vs. stochastic inputs, input distributions, etc., is left to your discretion. However, the model must enable the user to input the following business cost/expenses and loan details/options.

Deterministic/Fixed inputs:

• Utilities (Electricity& Gas &Water)
• Telephone/mobile and Internet
• Insurance
• Maintenance
• Paid salaries
• Own wage
• Leasing

No data is provided. You are required to create a fictitious business and demonstrate the utility of the decision model using real data where available (e.g., interest rates) and create data where it is not.

Your model needs to take into account the costs of running the business (e.g., coffee supplies) and the sales revenue in order to determine the profit generated in each four-week period. You will then use the model to explore the risks associated with Emma taking the loan under various scenarios of your choosing.

Note that Net Profit = Sales revenue – Total variable costs – Fixed costs – Overheads.

You can also assume the following:

• Weekly coffee supplies has a Normal distribution with mean $864 and standard deviation $29
• Sales revenue for any week = $3,624.3+8.76*Coffee supplies for that same week + Error term, where the Error term is Normally distributed with mean zero and standard deviation $253.5 For each of the remaining stochastic inputs of your choice, you will need to determine appropriate distributions with parameter values and support your answers with goodness-of-fit tests for all stochastic inputs.

The minimum requirements of the decision model are:

1. Ability to enter loan details, income, costs, and expensesto explore decision optionsrelating to the businessloan such as amount and repaymentsto calculate outputsfor example total interest paid.

2. Ability to explore decision options relating to the size of the loan, the interest rate, the loan term, and the percentage of profit to be set aside each week for repaying the loan. Base the interest rate on the published RBA rates.

3. Ability to calculate outputs such as whether Emma is in default of the loan agreement (i.e., whether she has sufficient funds to cover a repayment) and the amount of repayment outstanding (which will be zero if she can have sufficient funds to cover the repayment).

4. Stochastic treatment of some of the inputs, so that the resulting simulated output can be explored.

5. It must also enable Emma to explore and understand the risks associated with the decision.

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 Emma’s financial situation if she takes out a loan. The model should include the following:

• Fixed inputs
• Stochastic inputs
• Decision variables
• Calculated variables
• Output variables

Section 3: Scenario analysis report.

Thissection relatesto Topic 7. Use Excel’s scenario analysis and investigate the impact that different scenariosfor each stochastic input on the outputs. The choice of best and worst scenarios is yours.

Section 4: Stochastic modelling including choice of distributions.

This section relates to Topic 8. Undertake stochastic modelling where each of the stochastic inputs are now random. This will require you to choose an appropriate distribution for each of the stochastic inputs and justify each one.

Section 5: Simulated output distribution and risk analysis report.

This section relates to Topic 9. This requires you to examine the simulated distributions for each output, and 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 (maximum 100 words)

2. The conceptual model and assumptions behind your 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. Justification for the choice of distributions.

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

This MIS775 Management Assignment has been solved by our Management Expert at Exam Question Bank. 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 Turn tin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.