Any Answers (back to index)
Complex excel formula
Posted by Anonymous on Tue, 09/02/2010 - 16:34
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,
- 526 reads
- login or register to post comments
- Add to a social bookmarking site
Latest questions on 
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.
I agree
the question cannot be answered without further info.
If B can be sold for £3.5k, C sold for £750k and A sold for £250k, I don't need an Excel formula to tell me the most efficient way of spending my £50k!
Excel Analysis Toolpak
Tell you client to install the Excel Analysis Toolpak, which includes some useful programs such as 'Solver' - some of the tools will be useful, and are easy to use.
Solver, for example, allows you to change a variety of cells in order to find an optimum solution for a given cell.
As others have said, you'll need additional information before deciding what to buy though.
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
-- http://www.sysmod.com