How do you record purchase of raw materials?
When you purchase a product to be used to make another product, it is a raw material for your business.
You need to enter it in your raw material inventory spreadsheet.
The cost and quantity of the raw material purchased will be entered in inventory as purchases during the year.
You take the cost including amount, any sales tax charged, and shipping and divide by the quantity or unit you plan to use to measure the quantity taken out of inventory for production. You will calculate the unit cost that way to later help you determine the cost associated with the quantity used to calculate the cost of finished products.
If it is a new raw material, you must create a row for it in your spreadsheet and make the entry.
If you have a summary tab that pulls your daily raw material entries to the summary sheet where it calculates beginning of inventory, purchases during the year, out of inventory, and year-end inventory for each product, you should create a row and enter the new raw material on the summary tab as well.
Your daily entry sheet will have rows for each time you purchase a raw material and the cost associated with each purchase to help you pull use the FIFO (First In First Out) inventory method when estimating the cost of raw material used during the year.
How to record raw materials used in production?
When you take raw materials to use in your production, you make some entries in your spreadsheet.
You take the quantity of these raw materials taken out for production, out of inventory. If you have an out of inventory sheet, you make entries of these raw materials in the Out of inventory tab and you can use negative numbers on the out of inventory tab.
You make an entry in the finished product sheet for the finished products made with the raw materials at the cost of the raw materials used to recapture the cost of raw materials taken out of inventory.
If it takes a while to make the finished product, you could make an entry to raw material sheet and its summary sheet for in production material or semi-finished products. When the finished products are ready, the semi-finished products should be deducted from raw material through a negative entry made to the out of inventory sheet and reentered in the finished product sheet as finished product. This process is to ensure the cost of raw material is accounted for in the finished product and is not duplicated.
Taking raw material out of inventory ensures the cost associated with it is not later duplicated when finished products are entered in inventory.
Finished product entry in inventory ensures the raw materials taken out of inventory and used, have been accounted for or recaptured.
How do you split shipping cost per item?
Total the cost of all raw materials purchased and covered by one shipping cost.
Divide the shipping cost by the total cost of the raw materials to find the ratio of the shipping cost.
Multiply each raw material cost by that ratio to calculate the cost for the raw material and its portion of the shipping cost.
That is the cost to enter in your raw material inventory spreadsheet for that raw material.
Repeat the process for each raw material purchased.
When done properly, the total cost of these raw materials should total to the amount paid or invoiced by the vendor including the shipping.
How to record sales in excel?
Record the name of the product sold in the sales sheet.
Type the name exactly the way you record it in your inventory spreadsheet.
Record the quantity sold.
Record the price paid by the buyer.
Record the tax paid by the buyer.
Keep in mind that for sales on marketplaces, the marketplace will remit the sales tax to the state on your behalf.
Even if you record the tax paid by the buyer, you need to keep in mind that it should not be included in your gross sales receipt.
Sales tax collected on your website from buyers from your own states, you will remit that to your state. Still, you do not include these sales tax in your gross receipt.
You can design your inventory spreadsheet to pull total summary in a way it is easy to retrieve financial data needed to file your business tax returns with IRS and your state.
Record the cost of the product sold or cost of goods sold (COGS).
This process allows your inventory spreadsheet to automatically reduce the product on hand by the quantity sold and decrease the remaining cost of that product sold by the cost associated with the number that is sold.
Your inventory spreadsheet will adjust for you to have year end inventory up to date.
Review the summary sheet to make sure you withdraw from inventory the right cost of goods sold. Keep in mind to use FIFO (First in First Out) method to allocate the cost of goods sold using the quantity and cost associated with the first entries of the product in inventory.
Conclusion: How do you use Excel to track inventory?
The muted video with ambient sound shows few examples including:
- How do I manage raw material inventory in Excel
- How do you split shipping cost per item
- How to record finished goods inventory
- How to calculate cost of goods manufactured
- How to calculate COGS in Excel.
Resources
Related article categories
Disclaimer:
“I am not an attorney to practice law. I am not allowed to draft legal documents, give advice on legal matters including immigration, or charge a fee for these activities.” Our contents are informative and based on our knowledge and experience. Use them at your own discretion.
Ads Disclaimer:
Ads are displayed in our contents. These ads do not influence the content we create and publish. We do not endorse them either.
Affiliate links Disclaimer:
Our Videos and articles contain affiliate links. When you click on them and make a purchase, we will receive a commission. We thank you very much for your support!
Our Vision
Nina’s Soap was created to help you live a healthy lifestyle within your budget while increasing your net worth.
Liberman Consulting L.L.C.’s contents are about different ways to save money, different ways to make money to reach your financial independence where you don’t worry about money or depend on your job to provide for your family.
Our Objectives
At Nina’s Soap (Liberman Consulting L.L.C.), you will find information about living a quality life within budget and increasing your net worth. Topics covered include personal finance, investment, business management, cooking from scratch, and growing some vegetables and fruit to save money and eat healthy.
“https://ninasoap.com/membership-join/ (We welcome you to join us. Email List)
care@ninasoap.com (contact info for any questions you may have)
https://ninasoap.com (blog), https://liberdownload.com (digital products), https://ninassoap.com (natural products), https://liberlabel.com (custom apparel), https://liberoutlet.com (resale store), https://www.youtube.com/c/LibermanConsultingLLC (YouTube Channel), https://www.pinterest.com/ninassoap/ (Pinterest), https://ninasoap.com/affiliate-products/ (Our resource page)”.
