Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Solver
Goodmorning,
Could you please help me with the following? I have the following data Development Year Accident Year 0 1 2 3 4 2004 23,2 10,6 3,5 1,6 0,2 2005 25,8 11,5 5,6 2,7 2006 22,1 8,2 0,4 2007 35,9 7,1 2008 34,9 the above triangular data refers to a stream of Payments ( Cij) where i : Accident Year (rows) j : Development Year (columns) e.g. C13 = 1,6 , C01 = 7,1 In order to fill the missing values in the above triangle (e..g C24, C33, C34, ...C54) i assume that Cij can be approximated by quantities Xi*Pj whe Xi : is the total amount of payments in respect of Accident Year i Pj : is the fixed proportion of the amount Xi paid in Development Year j, i.e. in payment year i+j From the data I want to estimate the Xi and Pj, which are, of course, identified up to a multiplicative constant. The parameters will be fully identified if, for instance we set: Sum(Pj)=1 the summation from j=0 to k. In other words I am looking for Xi and Pj that minimize: Sum[(Cij-Xi*Pj)^2] where the summation is taken ocer all occupied cells. Solutions are given by the following equations: Xi = Sum(Cij*Pj) / Sum(Pj^2) where both sums are for all j's Pj = Sum(Cij*Xi) / Sum(Xi^2) where both sums are for all i's where the summations are taken over the occupied cells only, and under the constrain that Sum(Pj)=1 the summation from j=0 to k. In other words I would like to use Solver in order to arrive at the following solution i 0 1 2 3 4 Xi .. .. .. .. .. Pj .. .. .. .. .. I would be grateful if you could provide me with some hints Yours sincerely Nikos |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL Solver | Excel Worksheet Functions | |||
Solver | Excel Discussion (Misc queries) | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
Solver | Excel Discussion (Misc queries) | |||
Solver | Excel Discussion (Misc queries) |