Excel(custom function and recording macro)

P2Q 5 Scenario Analysis (15 points) Scenarios are part of a suite of commands sometimes called what-if analysis tools. A scenario is a set of values that Microsoft Excel saves and can substitute automatically in your worksheet. You can use scenarios to forecast the outcome of a worksheet model. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.

Symbol Company Industry Shares Purchased AMGM Amgen Health Care 100 CPB Campbell Soups Co Food 100 WMT Walmart Retail 100 JCI Johnson Controls Inc Manufacturing 100

In the current problem, the number of shares purchased in the portfolio for the above 4 companies (AMGM, HSY, WMT and HPY) are 100 shares.

In a separate worksheet called P2Q5, Create the following scenarios,

Case 1: If they had bought 0 shares in each of the above 4 companies (AMGM, CPB, WMT and JCI), (rest of the companies shares remain the same), what will be the Total Return (%)?

Case 2: If they had bought 200 shares in each of the above 4 companies (AMGM, CPB, WMT and JCI), (rest of the companies shares remain the same), what will be the Total Return (%)?

Case 3: If they had bought 300 shares in each of the above 4 companies (AMGM, CPB, WMT and JCI), (rest of the companies shares remain the same), what will be the Total Return (%)?

Include the Scenario Summary table, fully labeled. Also create a plot of the total return Vs Shares using an appropriate graph on the same page, labeling the graph fully.

P2Q6a Create your own custom function (15 points) Custom functions are a way of simplifying the use of long equations. If you find yourself typing the same equation over and over again you can save yourself some time by creating a custom function that you plug cell addresses into it, just as you can with Excel’s built in functions. As defined above in this portfolio, Commission was calculated as percentage of the Total Costs. Specifically, Commission was calculated by multiplying Purchase Price * Number of Shares Purchased * 0.01. Write a custom function called Commisn with “3 Inputs” to calculate the commission with the following characteristics: If the commission is less than $30, then Commisn will be Purchase Price * Shares Purchased * 0.01. Otherwise, if commission $30 or more but less than $40, then Commisn would be a flat rate of $35. If commission is $40 or more, then the commission is a flat rate of $45 Copy the appropriate data from the Portfolio sheet for all the companies to a worksheet named P2Q6a and execute the function in a column labeled Commission (example as illustrated below).

Symbol Company Purchase Price Shares Purchased Rate

Commission

COL Rockwell Collins GIS General Mills M Macy’s Inc. AMGN Amgen Inc

P2Q6b: Create two Macro executed by two respective buttons (25 points)

In a separate worksheet called P2Q6b, copy your portfolio sheet beginning on Row10.

MACRO 1:

On that sheet create a Macro button at location B3, when clicked, it will do the following,

1. Will plot the Last price of stocks on the Y-Axis and Purchase price of stocks on the X-Axis with appropriate title and Axes labels.

2. Insert the Regression line and Equation on the graph. 3. It will sort the stocks from the “Highest Last Price” to the “Lowest Last Price.”

MACRO 2:

On that sheet create a Macro button at location D3, when clicked, it will do the following,

1. Will plot the Last price of stocks on the X-Axis and Purchase price of stocks on the Y-Axis with appropriate title and Axes labels.

2. Insert the Regression line and Equation on the graph. 3. It will sort the stocks from the “Highest Purchase Price” to the “Lowest Purchase Price.”