Using the information from the Bear Bar Case revision case/tutorial activity, prepare a spreadsheet model to incorporate the following new information 1. The bear bar reopens at the beginning of 2023 and you have explicit assumptions for the next 5 years 2. Monthly number of drinks sold in 2023 is expected to be 4,000. The quantity of drinks sold will increase by 5% per year. 3. Drink prices and costs in 2023 are $14 and $5 respectively 4. Labour costs will be $8,000 per month in 2023 5. Social media influencers will be retained to help promote the bar. They have negotiated a fee of $0.25 per drink sold. 6. In negotiations with the landlord, Andy and Grace agreed to a rent of $2,800 per week in 2023. 7. Annual utility expenses are expected to be $25,000 in 2023. 8. Drink prices, drinks costs, labour costs, social media, rent and utilities prices are expected to increase at 3% per year beginning in 2024. 9. You believe a fair discount rate for the project should be 15% 10. The tax rate is 30% You can ignore the "Friends of the Bear Bar" scheme in your analysis. Determine the amount you would be willing to pay for a 20% stake in the business at the end of 2022 (i.e. beginning of 2023). Note: The first cash flow is at the end of 2023 and the last cash flow is at the end of 2027.

Assume thereafter that cashflows will grow at 2% per year You believe Andy and Grace may have been too optimistic in their projections. You would like to test a few scenarios using the following values for drinks per month in 2023 (3,000; 4,000 and 5,000) and different values of price per drink in 2023 ($13, $14, $15) Please show the output for these 9 scenarios and If you can successfully negotiate a 20% shareholding of the company for an investment of $200,000, please shade in green those scenarios (combinations of drinks per month/price per drink) in which you would gain if you made this investment. Please ensure your spreadsheet includes the name and student number of your team members. Show this at the top left of the spreadsheet. To get full points, you will have to use modelling best practices: clearly identifying inputs and calculations linking model calculations to assumptions presenting a model that is flexible and easy to understand