Hello,
I am involved in starting a new company and have been honored with the task of creating a system on excel that will track inventory automatically.
We have sales reps that sell our inventory daily, and I need to create some kind of system where their daily sales can be subtracted from the total inventory that we have.
This is how I have begun:
Firstly, I have 700, 300, 40 and 200 as starting values for items of product on row 2, cells B-E, and product titles on row 1, Cells B-E of one spreadsheet.
Secondly, I made another spreadsheet for sales, with each individual rep in column A, cells 2-4 (or however many), and the day's date on Row 1. Each day will have the sales made, respective to each sales rep, where I will sum them, so the last row, row 5 (or whatever row as we will be gaining and losing reps as time goes on) will be the day's sum.
Example: Sales
Date: M T W F Th Sat (spreadsheet will have actual date)
Larry 5 6 7 8 9 10
Moe 5 6 7 8 9 10
Curly 5 6 7 8 9 10
Total:15 18 21 24 27 30
Product: Apples Oranges Pumpkins Tomatoes
Starting: 700 300 40 200
Ending
Say, this week they sold apples.
My questions are:
1. Is this the proper way to set up the sheets? These spreadsheets will be added to as far as reps and products. Will I have to reconfigure the collation of the spreadsheets every time I add a product or rep, or remove a product or rep? What is the most efficient way to set up the sheets in light of a dynamic sales business?
2. How do I set it up so that whenever I put in a value for the rep's number of sales for the day, it subtracts from the total inventory, still leaving the starting amount but changing the ending amount daily for the product that is being sold.
Any help would be much appreciated! I've been watching videos on consolidating worksheets but I feel that something else is needed involving formulas.
THANK YOU FOR YOUR TIME!!!!
I am involved in starting a new company and have been honored with the task of creating a system on excel that will track inventory automatically.
We have sales reps that sell our inventory daily, and I need to create some kind of system where their daily sales can be subtracted from the total inventory that we have.
This is how I have begun:
Firstly, I have 700, 300, 40 and 200 as starting values for items of product on row 2, cells B-E, and product titles on row 1, Cells B-E of one spreadsheet.
Secondly, I made another spreadsheet for sales, with each individual rep in column A, cells 2-4 (or however many), and the day's date on Row 1. Each day will have the sales made, respective to each sales rep, where I will sum them, so the last row, row 5 (or whatever row as we will be gaining and losing reps as time goes on) will be the day's sum.
Example: Sales
Date: M T W F Th Sat (spreadsheet will have actual date)
Larry 5 6 7 8 9 10
Moe 5 6 7 8 9 10
Curly 5 6 7 8 9 10
Total:15 18 21 24 27 30
Product: Apples Oranges Pumpkins Tomatoes
Starting: 700 300 40 200
Ending
Say, this week they sold apples.
My questions are:
1. Is this the proper way to set up the sheets? These spreadsheets will be added to as far as reps and products. Will I have to reconfigure the collation of the spreadsheets every time I add a product or rep, or remove a product or rep? What is the most efficient way to set up the sheets in light of a dynamic sales business?
2. How do I set it up so that whenever I put in a value for the rep's number of sales for the day, it subtracts from the total inventory, still leaving the starting amount but changing the ending amount daily for the product that is being sold.
Any help would be much appreciated! I've been watching videos on consolidating worksheets but I feel that something else is needed involving formulas.
THANK YOU FOR YOUR TIME!!!!
