Building an FOA or C2C Formula to calculate Sales Revenue and Cost of Sales

Here we'll walk you through creating a couple of simple formulas.

A common use of the C2C formula is for calculating Sales Revenue. And this is also a good example of how our non-financial categories can be used.

We're going to show you how to create Sales Revenue from two inputs - Units Sold, and Average Price Per Unit.

Step one is to create a Group in the Non-Financial section, then we'll add some categories to that group, and finally create a formula to calculate your revenue.

  • Go to Settings > Manage Groups > Add new. Give your new group a name and choose a Header (there is a default Non-financial header, but you can rename this or create your own in Manage Headers), then in the Mapping Identifier menu, choose Non-Financial.  
  • Now go to Settings > Manage Categories > Add new. Name your new categories something like Unit Sales and Average Price per Unit, and choose the Non-Financial group you just created as the group. Choose Expense as the type (you only need to choose the Revenue type for Revenue accounts where you will be importing data). Give your categories a code - we usually use category codes beginning with a letter for our non-financial categories to avoid confusion. Also, select the Departments in which these categories need to appear.  Choose the number of Decimals you want - probably zero for your Unit Sales, and maybe two for the Average Price (so you can enter the price in Dollar and Cents)
  • You can fill in the values for these categories by going to Data/Formulas > Data Sheet and selecting them from the dropdown menu at the top left where you see your Chart of Accounts - Non Financial categories appear below your GL Accounts in this list, which is searchable.
  • Now to build the formula, go to Data/Formulas > Manage C2Cs > Add New. Give your formula a name eg Sales Revenue, choose your target category (in this case it will be one of your revenue accounts, choose Multiplication from the Function menu, and then from the Source Categories select your new categories (you can search the list, or scroll down to that group. Now hit Save, and the formula will populate the target category with the correct numbers.

Now let's use the FOA formulas to create a simple Cost of Sales formula driven by the Sales Revenue that we just created.

With the FOA formulas there is no need to create non financial categories as you will enter a variable directly into the formula.

  • Go to Data/Formulas > Manage FOAs, and click Add New.
  • Give your formula a name.
  • Choose your target category/account - this will be one of your Cost of Sales accounts.
  • Choose your operator - in this case we're going to pick Multiplication.
  • Choose your source category - this will be the Revenue account that was the target of the C2C above.
  • You'll now see a table with all your departments on the left. To fill the grid, check the box to the left of any departments that will use this formula, then start entering your data. You can use the blue arrow to the left of each line to fill the first period into the others.

To learn more about formulas click here.