The video shows how to record raw material purchases, how to manage business inventory in Excel, how to keep track of business sales in Excel, how to reconcile bank statements, and how to reconcile business credit card statements.
We do our S Corp bookkeeping in Excel spreadsheet and manage our business inventory in Excel.
We track business sales in our inventory and sales worksheet to keep track of all the business incomes together and our inventory level at the same time.
We record business expenses like shipping expenses for example in our business bookkeeping spreadsheet to track all the business expenses together, reconcile the business bank account and categorize the business expenses in relation to the type of business tax return they will be reported on.
The processes below are the steps to track business income and expenses in Excel:
Step 1: How do I keep track of business sales in Excel?
Record gross revenue.
Record the product sold, the amount it sold for, as well as any sales tax paid.
The sales tax if the product is sold on a marketplace will be paid to the buyer’s state by the marketplace. However, I record the sales tax in our inventory and sales spreadsheet for record purposes and it is not included in the gross receipts. The gross receipt is the gross revenue to report on business tax return.
If your state collects sales tax, you could tag these sales to “out of your state buyers” in a way to calculate the sales tax collected on your website from “in your state buyers”. You will pay your state sales tax on taxable products you sold from your website to buyers in your state.
Sales to buyers from your state that purchased your products on a marketplace can be tagged as out of state sales as well, as the marketplace will pay the tax to your state.
Record the cost of the goods sold.
You record the direct cost associated with the product sold. You locate the right products sold in your inventory list and record the cost of the products associated with that sale. If you have multiple inventory entries for one product with different cost of goods, you need to record the cost of goods entered first in inventory that has not yet been accounted for. By default, you take products out of inventory using the FIFO (First In First Out) method.
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.
If you resold a raw material, you just record it in your sales sheet for the price paid by the buyer and the cost of goods associated with the quantity sold. This entry will automatically reduce the remaining raw material on hand in your inventory.
How to record cost of raw materials purchased?
You record the cost of the raw material purchased in your raw material inventory worksheet.
Then you record the expense in your one entry journal in the expense detail tab. You categorize the raw material purchase as inventory, for your bookkeeping spreadsheet summary tabs can pull it to the right category. You can also record the cost of raw material in the inventory column depending on how you design your bookkeeping spreadsheet.
Raw materials purchases are inventory, and you only deduct the cost associated with the goods sold.
However, you could track all your expenses in one worksheet but categorize them in a way to pull your business expenses reports with expenses properly categorized including deductible expenses, inventory, and depreciable assets.
Is shipping cost included in inventory cost?
The shipping cost of raw materials is included in the raw material inventory cost.
If you purchased more than one raw material product on one receipt, any discount and shipping applied to the receipt should be allocated to each product on the receipt. You can use a weighted average method to allocate the shipping cost and any discount to the different products on the receipt.
How do I manage material inventory in Excel?
When you record the cost of goods sold associated with each sale, your inventory sheet will update the value of the remaining quantity of the products left.
The value of year-end inventory = beginning year inventory value + any purchases during the year or cost of production made during the year – any raw material taken out of inventory to make the finished products – cost of goods sold.
When you enter raw material purchases in your inventory, you need to take out of inventory the quantity and cost of the ones used to make the finished products. Then you enter the finished products in inventory during the year at the cost of the raw materials taken out of inventory. That process adjusts your inventory level in terms of its value and quantity.
If you do not take raw materials out of inventory when you use them to make products and you enter the finished products in inventory, the cost associated with the finished products will be duplicated in the inventory when your actual value of the inventory on hand is lower.
On the other hand, when you take raw materials out of inventory to make finished products and you do not enter the finished products in your inventory tracker, your inventory value will be low when its actual value is higher. There will be a discrepancy either way between your raw materials end inventory and your finished products end inventory.
How do I manage raw material inventory in Excel
When you manage raw material inventory in Excel, you record your raw material purchases, your out of inventory, your finished products. When you make sales and record your sales in your inventory tracker, you will have an inventory that is up to date for your business.
Knowing the cost of the products sold allows you to update your inventory value and be able to file Form 1125-A Cost of goods Sold when filing business taxes.
How do you record shipping charges to customers?
You can record the shipping cost in your sales sheet to quickly estimate if that sale is profitable.
However, the shipping cost associated to shipping goods sold to buyers, is a business expense that is recorded in the business expense spreadsheet as well.
It is a deductible business expense to report on business tax return Form 1120S S corporation tax return Line 19 Other Deduction, Form 1065 Partnership tax return Line 20 Other Deductions, or Schedule C (Form 1040) Part V Other Expenses Line 48.
When using one entry journal, categorize your shipping expenses well for the summary report to include it in the right expense category.
Step 2: How do I keep track of small business expenses in Excel?
The business expenses spreadsheet allows you to track all business expenses paid for, using your business credit card or bank check to capture all the business expenses in one spot.
You record daily business expenses on a sheet and categorize them. Then you pull reports from that sheet to create monthly reports or total by tax return line.
If you recorded your business credit card transactions and paid for the shipping using your business credit card, chances are you recorded the shipping expenses in your spreadsheet. Just make sure you do not duplicate expenses. Dating your expenses in your worksheet can be helpful.
When you record your business expenses, you should categorize them and use the categories to summarize these entries on another sheet in the business expense workbook.
This makes it easy to have your business expenses sorted out for filling business tax return.
You could also pull the monthly report on another sheet as well.
The bookkeeping worksheet can be used by LLC taxed as an S corporation.
The inventory sales spreadsheet can be used by sole proprietors, LLC that file Form 1065, and LLC taxed as S Corp that file Form 1120S.
Small business income expenses tracker spreadsheet
Another way to design your business expense spreadsheet is to have a column for each line of the type of business tax return you file for your business.
To enter a business expense, you enter the date, and you enter cost in a column for all expenses and you enter the charge in the right the tax line column that expense will be claimed on. This allows you to have the total for each tax line ready for your business tax return preparation.
This approach is simple and can be used at the start of the business when you have a few expenses. I designed my earlier small business income expenses tracker spreadsheet following this model during the first few years of our business. I improved upon it as our business grew.
How do reconcile bank accounts in Excel?
Every month, compare your business bank account transactions to your bank account reconciliation sheet.
If there are transactions you don’t have in your spreadsheet and you acknowledged them, record them.
You can record on your spreadsheet, your business income, or sales deposits in your bank account by payment gateways and public marketplaces you sell at.
You can design an income summary tab to pull business income by month and by source to study which platforms your business generates more income from. You compare these deposits to the payers’ financial records you pull on a monthly basis from the payers’ websites.
When all the expenses and income directly reported to your business bank account are recorded in your journal, your bank account balance should match what your bookkeeping spreadsheet reflects.
Expenses that were directly charged to your bank account, record them on your expense detail tab and categorize them properly.
The expense detail sheet captures all the business expenses whether they are paid by check, debit card or credit card.
Try to do your business bank account reconciliation every month.
How do I reconcile my business credit card statement?
If your business has a credit card you use to make business purchases, you open the credit card statement and compare the transactions to your expense detail entries.
As for the transactions that are not yet recorded, see if you acknowledge them. If you do, record them on your bookkeeping worksheet in the expense detail tab. Categorize each expense so that your summary reports sheets can include them in the right categories for you.
Pay the balance due on the business credit card if your bank account balance can afford it. Record the payment on your bank reconciliation sheet.
Record the credit card payment in the expense detail tab but enter the payment in its column which is only for credit card payments. The credit card expenses are already recorded on the expense detail tab. When you record the credit card payment, do not enter it in the same column as the other expenses to avoid duplicating expenses.
How to record marketplace fee?
Some marketplaces like eBay and Etsy can deduct fees from your sales, or charge your bank account, or your business credit card.
Until you check your marketplace financial reports you may not be aware that they charge your bank account or your credit card.
If your credit card is charged for marketplace fee, record that expense in your expense detail sheet and categorize it.
If you see marketplace fee on your bank statement, record it on your bank reconciliation sheet and on the expense detail sheet and categorize the expense on the expense detail sheet.
The fees the marketplace deducted from your sales, you pull your financial reports from the marketplace and transfer the data to your marketplace fee worksheet. From there, you record the fees the marketplace charged you not yet recorded in your expense detail sheet, and record them in the expense detail sheet and categorize them.
Bookkeeping for small business Excel
You could add some expense category columns to categorize the expenses entries. You track and record your daily business expenses on a master sheet and properly categorize them. You enter each expense amount in its category column. You enter the description of the expense on a row and enter the amount in the right expense column corresponding to that row.
You can create summary sheets to pull data from the master sheet.
On the summary sheets you could track your business monthly income and expenses. You could also track your business expenses by your business tax return line.
Our bookkeeping spreadsheet is designed to track monthly reports and tax return line items. The expense detail sheet tracks all the expenses in one column where the summary sheets pull their reports from. There are expense columns on the expense detail tab to enter each amount in the right expense column as well to track each expense category under its own category column. We added the tax lines for the expenses to total to each line if the expenses are categorized properly. The bookkeeping worksheet is designed to track expenses for S corporation tax return. Therefore, it is best suited for Form 1120S filers.
Both Business income and expense spreadsheet and inventory sales spreadsheet are free to download from our blog Nina’s Soap under Free Downloads. They are titled as below:
Free Bookkeeping Workbook for S Corporation F23D3
Free Inventory and Sales Worksheet F23D2
Free Inventory and Sales Worksheet F23D1
Double check your numbers as you enter them in your workbook and check the summary sheet to make sure no formula is broken.
Conclusion: 2- How do I keep accounting records for a small business in Excel?
We used sales income and shipping expenses to show how you can use Excel to keep track of your small business income and expenses and manage your material inventory.
Resources
Related article categories
Related articles
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)”.
