Inventory Profit Margin Report not giving expected results

1

Hello,

I am using Manager to account for stock trading activity (purchases and sales of shares of public companies), and “profit” according to the inventory profit margin report does not match the “profit” (a.k.a. capital gain/loss) on the Profit and Loss statement. The Profit and Loss statement is correct, but the Inventory Profit Margin report is not. Here is a typical scenario:

Purchase of a stock (ABC Inc):
I create a “Payment” in Receipts & Payments, and have two rows as follows:

Row 1) Select item (ABC Inc), record the quantity (100), and unit price ($50.00). This represents the purchase price paid for the shares ($5,000.00)
Row 2) The broker charges a commission for the transaction, of say $10. To record the commission, I select item (ABC Inc), leave quantity field empty, and unit price $10.00.

Sale of a stock (ABC Inc):
I create a “Receipt” in Receipts & Payments, and have two rows as follows:

Row 1) Select item (ABC Inc), record the quantity (100), and unit price ($55.00). This represents the sale price received for the shares ($5,500.00)
Row 2) The broker charges a commission for the transaction, of say $10. To record the commission, I select item (ABC Inc), leave quantity field empty, and unit price $10.00.

The true profit on this stock trade is: $5,500 - $10.00 - $5,000.00 - $10.00 = $480.00 and this is the amount I will see on the Profit and Loss statement.

On the Inventory Profit Margin Report this will be shown as:
ABC Inc; $5,500.00 (Sales); $5,000 (Cost of sales); $500.00 (Profit)
(broker commission is ignored)

It appears to me that the Inventory Profit Margin Report ignores fields where quantity is Null, and I believe it should not be ignored as broker commissions are part of the profit calculations. I also cannot enter Zero in the quantity field because any amount multiplied by Zero is Zero, so I must leave the quantity field empty when recording the broker commission.

Please let me know if you agree that it’s a bug in the profit margin report and should be fixed or if you have other suggestions. I find the Inventory Profit Margin Report very convenient to use for tax reporting, but the result isn’t accurate.

2

The Inventory Profit Margin report is not wrong. Your description of the program’s behavior is wrong, and your entry of the transactions is also wrong.

The cost of sales on the Inventory Profit Margin report for the example you gave would be 5,010, not 5,000. That is because the average cost when sold is 50.10 per share. The commission is treated like a freight-in cost and added to the value of Inventory on hand for the stock. See Add freight-in to inventory item costs | Manager.

Your receipt was entered incorrectly on two counts:

  • By entering +10, you would be adding to the amount received. The commission was actually subtracted. So, if anything, that should have been -10, which would, in fact, produce a receipt total of 5,490.
  • By leaving the quantity blank, though, you were expecting the program to treat the commission the same way as it did on the payment. But it doesn’t work that way. The zero-quantity addition to average cost works only for purchases.

Think of it this way. When you have additional costs on a purchase, such as freight-in or commission, these can be distributed across the 100 shares in inventory. That is what contributes to average cost. And cost of sales is average cost times units sold. So far, so good.

But the sales figure is unit sales price times units sold. When you sell zero units (as you are telling the program you did when you leave the quantity field blank) that results in no sale for the commission line on your receipt (as far as the margin report is concerned).

The P&L works out (assuming you enter the sales commission properly as a negative number) because it works from the receipt and payment amounts, not units sold. So if you want to use the Inventory Profit Margin report, you must enter the proceeds of the sale, not sales price before commission. Why not use the amounts for the margin report? Because it is calculating based on units to get the margin percentage. And you might purchase 100 shares, but only sell 25. So the program must calculate margins based on units actually sold

With the correct entries, the report looks perfect:
28%20PM

3

Thank you so much for your thorough response. You are 100% correct on all accounts, and I apologize for the +10 and -10 errors. Ultimately, the receipt needs to be entered as follows, all on 1 line:

Quantity = 100
Unit price $54.90 (net proceeds / quantity; $5,490 / 100 = 54.90)

This does give the expected result now :slight_smile:

Thank you.

Enjoy your day/night!

1 Like
4

But this is not full disclosure, as you have netted off proceeds and transaction costs into one amount.

On acquisition of a stock you are correct in that it’s entered over 2 lines, the stock acquisition + the brokerage as freight-in.

However on disposal, you still need to enter it over two lines, the stock disposal + the brokerage. However, in this case the brokerage gets directly allocated to the P&L rather then to the inventory item.
0000000%20Bug%205

For the brokerage to work on disposal, Manager needs to have the brokerage as a freight-out instead of freight-in, then it would deduct. There has been discussion about this but nothing to date.

Declaration: I have used Manger for stock acquisition and disposal for years but have never used the Inventory Profit Margin Report in relation to that stock trading.

5

@slavaf2000, you do not need to do this. The point is to record how much money you receive from the sale. You may want to do this to keep separate track of commissions paid. In some jurisdictions, that might be a government requirement. In others, it is not, and stockbrokers may report only net proceeds of a sale. The key is to determine what information you are required to provide when reporting capital gains income.

As Manager is currently configured, if you want to use the Inventory Profit Margin report, which you said was your goal, you have no choice. If your tax authority requires you to report expenses of sales separately, you could do that two ways:

  1. Enter the two lines and give up use of the Inventory Profit Margin report.
  2. Enter one line with net proceeds and use other documentation from your brokerage to support tax filings.
6

That maybe your personal opinion (or an attempt to discredit my post) but a professional would NEVER advise truncating a transaction to a single line entry.

EG: If a single transaction (property purchase) has multiple components being a mix of both debits and credits, then each component within that transaction is entered on its own line and is NOT unprofessionally truncated to a single line - besides it fails the full disclosure test.

7

It was neither. It was a simple discussion of what @slavaf2000 will have to do to use the Inventory Profit Margin report to calculate profits on securities transactions. I clearly acknowledged that entering net proceeds might not be advisable in some jurisdictions. But in others, it definitely is acceptable. And in still others, there are alternative paths to obtaining and documenting required information for required filings.

Since you have mentioned it twice, what is this “full disclosure” you apparently think is so critical? Who defines it? What must it include? Where must it be disclosed, and to whom? If you are honest, you will acknowledge that requirements for documenting and reporting securities transactions and their results vary by jurisdiction. The phrase “full disclosure” in legal and accounting circles generally refers to making information known to affected parties that could affect their willingness to enter into contracts or transactions under stated terms or to rely upon the advice of an entity without fear of conflicts of interest. It does not normally refer to how a specific transaction is entered on your books.

8

The full disclosure concept is part of the accounting principle which requires business transactions to be reported as gross values (not nett) and the simplest way to do this is to ensure that the initial recording of a transaction’s components are at gross values.

Various accounting standards.

Yes it does. The following two examples illustrates how Manager both complies and doesn’t comply with the principle.

Part 1 - Inventory sales. The gross value of the inventory sale should be posted to an income account and the gross value of the inventory cost of that sale should be posted to an expense account. Manager complies by having both an income (Inventory sales) and expense (Inventory cost) accounts.

An alternative would be to have only a single “nett sales” account where the sale and cost values would contra off. This would be non-complying as the gross turnover of the business is not being disclosed.

Part 2 - Fixed Asset disposal. The gross profit on sale would be posted to an income account and the gross loss on sale would be posted to an expense account. Manager doesn’t comply as it only has a single disposal account which nett off both profit and loss transactions.

This full disclosure just doesn’t apply to businesses but can also apply to individuals - taking interest earnings (and there are other incomes) as an example. Gross interest earned is reported separately from costs incurred in earning that interest.
000%201

000%202

This is most applicable where the tax authority practises “data matching”, that is, they compare the data received from the interest payers with that declared by the individual.

In closing, modifying a transaction’s input just so that a report can look right is not justification.
Accounting standards come first, jurisdictional requirements come second, that is why we have tax returns - it is the document which transitions between accounting and jurisdiction.

9

So, @Brucanna, you have nicely illustrated the point I already made:

In this thread, I was initially answering the direct issue raised by @slavaf2000, namely, the belief the Inventory Profit Margin report is wrong. It is not. Later in the thread, when the topic expanded, I was very clear in stating that entering net proceeds might not be advisable and that tracking expenses of sales might even be a government requirement. Your example gives a case where separate reporting of expenses of generating income is required, although reporting of interest is different from reporting capital gains on sales. Specifically, I wrote:

As you correctly point out, the tax return is the transition between accounting and jurisdictional needs. The dual-line, dual-account option #1 is one way get the necessary information for the tax return. Using other supporting documentation as in option #2 is another. As an example, in the United States, the Internal Revenue Service Form 8949 is where sale of capital assets is reported. Headings on the form are as below:

50%20AM
50%20AM781×112 33.1 KB

And in the instructions, definitions for content of Box 1(d) give two alternatives:

If you received a Form 1099-B or 1099-S (or substitute statement) for a transaction, enter in column (d) the proceeds shown on the form or statement you received. If there are any selling expenses or option premiums that aren’t reflected on the form or statement you received (by an adjustment to either the proceeds or basis shown), enter “E” in column (f) and the necessary adjustment in column (g).

Usually, sales commissions are already subtracted by the brokerage and no entries are required in columns (f) or (g). A need for separate indication of selling expenses is relatively rare. The second alternative is:

If you didn’t receive a Form 1099-B or 1099-S (or substitute statement) for a transaction, enter in column (d) the net proceeds. The net proceeds equal the gross proceeds minus any selling expenses (such as broker’s fees, commissions, and state and local transfer taxes).

Either way, the tax authority is looking for net proceeds. That is because they require submission by brokerages in that format for the kind of data matching you mentioned.

That brings us back to what I’ve already said in several ways. You can use the net proceeds approach and have the use of the Inventory Profit Margin report. Or you can use the dual-line, dual-account approach if that is more convenient for satisfying your local reporting and tax filing needs. But you cannot do both.