This example demonstrates:
•Using FormCalc SST's "mini-spreadsheet" feature to do calculations which use data from other rows of the form
•Using IF() function logic to determine calculation results based on contents of the Tax column
•Entering data in header fields (fuel surcharge, freight charges) and using it in formulas
•Totaling a column on the form
•Protecting formulas from divide-by-zero errors
•How to set up trigger Items in the QuickBooks Item list
•Using trigger Items to control where calculations occur
 | This very complex example is based on a real-world problem FormCalc SST was able to solve for one of our customers. |
How to Use the Mini-Spreadsheet Feature for Multi-Row Calculations
Overview
Paul Hibbs is a partner in an industrial cleaning service specializing in cleaning machine tools and parts, and paint and varnish removal. Paul's company often picks up parts at the customer's site, cleans them at his facility, then delivers the cleaned tools or parts back to the customer.
Business has been good, but the prospect of having to comply with new state sales tax rules is taking some of the fun out of it! Customer invoices often have both taxable and non taxable items on them—and QuickBooks handles the sales tax calculations on those without any problem. But when parts are picked up and delivered freight charges and a fuel surcharge are usually involved. And that's where the trouble begins.
As of January 1, 2014 Minnesota's sales tax regulations will require Paul to charge sales tax on the freight charges and fuel surcharges in proportion to the dollar amount of taxable items on the invoice. So if 62% of the rest of the invoice is taxable, then sales tax must be charged on 62% of the freight charge and 62% of the fuel surcharge. (Do people in government ever fully comprehend the burden laws and regulations can place on the everyday lives of the people they serve?)
Paul is looking for a solution which will calculate the sales-taxable portion of his invoices, including the taxable part of freight and fuel surcharges, and that's something you can provide with FormCalc SST. To set this up for Paul you will need to:
•Customize Paul's Invoice forms, adding two fields to the header area as places to enter freight charge and fuel surcharge amounts, and adding two columns to the detail area which FormCalc SST will use for separately tallying taxable and nontaxable amounts on the invoice.
•Take a snapshot of the Invoice with FormCalc SST, and assign column types and column labels.
•Add formulas to put taxable and nontaxable amounts in the appropriate (taxable or nontaxable) column.
•Add Item-triggered formulas to total the taxable and nontaxable columns, and to calculate the taxable and nontaxable portions of freight charges and of fuel surcharges.
QuickBooks Setup
Customizing the Invoice: adding fields
| If you need more detail on customizing Invoices, see the Customizing QuickBooks forms (for FormCalc SST) topic. |
1.Click on the command to customize the Invoice form's layout.
| The Additional Customization window will open. |
| Paul's staff needs header fields where freight charges and fuel surcharges can be entered. QuickBooks doesn't provide any fields dedicated to those two jobs, but most other fields can be used for such purposes. The main qualifications for fields you can use this way, is that (1) they must not already be in use for other information Paul needs on Invoices, and (2) they must accept all kinds of data entries—which mostly means they cannot be date fields and cannot be fields like the Terms or Rep fields, which only accept entries from their associated drop-down lists. |
| Paul is not using the S.O. No. and FOB fields on invoices, and they will work fine for holding freight charges and fuel surcharges. All you need to do is add them to the Invoice and give them more suitable titles. |
2.On the Header tab of the Additional Customization window, checkmark boxes in the Screen column for the S.O. No. and FOB fields and give them appropriate titles as shown in the Title column in the screenshot below.
| Next you need to add two columns to the Invoice form's detail area, as places for FormCalc SST to tally taxable and nontaxable amounts. Other 1 and Other 2 are spare fields you can use for this purpose. They are not available on QuickBooks reports, but that's fine, because the calculated results in them will never be needed on reports. |
3.On the Columns tab of the Additional Customization window, checkmark boxes in the Screen column for the Other 1 and Other 2 fields, and change their titles to Taxbl and NonTaxbl, in the Title column.
| You may also renumber the Order column to have these newly added columns appear where you want them on the Invoice. |
| Here's the Invoice after customization, showing the added header fields and added columns in the detail area: |
Adding some new Items
Using FormCalc SST's mini-spreadsheet feature requires using one or more QuickBooks Items for triggering some Item-triggered calculations; that is, for indicating the rows on which calculations are to happen.
Existing Items can sometimes be used as trigger Items, but often some new ones must be added. In this case, new Items are needed for taxable and nontaxable freight and fuel surcharges to be calculated by FormCalc SST. They will be added near the bottom of Paul's invoices, arranged something like this:
| Item order can be important, but row spacing never is! FormCalc SST ignores blank rows. So the Items shown above could immediately follow each other, or they could have one or two blank rows between them...it wouldn't matter to FormCalc SST. |
Here is the list of Items which need to be added:
Item |
Item |
Description |
Tax Code |
|---|---|---|---|
Service |
FuelSurchT |
Fuel surcharge - Taxable |
Tax (taxable) |
Service |
FuelSurchNT |
Fuel surcharge - Nontaxable |
Non (nontaxable) |
Service |
FreightT |
Freight - Taxable |
Tax (taxable) |
Service |
FreightNT |
Freight - Nontaxable |
Non (nontaxable) |
| Either Service or Non-inventory part Item types are usually best for creating trigger Items. |
And here is a screenshot of the New Item window as one of these Items is being added:
FormCalc SST Snapshot Setup
1.Select File > New from the main menu to start a new FormCalc SST file.
| (Optional. You may overwrite the existing file if you want.) |
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 in the Detail area, to identify the spreadsheet's column types with their corresponding QuickBooks columns. Enter column labels too, if you want, to have column names which match those on the Invoice.
| The column types and column labels rows should look something like the following when you are done. |
| Also figure out which Header fields are the Fuel Surcharge and Freight fields, and label them. That will make it easier to find them later, when building formulas which refer to them. |
FormCalc SST: Add General Formulas
When FormCalc SST processes Invoices, the Formulas row's formulas will be applied to each Item row on the Invoice (with the exception of Item-triggered formula rows, discussed farther below). For this job you need to enter formulas which identify each row's amount as taxable or nontaxable.
1.Enter formulas for tallying taxable and nontaxable amounts, on the Formulas row.
| The Taxbl column's formula returns the Amount if the Tax column's contents begins with "T", or a blank space if not: |
=IF(LEFT(J21)="T",I21," ")
= |
Every formula begins with an equal sign (=). |
IF(LEFT(J21)="T" |
If the first character of cell J21 (the Tax column) is "T", return the IF() function's first option; otherwise return the second. |
I21 |
IF() function's first option: return the amount from I21 (the Amount column). |
" " |
IF() function's second option: return a blank space. |
| The NonTaxbl column's formula returns a blank space if the Taxbl column contains a number; otherwise it returns the Amount: |
=IF(ISNUMBER(G21)," ",I21)
= |
Every formula begins with an equal sign (=). |
IF(ISNUMBER(G21) |
If cell G21 contains a number, return the IF() function's first option; otherwise return the second. |
" " |
IF() function's first option: return a blank space. |
I21 |
IF() function's second option: return the amount from I21 (the Amount column). |
"Playing" with the sample data
After you have entered these formulas FormCalc SST copies them to the sample data rows (immediately below the Formulas row) to let you see how they work, using sample data which was copied from the Invoice when the snapshot was taken. In this example the Amount column's values were all copied to the Taxbl column, as they should be—because all of the sample rows are for taxable Items.
The only problem is that this sample data doesn't test the formula in the NonTaxbl column. But you you can edit the sample data to do that. Here's another screenshot, taken after making one of the rows' Tax column entry from "Tax" to "Non". As you can see, the formula in the NonTaxbl column seems to be working correctly too.
FormCalc SST: Add Item-Triggered Formulas
Now we can add the formulas which calculate the taxable and nontaxable portions of freight and of fuel surcharges. The calculations will be triggered by the appearance of the Items added earlier—which is why they are referred to as trigger Items.
Here are descriptions of the entries needed on four of the Item-triggered formulas rows.
Item-triggered formulas 1
1.In the Item column, enter "FuelSurchT"—the name of the taxable fuel surcharge Item.
2.In the Taxbl column, enter a formula to total the Taxbl and NonTaxbl columns:
=SUM(G27:G29)+SUM(H27:H29)
= |
Every formula begins with an equal sign (=). |
SUM(G27:G29) |
Will sum the Taxbl column when an invoice is processed. (Referring the range of sample data rows in a formula tells FormCalc SST to refer to the entire column during form processing.) |
+ |
Adds the two sums together. |
SUM(H27:H29) |
Will sum the NonTaxbl column when an invoice is processed. |
| Remember, you can use "close enough" cell referencing. For instance, you can enter the first SUM() above as SUM(G1:G1) and FormCalc SST will correct it to refer to the range of sample data rows (which implies summing the whole column, during form processing). |
3.In the NonTaxbl column, enter a formula to calculate the taxable portion of the fuel surcharge:
=IF(H35<>0,B11*(SUM(G27:G29)/H35),0)
= |
Every formula begins with an equal sign (=). |
IF(H35<>0 |
Using the IF() function this way protects against a divide-by-zero error, which could occur in the unlikely even that cell H35 (the Taxbl + NonTaxbl total) was zero. |
B11 |
The total fuel surcharge amount entered in the form's Header. |
*(SUM(G27:G29)/H35) |
Multiplies the total fuel surcharge amount by the invoice's taxable portion (SUM(G27:G29)) of the invoice total (H35). |
0) |
Returns 0 if H35 was zero (to prevent divide-by-zero errors). |
Item-triggered formulas 2
| This row's formula is the first one in this example to actually illustrate the mini-spreadsheet idea, because it refers to an anchor row—which represents a "prior row of data" during actual processing of an Invoice. |
1.Show the anchor rows for Item-triggered formulas 2, using the Show/hide Anchor rows button in the upper right corner of the main window.
| Having the anchor rows visible will make formula entry easier to comprehend. |
2.In the Item column, enter "FuelSurchNT"—the name of the nontaxable fuel surcharge Item.
3.In the Amount column, enter a formula to calculate the nontaxable portion of the fuel surcharge:
=B11-I41
|
| After entering the formula, the Amount cell should show the total fuel surcharge: |
| Why doesn't it just show the nontaxable portion? Because the anchor row the formula refers to contains no data, which the formula interprets as a zero. To mimic what the formula will "see" during actual form processing, you can enter some sample data in the anchor row cell the formula refers to. In this example the taxable fuel surcharge amount calculated on the Item-triggered formulas 1 row, has been entered in cell I41. Now the just-entered formula makes sense, calculating the nontaxable amount as $21.64. |
| You are free to enter data anywhere in anchor rows, as sample data for your formulas to reference. |
Item-triggered formulas 3
1.Show the anchor rows for Item-triggered formulas 3 and 4, as you did above.
2.In the Item column, enter "FreightT"—the name of the taxable freight Item.
3.In the Taxbl column, enter a formula to total the Taxbl and NonTaxbl columns, the same formula you entered on the Item-triggered formulas 2 row earler.
=SUM(G27:G29)+SUM(H27:H29)
| Why use the same formula here as you did above? It makes the fuel surcharge and freight calculations independent of each other. If Paul wants to charge for freight on an invoice without adding a fuel surcharge, FormCalc SST will accommodate that. |
4.In the NonTaxbl column, enter a formula to calculate the taxable portion of the freight:
=IF(H49<>0,B15*(SUM(G27:G29)/H49),0)
| This is like the taxable fuel surcharge formula except that it refers to cell B15, the freight charge field. |
Item-triggered formulas 4
1.In the Item column, enter "FreightNT"—the name of the nontaxable freight Item.
2.In the Amount column, enter a formula to calculate the nontaxable portion of the freight charge:
=B15-I55
| This is like the nontaxable fuel surcharge formula except that it refers to cell B15, the freight charge field. After entering this formula, its cell shows the freight charge amount—$50.00 in this example. |
| As happened for the nontaxable fuel surcharge calculation, this formula returns the entire freight charge. The reason is that it is subtracting a blank cell (I55, which equates to zero) from the freight charge (B15). To see whether the formula is calculating properly you only need to enter sample data in the anchor row cell (I55), such as the taxable amount from the Item-triggered 3 row, $43.34: |
The completed snapshot
Here's a view of the snapshot's Detail area tab with all formulas and item names in place.
Processing an Invoice
On the first attempt at using this FormCalc SST setup to process an Invoice, QuickBooks stopped the processing attempt with a pop-up message:
The problem is that FormCalc SST is trying to write "63.4688757" in the Amount column (labeled Net here), but QuickBooks won't accept more than two decimal places in that column.The solution is to change the format FormCalc SST uses for writing results to the Amount column.
Changing formats in the Amount column
1.In FormCalc SST, right click one of the Amount column formula cells, and select Format from the pop-up menu.
| The Format data or results window will open. |
2.Select a two-digit number format, and be sure to turn off the Override spreadsheet format option.
3.Click OK to close the window.
| The cell's results should then be displayed with two-decimal places, like this. |
4.Use the same steps to format each of the Amount column's formula cells.
| When you are finished, they should look something like this (anchor rows have been hidden to make the screenshot more compact): |
 | "Why not format cells in the NonTaxbl column?" You can format them if you want, but it isn't necessary, because: (1) The NonTaxbl column is an Other column type. Unlike the Amount column, it will accept any number of decimal places without complaint, and (2) Neither the Taxbl nor NonTaxbl columns will appear on printed Invoices, so their appearance is of little importance. If they were to be printed out for customers to see, then they certainly should be formatted! |
Processing the Invoice Again...Success!
Here's the result of processing the Invoice again.
The taxable and nontaxable amounts are correctly calculated, based on the fuel surcharge and freight amounts in the Invoice's header fields, and this FormCalc SST setup is ready to use for processing Paul's invoices.