This example demonstrates:
•Interdependent formulas; for example, column D depends on the result calculated in column C, which depends on values from columns A and B. (Like any spreadsheet, FormCalc SST handles formula dependencies automatically.)
•Using "dummy Items" for entering data on the invoice without recording income
•Calculating Item quantities from other data entered on the same row
•Protecting formulas from divide-by-zero errors
•Totaling a column
Calculate Net Weight and Bushels of Grain from Truck Loaded and Empty Weights
Overview
Schaaf Farms and Trucking, LLC is a multi-generation family business producing feeder cattle and grains—mostly corn, soybeans, and wheat—plus operating a trucking business. Over the past few years they have edged into producing specialty grains: organic soybeans and high-anti-oxidant grain sorghum for the health food market.
Sales of the organic soybeans and grain sorghum are handled a lot differently than for generic, commodity grains. These specialty grains are delivered on contract to two small, regional food processing companies. Neither company's mill has a truck scale, so Schaaf's drivers have to weigh loads at a nearby public scale, drive to the mill to dump their grain, then drive back to the public scale to get a truck empty weight. Also, neither food company pays directly for the delivered grain; rather, Schaaf Farms has to invoice them for it. Along with each invoice they include copies of the scale tickets showing truck loaded and empty weights, plus calculations of the net weight and number of bushels on each load and the per-bushel contract price.
Mary Schaaf does most of the operation's bookkeeping and follows these steps to do the (usually weekly) invoicing:
1.As she gets time during the day she calculates a net weight for each unrecorded scale ticket, using a calculator, and hand-writes the net weight on the ticket.
2.When it's time to send an invoice she gathers the scale tickets for the week (typically for one to three loads of grain) and uses a paper-tape adding machine to total the loads' net weights, then divides by the number of pounds per bushel to calculate the total number of bushels delivered.
3.She verifies the paper tape's numbers against the net weights written on the tickets, then staples the tape to copies of the scale tickets for sending along with the invoice.
4.She enters an invoice in QuickBooks for the total number of bushels at the contract price, and prints it out.
5.She mails the invoice to the food company along with the stapled-together scale tickets and paper tape.
Someone told Mary she should be using an Excel spreadsheet for adding up the tickets and calculating bushels. But she doesn't feel comfortable with Excel and knows it would require printing out a separate, full-sized sheet of paper to send out with the invoices and scale tickets, so she has continued using the paper-tape adding machine.
You tell her she could accomplish everything she does now by entering truck loaded and empty weights directly into QuickBooks as she prepares each invoice and let FormCalc SST do all the calculations. That would eliminate using the calculator and paper-tape adding machine and would have the added benefit of documenting each truckload of grain (loaded, empty, and net weights) directly on the invoice, making that information available to anyone who reviews the invoice.
Mary gives you the go ahead to set this up for her.
QuickBooks Setup
Mary already has QuickBooks Items set up to record income from grain sales:
OrgSoybns
HASorghum
These will also work as trigger Items for FormCalc SST's calculation of total net weight and total bushels. However, you need to set up a couple "dummy Items" (ones which don't record income) to use on the invoice lines where truck weights are entered—one dummy Item for each grain.
Why two dummy Items? Because to calculate net bushels FormCalc SST will need the per-bushel weight of the grain being sold. When one of these dummy items is selected on an invoice you'd like that grain's per-bushel weight to appear on the same invoice line, to make it accessible to FormCalc SST. The easy way to have that happen is to add a custom field to the Items list—let's call it BuWt—then enter the appropriate per-bushel weight in that custom field for each dummy Item. Of course, you will also need to customize the invoice template to include the custom field.
You will need several extra invoice columns for holding things like truck loaded and empty weights. You can use the two spare columns Other 1 and Other 2 for this purpose, but they won't be enough. To make another column available you will need to add yet another custom field, NetWt, for the net weight calculated for each truckload.
 | For easy understanding this example uses the terms loaded weight, empty weight, and net weight in place of the common commercial terms gross weight, tare weight, and net weight, respectively. |
1.Add the two dummy Items to the Items list.
2.Define custom fields in the Item list, named BuWt and NetWt:
3.For each dummy Item, enter a per-bushel weight in the BuWt custom field.
| You don't need to enter anything in the NetWt field; it will be calculated by FormCalc SST. |
4.Create a new Invoice template by copying the existing one and renaming it.
| Mary will only be using the new invoice template for specialty grain invoices, so make a copy of her existing invoice template, then customize the copy, leaving the original template available for other invoicing. |
5.Customize the new Invoice template to include the necessary columns.
| On the Columns tab: |
•Checkmark the BuWt and NetWT custom fields—the ones added to the Item list earlier.
•Checkmark the Other 1 and Other 2 fields, renaming them LoadedWt and EmptyWt.
•Rename the Quantity field to Bushels. (This template will only be used for specialty grain invoices, so that's OK.)
•Renumber the Order as desired, to arrange columns as you want them on the invoice form.
6.Fill out the form as Mary normally would for one of the grains.
| Include at least three rows of sample truckload data for FormCalc SST to copy when taking a snapshot of the form, and put the appropriate grain Item at the bottom: |
FormCalc SST Setup
1.Select File > New from the main menu to start a new FormCalc SST file.
| (Or, you may overwrite the existing file if you prefer.) |
2.Take a snapshot of the QuickBooks form.
| FormCalc SST will gather data about the form and build a spreadsheet based on it. |
3.Assign column types on the Column types row, to identify the spreadsheet columns with their corresponding QuickBooks columns.
| The column types and column labels rows should look something like the following when you are done, if you update the column labels to match the QuickBooks column names. |
4.Format the NetWt cell to have no decimals, because truck weights are only recorded in whole pounds.
| Right-click on that cell (of the Formulas row), then select Format from the pop-up menu. The Format data or results window will open, where you can select a format no decimal places as shown here. |
5.Likewise, format the Bushels (Quantity) cell to also have two decimals:
6.Enter a formula to calculate the NetWt:
=G20-H20
| which breaks down as: |
= |
Every formula begins with an equal sign (=). |
G20 |
The LoadedWt value. |
-H20 |
Minus the EmptyWt value. |
7.Enter a formula to calculate Bushels:
=IF(F20>0,I20/F20,0)
| which breaks down as: |
|
| Here's a view of the formulas: |
| ...and the sample results they calculate: |
8.Type "OrgSoybns" in the Item column on the Item-triggered formulas 1 row.
| This makes OrgSoybns a trigger item for the formulas you will enter on this row. |
9.Enter this formula In the NetWt column on the same row:
=SUM(I1:I1)
| This is a shorthand way of saying "sum the entire column". As soon as you've entered it, FormCalc SST converts the formula to: |
=SUM(I26:I28)
| which is the actual cell range that indicates to FormCalc SST to sum the entire column of net weights. |
10.Enter a similar formula in the Bushels column:
=SUM(J1:J1)
| Which FormCalc SST converts to: |
=SUM(J26:J28)
| After entering formulas the row may look something like this: |
| As you can see, the sample result shows bushels as 2498.3333. The cell needs to be formatted to show fewer decimal places. |
11.Format the Bushels cell on the Item-triggered formulas 1 row, to have two decimals:
12.Enter similar formulas and formatting for the HASorghum Item, on the Item-triggered formulas 2 row.
| That way both trigger Items will be set up in FormCalc SST, so it will work with invoices involving either OrgSoybns or HASorghum. |
 | "But wait a minute! With the LoadSoy Item name on the Formulas row, won't this setup only work for sales of soybeans?" No. Item names on the Formulas row are always ignored for FormCalc SST calculations. The LoadSoy Item is only there because it was part of the data FormCalc SST harvested when the invoice snapshot was taken. The point to understand is that Item names are only important on the Item-triggered formulas rows. |
13.Save the FormCalc SST file to prevent accidentally losing your changes.
Processing a QuickBooks Invoice
To verify that your FormCalc SST setup is working as desired, try processing the QuickBooks invoice you filled out earlier:
To invoke form processing from FormCalc SST
1.Press the Action button in FormCalc SST's main toolbar.
| The focus will switch to QuickBooks, and the Choose an action dialog will appear: |
2.Click on the Process this QuickBooks form button to process the form. (See example results below.)
To invoke form processing from from QuickBooks
1.Press the FormCalc SST hotkey, which is F11 unless you have changed it in Preferences.
The same Choose an action dialog as shown above will appear.
2.Process the form by either pressing the hotkey a second time (quickest for most users), or clicking on the Process the QuickBooks form button.
Results
The invoice should look something like the following after processing, with the weights and bushels calculated as shown here.
