positioning report
BA 555 Practical Business Analysis
Group Project 2: Business Simulation
**** Due: November 12, 2014 ****
NAME
Tailong Wu (Section 001)
NAME
Ling Yang (Section 002)
NAME
Daijie Qi (Section 002)
1. The file Group Project 2 Model.xlsx contains 50 monthly returns of the SCHO and SPY EFT funds from September 2010 to October 2014. Suppose that in each of the next 72 months (six years), it is equally likely that any of the historical returns will occur. Develop a spreadsheet model to simulate the two suggested investment strategies over the six-year period. Plot the value of each strategy over time for a single iteration of the simulation. What is the total value of each strategy after six years? Do either of the strategies reach the target?
Based on our simulation model analysis of a single iteration, for the first strategy, the total value after 6 years is $ 22361. For the second strategy the value is $ 16610. So the first strategy reaches the target while the other one does not.
Hint:
1. To simulate ETF returns based on the historical return, use the @Risk function =RiskIntUniform( minimum, maximum ) to simulate an integer value between 1 and 50 to randomly pick a month. Use Excel function =VLOOKUP to pick the corresponding historical return based on the randomly selected month.
2. Repeat the process 71 more times to generate random monthly returns for the next 6 years. Use the simulated returns to calculate the ending portfolio values.
3. Make sure that Random Values (Monte Carlo) under Setting is selected. This would allow random numbers to be generated every time the spreadsheet is refreshed.
2. Simulate 1000 iterations of the two strategies over the six-year period. Create a histogram of the final fund values. Based on your simulation results, which of the two strategies would you recommend? Why?
Below are the histograms of the two strategies’ fund values:
Our group would recommend the first strategy. Although this strategy has a higher
Group Project 2: Business Simulation
**** Due: November 12, 2014 ****
NAME
Tailong Wu (Section 001)
NAME
Ling Yang (Section 002)
NAME
Daijie Qi (Section 002)
1. The file Group Project 2 Model.xlsx contains 50 monthly returns of the SCHO and SPY EFT funds from September 2010 to October 2014. Suppose that in each of the next 72 months (six years), it is equally likely that any of the historical returns will occur. Develop a spreadsheet model to simulate the two suggested investment strategies over the six-year period. Plot the value of each strategy over time for a single iteration of the simulation. What is the total value of each strategy after six years? Do either of the strategies reach the target?
Based on our simulation model analysis of a single iteration, for the first strategy, the total value after 6 years is $ 22361. For the second strategy the value is $ 16610. So the first strategy reaches the target while the other one does not.
Hint:
1. To simulate ETF returns based on the historical return, use the @Risk function =RiskIntUniform( minimum, maximum ) to simulate an integer value between 1 and 50 to randomly pick a month. Use Excel function =VLOOKUP to pick the corresponding historical return based on the randomly selected month.
2. Repeat the process 71 more times to generate random monthly returns for the next 6 years. Use the simulated returns to calculate the ending portfolio values.
3. Make sure that Random Values (Monte Carlo) under Setting is selected. This would allow random numbers to be generated every time the spreadsheet is refreshed.
2. Simulate 1000 iterations of the two strategies over the six-year period. Create a histogram of the final fund values. Based on your simulation results, which of the two strategies would you recommend? Why?
Below are the histograms of the two strategies’ fund values:
Our group would recommend the first strategy. Although this strategy has a higher