Dakota State University BADM 418 Financial Futures and Options Spring 2017 Assignment #2 Buy and Sell Signals Account Equity Data Assignment Due: Friday, April 21, 2017 11:59 pm Points: Thirty points possible. Two points per day lateness penalty Software: Excel, Word Part A: Buy and Sell Signals Make a copy of the spreadsheet in Assignment #1. That way you will always have the original if you make mistakes with this assignment. You will always be able to start over. Add two new columns to the spreadsheet, one for the long or short position and another for commissions. The following uses the 3-day and 8-day moving averages as an example: Use the initial data to determine whether you should start out long or short. For example, if on the eighth day the 3-day moving average is below the 8-day moving average, start out short on the first day. If on the eighth day the 3-day moving average is above the 8-day moving average, start out long on the first day. Use the data and chart to determine whether you should be long or short the contract at any given time. When the 3-day moving average crosses the 8-day moving average going up, it is a signal to buy. When the 3-day moving average crosses the 8-day moving average going down, it is a signal to sell. In the example below a buy signal was generated on Feb. 26. So on Feb. 27, the day after the signal was given, the position was switched to long. (Hence the position had to be short before that.) Remember that you are using settlement prices to detect the signals, so the signals are coming at the end of the day. So you can only make the switch the day after the signal. The signals are easier to spot by looking at the data rather than at the chart. In the attached example three signals were generated during the study. You may find many more or fewer signals generated in your study. Each time you switch from long to short or vice versa, charge your account $14 commission as a cash outflow. See the example spreadsheet. The formatting of the number can wait until you find out what values your marking to market generates. Part B: Account Equity Data Add columns for the change in price, the profit or loss for the day, and the cumulative profit or loss. Use a formula to calculate the change in futures price each day. (The actual change, not the percent change.) Always trade just one contract. Format with as many decimal places as required from the way your futures contract price is quoted. Do not round off either in the calculation or the display. Use a formula to calculate the profit or loss for the day, e.g., =IF(E12="L",G12*10,-G12*10) for cocoa. (See Excel Tips for an explanation of if/then formulas.) For cocoa each tick (a change of $1/ton in the futures price) represents $10 profit or loss. Whether that is a profit or a loss depends on whether the position is long or short. In the example there was an increase in price of $45/ton on February 2. Because the position was short one contract and the price went up, the equity went down, by $240. The if/then formula automatically takes the long or short into consideration when calculating profit or loss. Note that different contracts will produce numbers rather different from the example with cocoa. Check the Announcements page for the factor to use for your contract. This column of numbers should be formatted to the nearest penny if profit or loss is not always in whole dollars, as it is with cocoa. Put zeros in the first row for change in price, profit or loss, and cumulative profit or loss. In this exercise you are considering only the change in equity in the account, not the equity in total. Use a formula to increase or decrease the cumulative account equity as the case may be. This column of numbers should also be formatted to the nearest penny if profit or loss is not always in whole dollars. Remember to include the commission column in the calculation. Note that in this example the buy/sell signal technique worked poorly and generated a loss. I also tried 3-day MA versus 10-day MA and it turned out to be profitable. In general the fewer buy and sell signals generated, the more profitable the technique. Create a report in Word. Include a cover sheet, the display of data, and the cell formulas. . Use portrait for the display of data and landscape for the chart. Modify the spreadsheet and especially the chart from Assignment #1 to reflect suggestions or corrections needed in the grading of Assignment #1. Make sure to expand the column widths so the entire formulas can be read. Always include border row and column headings when printing cell formulas.