Hedging Simulation, Solver To Obtain A Maximum Profit

Apr 18, 2007

Im setting up a fixed hedging simulation using excel and want to use solver to obtain a maximum profit. I have taken historical stock prices and then predicted future prices using the random walk process. To create the hedge I am using european calls and puts in both long and short positions. The simulation has been set up so that the options are being brought/sold when the historical data ends and then sold/payed when the predicted data finishes.

I am letting solver change the values of the strike of each of the options and also the amount purchased/sold for each of the options too! My constraints are that all the strike prices have to be >= 0, all the amounts of each particular option has to be >= 0 and that profit has to be >= 0. It is worth noting that as well as the values already mentioned, d1 and d2 values are also being calculated on the sheet which are then used to calculate the value of the options being used (using Black-Scholes model), this value is then given a realistic spread value and then 3% is added on as the brokers fee. All of the values mentioned are being taken into consideration when caluclating profit.

View 6 Replies


ADVERTISEMENT

Using Excel Solver With Min And Maximum?

Jan 27, 2014

I'm having issues getting solver to use the minimum and maximum for hours and items to be produced. I can't seem to get a balance between the two.

Solver 1.xlsx

View 3 Replies View Related

Multiple Solver Constraints In Solver

Sep 10, 2006

I have a cell, D5, which is the sum of three other cells, A5 B5 and C5. (all currently empty). Cells A1 through C4 are filled with various numbers.

What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.

I was doing fine until trying to create the constraints. How can I make a constraint that says "this cell" must equal "one of the following cells"? And if I can't do that, is there an alternate method of achieving the same result?

View 9 Replies View Related

Simulation Model For Inventory

Nov 15, 2006

I am having a very difficult time determining how to get started with this problem in VBA. I am not sure how to generate the failures, or start the array. I have been racking my brain for quite some time. Company B makes a product that has two component parts. The results of reliability testing for each component shows that the components fail according to an exponential distribution with the following mean time between failure (mtbf).

ComponentMTBF(months)
115
224

Below are Company B's sales records for the past 36 months.

MonthSalesMonthSalesMonthSales...............................

View 5 Replies View Related

Create Spreadsheet That Use Monte Carlo Simulation?

Sep 22, 2005

Has ever created a spreadsheet that used Monte Carlo Simulation?

I've been able to create one that works using the random number generator in conjunction with discrete probability distributions - that's fairly easy, just assign a probability to each discrete value in the distribution and then decide whether or not to select it using a randomly generated number.

However, I want to perform a Monte Carlo simulation that uses a continuous (not discrete) probability distribution, but how I would use the random number generator to then select a value from the distribution based on its probability of occurance.

View 3 Replies View Related

Excel - Automate Simulation With Crystal Ball Add-in

Aug 14, 2012

I am trying to automate a simulation with the crystal ball add-in. Specifically the simulation will go through a set of workbooks in a directory - run the simulation (monte carlo x1000) and extra the "trial values" data in another spreadsheet.

I have tried to find crystal ball documentation but I'm not having any luck.

View 2 Replies View Related

Stopping A Counting Process In Dice Simulation

Oct 6, 2008

I am newbie in Excel so i got limited knowledge of programming in excel. I need to perform a dice simulation. I will toss a die repeatedly until I got all six outcomes, then record the numbers of tosses.

I tried to use the function RANDBETWEEN() in to simulate the toss of the die but I don't know how to stop my simulation.

For example, I can generate a sequence like this:
1 3 5 3 4 3 2 1 2 3 6 5 6

but I need to stop at the 11th generation since I achieve all possible outcomes (1,2,3,4,5,6) there.

View 9 Replies View Related

Solver Solver To Calculate Through VBA

Jan 12, 2007

I have to use use the solver to calculate something (a mean-variance framework).

I am using the solver to minimize a cartain cell (variance) by making two cells equal through (expected return) by varying 10 cells( weights of assets), but I have to repeat this for 500+ times (for different expected returns).

Someone told me that I could best use some sort of loop through VBA. But I don't have a clue how that works.

View 13 Replies View Related

SUM MAXIMUM Or Index/Max: Count Number Based On The Maximum Time??

Oct 23, 2008

I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).

=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)

Would a Index/Match/MAX function be more efficient?

View 4 Replies View Related

Find MAXIMUM, Show Date When MAXIMUM Occurred

Oct 9, 2008

Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.

View 2 Replies View Related

Conditional Maximum (find The Maximum 'value' For Each Individual 'type')

May 15, 2009

I have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'

The 'types' are not necessarily next to each other and the data cannot be sorted to do so.

Example:
type value
A 15
B 6
A 21
C 7
B 13

I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.

View 2 Replies View Related

Obtain An Interpolate Value For Z, Given A Value For X And A Value For Y

Mar 29, 2009

I just have a quick question with regards to two-way interpolation of data.

I have a data table which has x-values, y-values and z-values.

I need to obtain an interpolate value for z, given a value for x and a value for y.

The x value specified is 1.569 and the y value specified is 1.66.

I know how to use the FORECAST formula for a simple x, y table, but I have no idea what to do here when I need to find a z value, given the x and y values.

View 8 Replies View Related

Obtain A Value From Another Cell

Feb 27, 2009

I'm trying to find a way to enter the value of a cell from another worksheet into my currently active cell. The (obviously incorrect) code I'm working on looks something like this:

View 2 Replies View Related

Obtain Workbook Name

May 31, 2007

I would like to obtain the name of a workbook after activating it and then paste it into an input box.

How could I do this?

View 9 Replies View Related

Solver- When Solver "changes Cells"

Jan 21, 2009

I want to use solver program. But when solver "changes cells" i want it to trigger my pivot tables in the workbook. So i added the code to my worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.RefreshAll

End Sub

So when a change occurs, all my pivot tables will get refreshed and my data will change. Is solver able to trigger this event while solving an optimization problem?

View 9 Replies View Related

Precentage Profit Formula

Apr 21, 2009

In cell "C4" how would I write the formula to calcuate my precentage profit based on the given cost and sell pricing.

View 3 Replies View Related

Calculate The Profit And Loss

Jul 20, 2007

I have a trading system spreadsheet that I am at a loss as to how to calculate the profit and loss.

I was unable to attach a sample of the spreadsheet. I hope this explanation works. If not, I could email it.

Where:
Market position is either 1 or -1
Entry price is the price where the position is reversed.

For example:
H3=1 and I3=100.56, then
H4=-1 and I4=100.88, then
H5=1 and I5=100.53

I need to calculate the profit as follows:
I4 - I3 (because market position of 1 expects the price to go up) and then,
I4 - I5 (because market position of -1 expects the price to go down)

I want to calculate this as a running total to the bottom of the column.

This is the formula I was working with, but it doesn't work:

=IF(OR($J4="",ISERROR(MATCH(-$J4,$J4:$J$55,0)),),"",$J4*(OFFSET($K4,MATCH (-$J4,$J4:$J$55,0)-1,)-$K4))

View 14 Replies View Related

How To Calculate Max Profit Per Quarter

Nov 28, 2008

Formula to get the Quarter number for the highest profit per Product, like in B16:F16.

The excersiser instracted us that it must be with one formula with no Macro, not User Defined Function and not openning additional colums.

I know how to find the qtr. Number - like for A2: =INT((MONTH(A2)+2)/3) but I am lost in finding how to combine the calculation for the Qtr and the most profitable Qtr. probably with MAX on SUM.

View 14 Replies View Related

Inventory And Profit/Loss

Feb 11, 2007

I have a workbook that contains several worksheets. The 2 important sheets are Imports and Sales.

In reading the MS KB, the help screens and this forum, I found a way to create a pivot table from 2 sheets using Data>Pivot Table>Multiple Consolidation Ranges. While that PT will provide a basic inventory and profit picture, it only allows 4 selects.

When these products are imported there are several SKU's. What gums up the works is there are several vendors for the same SKU's. Each vendor has a different price. The selling price is constant so the profit made on each particular item varies.

I would like to be able to keep track of inventory by vendor as well as profit by vendor.

Using the Consolidation PT does not seem to work.

I also tried making one sheet to hold all the data. i.e. data showing imports and sales. However, each entry is driven by the date. So while item A was received on 1/1/07, it was not sold until 1/2/07. The necessary columns for imports are filled in while the columns for sales remain blank until it is their row entry indicating a particular sale. At which point all the columns regarding Imports are blank. I swear, looking at the data sheet with all those blanks (actually0's) is like looking at a mouth full of broken teeth. The resulting PT is also not attractive.

I can not imagine I am the first person to have this problem. How do others handle figuring inventory and profits when you have two sheets. Are Pivot Tables the wrong solution? What would be better?

View 11 Replies View Related

Obtain Details From Table

May 25, 2006

I am currently running XC running races and am wanting to know how i can have a quicker way of sorting out the best of sex, best of age group and best overall. The age groups are Under 18, under 30 and over 30. What can i do with v- lookup and other formulas to make it easier to record when they come in with out a long wait, i just want to be able to type in thier number and get all thier details so i can add the time straight in, then from there work out the winners instantly

View 2 Replies View Related

Obtain/Return FilePath/Name In VBA

Sep 4, 2006

Does anyone know the code for obtaining the filepath of the current workbook? Tried a search and can't find anything on this.

View 9 Replies View Related

Obtain Cell Formatting

Nov 23, 2006

how to obtain what the formatting of a cell is, be it a date, number, custom, currency, etc? I want to acquire the format of the cell in vBA code, in order to display the value exactly as it was formatted in the cell when sending that value to a label on a UserForm or Field on a Microsoft Word form.

Example:
Cell A1 has the value 12/25/2006 formatted with the Date format: "mmmm-dd-yyyy" appearing as December 25, 2006

Cell A2 has the value 9.247 formatted with the Number format: "#.##" appearing as 9.25 to show only two-decimals

Cell A3 has the value 899, formatted with the Custom format: "00000" appearing as 00899 to represent a five-digit US zip code

Cell A4 has the value 1234.56 formatted with the Currency format: I think -"$#,##0.00_);($#,##0.00_)" appearing as $1,234.56

For rownumber = 1 To 4
Entry = Cells(rownumber,1).Value 'gets whatever in cell
Formatting = Cells(rownumber,1).Format 'makes no sense in vBa.
msgbox Entry & " formatted as " & Formatting
Next

View 8 Replies View Related

Obtain The Array Values Of A Function

Jan 25, 2013

How can I obtain the array values from a function?

For example: Those steps were made by cytop to split a text (btw it works perfecet!!), I just added Function splitText(strSplit As Variant) As Variant ... End Function

VB:
Function splitText(strSplit As Variant) As Variant
Dim datosColumnaIncio() As Variant
Dim iTemp As Integer
Redim datosColumnaIncio(Len(strSplit) - 1)
For iTemp = 1 To Len(strSplit)
datosColumnaIncio(iTemp - 1) = Mid$(strSplit, iTemp, 1)
Next
End Function

So I want to know if its correct like that:

VB:
Function splitText(strSplit As Variant) As Variant
Dim datosColumnaIncio() As Variant
Dim iTemp As Integer

[Code] .....

So when I call the function:

VB : MsgBox splitText("F4")

It throws an error message :s what is wrong or how can I get the array values of a function ???

View 2 Replies View Related

Sort And Obtain The Lowest Value In A Range

May 1, 2014

Excelforum.jpg

What I want to do is in column 5 row 1, I want it to show a value only if all 4 columns, row 1 are filled in. For example in the picture I want to show a blank in the first 8 rows but to start showing a value on the rest of the rows, in this example IPE 240 to be the first value. When it shows this value from this row I want it to obtain the value with the highest percentege inside. For example in this picture the correct value first value to be shown in column 5 will be IPE (84%)

Ive done this code so far: =IF(AND(B256<>"",D256<>"",F256<>"",H256<>""),Here I want it to show the value on the row that has the highest percentege,"")

View 14 Replies View Related

Auto Calculate Profit By Part #

May 21, 2007

I have attached a chart I'm working on. Each item is assigned a part number and it automatically takes away from the inventory each time an item is sold. I was wondering if the bottom half could work with the top to determine profit.

Example:

Customer purchases Part # 3 (C15) Quantity 1 (D15) for $450 (E15). Is there any way to set it up so whatever part number they enter...it subtracts their sold price from the cost (C5 through C11) to show the profit in (F15).

View 13 Replies View Related

How To Split Profit Margin Into 5 Slices

Oct 17, 2013

This is the Case:

I Produce something with a €2000 cost price. this is variable.

I Sell it for lets say € 2500,- to someone this is also a variable.

That's makes a Profit of € 500,- on the sale. This is 125% revenue

Until here, no problem using a spreadsheet. but now the difficult part.

I have to split of the 25% margin over 2 persons. This has to be done as follow:

a. The first slice is 2,25 for Mr A
b. The second slice is 15,00 for MR B
c. The Third Slice is 2,75 for mr A

This divides 20 of the 25 % over 2 persons. Still 5% margin remains

The last 5 is devided as follow:
d. 85% for MR A
e. 15% for MR B

So in this case:
MR A receives: a:€45 + c:€55+ d:€85 = €185,-
MR B receives: b:€250 + e:€15 = €265,-

The tricky part is when the margin is less than 20%.
for example; If the margin is 14%. than only slice a. and a part of slice b. an be paid off. so in that case;
MR A receives: a:€45 + c:€0+ d:€0 = €45,-
MR B receives: b:€230 + e:€0 = €230,-

Any formula which calculates the revenue for MR A and B automatically based on only the cost and selling price.

A B
1 Cost price - €2000
2 Selling Price - €2500
3 MR A - €185,-
4 MR B -€265,-

View 1 Replies View Related

How To Obtain 6 Numbers In A Cell With No Duplicates

Jan 29, 2014

At any rate, for a statistics experiment, I need a way to obtain precisely 6 random, numeric values in a cell, separated by commas, with no duplicates.

For example, if cell A1 contains the following: 7, 12, 14, 7, 14, 3, then I need to remove the duplicate(s) -- in this case, the extra 7 and the extra 14. After the two duplicates are removed, I then would have to replace with two more values (non-duplicates, of course).

Here are the restrictions, in addition to having no duplicate values appear:

* The first 5 values need to be randomly drawn from values between 1 and 75, inclusive.

* The 6th value needs to be randomly drawn from values between 1 and 15 (inclusive).

* It would be nice - but not necessary - to have the first 5 values sorted in ascending order. For example: 7, 12, 14, 29, 70, 3.

View 7 Replies View Related

Calculating Price Based On Profit

Jun 26, 2012

I'm using a spread sheet to determine what I should charge customers. I want to make at least $300 profit on every job. If my profit is not at least $300 then I must up my price until profit reaches $300. Currently I'm manually adding a percentage (Factor) to the original price until I reach $300 profit. In the image below:

A is the original price
B is the factor by which I multiply the original price to increase it
C is the new figure which is reached after multiplying (1+B2)*A2
D is the costs related to that job (I enter these manually on a separate sheet within the workbook)
E if the profit (C-D)

I use a factor in B because when I increase the price, then I have to increase several parts of the job by the same percentage so I multiply each part by this factor to increase the price of each by the same percentage.

Now what I would like to do is if E2.

View 2 Replies View Related

How To Make Profit And Savings Equal

Dec 18, 2012

How do I come up with a price that will make my profit and my customers savings equal.

Example:
Retail price $178.99
My cost $113.46

If I charge $142.23 then my profit is $28.76

Customer pays $142.23 plus $8.00 shipping ($150.23)
$178.99 Retail minus $150.23 = $28.76 savings

View 9 Replies View Related

How Do I Obtain The Name Of The Form Calling Macro

Nov 21, 2007

I've assigned a single macro to numerous Form Checkboxes (not ActiveX). I'd like the macro to be able to identify the Checkbox calling it, so that if for example, if it were fired by clicking "Check Box 41", the macro should know that.

Is this possible, or must I use an ActiveX control?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved