gross profit

[Guest]

Subject Detailed: Universal formula to calculate negative and positve gross
profit margin

What is the correct financial reporting protocol for reporting negative
gross profit margin (i.e., GP%) on a line item basis in spreadsheets?

In seeking a universal formula to address postive and negative gross profit
margin, I considered the following, which would apply to both positive and
negative revenue.

Formula

=ABS(Revenue - COGS)/Revenue

Example A
Revenue = $100.00
COGS = $90.00
Gross Profit = $10.00
Rev - COGS Absolute Value = $10.00
Gross Profit Margin = 10.0%

Example B
Revenue = ($100.00)
COGS = $90.00
Gross Profit = ($190.00)
Rev - COGS Absolute Value = $190.00
Gross Profit Margin = -190.0%

Example C
Revenue = ($1,000.00)
COGS = $90.00
Gross Profit = ($1,090.00)
Rev - COGS Absolute Value = $1,090.00
Gross Profit Margin = -109.0%

The lower gross profit margin percent on a greater disparity between revenue
(i.e., negative revenue and COGS) may be misleading. However, I confirmed
that my HP 12C calculator generates the same results.

If the calculated gross profit margin is misleading and not in the best
interest to show, what is would be the best acronym/nomenclature to
incorporate into a logic statement indicating that the calculated value is
"not meaningful?"

 

[Guest]

Different countries have different accounting and financial reporting
standards. I would ask this question of a qualified accountant in the
country to which this question is applicable, not on an Excel newsgroup.

Dave

 

[Fred Smith]

I say the formula is:

=(Revenue - COGS) / ABS(Revenue)

I don't have any problem with the results of your calculations. In example B,
you lost 190% of revenue. in Example C, you lost 109% of revenue. So, in these
examples, the results are valid. All you are seeing is that, with small
denominators, you can get large percentages. It's the same as "fastest growing
sales". It's a lot easier for a small business to make this claim than a billion
dollar corporation.

However, your formula fails in the more common example where sales are positive,
but less than cost. For example, sales are $50, COGS is $90, your formula would
show an 80% profit, which is not correct. You need to show an 80% loss, which my
formula does correctly.