Creation of an efficient frontier using excel

[dilishan]

hi,


I want to create an efficient frontier using excel. I have data for different volume bands and the revenue earned. Revenue is arrived at using four different variables (eg: a,b,c,d). I want to calculate the maximum revenue that can be earned for different combinations. So there should be lot of iterations to find out which combination would yield the highest revenue for each volume band. Appreciate if you could tell me how to do the iterations (without using excel VBA). Is it possible just using Excel?


thanks

 

[kchiba]

Hi Dilshan,


There is a fantastic blog on using the Data Tables function in Excel on this site by Hui.


The link to it is:


http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/

 

[Hui]

Dilishan

You can use the Excel Solver Function to assist you here.

Solver will automatically go through a number of scenarios and look to maximimise, minimise etc results based on dependancies that you setup.


Solver is not installed in Excel 2007 by Default

Goto Excel Options, Addins Select "Manage Excel Addins" and Check Solver

The Solver Tab will now show up on the Data Tab


Solver allows you to goal seek a result (in your case Maximise Revenue) subject to applying a number of constraints.

In your case and not knowing what A, B, C & D are, but a Constraint maybe

A + B + C + D <= 100

A > 0

C = 2 x B

D > $H$9

H10 < G10

etc


When applying constraints and running solutions remember that excel may find solutions you don't expect eg: B = -10, This means that you need to add adequate constraints to ensure you get the answers you want ie: A>0 & B>0 & C>0 & D>0


Also remember that often Maximising Revenue doesn't necessarily Maximise Profit


Good Luck

 

[dilishan]

Thanks alot. I will try it and get back to you. I want to draw an efficient frontier graph..Hope the methods you suggested would help me to create one. Many thanks for your support

 

[dilishan]

One small question. Please find below the question I have.


1. I am planning to use solver.

2. How can I permanently embed solver function on to a form control. So the user could just change the value and the output table will be updated. I dont want the user to go to solver tab and do it. It will be easy for the management if it is included in some sort of control. Can we do that in excel?

3. I asked many people and they say that it cannot be done in excel.


Please let me know..


Thanks for the support.

 

[Hui]

Dilishan

You can use a VBA Custom form and an associated macros to setup and or change the constraints of a Solver problem.

This way the user doesn't have to play with the Solver dialog