Hi All The state of balsheet-pnl for now, imho is feature complete and has nicer well-documented internal functions. However I'm still stumped by various equity calculations, unsure how to emulate all combinations of trading/unrealized/realized gains/losses. JRalls has helpfully created some illustrative tests, but these would need plugging into the new balsheet which isn't immediately obvious. And for me to spend a few months getting to understand the formulas.
For this matter I wouldn't be keen to replace the current balsheet yet. I know some users are keen to maintain the current balsheet's calculations and I can't guarantee the new one will produce the same equity section. So, for now, the options are to add the multicolumn balsheet as a new menu item and seek user feedback about equity calculations, or leave it unmerged and unfinished, which is another shame. On Fri, 17 Aug 2018 at 21:20, Christopher Lam <christopher....@gmail.com> wrote: > Hi David > > I refer you to a prior discussion: > https://lists.gnucash.org/pipermail/gnucash-user/2018-June/077758.html > > I appreciate balance-sheet is a formal accounting report. The problem is, > as always, within the details. > > Initially I thought we could leave currencies unconverted. > > Then Geert reminded me *all* currencies *must* be allowed to convert to a > user-selected common-currency. Because a balance-sheet is really a > valuation of all of your assets, into a legal currency of your choice. The > price used must be either (1) actual transactional prices - either > average-cost or weighted-average - both terms whose exact definitions > currently elude me despite looking at code (2) from the pricedb - > latest/nearest. > (* PS what's the strategy if accounts are in EUR/USD/GBP, with plenty of > pricing data in these 3 currencies, but user prefers common-currency to be > JPY, and pricedb has EUR/USD, USD/GBP, GBP/AUD and AUD/JPY amounts? Answer: > Internal logic will try to find an intermediate pricedb pair, eg. > GBP->AUD->JPY at the nearest date available, and EUR/USD will result to 0 > JPY.... Except my report will convert GBP->JPY correctly and leave EUR/USD > in original currency <g>) > > The next issues are all to do with Equity: > > (1) trading-accounts are optional. As we know, trading accounts will > compute *REALIZED GAINS,* so that EUR>USD>EUR transfers at different > prices *must* result in a realized-gain recording. If the trading-accounts > are disabled, the current balance-sheet will make an attempt at computing > its own Realized Gains. It assumes the user hasn't recorded realized-gains > during currency conversions. IMHO There's currently a logic error if there > are trading-accounts in a book with book property trading-accounts > disabled. Moreover, if trading-accounts are disabled, and the user has > recorded but underestimated realized gains, I think the balsheet *should* > report the delta amount as unrealized-gains too??? > > (2) selection of a report common-currency must compute *UNREALIZED GAINS*, > e.g. if EUR/USD/GBP accounts are reported for balsheet in JPY, and JPY has > increased in value between transaction-dates and report-date, presumably > all amounts must increase? This is not proven correctly handled yet. I > still think there's an error - the Asset report-currency amount will use > the higher price, but the unrealized gains are not computed when > trading-accounts are disabled. > > (3) income and expense amounts are technically closed to equity on > balsheet-date but we don't enforce this, so, their difference must account > for *RETAINED EARNINGS* or some other similar heading. If user has closed > books correctly on balsheet-date, then the balsheet should report retained > earnings to be $0. > If anyone can help me, please do. Spreadsheets or formula sheets welcome. > Double-entry is all fun and games until trying to compute a balance-sheet! > The above, not an accountant yadda yadda. > > On 17/08/18 00:27, David T. wrote: > > Hi, > > I admit that I haven’t been following the details of thsi thread that > closely, but it would seem to me that if a user has selected price source > “Latest,” then the report will of necessity include an Unrealized Gains line > in order to balance, as John said. I agree with his suggestion that > Unrealized Gains might not belong in a Balance Sheet report, but I note that > as a personal user of GnuCash, I am rather interested in the current value of > my empire, ephemeral though it may be. I’m eager to see how rich I am Right > Now! Bwa Ha Ha Ha! > > Seriously, though, since “Balance Sheet” seems to be a commonly-used term in > accounting to refer to a particular general type of report and content (I > base this on the fact that Googling “Balance Sheet report definition” yields > numerous accounting websites with explanations of what a balance sheet is and > does), perhaps there could be a separately-identified and named report to > give the armchair trader the putative value of their holdings as of a given > date. Then the Balance Sheet could be dedicated to the real world situation > (i.e., no need for the PriceDB at all—just use the transaction prices), while > the new report could include unrealized gains explicitly. A descriptive name > (“Trade Value”? “Speculative Value”? “Castles in the Sand”?) could help make > clear the difference between the two. > > David > > > On Aug 16, 2018, at 7:30 AM, John Ralls <jra...@ceridwen.us> > <jra...@ceridwen.us> wrote: > > Chris, > > That’s because you used price source = “latest” which uses the most recent > price in the pricedb for the purchase as well as the valuation. > > Regards, > John Ralls > > > On Aug 16, 2018, at 12:58 AM, Christopher Lam <christopher....@gmail.com> > <christopher....@gmail.com> wrote: > > Hi John > > Just to be a pain again... I found a small discrepancy - (This is different > from the previously noted missing-capital-gains situation) > > if trading-accounts are enabled, > a single 100AAPL purchase @ $100 each dated 01/01/18 > a new increased price $200 on 01/06/18 is recorded, > price-source is 'latest' to pick up the new price > There's no trade sale yet -- this means 'trading gains' is $0 -- indeed the > book will not have any Trading accounts yet. > > I'd expect the balance-sheet to record the increased price as 'unrealized > gain'. > > Yet the balance-sheet just displays an increased FUNDS valuation at $20,000 > (i.e. total assets = $20000) without a corresponding increase in > right-hand-side (ie total equity+liability=$10000). > > I'd think the 'correct' balance sheet with trading-accounts enabled, *should* > still report Unrealized Gains, no? > > > On 13/08/18 22:51, John Ralls wrote: > > > On Aug 12, 2018, at 10:04 PM, Christopher Lam <christopher....@gmail.com > <mailto:christopher....@gmail.com> <christopher....@gmail.com>> wrote: > > Hi Jralls > > So just wish to double check my understanding of gnucash's data format for a > balance-sheet on date X > > There are two possibilities for displaying the right-hand-side > > Liabilities + Equity + Retained Earnings + Trading Balances > Liabilities + Equity + Retained Earnings + Unrealized Gains > "Retained Earnings" should be NULL if the user has properly closed the books > on the balance sheet date X. > > In my understanding, "Trading Balances" and "Unrealized Gains" are one and > the same -- in balance-sheet.scm the unrealized-gain-collector is only > populated if book->trading-accounts setting is disabled. (btw this causes a > 'bug' whereby a book with 'enable-trading-accounts', but was later switched > to 'disable-trading-accounts' will then add both the > unrealized-gain-collector and the trading-balance the right-hand-side). > > This seems to be deconstruct current balance-sheet? > > (I can't seem to find any unrealized-gain issue... from using different > price-sources... perhaps this is beyond my understanding.) > > > On 11/08/18 22:41, John Ralls wrote: > > Chris, > > Remember that we’ve long advised users that they need not close their books, > they can run a balance sheet report for any day. IMO removing that capability > would be a major breakage. > > I suspect that you needed to use trading accounts because you didn’t book the > trading gains and losses as income. Users should do that regardless of using > trading accounts and doing so should make it unnecessary for the balance > sheet report to include trading accounts. > > Unrealized gains are another matter entirely and are a result of using prices > from the price database instead of actual cost from the transaction data. IMO > the balance sheet report shouldn’t be taking prices from the price db and > shouldn’t be able to see unrealized gains, but if price source is going to be > an option then an unrealized gains line flows from that so that users don’t > waste a bunch of time chasing an imbalance caused by price differences. > https://bugs.gnucash.org/show_bug.cgi?id=775368 > <https://bugs.gnucash.org/show_bug.cgi?id=775368> > <https://bugs.gnucash.org/show_bug.cgi?id=775368> is related because that’s > currently how the balance sheet report gets the “actual” costs. > > Regards, > John Ralls > > > On Aug 10, 2018, at 11:40 PM, Christopher Lam <christopher....@gmail.com > <mailto:christopher....@gmail.com> <christopher....@gmail.com>> wrote: > > Hi John > > I've managed to make the left-side (activa?) match the right-side (passiva?) > https://screenshots.firefox.com/RNvkjaxnYyxpGkYn/null > <https://screenshots.firefox.com/RNvkjaxnYyxpGkYn/null> > <https://screenshots.firefox.com/RNvkjaxnYyxpGkYn/null> > > 1) it does require closing books on the balance-sheet date > > 2) it does require adding trading-accounts > > The existing balsheet introduces/calculates the "Retained Earnings", "Trading > Gains" and "Unrealized Gains", whereas the current > iteration of new-balsheet will not. > > To me this is the easiest method to ensure both sides produce the same total, > and is now technically correct - if the user has not closed their books, the > balance sheet won't balance. > > This is giving me a headache :( > > Should a new balsheet calculate and report these '(fake) retained earnings', > and 'unrealized gains' ??? > > C > > > On 09/08/18 08:32, John Ralls wrote: > > On Aug 8, 2018, at 8:51 AM, Geert Janssens <geert.gnuc...@kobaltwit.be> > <geert.gnuc...@kobaltwit.be> <mailto:geert.gnuc...@kobaltwit.be> > <geert.gnuc...@kobaltwit.be> wrote: > > I haven't been following every detail of this. However I note on most balance > sheets the total assets doesn't match total net worth (or liabilities/equity). > In most, this is fixed by including the retained earnings. > > I believe at least in most European countries the "left hand side" (Assets, > Active) and "right hand side" (Passive or liabilities + equity) of the > multicolumn view should balance (it's called balance sheet for a reason). > That would suggest retained earnings does have to be part of the balance > sheet. > > However I'm not an accountant and perhaps your book is slightly contrived so I > don't know the exact answer here. > > As for the "multi-column" vs one column debate, both present the same data. > The only difference is visual representation or style. > > As of recently I have become a strong proponent of separating structure (or > accounting functionality in a different context) from style, I think this > should be delegated to the realm of css. This particular layout variation can > IMO be handled by making divs for each large group and either let them follow > normal flow or use float to move them next to each other. If you will you can > have a European style sheet and an American one, or an Australian or whatever. > > As for "categories", I read Frank's earlier reply as if he agreed that at > least for now the account organization is something to be done in the CoA, not > in report code. > > The Balance Sheet is indeed supposed to balance, but in normal practice it > balances only when the book is “closed”, i.e. when all of the income and > expense accounts are summed up and added to > Equity. In US corporate books the cumulative total of income and expenses > lives in an Equity account called “Retained Earnings”. > > In the pen-and-paper days a “Trial Balance” was computed outside of the > books before closing as a way to catch errors before making the closing > entries and writing the formal Balance Sheet. > > GnuCash's existing Balance Sheet Report creates the “Retained Earnings” line > so that one need not close the books (Tools>Close Book) in order to get a > balanced report. Removing that feature might be more formally correct but it > would mean that users would have to close their book before running a balance > sheet. That would be a big change and I don’t think that we want to do it. On > the other hand “Retained Earnings” isn’t the right term for many cases, so it > would be a useful improvement to make it configurable. > > There’s a second problem with the current report as well: If the user does > close their books periodically they’ll have an account for the accumulation > that may well be called “Retained Earnings”. The Balance Sheet Report will > dutifully report the contents of that account and, if there are income and > expenses after the last close, add a second “Retained Earnings” line. That > looks a bit odd and might be confusing; ISTR we’ve had comments on the user > list about just that. > > > Chris, > > To demonstrate the price difference on assets creating an “Unrealized Gain” > line, I created a fake account with Trading Accounts off and purchased on 1 > January 100 shares of a stock for $100, then created a new price for the > stock of $200. The resulting Balance Sheet report is the first screenshot > below. Price source is set to “nearest in time”. > > I repeated the process in a new book with trading accounts enabled and got > the second screenshot. As you pointed out, the “Unrealized Gains” line > changes to “Trading Gains”. Selling the stock made no difference on the > report unless I also booked the 10,000 gain to Income:Short Term Cap Gains, > after which the calculated line became “Retained Earnings” as illustrated by > the third screen shot. > > I went back and did the sale on the non-trading-accounts book and found that > indeed “Unrealized Gains” didn’t change after I sold the stock; that’s wrong, > it’s a realized gain at that point. Booking the gain to Income changed the > line to “Retained Earnings” as it did with trading accounts enabled and as > expected. > > Finally, to illustrate the effect of price source I removed the sell > transaction and changed the price source in report options to “Avg Cost”. The > result is the last screenshot, showing the stock at book value and the > “Unrealized Gains” line at 0. > > Regards, > John Ralls > <Balance Sheet No > Trading.png><BalSheet-Trading.png><BalSheet-Trading-CapGain.png><BalSheetAvgCost.png> > > > _______________________________________________ > gnucash-devel mailing > listgnucash-devel@gnucash.orghttps://lists.gnucash.org/mailman/listinfo/gnucash-devel > > > _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
