The problem involves estimating the appropriate contributory values of the component land categories from a sample of data. The known variables are total value and the quantities of each component category (acres). Assuming three components (A, B, and C), the total value of the land equals the value of each components times the quantity of that component.

For instance, assuming a data set of 25 rows, we know both the total value and quantity of each row. For each row we also know the quantities of A, B, & C which, in total, equal the total quantity for that row. I would like to estimate the value of B & C relative to the value of A based on the data set. That is, the value of A equals 100% or 1. The values of B & C will be some percentage of A’s value. We can also assume that both B & C’s percentages will be greater than 0 and less than or equal to 1.

I am trying to create a model in Excel as the analysis may be done repeatedly for various data sets. I am using solver with constraints set for B & C as mentioned above. The changing cells are the value ratios or percentages for B & C. This is where I am stuck. Am I even going at this from the right direction? What is the best way to find an optimal solution(s) based on the data set? It doesn’t seem that maximization would be the best way nor does setting a particular value equal to something. Minimization seems the most appropriate.

I have selected minimization and am trying to minimize the predicted value range of a hypothetical property containing, say, one acre each of A, B, and C.

It would be easier if I could find a group of sales each with all A, all B, and all C then draw my conclusions from that through simple math. That is very rarely the case and why I am at this point. Any assistance is greatly appreciated!

## How To Estimate The Values Of Component Parts?

January 16th, 2013 admin