Melissa has attended:
Excel PowerPivot course
Excel or Dax functions
I am trying to build a 'what if' tool, that calculates row by row, based on the previous answers generated by the same tool. I also have a couple of conditions that need to be met, is it possible to do this?
for example
Headers
Product, Price, Quantity, Total Value, Available cash
If total value > available cash then return a new Quantity that doesn't exceed available cash.
On the next row, available cash will take into consideration the previous purchase, and we will continue to buy row by row until there is no available cash, or skip transactions until a product is returned and there for available cash is positive again and you can buy more products
Basically I want to build a comparison to the original had I had less or more cash available to begin with, would I have bought the same products and how would it differ?
Any suggestions would help. Thank you
RE: Excel or Dax functions
Hi Melissa
Thanks for getting in touch. Just to check are you trying to build this in a PowerPivot table with a DAX function, or are you open to other Excel-based models?
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Excel or Dax functions
I am definitely open to other excel-based models. Just looking for a solution.
Thank you for your help
RE: Excel or Dax functions
Hi Melissa
Thanks for your reply. Thinking about this one and trying out some scenarios, this kind of problem is perfect for an Excel tool called Solver. Solver allows you to program in multiple calculations and run with various scenarios until you find a solution that fits.
First you have to enable Solver. Go to File (or the Office button) and choose Options > Add-Ins > Go and check the box marked Solver. This will add a new item on the Data menu called Solver.
I have attached a workbook with some sample data in to get you started. If you go to Data > Solver you can see the inputs I've already programmed in.
I have set a target cell (that is, a goal) whereby the total spent (D7) must reach the starting cash value (I chose £100 arbitrarily).
The changing variable cells are the quantity purchased. I've added one constraint, in that all the quantities must be integers (whole numbers) to ensure it doesn't assume you can purchase for example 1.3 units of a product.
Once you enter these details and hit Solve it will generate a solution for you.
To fully realise this solution you will need to add in your own constraints that reflect things from the real world. For example, you might add a constraint where you must purchase at least 10 units of product B. Or Quantity of Product C must always be greater than Product D. You'll be able to add in these constraints according to how your model is supposed to behave.
Have a look at my attached workbook, give it a try and let me know how you get on. Solver is a very powerful tool with lots of possibilities for modelling what-if scenarios.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
GF Purchases.xlsxRE: Excel or Dax functions
Thank you, I am going to give it a go and see how I get on. Will let you know the verdict.
RE: Excel or Dax functions
Hello,
Is it possible to put date ranges as a constraint? Also, is there a way to trigger the solver to run automatically if another cell value is greater than a specific number? I am going through historic data and trying to work out what the case would be had we spent less or more at a specific time, and the knock on effects to the numbers after the solver has run. At the moment I can manually look up dates and create new scenarios but it would be great to make this less manual.
For example if the value in column D > 2, then for all rows in the table that have a date range between A and B change the values in column C as appropriate. The target cell is a function of the cell values (C*B)/E and cannot be greater than 2.
I feel there might be an easy solution but I have been thinking about it for so long I am going round in circles.
Thank you
RE: Excel or Dax functions
Hi Melissa
It sounds like you're making progress, good to hear.
Yes, you can use dates as a constraint. If it's having trouble understanding your dates use the serial number of the date instead. You can find this by formatting a date cell to General, and it will display the date as a number instead (e.g. 8/7/2013 is 41463).
Unfortunately you can't have conditional logic as part of the Solver setup. Perhaps a better route would be record a macro of you performing the Solver routine, and assuming the sheets have a similar setup this shouldn't be too difficult to repeat over many different data sets.
The only other way I can think of to fudge the conditional part is to sort the data on Column D first. Then all the "greater than 2" entries will be lumped together. Depending on how many outcomes there are, you can then group the constraints together. If cells D2:D31 have all got the result 2, you could specify the constraint "D2:D31 = 2". You can repeat for all the possibilities.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
