Reference no: EM131407342

Financial Modeling Assignment

Answer all questions and sub-questions asked in this assignment. You will need to use Excel for calculations and simulation, but make sure to show and explain your work when transferring results to your main Word document with your answers. Include both your Excel worksheet(s) created and the Word document with answers when submitting your assignment.

IMPORTANT: I. Show ALL your work and/or intermediate calculations where appropriate, as NO marks will be given for answers without justification i.e. provide enough detail that the reader knows what went into deriving the answer.

Question1. Coffeeshop plc is a restaurant company that is investigating the possibility of leasing premises at a small international airport terminal, for an up-front payment of £1.2 million covering the next five years. The premises will operate as a café/bar for outbound passengers. The number of outbound passengers using the airport was just over 2 million in 2011. During the next year (2012) the number is expected to be at least 1.8 million, with a most likely estimate of 2 million and a maximum of 2.3 million. From 2013 to 2016 the annual growth in passenger numbers is estimated as being between 1% and 3%, with a most likely estimate of 1.5%. The factors that affect the passenger numbers are likely to have the same sort of effect each year so the annual growth for each year should be correlated with a correlation of 0.7. The number of customers at the café/bar will depend upon the overall passenger numbers at the airport. It is thought that for any passenger there is a 10% chance that they purchase something at the café/bar. The average spending per customer is uncertain but can be modelled as a normal distribution with a mean (in 2012) of £5 and a standard deviation of £0.10. The mean spending per customer is expected to increase by between 10 pence and 20 pence each year. The gross margin on customer sales is between 64% and 66%. In 2012, other operating costs (cash expenses) for the business e.g. staff wages, will be at least £280,000 and at most £330,000, with a most likely estimate of £300,000. These costs will to some extent depend upon the passenger numbers at the airport and can be assumed to have a correlation of 0.8 with the number of outbound passengers in 2012. These costs are expected to increase in line with the passenger numbers from 2013 onwards. All annual cash flows can be taken to occur at the end of the year. The opportunity cost of capital for projects with this perceived level of risk is 15%.

Required: Retrieve the template file Coffeeshop.xlsx from Blackboard and answer the following questions:

Part A: 1. Create a spreadsheet model using Excel and Risk to find the Net Present Value and Internal rate of Return for the investment.

2. Use Risk to perform a simulation on the proposed investment. It is up to you to decide which cells in the model will be outputs from the simulation.

Part B: Using MS Word, write a summary of your work. As an indication your summary should include:

(a) Discussion of the simulation inputs and outputs, with relevant Risk results i.e. charts and statistical summary.

(b) Discussion of the simulation sensitivities.

(c) Discussion of the simulation scenarios.

(d) Explanation of what the simulation approach involved and what the model is actually doing.

(e) Discussion of the limitations of your model and how some of the assumptions could be changed to make it more realistic (and as a consequence more complicated).

Attachment:- Excell_Assignment.xlsx