Getting Total of all Receipt per Customer

January 13, 2010

While reviewing the survey responses we got last month, I found an interesting question/comment on getting the total of customer receipts for the year. This is one example of the type of flexibility you have with XLGL. Let’s look at the solution.

Basically, what we want is a list of customers with an amount next to each name showing how much money we received for a given period. For this example, we’ll suppose we have the beginning and ending date for our period in B2 and B3.

Getting the list of customer is quite easy. We open the XLGL Console and go to Receivables and Clients List. We  drag and drop the word ALL to cell A5. This is what we have:

Customer Receipts for period

 

Now come the real challenge. We want to know how much money we receive from these customers. Money received goes to the bank, so let use our GL function and get the amount of money that entered the bank account from each customer.

Still using the XLGL console, we go to General and Worksheet Functions. We then drag and drop Amount of GL transaction for period to cell B5 and fill out the Function Argument window like this:

Argument window

Argument window

The parameters are filled like this:

  • Accounts: 10600 >> This is our bank account. We could have put this in a cell and reference to it as well.
  • Beginning and End: the date in B2 and B3 on our worksheet.
  • Customers: A5 >> The first customer in our list.

Note that you have to scroll down to see the Customers parameter as shown in the second image above.

We click OK (or press Enter) and Fill down/copy our formula next to each customer to have the final result.

Final Result

  

For the more technically inclined, the formula bar is showing this:

Formula Bar

 

Change the dates in B2 and B3 to switch period and watch the amount update.

You can drill down on an amount by right-clicking on it and selecting Drilldown.

This is just one example of the flexibility of XLGL. Would you like to see another one? Tell us what you’d like.

Pierre Alain

Leave a Comment » | Financial reporting | Tagged: , , , , , , , , | Permalink
Posted by Pierre Alain Carrier

Financial Reporting #3

February 18, 2009

This is the last post of this series on basics of financial reporting with XLGL. So far, we have seen how to import financial figure using the XGLP (P stands for period) and XGL (to fetch the balance at a given date). Today we’ll see how to get budgeted amounts from the GL.

The name of the worksheet function is XGLBudgets (I’m sure you guessed that one) and it works very much like XGLP. Here are some examples:

=XGLBudgets(4010, “August”)

will return the budget amount for account 4010 in August of the current fiscal year.

=XGLBudgets(“5010, 5020″, “First Quarter”, “Second Quarter”, “0100″)

will return the total budget amount for accounts 5010  and 5020, and department 0100,  between the beginning of the first quarter and the end of the second quarter of the current fiscal year.

The function can be created using the XLGL console or using the Excel Insert Function wizard as explained in Financial Reporting #1.

As you may know, Simply Accounting requires you to define a budgeting period for your company: Monthly, Bi-Monthly, Quarterly, etc. When reporting using XLGL, you do not have to restrict yourself to these period. For example, if your budget are set as Monthly, you can still use the following function:

=XGLBudgets(4010, “First Quarter”)

In this case, XLGL will simply add the budget for the first three month of the year and return the result.

What if your situation is the opposite? You are using Quarterly budgets in Simply Accounting and want to report Monthly, as in:

 =XGLBudgets(4010, “August”)

By default, XLGL will use linear interpolation to calculate the budget. In this case, XLGL will return the proportional equivalent for August. Note that this won’t be exactly a third of the budget because XLGL interpolate using daily calculation. As August as 31 days, it will have a bigger budget than September.

Beside linear interpolation, you can also decide to clamp up or down the returned value, that is return the complete amount either when the period is partly present or only when the period is completely included, or using cubic interpolation. Cubic interpolation will take into account budgets from previous and following period to calculate acceleration or deceleration and return a more precise figure.

In summary, the XGLBudgets function will cover all budgeting scenario easily. Although we did not talk about it, it will also cover project budgeting.

Do you have a budgeting problem? Would you like to know more about project budgeting? Let us know!

Leave a Comment » | Financial reporting | Tagged: , , , , , , , , , , | Permalink
Posted by Pierre Alain Carrier

Financial reporting #2

January 21, 2009

Last time we saw that using the XGLP function we can fetch any figures from the GL related or not to other module. One thing that XGLP won’t do is return an account balance.

With XGLP, you can leave the beginning of the period empty and use only an end date to retrieve a pseudo-balance. It is not a real balance because it does not use starting balance. XLGL only adds transaction amount, not starting balance for account/department.

You can still use the method above to get the balance for a project, or a vendor, but when you need the exact balance for an account or account-department, you must use XGL.

XGL is very similar to XGLP, only with fewer arguments. Here is an example:

=XGL(1020, “12/31/2008″, “0100″)

will return the balance for account 1020, department 0100 on December 31st 2008. XGL take only one date as it returns a balance. XGL will take your starting balance and add transactions amount up to the date you request.

This is the function you would use to create a Balance sheet for example.

There is one thing concerning the GL we still have to look at and that is Budget. Next time, we’ll look at the new XGLBudgets function as it related to the GL.

Leave a Comment » | Financial reporting | Tagged: , , , , , , , , , , , , | Permalink
Posted by Pierre Alain Carrier

Financial Reporting #1

January 13, 2009

XLGL worksheet functionWhen doing financial reporting, you usually want to report some monetary figures from the General Ledger (GL) over a period or at a certain date.

Using traditional and general purpose reporting tools such as Crystal Report or Ms Query, you would need to create a custom query using multiple tables. This can become complex rapidly even when you’re not using Departments or projects.

Although XLGL has custom querying tools as well, it offers a nice alternative, both easier to use and more flexible: the worksheet function.

An XLGL worksheet function is a regular Excel worksheet function. It takes parameters, does some calculation and return a result in a cell. The only difference is that the XLGL function will fetch data from your Simply Accounting file as needed to perform its calculation.

For example, entering the following formula in a cell:

=XGLP(4010, “August”)

will return the amount of all transaction posted to account 4010 in August of the current fiscal year. Of course, the account number and the period can reside in other cell that you reference in your function:

=XGLP($A4,$ C$1)

Although you can learn the function and parameter name, XLGL offers a nice drag and drop interface integrated with Excel. You can drag a function, drop it in a cell, set up the parameter using an easy Wizard and then copy the formula to other part of the worksheet.

 Function Wizard

The following online videos shows how to build a complete Income Statement using this method:

http://www.logicimtech.com/learnxlgl.aspx

Here are some more example of how the XGLP function can be used:

=XGLP(“Revenue”, “August”, “December”, “0100″)

will return the total amount for all revenue accounts using department 0100 for transaction made between August and December.

=XGLP(“5010, 5020″, “First Quarter”,,,”ACME Novelty Ltd.”)

will return all purchase made at ACME Novelty Ltd. in account 5010 and 5020 in the first quarter of the current fiscal year.

Of course, the power of the worksheet function is best shown not in its individual use, but when use to create a full report (see video above). Once you have report finished in this way, you can just change the period to update everything very rapidly.

Next time, we’ll continue our financial reporting tour with another function use to get the balance of an account.

Is there some type of financial reporting you think would be difficult to do using this method? Let us know!

1 Comment | Financial reporting | Tagged: , , , , , , , , , , | Permalink
Posted by Pierre Alain Carrier