Excel for complex calculations
Download
1 / 20

Excel for complex calculations - PowerPoint PPT Presentation


  • 95 Views
  • Updated On :

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:

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

PowerPoint Slideshow about 'Excel for complex calculations' - flavio


An Image/Link below is provided (as is) to download presentation

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 l.jpg

Excel for complex calculations

Week 2

John Cubbin

City University


Overview l.jpg
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


Add ins l.jpg
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


Most useful add ins for us l.jpg
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.]


To install an add in l.jpg
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?


Goal seek l.jpg
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%.”


Goal seek format is l.jpg
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


Slide8 l.jpg

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


Solver l.jpg
Solver

  • More sophisticated than Goal Seek

  • Allows optimisation

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


Slide10 l.jpg

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.


Slide11 l.jpg

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 l.jpg
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.


Slide13 l.jpg

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


Slide14 l.jpg

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


Data analysis add in l.jpg
Data analysis Add-in =s.d.(X)^2

  • Useful for statistical analysis

  • Give an Excel example


Recording and using vba macros l.jpg
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 l.jpg
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.


Slide18 l.jpg

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 msd and mad estimators of population mean l.jpg
Application: =s.d.(X)^2MSD and MAD estimators of population mean

  • See homework sheet


A simple monte carlo study l.jpg
A simple Monte Carlo study =s.d.(X)^2

  • See homework sheet


ad