This example shows how to set up QuickBooks and FormCalc for the task described above. If you need more basic information about the setup steps, please see the FormCalc Tutorial.

Overview

As part of their farming operation near Moscow, Kansas, Elizabeth and Dennis Moser operate a custom chemical application business. Billing for custom application services and for the various chemical products they apply is complicated by the many different business arrangements represented among their customers.

For example when a herbicide mix is sprayed on a field, all charges may be billed to one person if the field is owned by the farmer or he cash rents it from someone else. But if the field is "share rented", then part of the charges are billed to the farmer who is renting the field, and part is billed to the landlord. What's more, many different share rent arrangements are possible. The farmer may pay 2/3 of the cost while the landlord pays 1/3, or they may split the costs 50:50, or they might just split chemical costs 50:50 while the farmer pays all of the custom application fee, or....you get the idea:  there's no limit to the number of ways a bill for chemicals and a custom application may have to be split.

Elizabeth does the billing for the operation, using QuickBooks. Since QuickBooks by itself provides no way to split charges among different parties, so she used to do a lot of work with a calculator and Microsoft Excel--including a lot of manual work to calculate the calculated amounts from Excel into invoices she was preparing. 

Then she found FormCalc. After a bit of experimentation and adding some columns to her invoices, she had FormCalc set up to do two jobs at once:

1. It calculates the amount of chemical applied on the field by multiplying the application rate per acre times the number of acres sprayed.
    
2. It calculates the portion of chemical and application fee charges to be billed to the farmer, the landlord, and anyone else involved in the transaction.

Now FormCalc handles all of the calculations for billing portions of each chemical application job to the various parties involved, and it does those calculations right there on her invoices--without any copying/pasting or transferring anything from Excel. Here's part of a completed invoice, with added explanation:

Let's explore what happens on Elizabeth's invoices, to understand how FormCalc works.

How Does it Work?

To fill in the item detail for an invoice, Elizabeth selects an item, then fills in the Rate/Acre, # Acres, and Split columns for each item.

• Rate/Acre holds the amount of chemical applied per acre. 
   
• # Acres is the number of acres on which chemical was applied--usually this number is the same on all item lines. 
   
• Split is the portion of this item line to be billed to this customer. If there's any question about the appropriate Split for a particular customer, she can look it up in notes maintained in the Customer list.

After the invoice is populated with items and the above-mentioned columns have been filled in, she presses the F10 key to invoke FormCalc, which applies the following calculations to each item line:

• Tot. Qty. is calculated by multiplying Rate/Acre by # Acres. This is the total quantity of each chemical that was used in the application job.
   
• Billed Qty. is calculated by multiplying Tot. Qty. by Split. This is often just a portion of the total quantity used for the application job (33.3% in the example above). When that's the case, Elizabeth prepares a second invoice which is the same except for the Split column's entries, for the other party to the transaction . (If the split is 0.333 on the landlord's invoice, for example, the split on the farmer's invoice will be 0.667.) 

It's important to note that Billed Qty is actually the QuickBooks Quantity column, renamed as Billed Qty. Because of QuickBooks' standard behavior for the Quantity column, when FormCalc fills in this column QuickBooks automatically multiplies it by the Rate column (renamed Price Per) and puts the result in the Amount column (renamed Your Bill).

QuickBooks Setup

To accommodate the FormCalc calculations she needed, Elizabeth mostly had to add a few columns to her invoice templates in QuickBooks. Here's a form customization window showing columns chosen for invoice's detail area.

The important added columns are circled in red: 

• Other 1 and Other 2 are spare columns QuickBooks makes available to use on any form. 
   
• Market Value and 2005 are custom fields Elizabeth has added in the QuickBooks Items list. They're normally meant to be used with other items, for another purpose, so on chemical application invoices they're available to use as blanks columns--which Elizabeth fills with data for FormCalc to use in calculations, as described earlier.

FormCalc Setup

FormCalc setup for this task is about as easy as it gets. Only two inter-column calculations are involved. Here are screen shots of FormCalc's Edit Action windows for the two calculations, followed by a screen shot of the Layout's window, which summarizes all calculations to be done on the form.

What Else?

There's not much left to say. This FormCalc application is relatively simple to set up, but it solves a significant problem:  how to accurately prepare invoices for multiple parties to a transaction, using QuickBooks.

Finally, it's worth noting that in addition to her farm business Elizabeth Moser operates a computer consulting business with a focus on QuickBooks setup and support. She's a certified QuickBooks ProAdvisor and is also a dealer for Flagship Technologies' software products, including FormCalc. All of that makes her someone good to know if you're in need of help with setting up or using FormCalc. You can contact Elizabeth Moser through her website at:  www.bizbyliz.com.

 

Buy Now