Reference no: EM132504942

MIS775 Decision Modelling for Business Analytics - Deakin Business School

Background

This assignment is to be completed in groups of 2-3 members. 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 MS PowerPoint file that summarises your model and results. In summary, two files should be submitted - an Excel spreadsheet and a PowerPoint file.

The assignment has six sections:
1. Model description, conceptual model, and assumptions
2. Spreadsheet-based decision model
3. Scenario analysis report
4. Stochastic modelling including justification for the choice of distributions
5. Simulated distribution for each output and risk analysis report
6. Overall presentation.

Learning Outcome 1: Conceptualise, formulate and represent a business problem as a decision model.
Learning Outcome 2: Develop and solve business problems using advanced decision modelling techniques such as optimisation, stochastic modelling and risk analysis in spreadsheets.
Learning Outcome 3: Interpret and analyse the results; investigate the sensitivity of the solutions to the assumptions of the
decision model.

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 hotel booking management (and reservation limits). The model should be designed to be used by the hotel sales manager for accommodation reservation bookings made by customers for the coming high season. You can assume the following:
• The number of available rooms is limited
• Cancellations are expected
• The hotel's policy for overbooking is to give customers a free room at another hotel
• Some limits on the number of possible reservations are expected (to handle overbooking).

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 business costs such as maintenance cost, overbooking cost, etc.

No data are provided for this assignment. You are required to create a fictitious client and demonstrate the utility of the decision model using real data where available and create data where it is not.

The minimum requirements of the decision model are:
1. The ability to enter revenue and costs, so that decision options can be explored and to enable outputs, such as profit, to be calculated.
2. Stochastic treatment of some of the inputs, so that the resulting simulated output can be explored.
3. It must also enable the decision maker to explore and understand the risks associated with decision.

Part 1: Model description, conceptual model, and assumptions
• Provide a brief overview of the model
• Include a conceptual model
• Note any relevant assumptions.

Part 2: Spreadsheet-based decision model
Design a spreadsheet model that you can use to investigate the problem. The model should include the following:
• Fixed inputs
• Stochastic inputs
• Decision variables
• Calculated variables
• Output variables.

Part 3: Scenario analysis report
This Part relates to Topic 7. Consider different scenarios for each stochastic input and examine the impact on the outputs.

Part 4: Stochastic modelling including choice of distributions
This Part 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.

Part 5: Simulated output distribution and risk analysis report
This Part 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 problem.

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