Break Even Analysis Help --dza

[dvpetta]

Hello,

I've been racking my brains on designing a pro-forma income statement
that can suggest a level of revenue required to attain zero net income
after COGS and expenses. The way this spreadsheet works is that the
user establishes three kinds of financial information:

*Gross margin %
*Variable costs (employment, advertising, etc.) as a % of revenue
*Fixed costs - always the same no matter what level of revenue (ie.
rent always $600k).

Now I've been able to establish a formula for to establish a level of
revenue that would provide zero net income for those three kinds of
cost. The problem I'm having lies in the last expense line item that
is dependent on revenues reaching a certain point.

The line items is called "Percentage Rent" and it's expense is derived
by the following formula: if revenue is greater than $2,000,000 then
the company will owe 8% to the landlord in additional rent expense for
every sale dollar grossed above $2,000,000. For instance, if location
is projected to make $2,500,0000 in revenue then expenses would
increase by $40,000 ($2.5m - $2m = $0.5m x 8% = $40k). In the case of
the model I'm using revenue is projected to be higher than $2M.

I can't seem to incorporate this Percent Rent line item into my
forecasted revenue calculation. I can figure out what revenue needs to
be for the other three kinds of expenses except for one of these.

Can someone help me out!?! Basically I come up with a formula that
would provide for a breakeven scenario (zero gross profit) taking into
consideration the Percentage Rent expense.

Here is a link to the spreadsheet I've been working on, it has a
revenue formula that covers the gross margin, variable & fixed costs,
just can't make it eliminate the Percentage Rent (highilghted).

http://beta.yousendit.com/transfer.php?action=download&ufid=31F8288510FA4E48

Thanks in Advanced,

The Dza

 

[RUSS BARTOLI]

If you don't want to solve the problem using algebra, then use the goal-seek
feature. Before invoking that menu, replace the income number with the
current value, then using the menu item, set net income to a zero target
with the income cell as the change cell.

 

[RUSS BARTOLI]

I should have said, replace revenue with its current value...

 

[dvpetta]

thanks for the goal seek info, worked great but I also would like to
somehow come up with a formula that can calculate the break even
revenue level on it's on as I'll be sending this model out to others
who will change the model's assumptions.

 

[vandenberg p]

Hello:

Just change the following formulas and you will have your result.

P5: =P7/0.445
P6: =P5-P7
P7: =P22

If you wish to solve for a particular level of profits change
P7 to =p22+profit amount so =p22+10000, will produce a profit
level of $10,000

Since this will create a circular reference besure that you have
set Tools/Option/Calculation/interation check box on and have
set maximum interation to some positive number, I usually leave
it set at 100


Pieter Vandenberg

In comp.apps.spreadsheets (e-mail address removed) wrote:
: Hello,

: I've been racking my brains on designing a pro-forma income statement
: that can suggest a level of revenue required to attain zero net income
: after COGS and expenses. The way this spreadsheet works is that the
: user establishes three kinds of financial information:

: *Gross margin %
: *Variable costs (employment, advertising, etc.) as a % of revenue
: *Fixed costs - always the same no matter what level of revenue (ie.
: rent always $600k).

: Now I've been able to establish a formula for to establish a level of
: revenue that would provide zero net income for those three kinds of
: cost. The problem I'm having lies in the last expense line item that
: is dependent on revenues reaching a certain point.

: The line items is called "Percentage Rent" and it's expense is derived
: by the following formula: if revenue is greater than $2,000,000 then
: the company will owe 8% to the landlord in additional rent expense for
: every sale dollar grossed above $2,000,000. For instance, if location
: is projected to make $2,500,0000 in revenue then expenses would
: increase by $40,000 ($2.5m - $2m = $0.5m x 8% = $40k). In the case of
: the model I'm using revenue is projected to be higher than $2M.

: I can't seem to incorporate this Percent Rent line item into my
: forecasted revenue calculation. I can figure out what revenue needs to
: be for the other three kinds of expenses except for one of these.

: Can someone help me out!?! Basically I come up with a formula that
: would provide for a breakeven scenario (zero gross profit) taking into
: consideration the Percentage Rent expense.

: Here is a link to the spreadsheet I've been working on, it has a
: revenue formula that covers the gross margin, variable & fixed costs,
: just can't make it eliminate the Percentage Rent (highilghted).

: http://beta.yousendit.com/transfer.php?action=download&ufid=31F8288510FA4E48

: Thanks in Advanced,

: The Dza