FINAL EXAM FOR COMPUTING FOR FINANCEC/C++

Java Python FINAL EXAM FOR COMPUTING FOR FINANCE

Modifying and Using a DCF Model (Fourth/Final Exam Graded Exercise)

Reminder:  You cannot seek assistance for this exercise from anyone.  This is a graded exercise and your submission must be the product of only your own work.  If necessary, you may ask clarifying questions via posts to “Final Exam Discussions With Professor” on the Final Exam module on Canvas.  

If your question would reveal your thinking about how to complete this exercise, send it by private email rather than as a post to the Final Exam Discussions With Professor.

Your completed G4 (x) model must be uploaded to Canvas by 5 PM on Saturday August 24.

This final graded model covers Excel, VBA, FAMOUS and some of the finance principles already developed by using the FAMOUS model over the last few weeks. It contains a good deal of the requirement that you “learn how to learn” -- i.e. to figure out some things on your own.

Your Tasks For This Exercise

Download the MSV 2023 Reversed model from the Final Exam Canvas module and save it as G4 (x), where, well you know, where you need to replace the x with your assigned number.

There are four substantial tasks for you in this exercise, with the details provided further below:

1. review the MSV 2023 Reversed model (which you have now renamed G4 (x)) to get some insight into how the market, as of 12/31/23, viewed MSV’s strategy and operations going forward (there is no further discussion of this task below and nothing need be recorded in your G4 (x) model following your review; this review is for your own enlightenment only and should be an example of a standard review when looking at any model for the first time -- you should consciously work at establishing such an “initial review protocol”);

2. take the necessary steps to develop a sensitivity analysis showing, by way of a data table, how sensitive MSV’s DCFSV is to two critical drivers, Sales Growth Rate (G) and EBIT Margin;

3. modify the VBA code in a macro in your G4 (x) model to change the range of input values used in that macro to develop a sensitivity analysis showing, by way of two Scenario Summary tables, how sensitive MSV’s DCFSV is to five critical drivers;

4. identify and explain the relationship between MSV’s DCFSV as of “today,” 12/31/23, and its Sales Growth Rate (G) as reflected in the one data table and two Scenario Summary tables you generated in your G4 (x) model.

Develop Sensitivity Analysis In One Data Table.  Undertake the necessary steps to generate a data table showing the sensitivity of MSV’s DCFSV to the general level of its future Sales Growth Rate and to the general level of its EBIT Margin.  Specifically, in a single Excel data table on Page 3 of the Sensitivity worksheet in your G4 (x) model, show the sensitivity of MSV’s DCFSV to:

· an increase in its Sales Growth Rate by 100 basis points relative to the model’s level

· an increase in its Sales Growth Rate by 200 basis points relative to the model’s level

· a decrease in its Sales Growth Rate by 100 basis points relative to the model’s level

· a decrease in its Sales Growth Rate by 200 basis points relative to the model’s level

· an increase in its EBIT Margin by 150 basis points relative to the model’s level

· an increase in its EBIT Margin by 300 basis points relative to the model’s level

· a decrease in its EBIT Margin by 15 FINAL EXAM FOR COMPUTING FOR FINANCEC/C++ 0 basis points relative to the model’s level and

· a decrease in its EBIT Margin by 300 basis points relative to the model’s level.

Modify VBA Code To Develop Sensitivity Analysis In Two Scenario Summary Tables. Undertake the necessary steps to generate two Scenario Summary tables showing the sensitivity of MSV’s DCFSV to the general level of its future Sales Growth Rate and to the general level of its EBIT Margin.

First, modify the VBA code in the macro assigned to the Scenario Analysis button in the Sensitivity worksheet in your G4 (x) model so that:

· EBIT Margin for all five columns in the Scenario Summary table is 300 basis points above the level of EBIT Margin in the model

· EBIT Tax Rate for all five columns in the Scenario Summary table is set to the level of EBIT Tax Rate in the model

· Invested Capital Turns for all five columns in the Scenario Summary table is set to the level of the Invested Capital Turns in the model and

· Enterprise WACC for all five columns in the Scenario Summary table is set to the level of the Enterprise WACC in the model.

Second, run the appropriate macro with this modified VBA code to generate one Scenario Summary worksheet in your G4 (x) model.

Third, generate a second Scenario Summary worksheet identical to the first, but with EBIT Margin for all five columns in this Scenario Summary table set to 300 basis points below the level of EBIT Margin in the model.

Make sure that the three relevant tables in your G4 (x) are clean and formatted so that it is easy to focus on the relevant results without being distracted by poorly formatted results.

Executive Summary of the Findings. Examine the relationships in the three sensitivity tables you’ve generated to identify the nature and magnitude of the sensitivity of MSV’s DCFSV to its Sales Growth Rate and EBIT Margin. Analyze the overall model further in order to be able to explain what is determining or driving the nature and magnitude of the sensitivity of MSV’s DCFSV to growth and margin.

Enter a summary of your analysis into the Profile text box on the Cover Sheet of your G4 (x) model, typing over whatever text you find there.  Your summary should include something like two short paragraphs consisting of:

· a description of the nature and magnitude of the sensitivity of MSV’s DCFSV to its sales growth and EBIT Margin and

· an explanation of the fundamental underlying causes of the nature and magnitude of that sensitivity.  

It is unlikely that a clean, concise and complete executive summary would fill up the entire Profile text box.

Upload Files To Canvas

When done with this exercise, upload the resulting version of your G4 (x) model to Canvas using the Fourth/Final Exam Graded Model link in the Final Exam module. The upload is due by 5 PM Saturday August 24. (Mac users upload a second copy of your G4 (x) model, named G4 (x) mac.)

To help make the processing of your uploaded files more efficient, as usual, do the following before uploading your model:

· turn off the zeros and turn on the estimates (Zeros On/Off button and Estimates On/Off button)

· select the Cover Sheet worksheet

· run the Cleanup macro (Ctrl+Shift+C)

· make sure you have named your submitted model correctly (e.g. no LastF anywhere and no extra spaces at the end, just a simple G4 (x)) and

· save the model and upload it