AC60055E: Financial Modelling- Simulation Sensitivities- Report Writing Assignment Help

Internal Code: 1GGBC

Report Writing Assignment Help:

Task: Question 1 Feelfresh plc is a soft drink company that is investigating the possibility of renting premises in a large shopping mall, for an up-front payment of £1.2 million covering the next five years. The premises will operate as a café/bar for the mall’s visitors. The number of visitors using the shopping mall was just over 2 million in 2014. During the next year (2015) 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 2016 to 2018 the annual growth in visitor numbers is estimated as being between 1% and 3%, with a most likely estimate of 1.5%. The factors that affect the number of visitors 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 visitor numbers at the shopping mall. It is thought that for any visitor 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 2014) 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 2014, 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 visitor numbers at the shopping mall and can be assumed to have a correlation of 0.8 with the number of visitors in 2014. These costs are expected to increase in line with the number of visitors from 2015 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 Feelfresh.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).