Complex excel formula
A client of has asked me a question I cannot answer. He asked me to come up with a formula that calculates this problem:
He has £50,000 in cash. Product A £10,000, Product B costs £3,500 and Product C costs £750. He wants a formula that will tell him the optimal and most efficient way to spend his money on the products.
Does anyone have a formula that would help?
Thanks,
from my student days
I think you need to do "key factor analysis"
Solver and similar tools
Excel Zone had a short series on this sort of thing this time last year - the third and last part looked at 'Solver':
http://www.accountingweb.co.uk/item/195115
Hope it helps
There was a great program that I came across a few years ago
called TK Solver. I see it has an entry on wikipedia. Does anyone out there still use it?
With kind regards
Clint Westwood
Solver, but you need more data
Simon is right, Solver it is.
Not part of basic Excel, but a free separate addin - not part of the Analysis Toolpak.
You need to know not just the cost but the return of each expenditure and whether there are any related constraints eg on time or personnel or sales caps or other resources.
Otherwise, the decision would be obvious - buy the most profitable one.
Patrick
Profit margins
I would suggest you need to look at the profit margins on each product before it can be calculated easily.
It would also be a good idea to be factoring in the likelihood of sale.
Afterall a simple answer would be 5 A's but if he only makes 10% on that and he can make 66 C's where he makes 30% then product C would be better.
Once you have that sort of information you might not actually need a formula and if you do it would be more useable although more complicated.