Breakeven Day - Formula

[Allison.S]

Does anyone know the formula to calculate the breakeven day baesd on the
following:-

Income
Row 1 A B C D
Row 2 Jan Feb March April
Row 3 51,000 23,000 24289 30,000

Expense
Row 6 25,000 40,000 22,000 15,000

I believe the answer is that we shall breakeven on day 95, 5th April. But I
need to input this into a formula which will automatically refresh if the
figures get updated.

Thanks for your Help.

 

[Eduardo]

Hi Allison,
the breakeven formula should be

Total row 6 / total row 3 * 30

Which give you 23 days and it make sense since your expenses in January were
25000 and your income 51000

Hope this helped

 

[Jarek Kujawa]

seems little data but presuming:

1. 120 days from Jan 1 till April 30
2. income per day =1.069075=120/102 (102 being total expenses)
3. =102/1.069075=95.41 days till income matches expenses

=(total days in a period)*(total expenses)/(total income)

HIH

 

[Allison.S]

Hi Eduardo,

sorry I don't think I could have explained myself correctly.

I am trying to find out on which day will my income support my expenses, ie
on which day will it become equal to the total value from January through to
April as a formula.

In reality I am working on a period from January through to December but the
example I have shown is the same principle.

Thanks

 

[Jarek Kujawa]

cd.

if you replace in B2:E2 Jan, Feb, March... with figures 1, 2, 3... you
might use the following formula:
(and E13=total expenses, E12=total income)

=SUM(DAY(DATE(2009,B2:E2+1,0)))*E13/E12

array-entered = with CTRL+SHIFT+ENTER rather than just ENTER (if you
insert the formnula correctly curly braces { } will show)

adjust ranges to suit

HIH

 

[Roger Govier]

Hi Allison

I don't understand what you are saying.
With the granularity of your data being monthly, and nothing to say when
within the month the respective incomes and expenses take place, then you
are always in a positive situation.

Jan............Feb............Mar...............Apr
26000.....9000.........11829..........26829

Cumulative Income is always exceeding cumulative Expenditure.
There is no breakeven point.

Perhaps you could explain in more detail.