Adding Columns and Custom Field Calculations in QuickBooks
Sometimes your unique business needs go beyond the current capabilities of QuickBooks. Your business may need additional functionality from add-ons to provide for those unique needs. For example, you might want to add totals for more than the Amount column on invoices, use custom and existing fields to perform item level calculations and displaying information based on an if/then statement.
I’ve tried a couple of add-ons that perform such calculations and work well with QuickBooks, but the one I found most flexible and easy to use is FormCalc SST by Flagship Technologies. This add-on program takes a snapshot of your screen (form), pulls it into a spreadsheet format where you can create formulas. With one stroke on the keyboard and one mouse click, you can then place these calculated fields on your invoice, purchase order, sales order, or any other template that pulls data from a form.
With FormCalc SST, you can perform numerous different kinds of calculations using custom fields. For example, you can add custom fields for Weight and Total Weight to an item and then use FormCalc SST to calculate total weight by multiplying Weight and quantity and placing the results in the Total Weight field. After that you can use a custom field from the customer, called Total Invoice Weight and total the Total Weight column at the invoice/sales order level.
Once you know your Total Invoice Rate, you can use an If/Then statement to determine if the total weight exceeds the amount allowed for air shipping and print a message on the invoice/packing list indicating the shipment must be sent via ground shipping.
If your customer needs part of the shipment in a hurry, you can subtotal the weight for those items needed in a hurry, indicate those will be shipped by air and then add another subtotal for the remaining items indicating they will be shipped by ground.
I used FormCalc SST as a solution for a client who sells air space on radio stations as a third party rep. She needed a way to show the Gross and Net cost of the airspace sold, with a total at the bottom, and her commission on an item (advertisement) level. Her commission was the “Sale” to be recorded in QuickBooks, while the Gross and Net were informational for the station.
I created custom fields for each customer/job (the media group/radio station) for Total Gross and Total Net. At the item level, I created custom fields for Gross and Net. On the Invoice data entry form, I added these custom fields to the print and screen version of the invoice.
My client only had to fill in the commission rate in the Qty field on the form, such as .15 for 15%, and the gross amount in the “Rate Field”. That calculated her commission by item and placed it in the Amount field.
Using FormCalc SST, I created a spreadsheet where I was able to calculate the Net (=Gross*(1-Qty)). Then, I summed the Gross and Net columns and placed the results in the Total Gross and Total Net fields on the customer level. A simple reformatting of the invoice allowed me to show the calculated fields and their totals in an easy to read format.
To run the calculations, my client hits the F11 key to process the form using the saved template in FormCalc SST. All the calculations are performed and the values placed in the appropriate fields. They are even entered into the fields as a number format (#,###.00), which is specified in FormCalc SST and entered into the form as text.
By using FormCalc SST, my client does not have to calculate the commission and net for each advertisement by hand, which is a real time saver and reduces chance of error. The information for each invoice is saved with the invoice so she doesn’t need to run the calculations each time she opens the form.
The FormCalc SST spreadsheet is saved in a separate file and must be open when you process the form. If more than one form needs to be processed with FormCalc SST, the file associated with that form must be open. Once the FormCalc SST spreadsheet file has be created and saved, any changes to the screen form will require a new FormCalc SST spreadsheet to be created.
If you have unique needs that can’t be performed using QuickBooks alone, contact us. Kritzberg Consulting will find easy to use solutions for you.