additional subtotal line

For ideas, tips and tricks and comments on using or creating MoneyWorks Forms (invoices, statements, labels etc.),
Forum rules
This forum is not the official Cognito Software support channel for MoneyWorks. If you need help from Cognito, then please contact us (or your local regional support representative) directly using the contact information on the Support page of the web site.

Please try the search facility before posting a new topic to see if your topic has already been covered.

If you do post a new topic, it's a good idea to choose a Topic Subject that it a bit more informative than "Help!" or "A problem". If your topic is actually recognisable by people scanning the forum, you'll have a much better chance of getting a response. Thanks and have fun.
Post Reply
odyssey
Posts: 9
Joined: 08 Jan 2011 05:05
Location: invermere, bc

additional subtotal line

Post by odyssey » 08 Jan 2011 05:10

Hi, new to moneyworks, been trying to understand the calculations for custom forms but can't figure them out. I need to add before the subtotal and tax and Totals 3 lines: subtotal, then 5%profit, then 8% overhead, and then the Subtotal, taxes and Total. Please help

sjb
Posts: 68
Joined: 09 May 2010 03:55
Location: Ontario, Canada

Post by sjb » 08 Jan 2011 08:24

You want to do this on invoices? If so, I'm unsure why. You would be showing your customers all the info. Instead figure that all into your selling price, moneyworks will sort out how much profit and your cost of goods if you account for it properly.

odyssey
Posts: 9
Joined: 08 Jan 2011 05:05
Location: invermere, bc

additional subtotal line plus profit and overhead

Post by odyssey » 08 Jan 2011 08:29

Hi, when doing insurance jobs, the insurance companies specify we MUST do this. We have to put down the cost of the subcontractors work, parts, etc, then subtotal it, then add our allowed profit, then our allowed overhead, and then subtotal again plus tax. We must also remit the invoices we have received from subcontractors, etc. for general public we dont do that, we build in our profit and overhead.

sjb
Posts: 68
Joined: 09 May 2010 03:55
Location: Ontario, Canada

Post by sjb » 09 Jan 2011 04:52

Got it. Unfortunately I've never done form designs so can't help you there.

jiranz1
Posts: 49
Joined: 22 Dec 2010 15:00
Product: Datacentre
Region: New Zealand
Location: New Zealand
Contact:

Post by jiranz1 » 10 Jan 2011 10:45

Is there GST/VAT on the 5% and 8%?

odyssey
Posts: 9
Joined: 08 Jan 2011 05:05
Location: invermere, bc

additional subtotal line

Post by odyssey » 11 Jan 2011 04:01

yes, there needs to be HST on top of that 5% & 8%, then the new total. Any help would be greatly appreciated.

jiranz1
Posts: 49
Joined: 22 Dec 2010 15:00
Product: Datacentre
Region: New Zealand
Location: New Zealand
Contact:

Post by jiranz1 » 11 Jan 2011 12:39

You can get SubTotal1 by setting up a calculation box with the formula: sum(ExpandList ("list", "detail.net")).

From there you could easily set up two other boxes to calculate the 5%, 8%, and subtotal2. I'm not too sure how to add this to the tax amount though.

The other option is to set up the percentages as product items and have them hidden in the printable list areamuch like the freight component is presented in the default forms, or in a separate list area. At least with this method you would have flow through tax calculations.

Hope this makes some sense? When I've got some more time I'll look further into it.

odyssey
Posts: 9
Joined: 08 Jan 2011 05:05
Location: invermere, bc

You can get SubTotal1 by setting up a calculation box with t

Post by odyssey » 26 Jan 2011 08:25

Hi, I believe I can make that work in the actual invoice layout design, but it won't actually take the tax calculation etc and put it in the original order will it? I need the program to calculate 5% and 8% on top of subtotal, and have it go to the proper account ledger for Revenue, tax, etc. I don't understand the Freight Option you listed. It seems you still have to put your own Frieght Total in, and I would need 2 freight boxes where entering the sale. I am so confused

jiranz1
Posts: 49
Joined: 22 Dec 2010 15:00
Product: Datacentre
Region: New Zealand
Location: New Zealand
Contact:

Post by jiranz1 » 15 Feb 2011 15:50

From what I can see the freight calculation on invoices is performed as follows:
In the forms designer, the line item details print as instructed in the list named "list". If you right click that list and select "list options" you will see a line of code that states "lookup(detail.stockcode, "Product.Type") <> "S@""
This essentially stops any product type that starts with S (ie "Ship Methods") from being displayed here.
The transaction Gross and GST is the same regardless of what the "List" subtotal returns, hence the freight amount on the invoices is revealed by simply taking the transaction.gross minus the transaction.gst minus the "List" subtotal.

What I think may work is to create a products representing your markups, using unique starting digits for the code such as "MarkupXXX". I think this is the best way to capture the GST. Add these specific products to the lookup formula in "List" to prevent them from appearing amongst the detail lines. Add a second list box to capture only markup items("lookup(detail.stockcode, "Product.Code") = "Markup@"" and display them how you wish. I would suggest two columns, column 1 = Detail.Description, column2 = detail.net. T

here may be a more direct route than a list box but it's harder to work out at the moment and I've successfully tested this approach.

Using this method you will need to calculate the percentages within the form, but this should be easy to do by putting ".08" in the qty field, and the current subtotal in the unit price field. You can then copy that subtotal figure down since as soon as you enter that line the transaction subtotal will have changed.

odyssey
Posts: 9
Joined: 08 Jan 2011 05:05
Location: invermere, bc

profit and overhead

Post by odyssey » 18 Feb 2011 06:20

Hi again,

I tried that, but it still shows the products for profit and overhead, and when I try to make a new list with the "lookup(detail.stockcode, "Product.Code") = "Profit@" it tells me that Product.Code doesn't exist and it also tells me there is a syntax error. This is really frustrating. Now we spent $600 on this program and my boss it tellin me it's useless!

donnamc
Posts: 213
Joined: 04 Jul 2005 10:37
Region: New Zealand
Location: Dunedin, NZ

Post by donnamc » 18 Feb 2011 10:33

Try contacting Craig at http://moneyworks.sussol.net. He & his team are whizzes at writing reports & forms and I am sure that for a reasonable fee he could get this form nailed for you. I know the frustration of customising forms but trust me, it is all worth it in the end. This is something that other products don't even offer!!


Regards
Donna

jiranz1
Posts: 49
Joined: 22 Dec 2010 15:00
Product: Datacentre
Region: New Zealand
Location: New Zealand
Contact:

Post by jiranz1 » 09 Mar 2011 14:21

Well, we got the form sorted and without having to use two list boxes. We simply applied a filter to prevent the required profit lines from being displayed in the main body of the invoice, and another filter in the 'subtotal stack' to show independent items not revealed within the form body. Have sent a sample form through and seems to be ok at present.

Lyndon

JOHNGLOBE
Posts: 6
Joined: 07 Jul 2011 19:36
Product: Gold

Re: additional subtotal line

Post by JOHNGLOBE » 24 Jul 2011 19:47

You can get SubTotal1 by setting up a calculation box with the formula: sum(ExpandList ("list", "detail.net"))

I need this also and it works, but I need to add the transaction currency as we use multi currencies. Any ideas?

jiranz1
Posts: 49
Joined: 22 Dec 2010 15:00
Product: Datacentre
Region: New Zealand
Location: New Zealand
Contact:

Re: additional subtotal line

Post by jiranz1 » 25 Jul 2011 09:25

I'm assuming you want the subtotal to read something like : "Subtotal1: USD 1,200.00" ??
I haven't tested this but you could try expanding the formula as follows: CurrencyFormat(sum(ExpandList ("list", "detail.net")), Transaction.Currency)

Lyndon

JOHNGLOBE
Posts: 6
Joined: 07 Jul 2011 19:36
Product: Gold

Re: additional subtotal line

Post by JOHNGLOBE » 25 Jul 2011 16:22

Thanks, works well, I had something similar but could not get it to work.
Just can't get my head around these formulas and I have a few more to do.

mgabriel
Posts: 17
Joined: 26 Jun 2012 10:22
Product: Gold
Region: USA

Re: additional subtotal line

Post by mgabriel » 15 Jun 2013 23:33

I have the same requirement of showing Overhead and Profit on an invoice.

Here's what I did:
On my custom invoice there is a filter (if statement) that does not show my O & P detail lines on the invoice as someone had previously mentioned. If you use one of the template invoices, this functionality is provided IF the item code starts with "*". So I just created two items similar to *OVERHEAD15 and *PROFIT10. They don't appear in the invoice and the item number tells me what they are and the relative percentage. Use the "Sale Price" of the O&P items as the percentage markup, then when you create the invoice, the quantity sold would be the total of the detail lines BEFORE you enter the O&P quantities....make sense?

However, this method still sums the O&P into the subtotal of the list object on the custom invoice, so I have to do some calculations on the subtotal to get it to show the proper subtotal without O&P....so this is what I have:

Invoice list object:

Item detail line
Item detail line...etc
Overhead detail line (hidden)
Profit detail line (hidden)
Subtotal=(Sum of Detail lines/(*OVERHEAD sell price + *PROFIT sell price))
Overhead = Subtotal*(*OVERHEAD sell price)
Profit = Subtotal*(*PROFIT sell price)
Total = Subtotal+Overhead+Profit

Unfortunately, the O&P percentages are hardcoded into the calculation engine of MW. Here is my question: How can I filter the detail line item codes to find the percent and use that as a variable in the invoice calculations. Example:
Item Code Description Sell Price
*OVERHEAD15 15% Overhead $.15
*OVERHEAD20 20% Overhead $.20
*PROFIT10 10% Profit $.10
*PROFIT25 25% Profit $.25
If I use any of these items in creating an invoice, on my custom invoice list I want to know how to filter "*OVERHEAD15" to pull the "15" into a variable that I could use in the Subtotal, Overhead, Profit and Total calculations mentioned above instead of hardcoding the percentages into custom invoices for every combination of O&P I could possibly have. Any ideas?

Maytaj
Posts: 2
Joined: 20 Aug 2014 18:20
Product: Gold
Region: New Zealand

Re: additional subtotal line

Post by Maytaj » 20 Aug 2014 18:43

I'm agree with you mate :)
_______________
daily deals

Post Reply