Excel for complex calculations

1. Excel for complex calculations Week 2 John Cubbin City University

2. Overview • Loading add-ins • More advanced (+ Add-in) functions: • Goal seek • Solver • Data analysis add-in • Last week’s homework • Simple programming concepts • Recording and using VBA Macros • Application: • MSD and MAD estimators of population mean • A simple Monte Carlo study

3. Add-ins • Extras to basic Excel • Take up more room • Many people do not use them • Have to be loaded if you want them • May have pitfalls if you do not understand them well

4. Most useful add-ins for us Analysis ToolPak Adds financial, statistical, and engineering analysis tools and functions. Solver Add-In Calculates solutions to what-if scenarios (scenario: A named set of input values that you can substitute in a worksheet model.) based on adjustable cells and constraint cells. [Analysis ToolPak VBA Allows developers to publish financial, statistical, and engineering analysis tools and functions using Analysis ToolPak syntax.]

5. To install an add-in • On the Menu bar click Tools… Add-Ins • Then select the ones you want • Click OK • NB extra procedures are required to make Solver available to VBA. We will cover these. • See Week 2 Example 1 Does anyone know what the underline represents in the menu bar?

6. Goal seek • “If you know the result you want from a formula, but not the input value the formula needs to get that result, you can use the Goal Seek feature.” (Excel help) • Example from homework: “Work out the portfolio that would be required to earn an expected return of 7.2%.”

7. Goal seek format is Set (Cell A) Equal to (Value) By changing (Cell B) For example, Value could be 7.2%, our target expected return

8. Let’s look at Homework1.xls as an illustration of Goal Seek

9. Solver • More sophisticated than Goal Seek • Allows optimisation • Be careful, seeks a local maximum, may depend on starting point

10. Solver Solver is a set of algorithms designed to : Maximise the value in a cell Minimise the value in a cell Make the formula in a cell attain a particular value Example: Find maximum or minimum of y = ax2 + bX + c Analytic solution derived from setting dy/dx = 0: Stationary point at y = -b/2a; max if a<0, min if a>0 We can use Excel to get the algebraic solution, or use Solver to find a numerical solution.

11. With simple problems algebraic solutions are better, but we can use simple problem to explain how you use Solver. See the spreadsheet “Solver Example 1.xls” This sets up the unconstrained problem for a quadratic function, then a constrained function

12. Issues with Solver Make sure it is available in Excel by selecting Tools, Add-Ins… and putting a tick in the box marked Solver Beware that in certain types of problem the "solution" may be sensitive to the starting value. See the paper on WebCT by Troxell "Optimisation Softaware Pitfalls: Raising awareness in the classroom" INFORMS transactions on Education :2 (40- 46). This discusses some of the numerical issues involved.

13. Now let us see how to use it in looking at portfolio optimisation; See chapter 6 of Jackson and Staunton. Portfolio return: E(rp)= Σwi E(ri) Porfolio variance: Var(rp) = σp2 = ΣΣwiwjcov(i,j,), Cov(i,i,) = σi2 Risk is measured by square root of variance Expected returns = e Weights = w

14. We have the variances from the standard errors Var(X) =s.d.(X)^2 To get the covariances use the following hint r = covariance /(sqrt of product of variances) So covariance = r *(sqrt of product of variances) We had r= 0.1 sd1 = 0.15 sd2=0.02 So Cov(X1X2)=0.1*0.05*0.2 = 3*10-4 See homework1.xls

15. Data analysis Add-in • Useful for statistical analysis • Give an Excel example

16. Recording and using VBA macros • Let’s do a simple example of repetitive task • Say, copy the result of an iteration in a Monte Carlo experiment • Say we generate 81 random numbers at a time, and we want to compare the properties of their mean and median over a wide range of sample drawings

17. Simple programming concepts Algorithm: set of steps setting out how to calculate something. Numerical Recipes describes lots of algorithms Variable: an entity e.g. a number or text whose value may be changed by the program Structured programming: breaking up a large task into smaller steps, some of which may be done several times. Subroutines and functions: a small group of steps which performs a specific operation. A function will calculate ("return") a particular value given certain input values. A subroutine may change the values of several of the variables in the program.

18. Programming concepts continued Subroutines and functions: a small group of steps which performs a specific operation. A function will calculate ("return") a particular value given certain input values. A subroutine may change the values of several of the variables in the program. Module: has many meanings involving the concept of building block. e.g. subroutines and functions will be grouped together into modules within a VBA project. Object-oriented programming: instead of manipulating a restricted range of data types, object-oriented programming manipulates all sorts of objects, including text boxes, menu bars, and lots of different kinds of variables. More on this later.

19. Application:MSD and MAD estimators of population mean • See homework sheet

20. A simple Monte Carlo study • See homework sheet