Excel Case 3
 
Make sure that when you save your work, you save the file as .xlsm (so that it saves your VBA code).
 
You will first create a column called “Date” and it will start at 12/1/2019 and increment by 1 month for 100 months.
 
You will then create a macro that generates random numbers between -5% and 5% for each of your dates you created and call this new column “Return”.  It should then copy/paste these random numbers as values (so that they do not change).  The macro should then create another column called “Cumulative Return” that is the current month’s return and prior months multiplied together (=(1+current_month)*(1+prior_month)-1, for example).  It’s okay to have an extra column in here that is 1 + the return, so that you’re able to calculate the cumulative return easier.
 
Your macro should then create a new column called “Success”.  If the Cumulative Return is less than 0%, then the color is “Red”.  Red should be written as text and it should be red in color.  If the number is greater than or equal to 0% and less than 2%, then the color is “Yellow”. If the number is greater than or equal to 2%, then the color is “Green”. (Note: Yellow and Green should also have the appropriate color of text).
 
Once your macro is created — run it until your last observation/date has a Green level of Success.
 
How many runs did it take to achieve Green? Why do you think this is?
 
Hint: to create random numbers in a range (that aren’t integers), use =rand()*(b-a)+a, where a is your lower limit and b is your upper (be careful of negative values).
 
Also remember to professional format your spreadsheet (appropriate spacing, making your headers bold, etc.).