Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
I have a spreadsheet with current inventory on hand and the sales for each
month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
JLewis wrote:
I have a spreadsheet with current inventory on hand and the sales for each month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? Look at this: http://tinyurl.com/bndrvj If you are having trouble making it work, put your worksheet on www.savefile.com and I will see if I can help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
Hi,
Suppose last years number are in A1:A12 (Jan-Dec) and your current supply is in B1 =12*B1/SUM(A1:A12) -- If this helps, please click the Yes button Cheers, Shane Devenshire "JLewis" wrote: I have a spreadsheet with current inventory on hand and the sales for each month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
This doesn't work for my data. I tried this approach already. It might work
if each month's sales were fairly equal. The variance between months is too great. For my example, I got 9.23 when I know the result should be 4.5 or at its basic using full cells, 5. Thanks for the help though. "Shane Devenshire" wrote: Hi, Suppose last years number are in A1:A12 (Jan-Dec) and your current supply is in B1 =12*B1/SUM(A1:A12) -- If this helps, please click the Yes button Cheers, Shane Devenshire "JLewis" wrote: I have a spreadsheet with current inventory on hand and the sales for each month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
"JLewis" wrote:
I have a spreadsheet with current inventory on hand and the sales for each month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? Shane Devenshire wrote: Hi, Suppose last years number are in A1:A12 (Jan-Dec) and your current supply is in B1 =12*B1/SUM(A1:A12) This will provide very inconsistent results. If you assume the known data (5, 10, 15, 40, 60 for March through July) and fill in 5 (minimum sales) for the unknowns, your answer is 7.5 months supply. If you fill in 60 (maximum sales) for the unknowns, your answer is about 2.45 months supply. Yet clearly the "correct" answer, according to the OP, is 4.5 months supply. That is why I posted the link to a more complex solution (originally provided by Harlan Grove) that will give you 4.5 months supply regardless of the other unknown data. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
It looks like this is on the same level as what I want to do, but I can't get
the formula to work. It gives me an error message saying "A value used in the formula is of the wrong data type". I don't understand as all of the data used in the formula is of the "Number" type. "Glenn" wrote: JLewis wrote: I have a spreadsheet with current inventory on hand and the sales for each month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? Look at this: http://tinyurl.com/bndrvj If you are having trouble making it work, put your worksheet on www.savefile.com and I will see if I can help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
OK, but I can't help without more information. Either post your worksheet on
www.savefile.com or describe your exact data and formulas here so I can recreate it on my end. JLewis wrote: It looks like this is on the same level as what I want to do, but I can't get the formula to work. It gives me an error message saying "A value used in the formula is of the wrong data type". I don't understand as all of the data used in the formula is of the "Number" type. "Glenn" wrote: JLewis wrote: I have a spreadsheet with current inventory on hand and the sales for each month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? Look at this: http://tinyurl.com/bndrvj If you are having trouble making it work, put your worksheet on www.savefile.com and I will see if I can help. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
File link he http://www.savefile.com/files/2013754
Thank you very much for your help!!! Jennifer "Glenn" wrote: OK, but I can't help without more information. Either post your worksheet on www.savefile.com or describe your exact data and formulas here so I can recreate it on my end. JLewis wrote: It looks like this is on the same level as what I want to do, but I can't get the formula to work. It gives me an error message saying "A value used in the formula is of the wrong data type". I don't understand as all of the data used in the formula is of the "Number" type. "Glenn" wrote: JLewis wrote: I have a spreadsheet with current inventory on hand and the sales for each month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? Look at this: http://tinyurl.com/bndrvj If you are having trouble making it work, put your worksheet on www.savefile.com and I will see if I can help. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
OK, first it looks like you missed the part about array formulas needing to be
committed by hitting CTRL+SHIFT+ENTER. Also, this is a two-cell solution. The formula you entered could/should be in a hidden cell, and then the second formula would be displayed. Try it this way (copy directly from this post if possible): R6 = SUM(($F$2MMULT(H5:Q5,(COLUMN(H5:Q5)=TRANSPOSE(CO LUMN(H5:Q5)))+0))+0) *** This is the array formula *** D6 = R6+(($F$2-SUM(H5:INDIRECT("R"&ROW()-1&"C"&R6+COLUMN()+3,FALSE)))/ (INDIRECT("R"&ROW()-1&"C"&R6+COLUMN()+4,FALSE))) Copy to R7 and D7 for the "High Value" results. JLewis wrote: File link he http://www.savefile.com/files/2013754 Thank you very much for your help!!! Jennifer "Glenn" wrote: OK, but I can't help without more information. Either post your worksheet on www.savefile.com or describe your exact data and formulas here so I can recreate it on my end. JLewis wrote: It looks like this is on the same level as what I want to do, but I can't get the formula to work. It gives me an error message saying "A value used in the formula is of the wrong data type". I don't understand as all of the data used in the formula is of the "Number" type. "Glenn" wrote: JLewis wrote: I have a spreadsheet with current inventory on hand and the sales for each month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? Look at this: http://tinyurl.com/bndrvj If you are having trouble making it work, put your worksheet on www.savefile.com and I will see if I can help. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
Hi,
If that won't work for you, you need to tell us what the thought process should be for making the estimate. For example, are you assuming that the monthly sales are the same each year so you can use the prior data to estimate the future figure. Or are you assuming the the future figures will be proportional to a prior years results? If you don't have prior data how can you predict the future? In other words if you know Jan - Jun what tells you what July's needs might be? -- If this helps, please click the Yes button Cheers, Shane Devenshire "JLewis" wrote: This doesn't work for my data. I tried this approach already. It might work if each month's sales were fairly equal. The variance between months is too great. For my example, I got 9.23 when I know the result should be 4.5 or at its basic using full cells, 5. Thanks for the help though. "Shane Devenshire" wrote: Hi, Suppose last years number are in A1:A12 (Jan-Dec) and your current supply is in B1 =12*B1/SUM(A1:A12) -- If this helps, please click the Yes button Cheers, Shane Devenshire "JLewis" wrote: I have a spreadsheet with current inventory on hand and the sales for each month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
Awesome! It works. I see now what I was and wasn't doing. Thank you, Thank
you! I really appreciate your help on this. This will help me in other reports as well. "Glenn" wrote: OK, first it looks like you missed the part about array formulas needing to be committed by hitting CTRL+SHIFT+ENTER. Also, this is a two-cell solution. The formula you entered could/should be in a hidden cell, and then the second formula would be displayed. Try it this way (copy directly from this post if possible): R6 = SUM(($F$2MMULT(H5:Q5,(COLUMN(H5:Q5)=TRANSPOSE(CO LUMN(H5:Q5)))+0))+0) *** This is the array formula *** D6 = R6+(($F$2-SUM(H5:INDIRECT("R"&ROW()-1&"C"&R6+COLUMN()+3,FALSE)))/ (INDIRECT("R"&ROW()-1&"C"&R6+COLUMN()+4,FALSE))) Copy to R7 and D7 for the "High Value" results. JLewis wrote: File link he http://www.savefile.com/files/2013754 Thank you very much for your help!!! Jennifer "Glenn" wrote: OK, but I can't help without more information. Either post your worksheet on www.savefile.com or describe your exact data and formulas here so I can recreate it on my end. JLewis wrote: It looks like this is on the same level as what I want to do, but I can't get the formula to work. It gives me an error message saying "A value used in the formula is of the wrong data type". I don't understand as all of the data used in the formula is of the "Number" type. "Glenn" wrote: JLewis wrote: I have a spreadsheet with current inventory on hand and the sales for each month from the last year. I want to figure how many months our inventory on hand will last based on the last years sales. For instance, WidgetA has 100 on hand and Mar, Apr, May, Jun & Jul sales were 5,10,15,40,60, respectively. I know that we have 4.5 months supply, but I have to manually figure this calculation. I have more than 400 items. This takes a long time to figure, is there any way to automate the figuring of the months supply? Look at this: http://tinyurl.com/bndrvj If you are having trouble making it work, put your worksheet on www.savefile.com and I will see if I can help. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
JLewis wrote:
Awesome! It works. I see now what I was and wasn't doing. Thank you, Thank you! I really appreciate your help on this. This will help me in other reports as well. Glad I could help. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
Glenn,
After checking the data calculations, I noticed that partial months (less than one month) calculations do not work quite right. I sorta fixed this by including an "IF" function in the 2nd half of the formula that says if the calculation is less than zero to add a 1. This works. For instance, I had 1 item that the calculation was -.02. I had 81 in stock and 83 sales in the first month. It should have been .98, so adding 1 was the solution. If there was no inventory the calculation was -1. Adding one solved this problem as well. Again, this is a great formula, it just needed a little tweaking for less than a month supply. Did you have a better fix for this? or did I have something wrong in your formula that caused the negatives? I also had another problem that wasn't really associated with the formula. We have some new items that do not have sales in the first few or more months of the year (because the item was introduced later in the year). The calculation counts these zero months as full months of supply. Is there any way to account for leading zero months and skip them? As a fix to get the report finished, I created a new formula in another cell to count zero months and then deducted them from the calculation. This worked somewhat well, but it also counts months where there was no sales in the middle of the year. (sales in Jan-May, no sales in Jun, sales in Jul-Dec for instance). These should be accounted for as having no sales. Mostly due to the seasonality of our business. Any thoughts you have would be appreciated. Again, I really love the formula, it saved me hours of work! Jennifer "Glenn" wrote: JLewis wrote: Awesome! It works. I see now what I was and wasn't doing. Thank you, Thank you! I really appreciate your help on this. This will help me in other reports as well. Glad I could help. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inventory Months Supply Question
I use a version of this formula to evaluate inventory against a forecast of
future sales, so empty months (or weeks, in my case) are counted. As Shane pointed out, when using sales history to predict future sales, missing data makes this much more difficult. You are probably going to have to use work-arounds suited to your business and the purpose of the data you are generating. For example, if you have partial information on a new item, you may need to find a comparable item that you have complete history for and use that for your projections. As for the adjustments you made, I would have to see the final formula and the data that led you to make the adjustment to see what was happening. However, if you are getting the correct answer, than the adjustment works and I wouldn't worry about finding a better fix. JLewis wrote: Glenn, After checking the data calculations, I noticed that partial months (less than one month) calculations do not work quite right. I sorta fixed this by including an "IF" function in the 2nd half of the formula that says if the calculation is less than zero to add a 1. This works. For instance, I had 1 item that the calculation was -.02. I had 81 in stock and 83 sales in the first month. It should have been .98, so adding 1 was the solution. If there was no inventory the calculation was -1. Adding one solved this problem as well. Again, this is a great formula, it just needed a little tweaking for less than a month supply. Did you have a better fix for this? or did I have something wrong in your formula that caused the negatives? I also had another problem that wasn't really associated with the formula. We have some new items that do not have sales in the first few or more months of the year (because the item was introduced later in the year). The calculation counts these zero months as full months of supply. Is there any way to account for leading zero months and skip them? As a fix to get the report finished, I created a new formula in another cell to count zero months and then deducted them from the calculation. This worked somewhat well, but it also counts months where there was no sales in the middle of the year. (sales in Jan-May, no sales in Jun, sales in Jul-Dec for instance). These should be accounted for as having no sales. Mostly due to the seasonality of our business. Any thoughts you have would be appreciated. Again, I really love the formula, it saved me hours of work! Jennifer "Glenn" wrote: JLewis wrote: Awesome! It works. I see now what I was and wasn't doing. Thank you, Thank you! I really appreciate your help on this. This will help me in other reports as well. Glad I could help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3D reference question in inventory Worksheet | Excel Worksheet Functions | |||
Inventory Spreadsheet Question | Excel Worksheet Functions | |||
I need a template for my supply inventory | Excel Worksheet Functions | |||
Inventory List Question | Excel Discussion (Misc queries) | |||
How can i get an inventory list that adds and subtracts inventory | Excel Discussion (Misc queries) |