Message Boards Message Boards

0
|
2416 Views
|
5 Replies
|
4 Total Likes
View groups...
Share
Share this post:

Find sub-item costs

Posted 10 years ago

Hi, Sorry if this is a mundane question for you all but I need some help that should be simple. I have already imported my data, it has 18 columns, the first being the the name of the product, and the last being the final cost of 1 product. The rest of the 16 columns are amounts of sub-items that make up the product. Not all 16 sub-items are used for every product. I do not have sub-item costs, whole or partial. I think I need to analyze all 84 products and based upon that data I think I could retroactively find the cost of each sub-item. The problem is, I don't know where to go from here. Can anyone help?

POSTED BY: Scott Jensen
5 Replies
Posted 10 years ago

Hint:

Suppose p1 is the unit price of sub-item 1, p2 is the unit price of sub-item 2, .... p16 is the unit price of sub-item 16 and those are all variables with unknown values.

Suppose q11 is the quantity of sub-item 1 for product 1, q21 is the quantity of sub-item 2 for product 1,... q161 is the quantity of sub-item 16 for product 1 and those are all known values.

Suppose you could construct p1 * q11+p2 * q21+...+p16 * q161==priceforproduct1. That is one equation with 16 unknown variables, note that == is not just =.

Suppose you could do that for all 84 items.That is 84 equations with 16 unknown variables.

Study this, think about it, see if you can understand what it has to do with your problem.

Only after that then perhaps look at the documentation for Solve.

Usually we think of n equations and n unknowns, but if you are certain there is a unique solution then having more equations than unknowns may not be a problem.

Then you have to think about how to assemble all these calculations. There are lots of ways you might do that. You could write out the whole thing manually. Or you might study the documentation for Part, Take and Table to see if you could let Mathematica extract and assemble the needed information to build the equations. You might also see what result Mathematica gives your for {a,b,c} * {2,3,4} and see whether something like that might be useful or not.

I would recommend not letting someone blurt out the answer to you. Thinking about this until you see how to express the problem will almost certainly help you more in the future unless all you are really trying to do is learn how to type in someone else's answer.

There are almost always multiple different ways to accomplish any task using Mathematica. Choosing one you understand and that is simple enough is a skill.

POSTED BY: Bill Simpson

Here is a possible start. Drop the first column since that's just a part's name.

And take the final column out but save it as an 84 element list. This is the vector of prices of your products: call it pProducts (for product prices).

Now you are left with a 84 by 16 matrix (84 rows, 16 columns). Call it qSubItems (q for quantity).

Lets say that the price per unit quantity of each sub item is a 16 element vector, call it pSubitems.

So, in effect, you have a matrix equation of the form qSubItems.pSubitems=pProducts

Given that this is potentially 84 linear equations in 16 unknowns, it is likely that it is overdetermined. And it may not have a consistent solution because the measurements may not be completely accurate, or the quantities may not be exact--they may be rounded, or the final product prices may have been rounded or modified in some way.

So, a very practical approach might be to take 16 of these equations randomly and see if there is sufficient information in them (i.e., that the submatrix from qSubItems has non-zeor determinant) to solve for the pSubitems vector.

POSTED BY: David Reiss

I think Bill and I almost tied for this ... ;-)

POSTED BY: David Reiss
Posted 10 years ago

I really appreciate the help and direction on where to start. Thank you!

POSTED BY: Scott Jensen

Better tied than died. But tie-dyed would have been more colorful..

POSTED BY: Daniel Lichtblau
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract