Re: On Hand correction

[..hidden..]

It seems no one has a fix for this COGS thing still, I started using SL
since 02. It is critical and I can't imagine how many real customers it'll
bring with a fix. Ideally speaking, if the logics were there, then there
should not be an issue with COG. However, since there are not, may be the
whole approach with calculating COG at real time is wrong (or just too
difficult). We basically had been calculating COGS at monthend and make a
jorunal entry to correct the number at that time. With the current codes,
it is suggested that when a part is being returned, perhaps it should be
returned via a vendor invoice so it won't break the FIFO/COGS logics. That
just wouldn't do for our business as we do have quite a few returns where
reversing a sales invoice is often and necessary (these sales/credit
invoices are also available online as paperless invoice for our customers
and they are not vendors nor do we want them to be).

As for the on hand calculation, we always do a count by comparing the
vendor and sales invoices and make an adjustment via psql (we no longer
have the need to do that anymore since we have kept a tight system).
Keeping the system tight has been nice as it allows us to offer real time
inventory check from the parts table for our online store.

Tim




> On Thu, Feb 19, 2009 at 5:08 PM, Ed W <..hidden..> wrote:
>> I think a similar "wish list" would be to see the net profit per
>> invoice.  ie something similar to the ledger view, but limited only to
>> transactions which related to a specific invoice, but also with more
>> information than the GL view shows.  The point being to look at an
>> invoice for 6 widgets and a thingamy and see that the system thinks that
>> the raw buy price came from these 4 purchase invoices and that the cogs
>> looks ok.
>
> Following query (from a custom report) can list this information
> (per-invoice COGS) in SL and should work in LSMB 1.2 too.
>
>                 SELECT
>                   aa.id,
>                   aa.invnumber,
>                   aa.transdate,
>                   ct.customernumber,
>                   ct.name,
>                   e.name AS employee,
>                   aa.netamount AS amount,
>                   aa.amount - aa.netamount AS tax,
>                   aa.amount AS total,
>
>         (SELECT SUM(0-ac.amount)
>         FROM acc_trans ac
>         JOIN chart c ON (c.id = ac.chart_id)
>         WHERE ac.trans_id = aa.id
>         AND c.link LIKE '%IC_cogs%') AS cogs,
>
>                 aa.invoice,
>                 aa.till
>
>                 FROM ar
>                 JOIN customer ct ON (ct.id = aa.customer_id)
>                 LEFT JOIN employee e ON (e.id = aa.employee_id)
>
>> This kind of view would be really helpful for flushing out cogs
>> problems.  Also personally I would love to see my profit per item so
>> that I can figure out what I am selling for the right price, where I am
>> "bundling", etc (this view would not come from the screen proposed
>> above, but the basic ground work would be similar)
>
> Done this too in SL. Was bit tricky and here is how I did it.
>
> 1. Added a new table FIFO which stored invoice line, FIFO cost for the
> item and invoice line qty for the item from that particular cost tier.
> This invoice line repeats if the sold item comes from more than one
> cost tier.
>
> 2. A new per-item sale report utilizing info from this table with a
> subquery something like:
>
>         (SELECT SUM(qty * costprice)
>         FROM fifo f
>         WHERE f.trans_id = aa.id
>         AND f.parts_id = i.parts_id
>         $fifowhere) AS cogs,
>
> Shall hopefully contribute this to 1.3 if there is a need and as soon
> as I get comfortable with 1.3.
>
> Regards
> --
> http://www.ledger123.com/
>
> - Free SQL-Ledger Hosting
> - Documentation wiki
> - Virtual appliances
> --
>
> ------------------------------------------------------------------------------
> Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco,
> CA
> -OSBC tackles the biggest issue in open source: Open Sourcing the
> Enterprise
> -Strategies to boost innovation and cut costs with open source
> participation
> -Receive a $600 discount off the registration fee with the source code:
> SFAD
> http://p.sf.net/sfu/XcvMzF8H
> _______________________________________________
> Ledger-smb-users mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>