"What is the minimum I must raise [input] to make [output] >= zero?"

[crypkilla]

I have a financial model (revenues, expenses, etc.) and the bottom line is cash flow. I have a dashboard where users can change inputs (price of products, when certain one-time expenses occur) that impact cash flow.

Depending on the inputs, cash flow might end up negative.

I need to know the minimum amount to raise a single input (say the price of a single product) in order to make cash flow greater than or equal to zero.

In other words, I need to know "what is the minimum amount I must raise the price of this product so that cash flow is greater than or equal to zero (keeping in mind that cash flow might already be greater than zero)?

This doesn't seem to be a job for goal seek alone. Is there a way to use solver to accomplish this? I ultimately must do this for each year, but I'd like to work out how to do for a single year first, then I plan to just make a macro to do whatever needs doing multiple times (the pro forma is for 10 years).

 

[iliace]

There is an add-in that comes with Excel called Solver. It allows you to set not just the input/output range, but also multiple other constraints. I would look into that.

 

[crypkilla]

Thank you, but I'm familiar with Solver (as mentioned in my post). The constraints won't help me. I need to "Set Objective" cell (my cash flow) to "value" and I would need the value to be greater than or equal to zero. But you can't use a formula for a value.

Hopefully, someone can offer some more specific advice.

 

[iliace]

Thank you for the insult. It is as if I didn't know how Solver works. This puts me right back into place.

If you post a more specific example of the model you are trying to solve, perhaps then you will get some more specific advice to your specific situation.

 

[crypkilla]

I'm sorry.

Here is my spreadsheet.
[TABLE="class: grid, width: 355"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3[/TD]
[/TR]
[TR]
[TD]Product price this year
[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]$10[/TD]
[TD="align: right"]$10
[/TD]
[/TR]
[TR]
[TD]Number of products sold
[/TD]
[TD="align: right"]75
[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD="align: right"]$750[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$1,000[/TD]
[/TR]
[TR]
[TD]Expenses
[/TD]
[TD="align: right"]$2,500[/TD]
[TD="align: right"]$500[/TD]
[TD="align: right"]$3,000[/TD]
[/TR]
[TR]
[TD]Cash Flow[/TD]
[TD="align: right"]($1,750)[/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]($2,000)
[/TD]
[/TR]
</tbody>[/TABLE]

I need a way to raise the product price by the minimum amount necessary each year to bring cash flow to zero. As you can see, the product price does not need to be raised in Year 2.

And whatever I do, I need to make a macro so users can easily change the product prices again after making changes to expenses.

 

[iliace]

If you use Goal Seek, you will see that your selling price in Year 1 should be $33/unit, and in Year 3 should be $30/unit. However, you can keep your unit price at $10 in Year 2?? That doesn't make much sense.

It seems like you need to set up a more robust model to get a more reasonable number.