Excel代写 | BU.610.625 – Simulation and Strategic Options

这个作业是用Excel创建商业模型并模拟生成策略
BU.610.625 – Simulation and Strategic Options, Spring 2020
Assignment # 3
Please submit three documents for this assignment:
1. Your answers to the questions in .pdf format,
2. your completed HW3_model.xlsx file, and
3. your completed sim_results.csv file.
This assignment can be done either individually or as a group of 2-3 students. Please submit
your assignment through Blackboard. Only one set of submissions per group is required.
In this assignment, you will return to the SCOR-eSTORE.COM case from the previous
assignment. In the previous assignment, you showed that the Abandon and Switch options made
the business quite attractive and Bernard should invest in the business. In this assignment, you
will be asked to evaluate the other two options suggested in SCOR-eSTORE.COM case by
Bernard’s friends; the expand option and the buyout option.
The buyout option could be evaluated separately from the expand option, or it could be
evaluated assuming that the expand option might also be exercised. For this assignment, we
evaluate the buyout option assuming the expand option might be exercised. We refer to this
combined options by expand&buyout option.
For your convenience, I have provided some hints and information in HW3_model.xlsx and also
some formulas in the following parts. You will need to read the case carefully and also use my
hints and information to do this assignment.
a) In this part, you are asked to use HW3_model.xlsx and develop a simulation model to calculate
the NPV incorporating the expand&buyout option. Please ignore the case Exhibit 2 and instead,
use the formula below to calculate the Valuation, EBITDA, Page Views, and Margin per
Purchase for months 7-18.
𝑜𝑐 = 𝑇𝑟𝑖𝑎𝑛𝑔𝑢𝑙𝑎𝑟(4.54, 5.27, 6)
𝑐𝑟 = 𝑇𝑟𝑖𝑎𝑛𝑔𝑢𝑙𝑎𝑟(0.08, 0.14, 0.20)
𝑝𝑣7 = 𝑇𝑟𝑖𝑎𝑛𝑔𝑢𝑙𝑎𝑟(90, 110, 130)
𝑚𝑝7 = 0.88
𝑚𝑝𝑖 = 𝑚𝑝𝑖−1 + 𝑁𝑜𝑟𝑚𝑎𝑙(𝜇 = 0, 𝜎 = 0.08) 𝑓𝑜𝑟 𝑖 = 8 𝑡𝑜 18
𝑝𝑣𝑖 = 𝑝𝑣𝑖−1 ∗ (1 +
0.6
12
+
0.55
√12
∗ Normal(𝜇 = 0, 𝜎 = 1)) 𝑓𝑜𝑟 𝑖 = 8 𝑡𝑜 18
𝑒𝑏𝑖 = 𝑐𝑟 ∗ 𝑚𝑝𝑖 ∗ 𝑝𝑣𝑖 − 𝑜𝑐 𝑓𝑜𝑟 𝑖 = 7 𝑡𝑜 18
𝑣𝑖 = 𝑚𝑎𝑥(0,12 ∗ 15.1 ∗ 𝑒𝑏𝑖) 𝑓𝑜𝑟 𝑖 = 7 𝑡𝑜 18
Where 𝑚𝑝𝑖
is Margin per Purchase at month i, 𝑝𝑣𝑖
is Page Views at month i, 𝑣𝑖
is the Valuation
at month i, 𝑒𝑏𝑖
is the EBITDA at month i, 𝑐𝑟 is Conversion Rate, and 𝑜𝑐 is the Monthly
Operation Cost.
Use the calculated EBITDA and valuation, and the information provided in the case to calculate
the NPV with Expand & Buyout option. (You do not need to report this in the pdf submission.)
b) Use the data table feature of Microsoft Excel to run the model for 10,000 trials, and record the
following information about each trial in the “Simulation Results” table: Margin per purchase at
month 12, Coversion Rate, Monthly Operating Cost, Page Views at Month 12, EBITDA at
Month 12, and NPV with Expand & Buyout. (You do not need to report this in the pdf
submission.)
c) Calculate the expected NPV with Expand&Buyout option and compare it with the present value
of the original $500k ($456.44k) to explain why Bernard should exercise the Expand&Buyout
option.
d) Perhaps, there is a better cutoff level for exercising Expand&Buyout option than $18k value per
month of Exercise Price EBTIDA mentioned by Bernard. To check this, run your simulation
model with Exercise Price EBTIDA in range of 0-$21k value with increments of $3k and record
in a table the expected and confidence interval of NPV. Include this table in your pdf
submission. Based on this result, what value of Exercise Price EBTIDA will you suggest to
Bernard?
e) It is also interesting to speculate whether there is a better triggering variable or even a
combination of different variables for the Expand&Buyout option than EBTIDA. The variables
that could be used to trigger expansion are the variables available at month 12 (see the case
Exhibit 1) which have been listed in the “Simulation Results” table. Linear regression can be
used to investigate a linear additive triggering function. In this part, you will use the best model
selection you learned in Data Analytics class to find the best regression model. You may want to
review Model Selection session from Data Analytics class. You need to the following tasks to
answer this part:
1. Use the best Exercise Price EBTIDA found in part d to generate the corresponding
“Simulation Results” table.
2. Copy and paste the values of this “Simulation Results” table to the sim_results.csv file.
3. Run the Hw3_bestmodel.R file. You do not need to revise the code. Do not forget to set
the working directory in R.
4. Based on the summary and the adjusted R2 of the model from the output of the R code,
explain whether Bernard should use a different set of triggering variables or not?