Display Income & COGS Amount on a Project Search

1.Navigate to Lists> Search> Saved Searches> New> Projects

2. On the Criteria tab> Standard subtab:

Use Expressions = T

Parens | Filter | Description |  Formula | Parens | And/Or

(  | Transaction: Account Type | is Income |  |  | Or

   | Transaction: Account Type | is Cost of Goods Sold |  | ) | And

   | Transaction: Posting | is true |  |  | 

 

3. On the Results tab> Columns subtab:

Field | Summary Type | Function | Formula | When Orderd By Field | Custom Label

Name | Group

Transaction: Type |

Transaction: Number |

Transaction: Amount |

Formula (Currency) | Sum |  | Decode({transaction.accounttype},'Income',{transaction.amount},0.00) |  | Income

Formula (Currency) | Sum |  | Decode({transaction.accounttype},'Cost of Goods Sold',{transaction.amount},0.00)|  | COGS

 

4. Enter Search Title and click on Save & Run.

 

Note: The amount displayed on the search result is based on consolidated exchange rate. You have an option though to navigate to Home> Set Preferences> Restrict View> and specify your preferred subsidiary.

 

Alternatively, you can use this formula as well:

 

For now we do not have a complete profitability report wherein all transactions associated to a project are shown.  Below are the steps to make a search to find those transactions and make a profitability report for the project.

 

1. Navigate to Reports>New Search.
2. Choose Project.
3. Under Criteria tab choose Transaction fields.  A new window will pop up.  Select Posting then set it to Yes.
4. Under Results tab, set the following Field:

a.  Name

b. Customer

c. Formula (Currency) then set the following expression on the Formula box: case when {transaction.accounttype} = 'Cost of Goods Sold' then {transaction.amount} when {transaction.accounttype} = 'Expense' then {transaction.amount} else 0.00 end

The expression above will pull up the bills with account type using expense and COGS account

 

d. . Formula (Currency) then set the following expression on the Formula box: case when {transaction.accounttype} = 'Income' then {transaction.amount} else 0.00 end

The expression above will be the revenue side.

 

e.  Formula (Currency) then set the following expression on the Formula box: case when {transaction.accounttype} = 'Cost of Goods Sold' then {transaction.amount} when {transaction.accounttype} = 'Expense' then {transaction.amount}*-1 when {transaction.accounttype} = 'Income' then {transaction.amount}else 0.00 end

This will represent the profit/loss of the project.

 

5. Save the search.

 

Note: Alternatively, you can set Posting = No to search for transactions that are not posting; in effect serves as a forecast of the possible profitability of the project.