QBAR

What is the Cost of Goods Sold (COGS):

The QuickBooks creates two accounts whenever an inventory item is set up.

These accounts are:

• 12100 – Inventory Asset – Other Current Asset
• 50100 – Cost of Goods Sold (COGS) – Cost of Goods Sold

A purchase of an inventory item debits (+) the Inventory Asset account, and the sale of an inventory item credits (-) this account.

COGS, on the other hand, is generally debited (+) when an inventory item is sold.

A sales of an inventory item affects four accounts.

• Inventory Asset
• COGS
• AR
• Income

The amount on AR and Income account is Number of Items Sold x Average Cost of Item.

The amount on Inventory Asset and COGS is Number of Items Sold x Average Cost of Item.

Number of Items Sold is the quantity on the transactions.

Let’s talk about Average Cost of Item.

Let’s understand it with an example:

1. If you purchase the 2 items for $5.00, then the current average cost is (5 + 5) / 2 = $5.00. Right now the Total asset value is $10.
2. When you again purchase the same item for $3.00, then the current average cost will be (Total Asset Value + New purchase cost) / Total quantity. So it is ($10 + $3) /  3 = $4.33
3. Now suppose you sell  one of the items then the average cost remains $4.33, but the Total Asset values is $4.33 * 2 = $8.67.
4. You repurchase the same item for $5.50 then the current average cost will be(Total Asset Value + New purchase cost) / Total quantity. So it is ($8.67 + $5.50) /  3 = $4.72

So, as explained in this example, it is quite evident that the average cost of an item keeps changing.  

It is also apparent that an inventory item has two types of average costs.

• Current average cost
• Historical average cost

The current average cost is good enough to use if the report is showing the COGS amount for the current date.

The historical average cost is required to show the past values of COGS amount.

The QuickBooks standard reports use current or historical average cost automatically depending on the dates of the report.

The QuickBooks Advanced Reporting also have these average costs.

Let’s see the place to find them and how to use them.

Current Average Cost of Item in QuickBooks Advanced Reporting (QBAR):

 Historical Average Cost of Item in QuickBooks Advanced Reporting (QBAR):

The ItemHistory table stores the historical average cost of all inventory items.

You may use these expressions to find the latest or the oldest average cost of an item:

Latest Average Cost : =FirstSortedValue([ItemHistory.Average Cost], -[ItemHistory.Sequence Number])

Oldest Average Cost: =FirstSortedValue([ItemHistory.Average Cost], [ItemHistory.Sequence Number])

Here is a challenge for you: 

Write an expression to find the COGS amount using the historical average cost.

Just imagine this:

You got your sales report built, and it got hundreds of rows and tens of columns. Everything looks great, and you run the report for a specific date range and want to find a sales amount of less than $1000 and quantity more than 20. You are scrolling up and down and trying to figure that out, but there are so many rows that you lose track. Your eyes are getting stressed scanning the report, and you wish if you could just color code or add some kind of visual cues to those numbers, so they are easy to track.

If you have wanted a solution to this problem, then you are in luck because this article is a step by step guide to add visual cues or color code the columns.

We are going to use Fiscal Year date sales report to show the steps:

You can download this report for free if you subscribe to our mailing list.

SUBSCRIBE

Ok, let’s add the visual cues.

Open the “Properties” dialog from the right-click menu of the chart and choose the Visual Cues tab.

.We can choose any expression and add the rules for the visual cues as shown in the image below.

 

The QuickBooks Fiscal YTD Sales report looks like this.

Now here is a task for you:

Add visual cues to the 3rd expression, so the background turns to yellow and text to black only if the amount is in between 2000 and 3000.

If you want this report, then please subscribe to our mailing list.
SUBSCRIBE