Break Even Analysis Formula Issue

[jdizzle921]

I have a quick question about a function regarding a workbook assignment in which a
break even point in years must be calculated.

We must calculate the break even point in years based on the Revenue and Expense amounts given with each different growth rate.

I can do things algebraically, however I'm stuck since only a formula can be used to fill in the missing cells.

Any insight would be greatly appreciated.

[Richard Buttrey]

Hi,

Are you trying to use the Solver tool or wanting to manually enter values for the year to find the break even?

Assuming the latter and that by 0.2 you mean that to be .02% then Worst case scenario

C13: =C6*(1+C8/10)^C$12
C14: =C7*(1+C9/10)^C$12

by trial and error a value for the years of 41.1543755 is the break even, i.e. approx 41 years 1 month 25 days

Expected Case = 20.880797 years

Best Case = 14.12273112 years

Or just use the Add In Solver with target cell C12 minimum value 0, changing cell C12 and constraint C15 = 0

[jdizzle921]

Thank you for the quick reply Richard, I greatly appreciate it as time is a factor on this assignment. (Rating Added!)

In regards to the method, we essentially cannot input any manual values ourselves, the values need to be linked to one another via formulas.

For the .2 value, that is actually .2 as in 20%. (In the context of the problem they give an projected growth rate of 35%, so the .2, .4, and .6 are just 'test' numbers since the 35% was just an estimation)

I popped in those formulas and they do indeed give the same amount (Which is what I need) but how did you get the year amounts? With another formula?


Edit: Refreshed and saw your edit. Could you perhaps point me in the right direction on this 'Add In Solver'? That sounds like it might work.

I also added the problem context document.

[Richard Buttrey]

What algebraic formula are you using?

Is the Solver Add In Solution not an option? Admittedly it's an automatic iterative process but it means you don't need to manually try various values - solver does it automatically.

[jdizzle921]

I haven't actually gotten into the algebra yet as I was going to use it as a check-up to confirm things once I got the formulas on the excel workbook figured out.

I believe that the solver shouldn't be a problem as far as the assignment specifications go, but it appears that I don't have it on my Add-in's list. Is it readily downloadable?

Edit: Would this be it?
http://www.solver.com/suppstdtimelim.htm

[Richard Buttrey]

Hi,

Here's the link explaining how to set up the Solver add Inhttp://office.microsoft.com/en-us/ex...001127725.aspx

I'm attaching a picture showing how it appears.

[Richard Buttrey]

...
and forgot to add that since the .2 represents 20% and not 2% as I originally thought you'll need to remove the divide by 10, i.e. now

C13: =C6*(1+C8)^C$12
C14: =C7*(1+C9)^C$12

[jdizzle921]

Upon clicking the Add-Ins tab, the Solver does not show up on the tab, nor am I able to 'Browse' for the Add-In anywhere on the toolbar when Add-Ins is selected.

Is there another way I can install the Add-In?

Edit: Never mind, got it! I'm running things right now. Will upload in a few minutes.

[shg]

Solver is optional when you install Excel. Go back to the installation C D and add it.

Or you can do it without Solver:

Please Login or Register  to view this content.

Three notes:

o I changed your factors to year over year.

o I divided your dollar amounts by 1000 to save space.

o The formula in C12 and copied across MUST be confirmed with Ctrl+Shift+Enter

[jdizzle921]

@Richard- I have all the constraints and such entered into the Solver however I am getting an error box that states "All Cell Contents Must Contain A Formula"

Attached are my constraints, does everything appear correct to you?

@shg- Thank you for the help, but unfortunately I need the Row 15/Total Profit values to all equal zero as they all need to represent the break even points.


Edit: I fiddled around with the Solver some more and it seems no matter what Objective and Variable combination of cells I select, I cannot get the correct values to show up the same way as yours did.

[shg]

The solution I provided computes an integer number of years. If that doesn't work for you, I leave you to a solver or other solution.

[Richard Buttrey]

Hi,

You are not showing the same values in your Solver picture as I showed in mine. Using the Best case scenario

The target cell should be I12 not I15
The changing cell should be I12 not I13:I14
and the constraint should be I15 = 0, and not the three constraints you show. Each scenario needs its own solver solution