1 / 20

# Excel for complex calculations - PowerPoint PPT Presentation

Excel for complex calculations. Week 2 John Cubbin City University. 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:

Related searches for Excel for complex calculations

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Excel for complex calculations' - flavio

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Excel for complex calculations

Week 2

John Cubbin

City University

• Goal seek

• Solver

• 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

• 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

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.]

• 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?

• “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%.”

Set (Cell A)

Equal to (Value)

By changing (Cell B)

For example, Value could be 7.2%, our target expected return

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

• More sophisticated than Goal Seek

• Allows optimisation

• Be careful, seeks a local maximum, may depend on starting point

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.

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

Issues with Solver can use simple problem to explain how you use 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.

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

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

• Useful for statistical analysis

• Give an Excel example

Recording and using VBA macros =s.d.(X)^2

• 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

Simple programming concepts =s.d.(X)^2

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.

Programming concepts continued =s.d.(X)^2

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.

Application: =s.d.(X)^2MSD and MAD estimators of population mean

• See homework sheet

A simple Monte Carlo study =s.d.(X)^2

• See homework sheet