Reference no: EM132307165

STOCHASTIC DECISION MODELS

Requirements:

This assignment is to be completed as a MS Excel file and a MS PowerPoint file. The Excel file must be an original created by you for this assignment.

Assignment Details:

You are required to develop a spreadsheet-based decision model that can be used to investigate and explore decisions and risks relating to taking a business loan (e.g. for investment) for a small-to-medium sized business. The model needs to be generic enough to enable the decision maker 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.

• Utilities (Electricity& Gas &Water)

• Telephone/mobile

• Insurance

• Maintenance

• Paid salaries

• Other

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 (e.g. income, business expenses).

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

The minimum requirements of the decision model are:

1. Ability to enter loan details, income, costs and expenses to explore decision options relating to the loan amount and repayments to calculate outputs such as total interest paid and duration of loan.

2. Ability to understand the impact of variation to the loan interest rate, income, cost and expenses over the duration of the loan.

3. Stochastic treatment of some of the inputs to explore resulting simulated output and summarize risks.

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

1. A brief description of the model (maximum 100 words)

2. A bubble diagram or similar capturing the conceptual model used and assumptions behind the decision model (Optional: Using Analytica).

3. The decision model copied from the spreadsheet.

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

5. Summary report of the sensitivity analysis of inputs.

6. Risk analysis report corresponding to the simulation modelling along with summary of input parameters/distributions used.