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 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.
Record the shipping cost.
Record the shipping cost to quickly estimate if that sale is profitable.
However, the shipping cost is a business expense that will be recorded in the business expense spreadsheet as well.
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.
Bookkeeping for small business Excel
Alternatively, you could add some category columns to categorize the expenses entries. You track and record your daily business expenses on a master sheet and properly categorize them.
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. We added the tax lines for the expenses to total to each line if the expenses are categorized properly.
Both Business income and expense spreadsheet and inventory sales spreadsheet are free to download from our blog Nina’s Soap under Free Download. 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: 1-How do I keep track of business expenses and income 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)”.
