cancel
Showing results for 
Search instead for 
Did you mean: 

Income Statement Formulae (Gross Margin, Net Income, Percentages)

Former Member
0 Kudos

Hello everyone,

Using WEBI, I was able to create an income statement, wanting more flexibility and customization than the one generated by my company's accounting software (fyi. not Oracle, I wish.). So far this is what I have:


This is a crosstab report, with category tables on the rows, by month and year at the columns. The [Report], [Section], [Category], [Group] are hidden dimensions, used only to create subtotal and formula breaks. [Amt] are the account balances. I need help in the Gross Profit/Margin formula, and Percent by Revenue.


For example: what would the formula in the break cell for Gross Margin so that it would do this: sum of [Amt] where [Category]="Revenue" - sum of [Amt] where [Category]="Cost of Sales" (this formula is incorrect sql language, I'm not an experienced sql coder)


Other one would be the net income formula which would be gross margin - expenses 1 & 2 - other.


Lastly, the percent by Revenue, other suggested [Amt]/[Amt] in Body but that would only result in the amount percentages by break subtotals. But I'm looking for for something like [Amt]/sum of [Amt] where [Category]="Revenue"


Thank you for reading this post, I'm really hoping to find the answer here...I've been stumped for weeks.


[Report][Section][Category][Group][Account Description][Account]JAN 2013% RevenueFEB 2013% Revenue
ISGross MarginRevenue                       Revenue                            SALES424021,670.97 [Amt] / Total Revenue 24,193.03 89.7%
ISGross MarginRevenue                       Revenue                            SALES42502,947.39 See example data -> 2,776.21 10.3%
ISGross MarginRevenue                       Revenue                            Revenue                            Subtotal24,618.35 26,969.24 100.0%
ISGross MarginRevenue                       Revenue                            Revenue                       Total 24,618.35 26,969.24 100.0%
ISGross MarginCost of Sales                 Direct Labor                       LABOR50204,238.92 3,853.36 14.3%
ISGross MarginCost of Sales                 Direct Labor                       LABOR50601,602.60 1,485.33 5.5%
ISGross MarginCost of Sales                 Direct Labor                       Direct Labor                       Subtotal5,841.52 5,338.69 19.8%
ISGross MarginCost of Sales                 Direct Materials                   MATERIALS51908,966.08 9,453.27 35.1%
ISGross MarginCost of Sales                 Direct Materials                   Direct Materials                   Subtotal8,966.08 9,453.27 35.1%
ISGross MarginCost of Sales                 Overhead                           OVERHEAD5010628.78 628.78 2.3%
ISGross MarginCost of Sales                 Overhead                           OVERHEAD52902,368.40 2,368.40 8.8%
ISGross MarginCost of Sales                 OverheadOverheadSubtotal2,997.18 2,997.18 11.1%
ISGross MarginCost of Sales                 Cost of Sales                 Total                17,804.79 17,789.14 66.0%
ISGross MarginGross Margin Need Revenue-Cost of Sales Formula Need Revenue-Cost of Sales Formula
ISExpenses 1 & 2Selling & Mktg Expenses       Selling & Mktg Expenses            SALES EXPENSES601020.14 65.48 0.2%
ISExpenses 1 & 2Selling & Mktg Expenses       Selling & Mktg Expenses            SALES EXPENSES6020333.33 333.33 1.2%
ISExpenses 1 & 2Selling & Mktg Expenses       Selling & Mktg Expenses            SALES EXPENSES602512.85 12.85 0.0%
ISExpenses 1 & 2Selling & Mktg Expenses       Selling & Mktg Expenses            Selling & Mktg Expenses            Subtotal488.57 532.13 2.0%
ISExpenses 1 & 2Selling & Mktg Expenses       Selling & Mktg Expenses            Selling & Mktg Expenses       Total488.57 532.13 2.0%
ISExpenses 1 & 2General & Admin Expenses      General & Admin Expenses           G&A EXPENSES70102,502.94 2,466.55 9.1%
ISExpenses 1 & 2General & Admin Expenses      General & Admin Expenses           G&A EXPENSES75001,075.58 978.53 3.6%
ISExpenses 1 & 2General & Admin Expenses      General & Admin Expenses           General & Admin Expenses           Subtotal4,983.49 4,679.76 17.4%
ISExpenses 1 & 2General & Admin Expenses      General & Admin Expenses           General & Admin Expenses      Total4,983.49 4,679.76 17.4%
ISExpenses 1 & 2Expenses 1 & 2 Need Total Expenses 1 & 2 Sum Formula Need Total Expenses 1 & 2 Sum Formula
ISOtherProvision for Taxes           Provision for Income Taxes         TAX PROVISION9020-   -   0.0%
ISOtherProvision for Taxes           Provision for Income Taxes         Provision for Income Taxes         Subtotal-   -   0.0%
ISOtherProvision for Taxes           Provision for Income Taxes         Provision for Taxes           Total-   -   0.0%
ISOtherOther-   -   0.0%
ISNet Income Needs a Gross Margin - Total Expenses 1 & 2 and Other Formula Needs a Gross Margin - Total Expenses 1 & 2 and Other Formula

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Thanks Amit,

The sum formulas worked well. The % by revenue still doesn't work too well.

amitrathi239
Active Contributor
0 Kudos

Hi,

Use this formula to get the % revenue.

=([Amount])/(Sum([Amount]) Where([Category]="Revenue") In Report)*100

Amit

amitrathi239
Active Contributor
0 Kudos

Hi,

Use this formula for to get the % revenue  =([Amount])/Sum([Amount] In Break)*100

Sum([Amount] where ([Category]="Revenue")) - sum ([Amount] where ([Category]="Cost of Sales") )


Give a try with Block,Body,Section functions for other calculations.


Amit