In this blog post, we will continue our practice problem with QuickBooks Online 2023 and focus on an e-commerce inventory scenario using the weighted average method in Excel. If you haven’t read the previous part of this series, I recommend checking it out first for better context.
To begin, let’s assume we are working in QuickBooks Online’s test company file using the accountant view. You can switch between the accountant view and the business view by going to the cog icon at the top and selecting the desired view. Additionally, duplicate some tabs for reports by right-clicking on the tab and selecting “Duplicate.” We’ll open the “Balance Sheet” report from the favorites list on the left side, review it, and then close it.
Next, let’s open the “Profit and Loss” report, adjust the date range to 04/01/2023 – 05/30/2023, and run the report. We’ll go back to the previous tab and note that we are dealing with an e-commerce situation where inventory is sold online through platforms like Shopify or Amazon. We’re using a periodic inventory tracking system with the assistance of an Excel sheet that employs the weighted average method.
Now, let’s make the current inventory data into a formal table. Select the entire data range and go to the “Insert” tab, then click on “Table.” Ensure that the header row is selected, and click “OK.” Adjust the column widths and headers as needed to make the table more presentable. You can rename the headers to include corresponding numbers if desired, especially if you plan to create pivot tables in the future.
With the table format established, we can proceed to the second month of operations, May. We’ll record the purchase transactions as needed. For example, let’s say we need to make a purchase on 05/01/2023. As you input the date, notice that a new cell is automatically added to the table, which helps streamline the process. Enter the product number (e.g., product number 1) and specify the quantity to be purchased (e.g., 6 units). The cost per unit is $23, so the total cost for this purchase is $138.
To calculate the cost per unit, you can divide the total cost by the quantity purchased. Copy the formula down to apply it to other rows as well. Note that this formula relates to the purchase side of the inventory and not the sales side.
Ensure that all the columns in the table are copied down correctly, including the totals and changes. The totals column should sum up the ending balances, while the change column should display the difference between the current and previous ending balances.
In this particular month, we’re only making a purchase for product number 1. Therefore, there won’t be any changes in the other product lines. Double-check that all the formulas and data are correctly copied down.
Now, based on the purchase we recorded, the ending inventory balance will change to 2,948 units from the previous balance of 2,810 units.
To reflect this purchase in QuickBooks Online, you can enter the transaction accordingly. Update the ending inventory balance to match the new value of 2,948 units.
However, for our financial records, we have been using a periodic inventory system and updating the inventory at the end of each month. So now it’s time to perform our physical count and reconcile our records with the actual inventory on hand.
We’re going to imagine that it’s the end of May, and we’re going to physically count the units we have in our inventory. Let’s say we count the following:
- Product 1: 50 units
- Product 2: 10 units
- Product 3: 15 units
Now we need to compare these counts with the ending balances in our worksheet. Let’s see if they match up:
- Product 1 ending balance: 48 units
- Product 2 ending balance: 5 units
- Product 3 ending balance: 10 units
Uh-oh! It seems like our counts don’t match our records exactly. This is where we’ll need to make adjustments to our inventory to reflect the actual counts.
To do this, we’ll calculate the difference between the physical count and the recorded ending balance for each product. Let’s see the adjustments we need to make:
- Product 1 adjustment: Physical count (50) – Recorded ending balance (48) = 2 units
- Product 2 adjustment: Physical count (10) – Recorded ending balance (5) = 5 units
- Product 3 adjustment: Physical count (15) – Recorded ending balance (10) = 5 units
Now we’ll update our records with these adjustments. Let’s make the necessary changes in our worksheet:
- Product 1: Add 2 units to the recorded ending balance
- Product 2: Add 5 units to the recorded ending balance
- Product 3: Add 5 units to the recorded ending balance
After making these adjustments, our updated ending balances should be:
- Product 1: 50 units (48 + 2)
- Product 2: 10 units (5 + 5)
- Product 3: 15 units (10 + 5)
Great! Now our records are reconciled with the physical count. We can proceed with generating our financial reports and analyzing the inventory situation for the month of May.
By using QuickBooks Online 2023 and implementing proper inventory management practices, such as periodic counts and adjustments, we can ensure that our financial records accurately reflect the inventory on hand. This is crucial for making informed business decisions and maintaining an accurate picture of our inventory’s value.
As a quick recap, we have been recording our inventory purchases but haven’t accounted for the sales side of our business in our financial statements. Using the periodic system, we rely on physical counts and the Shopify inventory tracking system to determine the units sold. Let’s dive into the adjustment process.
At the end of May (on 531), we conduct our physical count and discover that we have only four units of product number one remaining. This means we must have sold eight units since our last inventory update. As for product number two, unfortunately, we didn’t sell any units. However, for product number three, we have two units left, indicating that we sold five units.
To adjust our inventory records accordingly, we’ll use the weighted average method. We’ll calculate the average cost per unit by dividing the total value of the inventory by the number of units we had before the adjustment. Let’s see how this plays out:
For product number one:
- Physical count: 4 units
- Average cost per unit: $24 (ending inventory before the adjustment divided by units before the adjustment)
- Ending balance: $96 (4 units * $24)
For product number two:
- No adjustment needed as there were no sales
For product number three:
- Physical count: 2 units
- Average cost per unit: $66 (ending inventory before the adjustment divided by units before the adjustment)
- Ending balance: $132 (2 units * $66)
Now, let’s make the necessary journal entry to reflect these adjustments in our financial statements. We’ll debit the cost of goods sold account with the total cost of the units sold, and credit the inventory account to decrease its balance. In this case, we’ll record a journal entry of $3,399.57 for the cost of goods sold and a corresponding decrease in the inventory account.
After saving and closing the journal entry, let’s run the balance sheet to see the impact. We expect to see a decrease in the inventory balance to $18,494.43, which should match the adjusted ending balances we calculated.
Upon reviewing the balance sheet, we notice a discrepancy in the numbers initially. However, after rechecking, we confirm that the ending inventory balance is indeed $18,494.43, aligning with our calculations.
By periodically adjusting our financial statements using physical counts and the weighted average method, we ensure that our inventory records accurately reflect the units sold and the corresponding costs. This allows for more accurate financial reporting and decision-making.
First, let’s correct a minor error in the previous blog. I mistakenly categorized the cost of goods sold as the cost of labor. My apologies for the confusion. I’ve now rectified the error and ensured accurate labeling.
Now, let’s delve into the cost of goods sold. As mentioned earlier, we’re not using a perpetual system to track inventory in real-time. Instead, we’re relying on periodic adjustments to calculate the cost of goods sold. This means that our income will increase separately from the inventory tracking process.
To determine the cost of goods sold, we’ll use the weighted average method in this case. This allows us to calculate a more accurate cost of goods sold figure by considering the average cost per unit based on the total inventory value and the number of units before the adjustment.
By using this method in the periodic system, we can assess our financial statements on a monthly basis and make more informed decisions. Additionally, analyzing profitability becomes easier as we can evaluate profit margins for each product, taking into account both sales and cost of goods sold.
Now, let’s discuss the two primary flow assumptions commonly used in inventory management: first-in, first-out (FIFO) and weighted average. We’ve already explored the weighted average method, which calculates the average cost per unit by dividing the total value of the inventory by the units before the adjustment.
In contrast, FIFO assumes that the first units purchased are the first ones sold. This means that when determining the cost of goods sold, FIFO assigns the cost of the oldest units to the units sold. Consequently, the balance sheet reflects the inventory at the most recent cost, as the older, cheaper units are assumed to be sold first.
While the weighted average method considers all units equally, FIFO takes into account the specific order in which units were purchased. The choice between these methods depends on factors such as the market’s price trend and tax considerations.
To highlight the impact of these flow assumptions, let’s compare our previous FIFO-based analysis in 2024 with the weighted average method we used here in 2023. By running the reports for April and May of both years and focusing on the cost of goods sold, we can observe the differences.
Due to the inflationary trend in 2024, where the cost of inventory increased, FIFO would likely result in a higher cost of goods sold figure. This is because FIFO assumes that the oldest, cheaper units were sold first, leaving the newer, more expensive units in the inventory.
Conversely, the weighted average method provides a middle-ground approach, considering the average cost per unit across all inventory items.
Lastly, we briefly mention the last-in, first-out (LIFO) method, which assumes that the newest units purchased are the first ones sold. Although LIFO has tax advantages, it’s rarely used for practical purposes due to its flow assumption and its tendency to conflict with the physical flow of goods.
Understanding the impact of different flow assumptions on the cost of goods sold helps us make more informed decisions about inventory management and financial reporting. By carefully evaluating market conditions and specific business needs, we can select the most appropriate method for our operations.