Incorrect trial balance with inventory lots?

[gargoy...@gmail.com]

Hello! I'm a newcomer to Beancount and text-based accounting, and I'm working on moving from spreadsheets to Beancount for my small business. I am using "inventories" to track physical inventory - in my case, notebooks which I make and sell.

My question is as follows: when I purchase inventory with different prices per lot, my trial balance does not "balance". Rather, the value of assets is is calculated using the most recent inventory purchase price, rather than the price specific to that lot. Here is an example:

option "booking_method" "FIFO"
option "operating_currency" "USD"

plugin "beancount.plugins.implicit_prices"

2014-01-01 open Equity:Opening-Balances
2014-01-01 open Assets:Checking:Chase
2018-12-01 open Assets:Inventory

2018-01-01 * "Deposit"
  Assets:Checking:Chase                1000.00 USD
  Equity:Opening-Balances

 2018-12-27 * "Original run of notebooks"
   Assets:Inventory 25 NOTEBOOKS {             20.00 USD, "Original lot of books"}
   Assets:Checking:Chase -500.00 USD

 2019-12-27 * "New run of notebooks"
   Assets:Inventory 25 NOTEBOOKS {             12.00 USD, "New run of books"}
   Assets:Checking:Chase -300.00 USD

In this case, the stated value of my inventory is $600.00. However, I would expect the value to be $800.00: the first lot was purchased for $500.00, and the second was purchased for $300.00.

I realize that is is a bit of a misapplication of "inventories" - in beancount (and ledger), inventory refers to securities. I surmise that the remaining $200.00 is considered an unrealized loss within the system, and thus the trial balance does not, well, balance by that amount.

My question is: would there be a more appropriate way, or directive, to change the behavior of beancount to not do this, and simply use the cost of an item at the time it was purchased for determining the value of assets?

Thank you!

[Martin Blais]

On Sun, Jun 21, 2020 at 11:42 PM <gargoy...@gmail.com> wrote:

Hello! I'm a newcomer to Beancount and text-based accounting, and I'm working on moving from spreadsheets to Beancount for my small business. I am using "inventories" to track physical inventory - in my case, notebooks which I make and sell.

My question is as follows: when I purchase inventory with different prices per lot, my trial balance does not "balance". Rather, the value of assets is is calculated using the most recent inventory purchase price, rather than the price specific to that lot. Here is an example:

option "booking_method" "FIFO"
option "operating_currency" "USD"

plugin "beancount.plugins.implicit_prices"

2014-01-01 open Equity:Opening-Balances
2014-01-01 open Assets:Checking:Chase
2018-12-01 open Assets:Inventory

2018-01-01 * "Deposit"
  Assets:Checking:Chase                1000.00 USD
  Equity:Opening-Balances

 2018-12-27 * "Original run of notebooks"
   Assets:Inventory 25 NOTEBOOKS {             20.00 USD, "Original lot of books"}
   Assets:Checking:Chase -500.00 USD

 2019-12-27 * "New run of notebooks"
   Assets:Inventory 25 NOTEBOOKS {             12.00 USD, "New run of books"}
   Assets:Checking:Chase -300.00 USD

In this case, the stated value of my inventory is $600.00. However, I would expect the value to be $800.00: the first lot was purchased for $500.00, and the second was purchased for $300.00.

You're using the implicit_prices plugin; the latter (final) transaction sets the latest price at 12.00 USD and you're probably computing the market value based on that prices (the last price we saw for notebooks). 50 x 12 = 600

 

I realize that is is a bit of a misapplication of "inventories" - in beancount (and ledger), inventory refers to securities. I surmise that the remaining $200.00 is considered an unrealized loss within the system, and thus the trial balance does not, well, balance by that amount.

The term "inventory" in Beancount usually refers to a data structure, not to the concept of inventorization you're using (you're doing *real* inventories).

 

My question is: would there be a more appropriate way, or directive, to change the behavior of beancount to not do this, and simply use the cost of an item at the time it was purchased for determining the value of assets?

Is it sufficient to report the cost basis? How are you calculating the value?

You can use COST() in the SQL query tool to get the total cost basis for these, which should be 800.00 USD

 

Thank you!

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/b31ffec7-ff19-4ea8-8730-7fdcc519d0fao%40googlegroups.com.

[Martin Michlmayr]

* Martin Blais <bl...@furius.ca> [2020-06-21 23:51]:

> Is it sufficient to report the cost basis? How are you calculating the
> value?
> You can use COST() in the SQL query tool to get the total cost basis for

I just answered the same question on Reddit. I was going to mention
COST() but it seems that beancount doesn't set the correct cost when
you use FIFO:

https://github.com/beancount/beancount/issues/479

I'm not sure if I'm misunderstanding something but this looks like a
pretty serious issue to me.

--
Martin Michlmayr
https://www.cyrius.com/

[Martin Michlmayr]

* Martin Michlmayr <t...@cyrius.com> [2020-06-22 12:13]:

> I'm not sure if I'm misunderstanding something but this looks like a
> pretty serious issue to me.

Never mind. I was confused...

[Jay Goel]

Thank you for the swift response!

re: Implicit Prices - okay, now I understand the behavior of this plugin, which is to use market value, not book value, of inventories. In my case, I care most about book value. I will need to read up on Beancount's SQL language and can familiarize myself with COST().

If it were possible to achieve the behavior of the "implicit_prices" plugin but use the book value, that would be very convenient, as then I could use dollars for all of my reporting "out of the box". This wouldn't necessarily make sense for currencies, but convenient for this use-case. Maybe a project...

The reddit thread was mine! I seem to have come across the same behavior in two different contexts as I've worked through getting everything set up.

When I disable that plugin, then the balance sheet makes more sense: 200 USD and 50 NOTEBOOKS in assets, and -1000 USD in Opening-Balances. The bottom line of the trial balance is a little bit less intuitive to me: -800.00 USD, 50 NOTEBOOKS, though I can reason through why it is expressed that way.

It seems like, overall, I should avoid using using NOTEBOOKS interchangeably with USD. If I'm incrementing or decrementing the number of books that I have, then use that. Otherwise, it seems like the most sound thing is to avoid using book value as a shorthand for USD.

I'll keep working on this - many thanks for your help!

Jay

--
You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/gmMBWQyGOq4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20200622042037.GB1902%40jirafa.cyrius.com.

[Martin Blais]

On Mon, Jun 22, 2020 at 1:05 AM Jay Goel <gargoy...@gmail.com> wrote:

Thank you for the swift response!

re: Implicit Prices - okay, now I understand the behavior of this plugin, which is to use market value, not book value, of inventories. In my case, I care most about book value. I will need to read up on Beancount's SQL language and can familiarize myself with COST().

To be clear: the plugin doesn't do that, it merely ensures that the @ price annotations on your lots are carried to the internal price database. If you use COST() it won't make any difference.

If it were possible to achieve the behavior of the "implicit_prices" plugin but use the book value, that would be very convenient, as then I could use dollars for all of my reporting "out of the box". This wouldn't necessarily make sense for currencies, but convenient for this use-case. Maybe a project...

The reddit thread was mine! I seem to have come across the same behavior in two different contexts as I've worked through getting everything set up.

When I disable that plugin, then the balance sheet makes more sense: 200 USD and 50 NOTEBOOKS in assets, and -1000 USD in Opening-Balances. The bottom line of the trial balance is a little bit less intuitive to me: -800.00 USD, 50 NOTEBOOKS, though I can reason through why it is expressed that way. 

It seems like, overall, I should avoid using using NOTEBOOKS interchangeably with USD. If I'm incrementing or decrementing the number of books that I have, then use that. Otherwise, it seems like the most sound thing is to avoid using book value as a shorthand for USD.

I'll keep working on this - many thanks for your help!

Jay

On Mon, Jun 22, 2020 at 12:20 AM Martin Michlmayr <t...@cyrius.com> wrote:

* Martin Michlmayr <t...@cyrius.com> [2020-06-22 12:13]:
> I'm not sure if I'm misunderstanding something but this looks like a
> pretty serious issue to me.

Never mind.  I was confused...

--
Martin Michlmayr
https://www.cyrius.com/

--
You received this message because you are subscribed to a topic in the Google Groups "Beancount" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/beancount/gmMBWQyGOq4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20200622042037.GB1902%40jirafa.cyrius.com.

--

You received this message because you are subscribed to the Google Groups "Beancount" group.

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAO3%3DNrhy3NWTLVp0GAVeiD41hwdBciuKS6FA5iAArsgjsEyh4w%40mail.gmail.com.