FormCalc for liquor sales/alcohol volume accounting

[FlagshipTech]

[Duplicated from email...]

I have a potential new client who is a distributor of wines and liquors. He needs to include on his invoices the total volume of alcohol sold. I can create a custom field for an item in QB which will describe each item by the liter. If I place the custom field on the invoice, can FormCalc total it and can I use that total in a comment saying that “x” liters of alcohol (or some similar wording) were sold on this invoice?

Thanks,

John V. Filiberti

[FlagshipTech]

John,

Yes, FormCalc should be able to do this easily.

How? Set up a FormCalc Layout using a "Total or Subtotal a column" Action type to total the column you describe. Also designate one of your QuickBooks items as a "trigger item" in FormCalc, and assign that trigger item to the Action. Then when you're filling in an invoice, select that item (the trigger item) on the last line of the invoice, to have FormCalc total the column for all lines above the location of the trigger item.

For a step-by-step example, please visit this page: http://www.goflagship.com/products/fcprobsolv1.htm The example is for item Shipping Weights, but that's very similar to what you're wanting to do.

Mark Wilsdorf
Flagship Technologies, Inc.
http://www.goflagship.com

NOTICE: Emails you send to us for technical support on any of our products become the property of Flagship Technologies, Inc. We reserve the right to post your technical support questions and our answers in our online discussion forums at http://www.goflagship.com/forums/ , for the informational benefit of other users of our products. If we use any of your emailed message content in this way, we will hide or disguise any information in it which we believe to be of a confidential nature.

[FlagshipTech]

One more question.

I can modify the items (wines, liquors, etc) to add a custom field called
volume which will show me the total volume for the case of wine in liters
and hard liquor in gallons. I'll take your word that I can easily modify the
invoice to subtotal the volumes. Assuming I subcategorize the items by
Wines, Sparkling Wines, Hard Liquor, Soft Liquor, can I create subtotals on
the invoice that will show me the volumes for each category?

If I run a sales report at the end of the month, can I also subtotal the
sales by item category above, so the accountant knows how much wine sold, hard liquor sold, etc?

Also, is the product compatible with QB Pro 2007?

Thanks,

John V. Filiberti
Certified QuickBooks Advanced ProAdvisor

[FlagshipTech]

John,

> I can modify the items (wines, liquors, etc) to add a custom field called
> volume which will show me the total volume for the case of wine in liters
> and hard liquor in gallons. I'll take your word that I can easily modify the
> invoice to subtotal the volumes. Assuming I subcategorize the items by
> Wines, Sparkling Wines, Hard Liquor, Soft Liquor, can I create subtotals on
> the invoice that will show me the volumes for each category?

Yes. Below each "section" of the invoice, you'd select that same trigger item, to cause a subtotal to be placed on a line below that respective section.

For and example of separate subtotals, go here: http://www.goflagship.com/products/fcprobsolv2.htm

> If I run a sales report at the end of the month, can I also subtotal the
> sales by item category above, so the accountant knows how much wine sold,
> hard liquor sold, etc?

FormCalc only works directly on QuickBooks forms. It has no interaction whatsoever with QuickBooks reports. However if you had FormCalc place each subtotal in the Quantity column on the trigger item's line, I suppose that would be reported as a "sale" of the trigger item(s), and thus would appear on QuickBooks reports. (This question hasn't come up before.)

That said, you'd probably then want to use a different trigger item for each unit of measure you want reported. In other words, a trigger item for "gallons" should be different from the one for "liters", so both quantities could be reported separately.


> Also, is the product compatible with QB Pro 2007?

Yes.


Mark Wilsdorf
Flagship Technologies, Inc.

[FlagshipTech]

I'll download the trial version tomorrow. If all goes well, I'll demo it for
my client and hopefully they'll purchase it. They are using a three user
version of QB Pro 2007 so they will need three copies - is that correct?

Thanks,

John V. Filiberti
Certified QuickBooks Advanced ProAdvisor

[FlagshipTech]

John,

> I'll download the trial version tomorrow. If all goes well, I'll demo it for
> my client and hopefully they'll purchase it. They are using a three user
> version of QB Pro 2007 so they will need three copies - is that correct?

Actually, a 3-user license...which costs less per license then 3 individual licenses.

See: http://www.goflagship.com/orders/onlineorders.htm

Mark Wilsdorf
Flagship Technologies, Inc.

[FlagshipTech]

Mark,

I appear to be almost there. I just have a little glitch that I'm hoping you
can help with.

I downloaded the FormCalc software and am trying to accomplish the
following.

My prospect sells Wine, Sparkling Wines, Software Liquor (24% or less
alcohol content) and Hard Liquor (greater than 24% alcohol content).

I have created items for...

Hard Liquor Total
Soft Liquor Total
Sparkling Wines Total
Wines Total

I also created an item for

Total Order Volume - which will give the total volume of the entire order.

When they create an order, they will group the purchases according to the
above categories.

Wines first
Sparkling wines next
Soft liquors third
Hard liquors fourth

I want separate subtotals because I can make the descriptions very clear on
what is being calculated.

For example, hard liquor subtotal will have a description of

Total quantity of liquor (greater than 24% alcohol content) for this order

The custom field for each item will have the liquid quantity of each case or
unit of measure in liters.

If an order contains all of the above, there should be one subtotal
detailing how much of the group was sold - then at the bottom a total volume
total for the entire order.

Is there anywhere where I can see the total volume by category on any of the
reports?

Hope this makes sense.

Thanks,

John V. Filiberti
Certified QuickBooks Advanced ProAdvisor

[FlagshipTech]

John,

> Is there anywhere where I can see the total volume by category on any of the
> reports?
> Hope this makes sense.

Not sure I understand what you want. Is it that you want a report which shows, say, total volume of Sparkling Wines sold for the month?

If FormCalc calculates the Quantity field for the Sparkling Wines item, when you save the invoice that should save a "sales" quantity for the Sparkling Wines item ("sold" at a price of $0.00 so it doesn't affect income).

When you generate a Sales by Item (Summary or Detail) report, the quantity should appear there. To get a total for the quantity column on the report, you may have to export the report to Excel, or use some other QuickBooks reporting product which totals columns. (Actually, this is a feature we'll likely be adding to our ManagePLUS for QuickBooks product soon...but it doesn't do that yet.)

Mark Wilsdorf
Flagship Technologies, Inc.

[FlagshipTech]

Yes - a monthly report to display volume of sparkling wines, wines, hard and
soft liquors individually.

Unless I'm mistaken, Sales by Item Detail will not give me the field on the
Invoice that I created to multiply the quantity by the custom Item field for
Item volume. Looks like this will only work by going to Excel and
recalculating.

Any ideas regarding the more important part of my prior email? About being
able to do separate totals by product?

Thanks,

John V. Filiberti
Certified QuickBooks Advanced ProAdvisor

[FlagshipTech]

John,

> Unless I'm mistaken, Sales by Item Detail will not give me the field on the
> Invoice that I created to multiply the quantity by the custom Item field for
> Item volume. Looks like this will only work by going to Excel and
> recalculating.

I believe you can accomplish this if the FormCalc Action for each trigger item puts the calculation *result* in the Quantity column.

For example, suppose you have a Volume column on each normal invoice line, where FormCalc calculates that's line's volume (by multiplying quantity times another column, which is a volume factor). And below the invoice section, you've inserted the Soft Liquor Total item. I believe the FormCalc Action for that item can total the Volume column, but put the *result* of that calculation in the Quantity column on the Soft Liquor Total line...which should make the Quantity for the Soft Liquor Total item available on reports, though the dollar value of sales would be $0. (Again, be sure the sales price for the Soft Liquor Total item is $0.00, so QuickBooks will leave the $Amount as $0 on that line.)

Sorry, but I'm doing a lot of this from memory. We're installing all new development hardware (yesterday and today), and I don't yet have FormCalc and QuickBooks available to run on this system to allow testing what I'm telling you.

> Any ideas regarding the more important part of my prior email? About being
> able to do separate totals by product?

Ah, I assumed from what you said that you had accomplished that already.

>> [From your prior email:]
>> ...there should be one subtotal
>> detailing how much of the group was sold....

This should be accomplished by inserting the trigger items you created (Hard Liquor Total, Soft Liquor Total, Sparkling Wines Total, Wines Total) below their appropriate sections of the invoice.

>> - then at the bottom a total volume total for the entire order.

The Problem Solved article I mentioned earlier (http://www.goflagship.com/products/fcprobsolv2.htm) shows the basics of how to do subtotals and a whole-invoice quantity total. However, your problem is a bit more complex, because each of your trigger items is a different item. (This facilitates separate quantity reporting later, as described above, but complicates getting a total for all of them on the invoice.)

Question: if, say, Hard Liquor Subtotal is in liters and Sparkling Wines Subtotal is in gallons, where will you convert them to a common unit of measure (I'm assuming you want to do that for the Total line)? Do you want the Total line to do that conversion? Or am I misunderstanding, and actually all Volume subtotal lines will each be in a common unit of measure, so the Total line can just sum them?

Mark Wilsdorf
Flagship Technologies, Inc.

[FlagshipTech]

Thanks for your responses. I'll test out some of your suggestions later on
today and tomorrow. I'll email you the results.

Thanks,

John V. Filiberti
Certified QuickBooks Advanced ProAdvisor

[FlagshipTech]

Here is where I'm stuck. I have created subtotals for wine, sparkling wine, software liquor and hard liquor.

When Formcalc runs, it adds the totals, but some of the subtotals include numbers from the prior section and subtotal.

For example.

Wine 5
Wine 5
Wine Subtotal 10

Hard liquor 5
Hard liquor 5
Hard liquor subtotal 20

Total Order 20

Thanks for looking into this for me.

John V. Filiberti
Certified QuickBooks Advanced ProAdvisor

[FlagshipTech]

John,

> When Formcalc runs, it adds the totals, but some of the subtotals include numbers from the prior section and subtotal.

> For example.

> Wine 5
> Wine 5
> Wine Subtotal 10

> Hard liquor 5
> Hard liquor 5
> Hard liquor subtotal 20

> Total Order 20


The problem likely is due to you using two different trigger items (Wine Subtotal, and Hard liquor subtotal). Each of these trigger items is suming *all* of the rows prior to it on the form. Hence, Hard liquor subtotal is adding up all rows prior to its incidence on the form.

The normal solution is to use the same QuickBooks item as a trigger item for each section, with possibly the need to retype/change the Description on each line. This would give you, for instance:

> Wine 5
> Wine 5
> Wine Subtotal 10

> Hard liquor 5
> Hard liquor 5
> Wine Subtotal 10 ........same trigger item as above, but you might want to retype the Description for it.

> Total Order 20


If I remember correctly though, you want to be able to get reports on subtotals for each different liquor category, so the suggestion above is problematic.

The only workaround I can see it to (1) Use a single trigger item for your subtotals, as described above, and (2) use Classes (a different Class on each subtotal line) to allow generating liquor quantity reports later.

Mark Wilsdorf
Flagship Technologies, Inc.
http://www.goflagship.com

NOTICE: Emails you send to us for technical support on any of our products become the property of Flagship Technologies, Inc. We reserve the right to post your technical support questions and our answers in our online discussion forums at http://www.goflagship.com/forums/ , for the informational benefit of other users of our products. If we use any of your emailed message content in this way, we will hide or disguise any information in it which we believe to be of a confidential nature.