CMP-5031Y: Importance of Model Planning - Economics Assignment Help

Assignment Task:

Learning outcomes

• Create spreadsheets to model life financial scenarios
• understand the importance of clear model documentation
• understand the importance of model planning
• understand how to create a complete yet concise audit trail

Overview
Your task is to complete the 4 questions as set out in the attached document. This is an individual piece of work with the aim of testing YOUR knowledge and understanding of the is NOT permitted and will be considered as plagiarism and disciplinary action will be taken

The following questions are all questions that you could be asked in an exam and required to answer using pen and paper. This assessment requires you to answer these questions by building a flexible model such that multiple versions of the same question can be answered easily simply by changing parameters. The aim here is not for you to answer the questions using the same approach as you would use in a written exam but to approach the problems by building a cashflow model instead. You should also document each of your models with a clear audit trail. The models should all include at least 3 sheets;
1- Parameters
2- Model
3- Audit Trail
1. Create a model and write an audit trail for it which would allow you to answer each part of the following question;
A loan is repayable by monthly installments paid in arrear for 25 years. Each installment £824.71 and interest is charged at a rate of 6% per annum convertible monthly.
a. Calculate the amount of the original loan We are now 2 and a half years into the loan term.
b. Calculate how much is currently owed to the loan company and what this is as
a proportion of the initial loan amount.
c. Calculate the proportion of the loan outstanding with the proportion of time outstanding.
d. Calculate how much capital was repaid in the first monthly payment and how much will be repaid in the last monthly payment.
HINT: You should build a model that includes the calculation of;
a. The amount of the original loan
b. The loan outstanding after each monthly instalment
c. The capital repaid in each monthly instalment
d. The proportion of loan outstanding after each monthly instalment
e. The proportion of time outstanding after each monthly instalment

2. Create a model and create and audit trail for it which would allow you to answer each part of the following question;
The owners of a hotel have decided to extend their current building in order to provide 25 extra double en-suite rooms. The development process will begin on
1 January 2021 and will take 2 years, with the actual building work taking place over the final 12 months of this period. The development cost will be £1,000,000
and they plan to spread the cost as follows:
£100,000 6 months after the start of the project
£300,000 when the building work commences
£600,000 when the building work is completed
The owners would like this development to have paid for itself after a minimum of 2 years. The suggested new room rate will be £70 per person, per night and the usual policy is for the room rate to increase by 3% each year compound. It is expected that the first increase will be applied 12 months after the development is completed.
You should assume the development is completed on time and that on completion all rooms immediately become available to book, and that a fully occupied double room will assume two people paying the per person rate. You should assume that the new rooms result in housekeeping and accommodation costs of £4,000 per month, paid at the start of each month. You should assume this amount does not vary with occupancy levels.
a. Calculate the percentage occupancy that the hotel requires on these new rooms in order that the owner’s target is met. You should assume an effective interest rate of 5% p.a.
b. Calculate the Internal Rate of Return for this project if actual occupancy is 60%.
3. Create a model and write an audit trail for it which would allow you to answer each part of the following question;
Molly who is exact age 60 purchases an insurance policy with the following benefits:
£100,000 on survival to age 90
A return of premium paid, at the end of year of death if she dies within the term of the policy.

a. Calculate the premium she should pay annually in advance throughout the term of this policy assuming the pricing basis below:
Mortality: AM92 Ultimate rated down by 3 years
Interest: 4% p.a.
b. Calculate the level sum assured that could be offered on death within the term or survival to the end of the term (assuming the same premium in a.) if this were a
standard endowment policy with the same level of benefit on both death and survival.

The following relationship with terms defined as in the lecture notes maybe helpful;

4. Using AM92 mortality (spreadsheet available on blackboard) create a model and write an audit trail for it, which can calculate ’s at non-integer ages using both a
uniform distribution of deaths assumption and a constant force of mortality assumption.
HINT: your model should use a vlookup function to find the number of lives at a particular age and at that age plus 1. These values can then be used to calculate the number of lives at any non-integer age between these
2 points. There should be the option to calculate the number of lives using either assumption.

This CMP5031Y: Economics Assignment has been solved by our Economics Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.