MSCI516 Spreadsheet建模

涵盖模块的不同主题，并且您只需要选择其中一个主题。你不会得到如果你在该任务中做了两个或两个以上的问题，则需要额外的分数。对于第二项任务一个案例研究，您需要使用VBA进行自动化。您需要同时执行任务和准备一份简短的报告，解释你做了什么，为么这么做，以及答案是什么问题是。这是个人作品。尽管没有人可以禁止你与每个人交流
在讨论问题时，预计您的模型和报告将不同的你不应该把你的电子表格模型或报告交给任何人，你应该不要使用其他人的文件。
MSCI516 – Spreadsheet Modelling

1. Spreadsheet Modelling

You have two tasks, each worth 50%. For the first one, you have three questions, coveringdifferent topics of the module, and you need to select only one of them. You will not getadditional marks if you do two or more questions in that task. For the second task, you havea case study, which you need to automate using VBA. You are expected to do both tasks andprepare a short report, explaining what you did, why you did that and what the answers tothe questions are.This is an individual work. Although no one can forbid you from communicating with eachother and discussing the problem, it is expected that your models and report would bedifferent. You should not give your spreadsheet model or report to anyone and you shouldnot use files of anyone else. If we notice that some files have resemblance, this will betreated as plagiarism.Some of the elements of the work might resemble the things discussed in the class. Thisdoes not mean that the code can be copied and pasted (this might also be treated asplagiarism). You are expected to either develop the code from scratch or to modify theexisting code, so that it becomes apparent that you have created a new program. Failing todo so might flag your work as plagiarism.The file "MSCI516CourseworkData.xlsx" contains your specific data for task 2. Use yourlibrary card number in order to find out what the initial conditions for the tasks are for you.“MSCI516CourseworkSharesData.zip” file contains the share prices, which you will need forportfolio optimisation.

2. Task 1 (50%)

Choose only one of the following questions, do it in Excel and prepare a short report (1000to 1500 words), explaining what you did, why and answering the questions.1. Energy consumption analysisThe file “EnergyConsumption.csv” contains half-hourly energy consumption in a region. Thecompany is interested in finding answers to several questions:a) How many seasonal cycles are there in the data and what type of seasonality thecompany is dealing with?
b) Is the average demand on energy increasing?c) Are there any unexpected observations in the data that cannot be explained by thestandard decomposition? When did they happen?2. Drivers Casualties
The file “DriversCasualties.csv” contains data on the number of deaths of drivers in caraccidents with the following variables: DriversKilled – number of drivers killed per month. front – front-seat passengers killed or seriously injured.rear – rear-seat passengers killed or seriously injured. kms – distance driven on average. PetrolPrice – average petrol price per month. law – binary variable, showing when the law for wearing seatbelts was in effect. January – December – dummy variables for specific month of year.You need to construct a meaningful regression model, do basic diagnostics of it and answerthe following questions based on the model:a) In what month historically the highest number of deaths happen?b) Was the seatbelts law effective in decreasing the number of drivers deaths?c) What would be the number of deaths in January 1985 if the law was still in effect,the distance driven was 20,000, petrol price was 0.11 and the number of front andrear passengers killed (or injured) was 700 and 450 respectively?3. Bitcoin price dynamicsThe file “BTC-data.csv” contains Open, High, Low, Close prices of Bitcoin in USD togetherwith the Volume sold each day. Use Monte-Carlo simulations to generate a 1000 potentialprice trajectories of the Bitcoin for the next two weeks. Assume a GBM process with Laplacedistribution (you might need to implement a quantile function for it in VBA, see Wikipediafor the formula) with zero mean and a fixed scale instead of the Weiner process. Aftergenerating the data, calculate the statistics for the Close prices necessary to answer thefollowing questions:a) If we invest a 1000 USD today, what is the expected gain/loss in two weeks?b) What is the Value-at-Risk of bitcoin on 1% level and how would you interpret it?c) Calculate the Expected Shortfall on the 2% level and interpret it.Monte-Carlo simulation of trajectories of price and calculation of VaR and ES. Assume GBMfor price.

3. Task 2

An investment company Rebel Inc is creating a portfolio, they have an initial capital andthey want to buy some shares (see csv-files in "MSCI516CourseworkSharesData.zip" archive;use “Close” prices). However, they also have some restrictions, because their CEO has strongfeelings about some types of shares (see "MSCI516CourseworkData.xlsx" file). They want toincrease the profitability of their portfolio, while decreasing the risks. However, they are not100% sure about the objective function, so they also want to have an option of simplyincreasing profitability and simply decreasing risks. Create a VBA script that would allow thecompany analysing different scenarios and selecting a portfolio for their needs. Write a briefreport with the analysis of the results (1000 to 1500 words).You can use the following as guidelines to the successful completion of the task:1. Formulate the task in terms of linear programming.2. Implement the model and solve the problem.3. Analyse the results. Do the proportions of shares in the portfolio make sense?4. What if the risk attitude was different?5. Generate efficient frontier and analyse it.6. Use VBA forms to collect the preferences of user.Make the macro as flexible as possible, allowing changes in constraints, the cost functionand the option of changing the original data.Assessment criteria:
Each of the tasks is assessed separately. After that the weighted average is calculated and isused as a final mark. The project carries 100% of the module assessment.The work will be assessed on the following criteria:1. The correctness of the implementation (is the model formulated correctly?);2. Ease of understanding of the MS Excel model (is it clear what should be changedand what is fixed on a sheet, is it easy to read?);3. The VBA code (efficiency, the absence of errors, the level of development etc);4. The flexibility of the VBA model (how many elements are hard coded);5. User interface (user forms, the readability of the model),6. VBA best practices;7. Analysis of the results (quality of the report).If the number of words in the report is exceeded, the mark is decreased by 10%.Submission

4.You need to submit

The report (two sections) written in Microsoft Word or PDF file:o the title of the report should contain the id of student,o the report should not contain the name of student. Excel files with implemented models; MS Word or PDF file with the code of your macros (you can copy and paste it fromVBA);
All the other files needed for running your program (if there are any).The files must be submitted on Moodle as they are (ZIP archives are not accepted) latest byMonday (16 January 2023) 10am.Note that the Moodle submission system is very strict in terms of deadline. Do not wait untilthe last minute to submit your work.Do not include your name in your submission to allow blind marking – your details canalways be found on Moodle after the marking.Plagiarism
Software to detect plagiarism will be used in marking. Standard university procedure forplagiarism applies for any detected plagiarism attempt. It is important to cite all the sourcesused in the report (such as: url, a paper, a textbook). The code is also checked for plagiarism,so make sure that you did not “borrow” it from anywhere else.Late submission policyStandard departmental penalties will apply for late submission unless you have been givenan extension (by departmental coordinators) for exceptional reasons.
WX：codehelp