# Cost of Goods Sold Calculator

• Version
• 10.7 Mb File Size
• January 3, 2022 Updated
• Rating
table of content

If you are running a company or if you are working as an accounting, you will be familiar with the meaning of Cost of Goods Sold or usually known as CoGS. There are two company types (in accounting terms) that use CoGS as part of their Profit and Loss calculation, merchandising and manufacturing companies.

In merchandising companies, CoGS is similar with purchasing price from those companies to their suppliers. But, in manufacturing companies, COGS is the costs that go into creating the products that a company sells; therefore, the only costs included in the measure are those that are directly tied to the production of the products.

For example, the COGS for an mobile phone maker would include the material costs for the parts that go into making the mobile phone along with the labor costs used to put the mobile phone together. The cost of sending the mobile phones to buyers and the cost of the labor used to sell it would be excluded. That is the example of the basic interpretation of COGS. The accounts included in the COGS calculation will differ from one type of business to another.

In conclusion, it is easier to calculate CoGS in merchandising companies. CoGS template you can download below is based on that merchandising companies model.

Based on accounting rules and inventory valuation method, COGS can be calculated using one of three cost flows :

1. First In, First Out (FIFO), items purchased first are sold first.
2. Last In, First Out (LIFO), items purchased last are sold first.
3. Average, items sold can be taken from any stocks regardless of time they are purchased. It is the most easiest and popular method to calculate CoGS.

Note that these are cost flow assumptions. This means that the order in which costs are removed from inventory can be different from the order in which the goods are physically removed from inventory. There are several factors that can affect the choice of cost flow, depend on your company policy.

Brief explanation below will give you consideration on choosing the better cost flow method for your company, where it states some consideration as follows :

FIFO gives us a better indication of the value of ending inventory (on the balance sheet), but it also increases net income because inventory that might be several years old is used to value the cost of goods sold. Increasing net income sounds good, but remember that it also has the potential to increase the amount of taxes that a company must pay.

LIFO isn’t a good indicator of ending inventory value because the left over inventory might be extremely old and, perhaps, obsolete. This results in a valuation that is much lower than today’s prices. LIFO results in lower net income because cost of goods sold is higher.

Average Cost produces results that fall somewhere between FIFO and LIFO

This COGS calculator is the tool I used to ease my accounting people to get the COGS quicker. If you are running the retail business with fast moving item, you can use this calculator. If you are working in manufacturing companies, you can use this to calculate cost of raw materials.

I put some samples inside to explain to ease you understanding the principle.

Steps to use this calculator are as follows :

2. Type in your beginning inventory unit and price
3. Fill number of sold unit
4. Start type in your net purchases including unit and price information within that period in particular table

Ending inventory will be calculated automatically with following equation :

Ending Inventory = Beginning Inventory + Net Purchases – COGS

There is only one worksheet to calculate one item in this free CoGS template. You can upgrade it to the Pro version to calculate until up to 50 item within one spreadsheet and having them calculated monthly (one worksheet accommodate 12 months net purchases) with any of your FIFO, LIFO and Average choices. And there are sales price columns you can fill to see margins between sales price and CoGS.

## Features

• Accommodate up up to 50 different items
• Type purchase and sales transaction date and let the formulas calculate CoGS based on FIFO, Average and LIFO method in monthly basis automatically
• Setup Report based on FIFO, Average and LIFO method with Sales Transaction and Margin included

Click on a star to rate it!

Average rating 4.9 / 5. Vote count: 70

No votes so far! Be the first to rate this template.

We are sorry 🙁

Help us improve!

How we can improve this template?

Hello !

Please be advised this calculations are not correct. Search an online example of FIFO (either periodic or perpetual) and plug in the numbers in your format and you will find difference.

Its a wonderful sheet and nice idea for formula.

Thanks

[ODD, THEY GOT CUT AGAIN. HERE’S A SMALLER VERSION OF WHAT IS CUT OFF]

Location: I11 (and copy down through I46)
Original: =IF(F11F11,F11,ABS(J11)))
Modified: =IF(-J11>=F11,F11,IF(J11<0,-J11,0))

[SECOND TRY AS SOME LINES GOT CUT]

I believe I may have found some errors in this excellent spreadsheet. I suspect these formulas may work better:

Location: F6
Original: =C8*SUMPRODUCT(E11:E46,F11:F46)/SUM(F11:F46)
Modified: =C7*SUMPRODUCT(E11:E46,F11:F46)/SUM(F11:F46)

Location: G6 (and copy down through G8)
Original: =F6/C8
Modified: =F6/\$C\$7

Location: I11 (and copy down through I46)
Original: =IF(F11F11,F11,ABS(J11)))
Modified: =IF(-J11>=F11,F11,IF(J11<0,-J11,0))

Location: H5
New: "Ending Inv"

Location: H6
New: =SUM(G\$11:G\$46)-F6

Location: H7
New: =SUM(G\$11:G\$46)-F7

Location: H8
New: =SUM(G\$11:G\$46)-F8

I believe I may have found some errors in this excellent spreadsheet. I suspect these formulas may work better:

Location: F6
Original: =C8*SUMPRODUCT(E11:E46,F11:F46)/SUM(F11:F46)
Modified: =C7*SUMPRODUCT(E11:E46,F11:F46)/SUM(F11:F46)

Location: G6 (and copy down through G8)
Original: =F6/C8
Modified: =F6/\$C\$7

Location: I11 (and copy down through I46)
Original: =IF(F11F11,F11,ABS(J11)))
Modified: =IF(-J11>=F11,F11,IF(J11<0,-J11,0))

Location: H5
New: "Ending Inv"

Location: H6
New: =SUM(G\$11:G\$46)-F6

Location: H7
New: =SUM(G\$11:G\$46)-F7

Location: H8
New: =SUM(G\$11:G\$46)-F8

Hi Guys,

It’s ok, but please test. If you buy and then sold out (inventory=0), and then you buy new one, so your formula will wrong if we want to calculate the average price of them.

I am running the following transaction in the worksheet-. No initial inventory
However the calculation is missing 6,000 units sold can you please let me know what I am doing wrong ?
4-Apr \$10 Qty 45,000
4-Apr 10 5,000
12-Apr 9.95 3,000
19-Apr 9.50 10
25-Apr 9.95 47,000
25-Apr 9.90 3,000

Sold Unit 51,000