# 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?