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
% Revenue
FEB 2013
% Revenue
IS
Gross Margin
Revenue
Revenue
SALES
4240
21,670.97
[Amt] / Total Revenue
24,193.03
89.7%
IS
Gross Margin
Revenue
Revenue
SALES
4250
2,947.39
See example data ->
2,776.21
10.3%
IS
Gross Margin
Revenue
Revenue
Revenue
Subtotal
24,618.35
26,969.24
100.0%
IS
Gross Margin
Revenue
Revenue
Revenue
Total
24,618.35
26,969.24
100.0%
IS
Gross Margin
Cost of Sales
Direct Labor
LABOR
5020
4,238.92
3,853.36
14.3%
IS
Gross Margin
Cost of Sales
Direct Labor
LABOR
5060
1,602.60
1,485.33
5.5%
IS
Gross Margin
Cost of Sales
Direct Labor
Direct Labor
Subtotal
5,841.52
5,338.69
19.8%
IS
Gross Margin
Cost of Sales
Direct Materials
MATERIALS
5190
8,966.08
9,453.27
35.1%
IS
Gross Margin
Cost of Sales
Direct Materials
Direct Materials
Subtotal
8,966.08
9,453.27
35.1%
IS
Gross Margin
Cost of Sales
Overhead
OVERHEAD
5010
628.78
628.78
2.3%
IS
Gross Margin
Cost of Sales
Overhead
OVERHEAD
5290
2,368.40
2,368.40
8.8%
IS
Gross Margin
Cost of Sales
Overhead
Overhead
Subtotal
2,997.18
2,997.18
11.1%
IS
Gross Margin
Cost of Sales
Cost of Sales
Total
17,804.79
17,789.14
66.0%
IS
Gross Margin
Gross Margin
Need Revenue-Cost of Sales Formula
Need Revenue-Cost of Sales Formula
IS
Expenses 1 & 2
Selling & Mktg Expenses
Selling & Mktg Expenses
SALES EXPENSES
6010
20.14
65.48
0.2%
IS
Expenses 1 & 2
Selling & Mktg Expenses
Selling & Mktg Expenses
SALES EXPENSES
6020
333.33
333.33
1.2%
IS
Expenses 1 & 2
Selling & Mktg Expenses
Selling & Mktg Expenses
SALES EXPENSES
6025
12.85
12.85
0.0%
IS
Expenses 1 & 2
Selling & Mktg Expenses
Selling & Mktg Expenses
Selling & Mktg Expenses
Subtotal
488.57
532.13
2.0%
IS
Expenses 1 & 2
Selling & Mktg Expenses
Selling & Mktg Expenses
Selling & Mktg Expenses
Total
488.57
532.13
2.0%
IS
Expenses 1 & 2
General & Admin Expenses
General & Admin Expenses
G&A EXPENSES
7010
2,502.94
2,466.55
9.1%
IS
Expenses 1 & 2
General & Admin Expenses
General & Admin Expenses
G&A EXPENSES
7500
1,075.58
978.53
3.6%
IS
Expenses 1 & 2
General & Admin Expenses
General & Admin Expenses
General & Admin Expenses
Subtotal
4,983.49
4,679.76
17.4%
IS
Expenses 1 & 2
General & Admin Expenses
General & Admin Expenses
General & Admin Expenses
Total
4,983.49
4,679.76
17.4%
IS
Expenses 1 & 2
Expenses 1 & 2
Need Total Expenses 1 & 2 Sum Formula
Need Total Expenses 1 & 2 Sum Formula
IS
Other
Provision for Taxes
Provision for Income Taxes
TAX PROVISION
9020
-
-
0.0%
IS
Other
Provision for Taxes
Provision for Income Taxes
Provision for Income Taxes
Subtotal
-
-
0.0%
IS
Other
Provision for Taxes
Provision for Income Taxes
Provision for Taxes
Total
-
-
0.0%
IS
Other
Other
-
-
0.0%
IS
Net Income
Needs a Gross Margin - Total Expenses 1 & 2 and Other Formula
Needs a Gross Margin - Total Expenses 1 & 2 and Other Formula
