Daily Inventory Sheet: Count and Valuation
![Daily Inventory Sheet: Count and Valuation]()
![daily inventory sheet count and valuation 1]()
![daily inventory sheet count and valuation 2]()
![daily inventory sheet count and valuation 3]()
Many small businesses simply don't have any idea what the value of their inventory is at any one time. This is especially true for those that have a lot of different inventory SKUs to keep track of. It doesn't mean you don't have any inventory or you are going to get in trouble with an audit necessarily. Often creative accounting is used to cover up lack of insight.
This simple Excel sheet is a good way to be more accurate. It lets the user enter current beginning balances of up to 40 SKUs and then enter the units used/sold per day as well as the units arriving per day. The Accounting department or general manager would likely oversee this and have control/own the process.
The sheet works as follows:
1. enter starting balances
2. a tab lets the user enter the daily activity for usage
3. another tab lets the user enter daily activity for arrivals
4. enter average cost of goods sold per SKU
5. a final tab shows the ending value of inventory as well as the ending count of inventory units per SKU
The user can define lower limits for when a given row should turn red/yellow/green. Red being when an item is getting close to its safety stock point and yellow being ok while green is plenty. The actual number that results in an item being alerted is defined by the user and instructions are clear in the file on how to do this (there are just two boxes to enter numbers into.
The file automatically knows the day and will highlight and underline the row of the current day through time.
To use this for multiple years, simply make a new file name for each new year, take the ending balance of the previous year and plug it into the new year's file. You will have a file that shows daily inventory levels by year for as many years as you need.
Note, you easily expand the file to work for more than 40 SKUs by simply dragging all the formulas over for as many SKUs as you need. There is no real upper limit as long as your machine can run the Excel file.
Many small businesses simply don't have any idea what the value of their inventory is at any one time. This is especially true for those that have a lot of different inventory SKUs to keep track of. It doesn't mean you don't have any inventory or you are going to get in trouble with an audit necessarily. Often creative accounting is used to cover up lack of insight.
This simple Excel sheet is a good way to be more accurate. It lets the user enter current beginning balances of up to 40 SKUs and then enter the units used/sold per day as well as the units arriving per day. The Accounting department or general manager would likely oversee this and have control/own the process.
The sheet works as follows:
1. enter starting balances
2. a tab lets the user enter the daily activity for usage
3. another tab lets the user enter daily activity for arrivals
4. enter average cost of goods sold per SKU
5. a final tab shows the ending value of inventory as well as the ending count of inventory units per SKU
The user can define lower limits for when a given row should turn red/yellow/green. Red being when an item is getting close to its safety stock point and yellow being ok while green is plenty. The actual number that results in an item being alerted is defined by the user and instructions are clear in the file on how to do this (there are just two boxes to enter numbers into.
The file automatically knows the day and will highlight and underline the row of the current day through time.
To use this for multiple years, simply make a new file name for each new year, take the ending balance of the previous year and plug it into the new year's file. You will have a file that shows daily inventory levels by year for as many years as you need.
Note, you easily expand the file to work for more than 40 SKUs by simply dragging all the formulas over for as many SKUs as you need. There is no real upper limit as long as your machine can run the Excel file.
