Cost of Goods Sold Calculator

  • Version
  • 15597 Downloads
  • 10.7 Mb File Size
  • April 14, 2019 Updated
  • 6 Number of comments
  • Yes Free
  • 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.

CoGS Calculator Spreadsheet

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 :

  1. Fill your month period
  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.

CoGS Calculator - Inventory Item Summary

You can read more information in particular worksheet in this CoGS calculator template.

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

How useful was this template?

Click on a star to rate it!

Average rating / 5. Vote count:

As you found this template useful...

Follow us on social media!

We are sorry 🙁

Help us improve!

Invoice Generator

11.5 Mb / 4259 downloads
Retail Business Accounting Templates

2.8 Mb / 322 downloads
Service Business Accounting Templates

111 KB / 3502 downloads
Sales Task List and Pipeline Manager

10.3 Mb / 2705 downloads
Employee Time Sheet Manager Excel Template

31.3 Mb / 32989 downloads
Employee Schedule Template

42 KB / 195 downloads
View Comments